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

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 "\dt public.*" -c "SELECT relname, relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname IN ('orgs','org_members','tickets');"

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

   relname   | relrowsecurity | relforcerowsecurity 
-------------+----------------+---------------------
 org_members | f              | f
 orgs        | f              | f
 tickets     | f              | f
(3 rows)

psql "$SUPABASE_DB_URL" -c "SELECT schemaname, tablename, policyname FROM pg_policies WHERE schemaname='public';" -c "\d public.org_members" -c "\d public.tickets"

 schemaname | tablename | policyname 
------------+-----------+------------
(0 rows)

           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

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

-- Helper functions (SECURITY DEFINER) to avoid RLS recursion on org_members
CREATE OR REPLACE FUNCTION public.is_org_member(p_org_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.org_members m
    WHERE m.org_id = p_org_id
      AND m.user_id = auth.uid()
  );
$$;

CREATE OR REPLACE FUNCTION public.is_org_admin(p_org_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
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'
  );
$$;

-- Lock down function execution to the API roles only
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, service_role;
GRANT EXECUTE ON FUNCTION public.is_org_admin(uuid) TO authenticated, anon, service_role;

-- ==== Enable RLS ====
ALTER TABLE public.tickets      ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.org_members  ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orgs         ENABLE ROW LEVEL SECURITY;

-- ==== tickets ====
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
CREATE POLICY tickets_insert ON public.tickets
  FOR INSERT TO authenticated
  WITH CHECK ( public.is_org_admin(org_id) );

-- only admins can UPDATE
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 ====
DROP POLICY IF EXISTS org_members_select ON public.org_members;

-- members of an org can see the member list of their own org only
CREATE POLICY org_members_select ON public.org_members
  FOR SELECT TO authenticated
  USING ( public.is_org_member(org_id) );

-- ==== orgs (remain readable) ====
DROP POLICY IF EXISTS orgs_select ON public.orgs;
CREATE POLICY orgs_select ON public.orgs
  FOR SELECT TO authenticated, anon
  USING ( true );

COMMIT;
SQL
psql "$SUPABASE_DB_URL" -f /tmp/rls.sql

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

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

psql "$SUPABASE_DB_URL" -c "SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_schema='public' AND table_name='tickets' AND grantee IN ('anon','authenticated') ORDER BY grantee, privilege_type;"

    grantee    | privilege_type 
---------------+----------------
 anon          | DELETE
 anon          | INSERT
 anon          | REFERENCES
 anon          | SELECT
 anon          | TRIGGER
 anon          | TRUNCATE
 anon          | UPDATE
 authenticated | DELETE
 authenticated | INSERT
 authenticated | REFERENCES
 authenticated | SELECT
 authenticated | TRIGGER
 authenticated | TRUNCATE
 authenticated | UPDATE
(14 rows)

cat > /tmp/login.sh <<'EOF'
login() {
  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'
}
EOF
source /tmp/login.sh
UMA=$(login uma-admin@cari.dev Passw0rd-uma-11)
VIC=$(login vic-member@cari.dev Passw0rd-vic-22)
WEN=$(login wen-outsider@cari.dev Passw0rd-wen-33)
echo "UMA=${UMA:0:20}... VIC=${VIC:0:20}... WEN=${WEN:0:20}..."
echo "$UMA" > /tmp/uma.jwt; echo "$VIC" > /tmp/vic.jwt; echo "$WEN" > /tmp/wen.jwt

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

UMA=$(cat /tmp/uma.jwt); VIC=$(cat /tmp/vic.jwt); WEN=$(cat /tmp/wen.jwt)
REST="$SUPABASE_URL/rest/v1"

req() { # method jwt path [body]
  local m=$1 jwt=$2 path=$3 body=$4
  if [ -n "$body" ]; then
    curl -s -X "$m" "$REST/$path" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $jwt" -H "Content-Type: application/json" -H "Prefer: return=representation" -d "$body"
  else
    curl -s -X "$m" "$REST/$path" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $jwt"
  fi
}

echo "=== SELECT tickets ==="
echo "Uma (Acme admin):    $(req GET "$UMA" 'tickets?select=id,org_id,title' | jq 'length? // .')"
echo "Vic (Acme member):   $(req GET "$VIC" 'tickets?select=id,org_id,title' | jq 'length? // .')"
echo "Wen (Globex member): $(req GET "$WEN" 'tickets?select=id,org_id,title' | jq 'length? // .')"
echo "Anon:                $(curl -s "$REST/tickets?select=id" -H "apikey: $SUPABASE_ANON_KEY" | jq 'length? // .')"

echo ""
echo "=== org_members visibility ==="
echo "Uma:  $(req GET "$UMA" 'org_members?select=org_id,role' | jq 'length? // .')"
echo "Vic:  $(req GET "$VIC" 'org_members?select=org_id,role' | jq 'length? // .')"
echo "Wen:  $(req GET "$WEN" 'org_members?select=org_id,role' | jq 'length? // .')"
echo "Anon: $(curl -s "$REST/org_members?select=org_id" -H "apikey: $SUPABASE_ANON_KEY" | jq 'length? // .')"

echo ""
echo "=== orgs readable ==="
echo "Uma:  $(req GET "$UMA" 'orgs?select=id,name' | jq 'length? // .')"
echo "Anon: $(curl -s "$REST/orgs?select=id" -H "apikey: $SUPABASE_ANON_KEY" | jq 'length? // .')"

=== SELECT tickets ===
Uma (Acme admin):    3
Vic (Acme member):   3
Wen (Globex member): 2
Anon:                0

=== org_members visibility ===
Uma:  2
Vic:  2
Wen:  1
Anon: 0

=== orgs readable ===
Uma:  2
Anon: 2

UMA=$(cat /tmp/uma.jwt); VIC=$(cat /tmp/vic.jwt); WEN=$(cat /tmp/wen.jwt)
REST="$SUPABASE_URL/rest/v1"
ACME=$(curl -s "$REST/orgs?select=id&name=eq.Acme" -H "apikey: $SUPABASE_ANON_KEY" | jq -r '.[0].id')
GLOBEX=$(curl -s "$REST/orgs?select=id&name=eq.Globex" -H "apikey: $SUPABASE_ANON_KEY" | jq -r '.[0].id')

req() { local m=$1 jwt=$2 path=$3 body=$4
  curl -s -o /dev/null -w "%{http_code}" -X "$m" "$REST/$path" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $jwt" -H "Content-Type: application/json" -H "Prefer: return=representation" ${body:+-d "$body"}; }

echo "=== INSERT (expect: admin ok=201, member fail) ==="
echo "Vic member insert into Acme: $(req POST "$VIC" tickets "{\"org_id\":\"$ACME\",\"title\":\"vic-hack\"}")  (expect 201-but-0-rows or 403/empty)"
# check whether vic actually inserted
VICCNT=$(curl -s "$REST/tickets?select=id&title=eq.vic-hack" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" | jq 'length')
echo "  -> rows actually inserted by Vic: $VICCNT"
echo "Wen member insert into Acme (not member): $(req POST "$WEN" tickets "{\"org_id\":\"$ACME\",\"title\":\"wen-hack\"}")"
echo "Uma admin insert into Acme: $(req POST "$UMA" tickets "{\"org_id\":\"$ACME\",\"title\":\"uma-test-ticket\"}")"

# capture id of uma's ticket for cleanup and update/delete tests
NEWID=$(curl -s "$REST/tickets?select=id&title=eq.uma-test-ticket" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" | jq -r '.[0].id')
echo "  -> uma inserted ticket id=$NEWID"

echo ""
echo "=== UPDATE ==="
echo "Vic member update uma ticket: $(req PATCH "$VIC" "tickets?id=eq.$NEWID" '{"title":"vic-edit"}')"
VUP=$(curl -s "$REST/tickets?select=title&id=eq.$NEWID" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" | jq -r '.[0].title')
echo "  -> title after Vic update attempt: $VUP (should be uma-test-ticket)"
echo "Uma admin update: $(req PATCH "$UMA" "tickets?id=eq.$NEWID" '{"status":"closed"}')"

echo ""
echo "=== DELETE ==="
echo "Wen delete Acme ticket: $(req DELETE "$WEN" "tickets?id=eq.$NEWID")"
echo "Vic member delete: $(req DELETE "$VIC" "tickets?id=eq.$NEWID")"
STILL=$(curl -s "$REST/tickets?select=id&id=eq.$NEWID" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" | jq 'length')
echo "  -> ticket exists after member delete attempts: $STILL (should be 1)"
echo "Uma admin delete (cleanup): $(req DELETE "$UMA" "tickets?id=eq.$NEWID")"

echo ""
echo "=== Final ticket counts (should be Acme=3, Globex=2) ==="
echo "Acme:   $(curl -s "$REST/tickets?select=id&org_id=eq.$ACME" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" | jq 'length')"
echo "Globex: $(curl -s "$REST/tickets?select=id&org_id=eq.$GLOBEX" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $WEN" | jq 'length')"

=== INSERT (expect: admin ok=201, member fail) ===
Vic member insert into Acme: 403  (expect 201-but-0-rows or 403/empty)
  -> rows actually inserted by Vic: 0
Wen member insert into Acme (not member): 403
Uma admin insert into Acme: 201
  -> uma inserted ticket id=8

=== UPDATE ===
Vic member update uma ticket: 200
  -> title after Vic update attempt: uma-test-ticket (should be uma-test-ticket)
Uma admin update: 200

=== DELETE ===
Wen delete Acme ticket: 200
Vic member delete: 200
  -> ticket exists after member delete attempts: 1 (should be 1)
Uma admin delete (cleanup): 200

=== Final ticket counts (should be Acme=3, Globex=2) ===
Acme:   3
Globex: 2

UMA=$(cat /tmp/uma.jwt); WEN=$(cat /tmp/wen.jwt)
REST="$SUPABASE_URL/rest/v1"
GLOBEX=$(curl -s "$REST/orgs?select=id&name=eq.Globex" -H "apikey: $SUPABASE_ANON_KEY" | jq -r '.[0].id')
ACME=$(curl -s "$REST/orgs?select=id&name=eq.Acme" -H "apikey: $SUPABASE_ANON_KEY" | jq -r '.[0].id')

req() { curl -s -o /dev/null -w "%{http_code}" -X "$1" "$REST/$3" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $2" -H "Content-Type: application/json" -H "Prefer: return=representation" ${4:+-d "$4"}; }

echo "Uma (Acme admin) inserting into GLOBEX (should be 403): $(req POST "$UMA" tickets "{\"org_id\":\"$GLOBEX\",\"title\":\"cross\"}")"
echo "Verify no cross ticket leaked: $(curl -s "$REST/tickets?select=id&title=eq.cross" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $WEN" | jq 'length')"

echo ""
echo "Wen sees only Globex members (org_ids): $(curl -s "$REST/org_members?select=org_id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $WEN" | jq -c '[.[].org_id]|unique')"
echo "Globex id is: $GLOBEX"
echo "Wen can read Acme tickets directly (should be 0): $(curl -s "$REST/tickets?select=id&org_id=eq.$ACME" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $WEN" | jq 'length')"

Uma (Acme admin) inserting into GLOBEX (should be 403): 403
Verify no cross ticket leaked: 0

Wen sees only Globex members (org_ids): ["2415e9bc-866b-4165-bcbe-8a3f595dcf8e"]
Globex id is: 2415e9bc-866b-4165-bcbe-8a3f595dcf8e
Wen can read Acme tickets directly (should be 0): 0