openai/gpt-5.6-sol · d2_rls_tenant · rep 3

set -euo pipefail
command -v psql
printf 'URL=%s\nREF=%s\nDBURL_set=%s\n' "$SUPABASE_URL" "$SUPABASE_PROJECT_REF" "${SUPABASE_DB_URL:+yes}"
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "select current_user, version();"

/usr/bin/psql
URL=https://<ref>.supabase.co
REF=<ref>
DBURL_set=yes
 current_user |                                   version                                    
--------------+------------------------------------------------------------------------------
 postgres     | PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
(1 row)

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
\pset pager off
SELECT c.oid::regclass AS table_name, a.attname, pg_catalog.format_type(a.atttypid,a.atttypmod) AS type, a.attnotnull
FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace JOIN pg_attribute a ON a.attrelid=c.oid
WHERE n.nspname='public' AND c.relname IN ('orgs','org_members','tickets') AND a.attnum>0 AND NOT a.attisdropped
ORDER BY c.relname,a.attnum;
SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE schemaname='public' AND tablename IN ('orgs','org_members','tickets');
SELECT schemaname,tablename,policyname,permissive,roles,cmd,qual,with_check FROM pg_policies WHERE schemaname='public' AND tablename IN ('orgs','org_members','tickets') ORDER BY tablename,policyname;
SELECT table_name,grantee,string_agg(privilege_type,', ' ORDER BY privilege_type) privileges FROM information_schema.role_table_grants WHERE table_schema='public' AND table_name IN ('orgs','org_members','tickets') AND grantee IN ('anon','authenticated') GROUP BY 1,2 ORDER BY 1,2;
SQL

Pager usage is off.
 table_name  | attname |  type  | attnotnull 
-------------+---------+--------+------------
 org_members | org_id  | uuid   | t
 org_members | user_id | uuid   | t
 org_members | role    | text   | t
 orgs        | id      | uuid   | t
 orgs        | name    | text   | t
 tickets     | id      | bigint | t
 tickets     | org_id  | uuid   | t
 tickets     | title   | text   | t
 tickets     | status  | text   | t
(9 rows)

 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)

 table_name  |    grantee    |                          privileges                           
-------------+---------------+---------------------------------------------------------------
 org_members | anon          | DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE
 org_members | authenticated | DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE
 orgs        | anon          | DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE
 orgs        | authenticated | DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE
 tickets     | anon          | DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE
 tickets     | authenticated | DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE
(6 rows)

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;

CREATE SCHEMA IF NOT EXISTS private;
REVOKE ALL ON SCHEMA private FROM PUBLIC, anon, authenticated;
GRANT USAGE ON SCHEMA private TO authenticated;

CREATE OR REPLACE FUNCTION private.is_org_member(target_org_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $function$
  SELECT EXISTS (
    SELECT 1
    FROM public.org_members AS om
    WHERE om.org_id = target_org_id
      AND om.user_id = auth.uid()
  );
$function$;

CREATE OR REPLACE FUNCTION private.is_org_admin(target_org_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $function$
  SELECT EXISTS (
    SELECT 1
    FROM public.org_members AS om
    WHERE om.org_id = target_org_id
      AND om.user_id = auth.uid()
      AND om.role = 'admin'
  );
$function$;

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

ALTER TABLE public.tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.org_members ENABLE ROW LEVEL SECURITY;

CREATE POLICY tickets_member_select
ON public.tickets FOR SELECT TO authenticated
USING (private.is_org_member(org_id));

CREATE POLICY tickets_admin_insert
ON public.tickets FOR INSERT TO authenticated
WITH CHECK (private.is_org_admin(org_id));

CREATE POLICY tickets_admin_update
ON public.tickets FOR UPDATE TO authenticated
USING (private.is_org_admin(org_id))
WITH CHECK (private.is_org_admin(org_id));

CREATE POLICY tickets_admin_delete
ON public.tickets FOR DELETE TO authenticated
USING (private.is_org_admin(org_id));

CREATE POLICY org_members_member_select
ON public.org_members FOR SELECT TO authenticated
USING (private.is_org_member(org_id));

COMMIT;
SQL

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

NOTICE:  schema "private" already exists, skipping

set -euo pipefail
work=$(mktemp -d); trap 'rm -rf "$work"' EXIT
login() { curl -fsS "$SUPABASE_URL/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H 'Content-Type: application/json' --data "{\"email\":\"$1\",\"password\":\"$2\"}" | jq -r .access_token; }
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')
get() { curl -fsS "$SUPABASE_URL/rest/v1/$1" -H "apikey: $SUPABASE_ANON_KEY" ${2:+-H "Authorization: Bearer $2"}; }
printf 'anonymous tickets: '; get 'tickets?select=*' '' | jq 'length'
printf 'Uma tickets/by org: '; get 'tickets?select=org_id' "$uma" | jq 'group_by(.org_id)|map({org_id:.[0].org_id,count:length})'
printf 'Vic tickets/by org: '; get 'tickets?select=org_id' "$vic" | jq 'group_by(.org_id)|map({org_id:.[0].org_id,count:length})'
printf 'Wen tickets/by org: '; get 'tickets?select=org_id' "$wen" | jq 'group_by(.org_id)|map({org_id:.[0].org_id,count:length})'
printf 'anonymous members: '; get 'org_members?select=*' '' | jq 'length'
printf 'Uma member-list orgs: '; get 'org_members?select=org_id' "$uma" | jq '[.[].org_id]|unique'
printf 'Vic member-list orgs: '; get 'org_members?select=org_id' "$vic" | jq '[.[].org_id]|unique'
printf 'Wen member-list orgs: '; get 'org_members?select=org_id' "$wen" | jq '[.[].org_id]|unique'
printf 'Authenticated org visibility counts (Uma/Vic/Wen): '
for jwt in "$uma" "$vic" "$wen"; do get 'orgs?select=id' "$jwt" | jq -c 'length'; done
# Attempt forbidden member insert; use an existing visible org_id and deliberately unique negative ticket id.
acme=$(get 'tickets?select=org_id&limit=1' "$vic" | jq -r '.[0].org_id')
code=$(curl -sS -o "$work/deny.json" -w '%{http_code}' "$SUPABASE_URL/rest/v1/tickets" \
 -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $vic" -H 'Content-Type: application/json' \
 --data "{\"id\":-9223372036854775807,\"org_id\":\"$acme\",\"title\":\"RLS denial probe\",\"status\":\"open\"}")
printf 'Vic forbidden insert HTTP: %s, response: ' "$code"; jq -c '{code,message}' "$work/deny.json"
# Assert no probe row was created using service-role REST access.
probe=$(curl -fsS "$SUPABASE_URL/rest/v1/tickets?id=eq.-9223372036854775807&select=id" -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY")
printf 'Probe rows after denied write: '; jq 'length' <<<"$probe"

anonymous tickets: 0
Uma tickets/by org: [
  {
    "org_id": "6af95c55-7c90-476b-a4de-ea3e2a1a910a",
    "count": 3
  }
]
Vic tickets/by org: [
  {
    "org_id": "6af95c55-7c90-476b-a4de-ea3e2a1a910a",
    "count": 3
  }
]
Wen tickets/by org: [
  {
    "org_id": "f3d390c5-8e4a-42b1-bdb1-ddcc1c9f6faf",
    "count": 2
  }
]
anonymous members: 0
Uma member-list orgs: [
  "6af95c55-7c90-476b-a4de-ea3e2a1a910a"
]
Vic member-list orgs: [
  "6af95c55-7c90-476b-a4de-ea3e2a1a910a"
]
Wen member-list orgs: [
  "f3d390c5-8e4a-42b1-bdb1-ddcc1c9f6faf"
]
Authenticated org visibility counts (Uma/Vic/Wen): 2
2
2
Vic forbidden insert HTTP: 403, response: {"code":"42501","message":"new row violates row-level security policy for table \"tickets\""}
Probe rows after denied write: 0

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
ALTER TABLE public.orgs ENABLE ROW LEVEL SECURITY;
CREATE POLICY orgs_readable
ON public.orgs FOR SELECT TO anon, authenticated
USING (true);
COMMIT;

\pset pager off
SELECT tablename, rowsecurity FROM pg_tables
WHERE schemaname='public' AND tablename IN ('orgs','org_members','tickets') ORDER BY tablename;
SELECT tablename, policyname, roles, cmd
FROM pg_policies
WHERE schemaname='public' AND tablename IN ('orgs','org_members','tickets')
ORDER BY tablename, policyname;
SQL

BEGIN
ALTER TABLE
CREATE POLICY
COMMIT
Pager usage is off.
  tablename  | rowsecurity 
-------------+-------------
 org_members | t
 orgs        | t
 tickets     | t
(3 rows)

  tablename  |        policyname         |        roles         |  cmd   
-------------+---------------------------+----------------------+--------
 org_members | org_members_member_select | {authenticated}      | SELECT
 orgs        | orgs_readable             | {anon,authenticated} | SELECT
 tickets     | tickets_admin_delete      | {authenticated}      | DELETE
 tickets     | tickets_admin_insert      | {authenticated}      | INSERT
 tickets     | tickets_admin_update      | {authenticated}      | UPDATE
 tickets     | tickets_member_select     | {authenticated}      | SELECT
(6 rows)