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

env | grep SUPABASE

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>

sudo apt-get update && sudo apt-get install -y postgresql-client

bash: line 1: sudo: command not found

which psql || echo "psql not found"

/usr/bin/psql

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

-- Drop existing policies if any (in case this is run again)
DROP POLICY IF EXISTS tickets_read_policy ON public.tickets;
DROP POLICY IF EXISTS tickets_write_admin_policy ON public.tickets;
DROP POLICY IF EXISTS tickets_insert_admin_policy ON public.tickets;
DROP POLICY IF EXISTS tickets_delete_admin_policy ON public.tickets;
DROP POLICY IF EXISTS org_members_select_policy ON public.org_members;

-- Policy for viewing tickets (both admin and member of the org can read tickets of their org)
CREATE POLICY tickets_read_policy ON public.tickets
FOR SELECT 
TO authenticated
USING (
    org_id IN (
        SELECT org_id 
        FROM public.org_members 
        WHERE user_id = auth.uid()
    )
);

-- Policy for inserting tickets (only admins can insert tickets for their org)
CREATE POLICY tickets_insert_admin_policy ON public.tickets
FOR INSERT
TO authenticated
WITH CHECK (
    org_id IN (
        SELECT org_id 
        FROM public.org_members 
        WHERE user_id = auth.uid() AND role = 'admin'
    )
);

-- Policy for updating tickets (only admin members can update tickets in their org)
CREATE POLICY tickets_write_admin_policy ON public.tickets
FOR UPDATE
TO authenticated
USING (
    org_id IN (
        SELECT org_id 
        FROM public.org_members 
        WHERE user_id = auth.uid() AND role = 'admin'
    )
);

-- Policy for deleting tickets (only admin members can delete tickets in their org)
CREATE POLICY tickets_delete_admin_policy ON public.tickets
FOR DELETE
TO authenticated
USING (
    org_id IN (
        SELECT org_id 
        FROM public.org_members 
        WHERE user_id = auth.uid() AND role = 'admin'
    )
);

-- Policy for viewing org_members (users can only see members of their own organizations)
CREATE POLICY org_members_select_policy ON public.org_members
FOR SELECT
TO authenticated
USING (
    org_id IN (
        SELECT om.org_id
        FROM public.org_members om
        WHERE om.user_id = auth.uid()
    )
);

-- Since we've enabled RLS, make sure anonymous users can't see tickets
-- Anonymous users will be blocked by default since TO authenticated is used
-- For org_members, ensure anon cannot select
CREATE POLICY org_members_anon_no_select ON public.org_members
FOR SELECT
TO anon
USING (false);

CREATE POLICY tickets_anon_no_access ON public.tickets
FOR ALL
TO anon
USING (false);

-- Ensure that users can only manipulate tickets in orgs they belong to as admin
-- We also need to make sure UPDATES that would change org_id follow the rules too
ALTER TABLE public.tickets
ADD CONSTRAINT prevent_org_change_if_not_admin
CHECK (
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM public.org_members 
            WHERE user_id = auth.uid() AND role = 'admin' AND org_id = org_id
        ) THEN true
        WHEN EXISTS (
            SELECT 1 FROM public.org_members 
            WHERE user_id = auth.uid() AND org_id = org_id
        ) THEN NOT(updating('org_id')) -- block changing org_id for non-admins
        ELSE false
    END
);
EOF

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

-- Drop existing policies if needed
DROP POLICY IF EXISTS tickets_read_policy ON public.tickets;
DROP POLICY IF EXISTS tickets_write_policy ON public.tickets;
DROP POLICY IF EXISTS tickets_insert_policy ON public.tickets;
DROP POLICY IF EXISTS tickets_delete_policy ON public.tickets;
DROP POLICY IF EXISTS org_members_select_policy ON public.org_members;

-- Policy for reading tickets (users can see tickets belonging to orgs they are members of)
CREATE POLICY tickets_read_policy ON public.tickets
FOR SELECT 
TO authenticated
USING (
    org_id IN (
        SELECT org_id 
        FROM public.org_members 
        WHERE user_id = auth.uid())
);

-- Policy for inserting tickets (only users who are admins of the org can insert)
CREATE POLICY tickets_insert_policy ON public.tickets
FOR INSERT
TO authenticated
WITH CHECK (
    org_id IN (
        SELECT org_id 
        FROM public.org_members 
        WHERE user_id = auth.uid() AND role = 'admin'
    )
);

-- Policy for updating tickets (only admins of the org can update)
CREATE POLICY tickets_write_policy ON public.tickets
FOR UPDATE
TO authenticated
USING (
    org_id IN (
        SELECT org_id 
        FROM public.org_members 
        WHERE user_id = auth.uid() AND role = 'admin'
    )
);

-- Policy for deleting tickets (only admins of the org can delete)
CREATE POLICY tickets_delete_policy ON public.tickets
FOR DELETE
TO authenticated
USING (
    org_id IN (
        SELECT org_id 
        FROM public.org_members 
        WHERE user_id = auth.uid() AND role = 'admin'
    )
);

-- Policy for selecting org_members (users can only see members of their own orgs)
CREATE POLICY org_members_select_policy ON public.org_members
FOR SELECT
TO authenticated
USING (
    org_id IN (
        SELECT org_id 
        FROM public.org_members om2
        WHERE om2.user_id = auth.uid()
    )
);

-- Block anonymous access to tickets entirely
CREATE POLICY tickets_anon_no_access ON public.tickets
FOR ALL
TO anon
USING (false);

-- Block anonymous access to org_members entirely
CREATE POLICY org_members_anon_no_access ON public.org_members
FOR ALL
TO anon
USING (false);

-- Don't add constraints directly on the tickets table in the way I incorrectly did above
-- The CHECK constraint doesn't properly work with auth context like that
EOF

psql "$SUPABASE_DB_URL" -c "\i rls_setup_fixed.sql"

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

psql:rls_setup_fixed.sql:6: NOTICE:  policy "tickets_read_policy" for relation "public.tickets" does not exist, skipping
psql:rls_setup_fixed.sql:7: NOTICE:  policy "tickets_write_policy" for relation "public.tickets" does not exist, skipping
psql:rls_setup_fixed.sql:8: NOTICE:  policy "tickets_insert_policy" for relation "public.tickets" does not exist, skipping
psql:rls_setup_fixed.sql:9: NOTICE:  policy "tickets_delete_policy" for relation "public.tickets" does not exist, skipping
psql:rls_setup_fixed.sql:10: NOTICE:  policy "org_members_select_policy" for relation "public.org_members" does not exist, skipping

psql "$SUPABASE_DB_URL" -c "SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual FROM pg_policies WHERE tablename IN ('tickets', 'org_members') ORDER BY tablename, cmd;"

 schemaname |  tablename  |         policyname         | permissive |      roles      |  cmd   |                                         qual                                          
------------+-------------+----------------------------+------------+-----------------+--------+---------------------------------------------------------------------------------------
 public     | org_members | org_members_anon_no_access | PERMISSIVE | {anon}          | ALL    | false
 public     | org_members | org_members_select_policy  | PERMISSIVE | {authenticated} | SELECT | (org_id IN ( SELECT om2.org_id                                                       +
            |             |                            |            |                 |        |    FROM org_members om2                                                              +
            |             |                            |            |                 |        |   WHERE (om2.user_id = auth.uid())))
 public     | tickets     | tickets_anon_no_access     | PERMISSIVE | {anon}          | ALL    | false
 public     | tickets     | tickets_delete_policy      | PERMISSIVE | {authenticated} | DELETE | (org_id IN ( SELECT org_members.org_id                                               +
            |             |                            |            |                 |        |    FROM org_members                                                                  +
            |             |                            |            |                 |        |   WHERE ((org_members.user_id = auth.uid()) AND (org_members.role = 'admin'::text))))
 public     | tickets     | tickets_insert_policy      | PERMISSIVE | {authenticated} | INSERT | 
 public     | tickets     | tickets_read_policy        | PERMISSIVE | {authenticated} | SELECT | (org_id IN ( SELECT org_members.org_id                                               +
            |             |                            |            |                 |        |    FROM org_members                                                                  +
            |             |                            |            |                 |        |   WHERE (org_members.user_id = auth.uid())))
 public     | tickets     | tickets_write_policy       | PERMISSIVE | {authenticated} | UPDATE | (org_id IN ( SELECT org_members.org_id                                               +
            |             |                            |            |                 |        |    FROM org_members                                                                  +
            |             |                            |            |                 |        |   WHERE ((org_members.user_id = auth.uid()) AND (org_members.role = 'admin'::text))))
(7 rows)

psql "$SUPABASE_DB_URL" -c "SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE tablename IN ('tickets', 'org_members');"

 schemaname |  tablename  | rowsecurity 
------------+-------------+-------------
 public     | org_members | t
 public     | tickets     | t
(2 rows)