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

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>

psql "$SUPABASE_DB_URL" -c "\dt"

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

psql "$SUPABASE_DB_URL" -c "\\d public.orgs"

                   Table "public.orgs"
 Column | Type | Collation | Nullable |      Default      
--------+------+-----------+----------+-------------------
 id     | uuid |           | not null | gen_random_uuid()
 name   | text |           | not null | 
Indexes:
    "orgs_pkey" PRIMARY KEY, btree (id)
    "orgs_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "org_members" CONSTRAINT "org_members_org_id_fkey" FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE
    TABLE "tickets" CONSTRAINT "tickets_org_id_fkey" FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE

psql "$SUPABASE_DB_URL" -c "\\d public.org_members"

           Table "public.org_members"
 Column  | Type | Collation | Nullable | Default 
---------+------+-----------+----------+---------
 org_id  | uuid |           | not null | 
 user_id | uuid |           | not null | 
 role    | text |           | not null | 
Indexes:
    "org_members_pkey" PRIMARY KEY, btree (org_id, user_id)
Check constraints:
    "org_members_role_check" CHECK (role = ANY (ARRAY['admin'::text, 'member'::text]))
Foreign-key constraints:
    "org_members_org_id_fkey" FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE
    "org_members_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE

psql "$SUPABASE_DB_URL" -c "\\d public.tickets"

                          Table "public.tickets"
 Column |  Type  | Collation | Nullable |             Default              
--------+--------+-----------+----------+----------------------------------
 id     | bigint |           | not null | generated by default as identity
 org_id | uuid   |           | not null | 
 title  | text   |           | not null | 
 status | text   |           | not null | 'open'::text
Indexes:
    "tickets_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "tickets_org_id_fkey" FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE

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

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

psql "$SUPABASE_DB_URL" -c "
-- Enable RLS on tickets and org_members tables
ALTER TABLE public.tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.org_members ENABLE ROW LEVEL SECURITY;

-- Create policy for tickets: members can read their org's tickets
CREATE POLICY ticket_read_policy 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()
    )
);

-- Create policy for tickets: only admins can insert/update/delete
CREATE POLICY ticket_write_admin_only ON public.tickets FOR ALL
TO authenticated
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'
    )
)
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'
    )
);

-- Create policy for org_members: only members of the same org can view
CREATE POLICY org_member_sees_same_org 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()
    )
);

-- Restrict write access to org_members to prevent unauthorized modification
CREATE POLICY org_member_management_policy ON public.org_members FOR ALL
TO service_role
USING (true)
WITH CHECK (true);
"

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

psql "$SUPABASE_DB_URL" -c "
SELECT 
    p.polname AS policy_name,
    t.relname AS table_name,
    n.nspname AS schema_name,
    CASE polcmd 
        WHEN 'r' THEN 'SELECT'
        WHEN 'a' THEN 'INSERT' 
        WHEN 'w' THEN 'UPDATE'
        WHEN 'd' THEN 'DELETE'
        WHEN '*' THEN 'ALL'
    END AS command_type
FROM pg_policy p
JOIN pg_class t ON p.polrelid = t.oid
JOIN pg_namespace n ON t.relnamespace = n.oid
WHERE n.nspname = 'public' AND t.relname IN ('tickets', 'org_members')
ORDER BY t.relname, p.polname;"

         policy_name          | table_name  | schema_name | command_type 
------------------------------+-------------+-------------+--------------
 org_member_management_policy | org_members | public      | ALL
 org_member_sees_same_org     | org_members | public      | SELECT
 ticket_read_policy           | tickets     | public      | SELECT
 ticket_write_admin_only      | tickets     | public      | ALL
(4 rows)

psql "$SUPABASE_DB_URL" -c "
SELECT o.id, o.name FROM public.orgs o;
"

                  id                  |  name  
--------------------------------------+--------
 b174578f-b524-4f96-b75f-7c4ad59dd2e6 | Acme
 0a2edbaf-bc52-4ecf-8a7f-f151f2781817 | Globex
(2 rows)

psql "$SUPABASE_DB_URL" -c "
SELECT u.email, om.org_id, o.name, om.role 
FROM auth.users u 
JOIN public.org_members om ON u.id = om.user_id 
JOIN public.orgs o ON o.id = om.org_id 
WHERE u.email IN ('uma-admin@cari.dev', 'vic-member@cari.dev', 'wen-outsider@cari.dev')
ORDER BY u.email;
"

         email         |                org_id                |  name  |  role  
-----------------------+--------------------------------------+--------+--------
 uma-admin@cari.dev    | b174578f-b524-4f96-b75f-7c4ad59dd2e6 | Acme   | admin
 vic-member@cari.dev   | b174578f-b524-4f96-b75f-7c4ad59dd2e6 | Acme   | member
 wen-outsider@cari.dev | 0a2edbaf-bc52-4ecf-8a7f-f151f2781817 | Globex | member
(3 rows)

psql "$SUPABASE_DB_URL" -c "
SELECT o.name as org_name, COUNT(t.*) as ticket_count 
FROM public.tickets t 
JOIN public.orgs o ON t.org_id = o.id 
GROUP BY o.name;
"

 org_name | ticket_count 
----------+--------------
 Acme     |            3
 Globex   |            2
(2 rows)