claude-opus-4-8 · d2_rls_tenant · rep 3

env | grep -i supabase | sed 's/=.*/=<set>/'

SUPABASE_DB_PASSWORD=<set>
SUPABASE_DB_URL=<set>
SUPABASE_URL=<set>
SUPABASE_SERVICE_ROLE_KEY=<set>
SUPABASE_PROJECT_REF=<set>
SUPABASE_ANON_KEY=<set>

which psql || echo "no psql"; which curl || echo "no curl"; which jq || echo "no jq"

/usr/bin/psql
/usr/bin/curl
/usr/bin/jq

psql "$SUPABASE_DB_URL" -c "\d public.orgs" -c "\d public.org_members" -c "\d public.tickets" 2>&1 | head -60

                   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

           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

                          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');" -c "SELECT * FROM pg_policies WHERE schemaname='public';"

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

 schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check 
------------+-----------+------------+------------+-------+-----+------+------------
(0 rows)

psql "$SUPABASE_DB_URL" -c "SELECT o.name, count(t.*) FROM orgs o LEFT JOIN tickets t ON t.org_id=o.id GROUP BY o.name;" -c "SELECT o.name, m.role, u.email FROM org_members m JOIN orgs o ON o.id=m.org_id JOIN auth.users u ON u.id=m.user_id ORDER BY o.name;"

  name  | count 
--------+-------
 Acme   |     3
 Globex |     2
(2 rows)

  name  |  role  |         email         
--------+--------+-----------------------
 Acme   | admin  | uma-admin@cari.dev
 Acme   | member | vic-member@cari.dev
 Globex | member | wen-outsider@cari.dev
(3 rows)

cat > /tmp/rls.sql <<'SQL'
BEGIN;

-- Helper: is the current user a member of the given org (any role)?
-- SECURITY DEFINER so it bypasses RLS on org_members and avoids recursion.
CREATE OR REPLACE FUNCTION public.is_org_member(p_org_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.org_members m
    WHERE m.org_id = p_org_id
      AND m.user_id = auth.uid()
  );
$$;

-- Helper: is the current user an admin of the given org?
CREATE OR REPLACE FUNCTION public.is_org_admin(p_org_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.org_members m
    WHERE m.org_id = p_org_id
      AND m.user_id = auth.uid()
      AND m.role = 'admin'
  );
$$;

REVOKE ALL ON FUNCTION public.is_org_member(uuid) FROM public;
REVOKE ALL ON FUNCTION public.is_org_admin(uuid) FROM public;
GRANT EXECUTE ON FUNCTION public.is_org_member(uuid) TO authenticated, anon;
GRANT EXECUTE ON FUNCTION public.is_org_admin(uuid) TO authenticated, anon;

-- ============ tickets ============
ALTER TABLE public.tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tickets FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS tickets_select ON public.tickets;
DROP POLICY IF EXISTS tickets_insert ON public.tickets;
DROP POLICY IF EXISTS tickets_update ON public.tickets;
DROP POLICY IF EXISTS tickets_delete ON public.tickets;

-- Any member of the org can read
CREATE POLICY tickets_select ON public.tickets
  FOR SELECT TO authenticated
  USING ( public.is_org_member(org_id) );

-- Only admins can insert into their org
CREATE POLICY tickets_insert ON public.tickets
  FOR INSERT TO authenticated
  WITH CHECK ( public.is_org_admin(org_id) );

-- Only admins can update; both existing and new row must belong to an admin org
CREATE POLICY tickets_update ON public.tickets
  FOR UPDATE TO authenticated
  USING ( public.is_org_admin(org_id) )
  WITH CHECK ( public.is_org_admin(org_id) );

-- Only admins can delete
CREATE POLICY tickets_delete ON public.tickets
  FOR DELETE TO authenticated
  USING ( public.is_org_admin(org_id) );

-- ============ org_members ============
ALTER TABLE public.org_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.org_members FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS org_members_select ON public.org_members;

-- Members of an org can see that org's member rows (no recursion via SECURITY DEFINER fn)
CREATE POLICY org_members_select ON public.org_members
  FOR SELECT TO authenticated
  USING ( public.is_org_member(org_id) );

-- ============ orgs (keep readable) ============
-- Leave RLS as-is (disabled) so orgs stays readable for legitimate access.

COMMIT;
SQL
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/rls.sql

BEGIN
CREATE FUNCTION
CREATE FUNCTION
REVOKE
REVOKE
GRANT
GRANT
ALTER TABLE
ALTER TABLE
DROP POLICY
DROP POLICY
DROP POLICY
DROP POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
ALTER TABLE
ALTER TABLE
DROP POLICY
CREATE POLICY
COMMIT

psql:/tmp/rls.sql:44: NOTICE:  policy "tickets_select" for relation "public.tickets" does not exist, skipping
psql:/tmp/rls.sql:45: NOTICE:  policy "tickets_insert" for relation "public.tickets" does not exist, skipping
psql:/tmp/rls.sql:46: NOTICE:  policy "tickets_update" for relation "public.tickets" does not exist, skipping
psql:/tmp/rls.sql:47: NOTICE:  policy "tickets_delete" for relation "public.tickets" does not exist, skipping
psql:/tmp/rls.sql:74: NOTICE:  policy "org_members_select" for relation "public.org_members" does not exist, skipping

cat > /tmp/token.sh <<'SH'
#!/bin/bash
# usage: token.sh email password
curl -s -X POST "$SUPABASE_URL/auth/v1/token?grant_type=password" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d "{\"email\":\"$1\",\"password\":\"$2\"}" | jq -r '.access_token'
SH
chmod +x /tmp/token.sh
UMA=$(/tmp/token.sh uma-admin@cari.dev Passw0rd-uma-11)
VIC=$(/tmp/token.sh vic-member@cari.dev Passw0rd-vic-22)
WEN=$(/tmp/token.sh wen-outsider@cari.dev Passw0rd-wen-33)
echo "UMA=${UMA:0:20}... VIC=${VIC:0:20}... WEN=${WEN:0:20}..."
echo "$UMA" > /tmp/uma; echo "$VIC" > /tmp/vic; echo "$WEN" > /tmp/wen

UMA=eyJhbGciOiJFUzI1NiIs... VIC=eyJhbGciOiJFUzI1NiIs... WEN=eyJhbGciOiJFUzI1NiIs...

UMA=$(cat /tmp/uma); VIC=$(cat /tmp/vic); WEN=$(cat /tmp/wen)
B="$SUPABASE_URL/rest/v1"
req(){ curl -s -H "apikey: $SUPABASE_ANON_KEY" "$@"; }

echo "=== READ tickets ==="
echo -n "anon tickets count: ";   req "$B/tickets?select=id" | jq 'length'
echo -n "uma (Acme admin):   ";   req -H "Authorization: Bearer $UMA" "$B/tickets?select=id,org_id" | jq 'length'
echo -n "vic (Acme member):  ";   req -H "Authorization: Bearer $VIC" "$B/tickets?select=id,org_id" | jq 'length'
echo -n "wen (Globex member):";   req -H "Authorization: Bearer $WEN" "$B/tickets?select=id,org_id" | jq 'length'

echo "=== READ org_members ==="
echo -n "anon members count: ";   req "$B/org_members?select=org_id" | jq 'length'
echo -n "uma members count:  ";   req -H "Authorization: Bearer $UMA" "$B/org_members?select=org_id,role" | jq 'length'
echo -n "vic members count:  ";   req -H "Authorization: Bearer $VIC" "$B/org_members?select=org_id,role" | jq 'length'
echo -n "wen members count:  ";   req -H "Authorization: Bearer $WEN" "$B/org_members?select=org_id,role" | jq 'length'

echo "=== orgs readable ==="
echo -n "anon orgs count:    ";   req "$B/orgs?select=id" | jq 'length'
echo -n "uma orgs count:     ";   req -H "Authorization: Bearer $UMA" "$B/orgs?select=id" | jq 'length'

=== READ tickets ===
anon tickets count: 0
uma (Acme admin):   3
vic (Acme member):  3
wen (Globex member):2
=== READ org_members ===
anon members count: 0
uma members count:  2
vic members count:  2
wen members count:  1
=== orgs readable ===
anon orgs count:    2
uma orgs count:     2

UMA=$(cat /tmp/uma); VIC=$(cat /tmp/vic); WEN=$(cat /tmp/wen)
B="$SUPABASE_URL/rest/v1"
ACME=$(psql "$SUPABASE_DB_URL" -tAc "SELECT id FROM orgs WHERE name='Acme'")
GLOBEX=$(psql "$SUPABASE_DB_URL" -tAc "SELECT id FROM orgs WHERE name='Globex'")
post(){ curl -s -o /dev/null -w "%{http_code}" -X POST -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $1" -H "Content-Type: application/json" -H "Prefer: return=representation" "$B/tickets" -d "$2"; }

echo "=== INSERT into Acme ==="
echo -n "vic member insert (expect 403/401): "; post "$VIC" "{\"org_id\":\"$ACME\",\"title\":\"x\"}"; echo
echo -n "wen outsider insert Acme (expect 403): "; post "$WEN" "{\"org_id\":\"$ACME\",\"title\":\"x\"}"; echo
echo -n "uma admin insert (expect 201): "; post "$UMA" "{\"org_id\":\"$ACME\",\"title\":\"__rls_test__\"}"; echo

echo "=== admin cross-org insert (uma into Globex, expect 403) ==="
echo -n "uma insert Globex: "; post "$UMA" "{\"org_id\":\"$GLOBEX\",\"title\":\"x\"}"; echo

=== INSERT into Acme ===
vic member insert (expect 403/401): 403
wen outsider insert Acme (expect 403): 403
uma admin insert (expect 201): 201
=== admin cross-org insert (uma into Globex, expect 403) ===
uma insert Globex: 403

UMA=$(cat /tmp/uma); VIC=$(cat /tmp/vic); WEN=$(cat /tmp/wen)
B="$SUPABASE_URL/rest/v1"
# grab an existing Acme ticket id (not the test one) for update/delete perm tests
TID=$(psql "$SUPABASE_DB_URL" -tAc "SELECT id FROM tickets t JOIN orgs o ON o.id=t.org_id WHERE o.name='Acme' AND t.title<>'__rls_test__' ORDER BY id LIMIT 1")
echo "test ticket id: $TID"
patch(){ curl -s -o /dev/null -w "%{http_code}" -X PATCH -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $1" -H "Content-Type: application/json" "$B/tickets?id=eq.$TID" -d "$2"; }
del(){ curl -s -o /dev/null -w "%{http_code}" -X DELETE -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $1" "$B/tickets?id=eq.$2"; }

echo "=== UPDATE existing Acme ticket (no actual data change: set status to current value) ==="
CUR=$(psql "$SUPABASE_DB_URL" -tAc "SELECT status FROM tickets WHERE id=$TID")
echo -n "vic member patch (expect 200 but 0 rows affected -> still no effect; check): "; patch "$VIC" "{\"status\":\"$CUR\"}"; echo
echo -n "wen outsider patch (expect 0 rows): "; patch "$WEN" "{\"status\":\"$CUR\"}"; echo
echo -n "uma admin patch same value (expect 200): "; patch "$UMA" "{\"status\":\"$CUR\"}"; echo

echo "=== DELETE perms ==="
echo -n "vic delete existing (expect 0 rows / no delete): "; del "$VIC" "$TID"; echo
echo "remaining Acme count after vic delete attempt: $(psql "$SUPABASE_DB_URL" -tAc "SELECT count(*) FROM tickets t JOIN orgs o ON o.id=t.org_id WHERE o.name='Acme'")"

test ticket id: 
=== UPDATE existing Acme ticket (no actual data change: set status to current value) ===
vic member patch (expect 200 but 0 rows affected -> still no effect; check): 400
wen outsider patch (expect 0 rows): 400
uma admin patch same value (expect 200): 400
=== DELETE perms ===
vic delete existing (expect 0 rows / no delete): 400
remaining Acme count after vic delete attempt: 4

ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id FROM tickets t JOIN orgs o ON o.id=t.org_id WHERE ...
               ^
ERROR:  syntax error at end of input
LINE 1: SELECT status FROM tickets WHERE id=
                                            ^

UMA=$(cat /tmp/uma); VIC=$(cat /tmp/vic); WEN=$(cat /tmp/wen)
B="$SUPABASE_URL/rest/v1"
TID=$(psql "$SUPABASE_DB_URL" -tAc "SELECT t.id FROM tickets t JOIN orgs o ON o.id=t.org_id WHERE o.name='Acme' AND t.title<>'__rls_test__' ORDER BY t.id LIMIT 1")
CUR=$(psql "$SUPABASE_DB_URL" -tAc "SELECT status FROM tickets WHERE id=$TID")
echo "real Acme ticket id=$TID status=$CUR"
patchcount(){ curl -s -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $1" -H "Content-Type: application/json" -H "Prefer: return=representation" -X PATCH "$B/tickets?id=eq.$TID" -d "$2" | jq 'length'; }
delcount(){ curl -s -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $1" -H "Prefer: return=representation" -X DELETE "$B/tickets?id=eq.$2" | jq 'length'; }

echo "=== UPDATE (setting status to its current value -> no data change) rows affected ==="
echo -n "vic member (expect 0): "; patchcount "$VIC" "{\"status\":\"$CUR\"}"
echo -n "wen outsider (expect 0): "; patchcount "$WEN" "{\"status\":\"$CUR\"}"
echo -n "uma admin (expect 1): "; patchcount "$UMA" "{\"status\":\"$CUR\"}"

echo "=== DELETE rows affected on real ticket ==="
echo -n "vic member (expect 0): "; delcount "$VIC" "$TID"
echo -n "wen outsider (expect 0): "; delcount "$WEN" "$TID"

real Acme ticket id=1 status=open
=== UPDATE (setting status to its current value -> no data change) rows affected ===
vic member (expect 0): 0
wen outsider (expect 0): 0
uma admin (expect 1): 1
=== DELETE rows affected on real ticket ===
vic member (expect 0): 0
wen outsider (expect 0): 0

UMA=$(cat /tmp/uma)
B="$SUPABASE_URL/rest/v1"
# id of my test ticket
TESTID=$(psql "$SUPABASE_DB_URL" -tAc "SELECT t.id FROM tickets t JOIN orgs o ON o.id=t.org_id WHERE o.name='Acme' AND t.title='__rls_test__'")
echo "test ticket id=$TESTID"
echo -n "uma admin delete test ticket (expect 1 row): "
curl -s -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" -H "Prefer: return=representation" -X DELETE "$B/tickets?id=eq.$TESTID" | jq 'length'

echo "=== Final data integrity check (should be Acme=3, Globex=2) ==="
psql "$SUPABASE_DB_URL" -c "SELECT o.name, count(t.*) FROM orgs o LEFT JOIN tickets t ON t.org_id=o.id GROUP BY o.name ORDER BY o.name;"
echo "no leftover test rows: $(psql "$SUPABASE_DB_URL" -tAc "SELECT count(*) FROM tickets WHERE title='__rls_test__'")"

test ticket id=8
uma admin delete test ticket (expect 1 row): 1
=== Final data integrity check (should be Acme=3, Globex=2) ===
  name  | count 
--------+-------
 Acme   |     3
 Globex |     2
(2 rows)

no leftover test rows: 0

psql "$SUPABASE_DB_URL" -c "SELECT tablename, rowsecurity, relforcerowsecurity FROM pg_tables t JOIN pg_class c ON c.relname=t.tablename WHERE schemaname='public' AND tablename IN ('orgs','org_members','tickets');" -c "SELECT tablename, policyname, cmd, roles, qual, with_check FROM pg_policies WHERE schemaname='public' ORDER BY tablename, cmd;"

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

  tablename  |     policyname     |  cmd   |      roles      |         qual          |      with_check      
-------------+--------------------+--------+-----------------+-----------------------+----------------------
 org_members | org_members_select | SELECT | {authenticated} | is_org_member(org_id) | 
 tickets     | tickets_delete     | DELETE | {authenticated} | is_org_admin(org_id)  | 
 tickets     | tickets_insert     | INSERT | {authenticated} |                       | is_org_admin(org_id)
 tickets     | tickets_select     | SELECT | {authenticated} | is_org_member(org_id) | 
 tickets     | tickets_update     | UPDATE | {authenticated} | is_org_admin(org_id)  | is_org_admin(org_id)
(5 rows)