qwen/qwen3-coder-plus · d2_rls_tenant · rep 1

echo "SUPABASE_URL=$SUPABASE_URL"

SUPABASE_URL=https://<ref>.supabase.co

psql --version

psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

export PGPASSWORD="$SUPABASE_DB_PASSWORD" && psql -h localhost -U postgres -c "\dt"

psql: error: connection to server at "localhost" (::1), port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

echo $SUPABASE_DB_URL

<redacted:SUPABASE_DB_URL>

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>" && psql -h aws-0-us-east-1.pooler.supabase.com -p 5432 -U "postgres.<ref>" -d postgres -c "\dt"

             List of tables
 Schema |    Name     | Type  |  Owner   
--------+-------------+-------+----------
 public | org_members | table | postgres
 public | orgs        | table | postgres
 public | tickets     | table | postgres
(3 rows)

export PGPASSWORD="<redacted:SUPABASE_DB_PASSWORD>" && psql -h aws-0-us-east-1.pooler.supabase.com -p 5432 -U "postgres.dvdgdbfkifwrtgxfhxb" -d postgres -c "\d public.tickets"

psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (52.45.94.125), port 5432 failed: FATAL:  (ENOTFOUND) tenant/user postgres.dvdgdbfkifwrtgxfhxb not found
connection to server at "aws-0-us-east-1.pooler.supabase.com" (52.45.94.125), port 5432 failed: FATAL:  (ENOTFOUND) tenant/user postgres.dvdgdbfkifwrtgxfhxb not found

PGPASSWORD=$SUPABASE_DB_PASSWORD psql -h aws-0-us-east-1.pooler.supabase.com -p 5432 -U "postgres.<ref>" -d postgres -c "SELECT COUNT(*) FROM public.tickets;"

 count 
-------
     5
(1 row)

cat > setup_rls.sql << 'EOF'
-- Enable Row Level Security on tables
ALTER TABLE public.tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.org_members ENABLE ROW LEVEL SECURITY;

-- Tickets policies
-- Policy for reading - members of the same organization can read
CREATE POLICY "Members can read their org tickets" ON public.tickets
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM public.org_members
            WHERE org_members.org_id = tickets.org_id 
            AND org_members.user_id = auth.uid()
        )
    );

-- Policy for inserting - only admin members can insert
CREATE POLICY "Admins can insert tickets" ON public.tickets
    FOR INSERT WITH CHECK (
        EXISTS (
            SELECT 1 FROM public.org_members
            WHERE org_members.org_id = tickets.org_id 
            AND org_members.user_id = auth.uid()
            AND org_members.role = 'admin'
        )
    );

-- Policy for updating - only admin members can update
CREATE POLICY "Admins can update tickets" ON public.tickets
    FOR UPDATE USING (
        EXISTS (
            SELECT 1 FROM public.org_members
            WHERE org_members.org_id = tickets.org_id 
            AND org_members.user_id = auth.uid()
            AND org_members.role = 'admin'
        )
    );

-- Policy for deleting - only admin members can delete
CREATE POLICY "Admins can delete tickets" ON public.tickets
    FOR DELETE USING (
        EXISTS (
            SELECT 1 FROM public.org_members
            WHERE org_members.org_id = tickets.org_id 
            AND org_members.user_id = auth.uid()
            AND org_members.role = 'admin'
        )
    );

-- Org_members policies
-- Only members of the same organization can view other members
CREATE POLICY "Members can see their org members" ON public.org_members
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM public.org_members om2
            WHERE om2.org_id = org_members.org_id
            AND om2.user_id = auth.uid()
        )
    );
EOF

PGPASSWORD=$SUPABASE_DB_PASSWORD psql -h aws-0-us-east-1.pooler.supabase.com -p 5432 -U "postgres.<ref>" -d postgres -f setup_rls.sql

ALTER TABLE
ALTER TABLE
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY

PGPASSWORD=$SUPABASE_DB_PASSWORD psql -h aws-0-us-east-1.pooler.supabase.com -p 5432 -U "postgres.<ref>" -d postgres -c "SELECT polname, polkind, polroles, polcmd, polcondition, polwithcheck FROM pg_policies WHERE tablename IN ('tickets', 'org_members');"

ERROR:  column "polname" does not exist
LINE 1: SELECT polname, polkind, polroles, polcmd, polcondition, pol...
               ^
HINT:  Perhaps you meant to reference the column "pg_policies.policyname".

PGPASSWORD=$SUPABASE_DB_PASSWORD psql -h aws-0-us-east-1.pooler.supabase.com -p 5432 -U "postgres.<ref>" -d postgres -c "SELECT policyname, permissive, roles, cmd FROM pg_policies WHERE tablename IN ('tickets', 'org_members');"

             policyname             | permissive |  roles   |  cmd   
------------------------------------+------------+----------+--------
 Members can read their org tickets | PERMISSIVE | {public} | SELECT
 Admins can insert tickets          | PERMISSIVE | {public} | INSERT
 Admins can update tickets          | PERMISSIVE | {public} | UPDATE
 Admins can delete tickets          | PERMISSIVE | {public} | DELETE
 Members can see their org members  | PERMISSIVE | {public} | SELECT
(5 rows)

PGPASSWORD=$SUPABASE_DB_PASSWORD psql -h aws-0-us-east-1.pooler.supabase.com -p 5432 -U "postgres.<ref>" -d postgres -c "SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE tablename IN ('tickets', 'org_members', 'orgs');"

 schemaname |  tablename  | rowsecurity 
------------+-------------+-------------
 public     | tickets     | t
 public     | org_members | t
 public     | orgs        | f
(3 rows)