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

set -euo pipefail
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "select table_name,column_name,data_type,udt_name from information_schema.columns where table_schema='public' and table_name in ('orgs','org_members','tickets') order by table_name,ordinal_position;" -c "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;" -c "select relname, relrowsecurity, relforcerowsecurity from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname='public' and relname in ('orgs','org_members','tickets');"

 table_name  | column_name | data_type | udt_name 
-------------+-------------+-----------+----------
 org_members | org_id      | uuid      | uuid
 org_members | user_id     | uuid      | uuid
 org_members | role        | text      | text
 orgs        | id          | uuid      | uuid
 orgs        | name        | text      | text
 tickets     | id          | bigint    | int8
 tickets     | org_id      | uuid      | uuid
 tickets     | title       | text      | text
 tickets     | status      | text      | text
(9 rows)

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

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

set -euo pipefail
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;

CREATE SCHEMA IF NOT EXISTS private;
REVOKE ALL ON SCHEMA private FROM PUBLIC;
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;
REVOKE ALL ON FUNCTION private.is_org_admin(uuid) FROM PUBLIC;
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;

DROP POLICY IF EXISTS tickets_member_select ON public.tickets;
DROP POLICY IF EXISTS tickets_admin_insert ON public.tickets;
DROP POLICY IF EXISTS tickets_admin_update ON public.tickets;
DROP POLICY IF EXISTS tickets_admin_delete ON public.tickets;
DROP POLICY IF EXISTS org_members_same_org_select ON public.org_members;

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_same_org_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
DROP POLICY
DROP POLICY
DROP POLICY
DROP POLICY
DROP POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
COMMIT

NOTICE:  policy "tickets_member_select" for relation "public.tickets" does not exist, skipping
NOTICE:  policy "tickets_admin_insert" for relation "public.tickets" does not exist, skipping
NOTICE:  policy "tickets_admin_update" for relation "public.tickets" does not exist, skipping
NOTICE:  policy "tickets_admin_delete" for relation "public.tickets" does not exist, skipping
NOTICE:  policy "org_members_same_org_select" for relation "public.org_members" does not exist, skipping

cat > /tmp/test_rls.py <<'PY'
import os,json,urllib.request,urllib.error
base=os.environ['SUPABASE_URL'].rstrip('/')
anon=os.environ['SUPABASE_ANON_KEY']

def req(path, key=anon, bearer=None, method='GET', body=None, extra=None):
    h={'apikey':key}
    if bearer: h['Authorization']='Bearer '+bearer
    if body is not None: h['Content-Type']='application/json'
    if extra: h.update(extra)
    data=None if body is None else json.dumps(body).encode()
    r=urllib.request.Request(base+path,headers=h,data=data,method=method)
    try:
      with urllib.request.urlopen(r) as x: return x.status,json.loads(x.read() or b'null')
    except urllib.error.HTTPError as e:
      raw=e.read();
      try: val=json.loads(raw)
      except: val=raw.decode()
      return e.code,val

def login(email,password):
    s,j=req('/auth/v1/token?grant_type=password',method='POST',body={'email':email,'password':password})
    assert s==200,(s,j)
    return j['access_token']

users={
 'uma':('uma-admin@cari.dev','Passw0rd-uma-11'),
 'vic':('vic-member@cari.dev','Passw0rd-vic-22'),
 'wen':('wen-outsider@cari.dev','Passw0rd-wen-33')}
tokens={k:login(*v) for k,v in users.items()}
results={}
for name,tok in tokens.items():
    st,tickets=req('/rest/v1/tickets?select=id,org_id,title,status&order=id',bearer=tok)
    sm,members=req('/rest/v1/org_members?select=org_id,user_id,role&order=user_id',bearer=tok)
    assert st==sm==200,(name,st,tickets,sm,members)
    results[name]={'tickets':tickets,'members':members}

assert len(results['uma']['tickets'])==3, results['uma']
assert len(results['vic']['tickets'])==3, results['vic']
assert len(results['wen']['tickets'])==2, results['wen']
acme={x['org_id'] for x in results['uma']['tickets']}; globex={x['org_id'] for x in results['wen']['tickets']}
assert len(acme)==len(globex)==1 and acme.isdisjoint(globex)
assert {x['org_id'] for x in results['vic']['tickets']}==acme
for n in ('uma','vic'):
    assert {x['org_id'] for x in results[n]['members']}==acme
assert {x['org_id'] for x in results['wen']['members']}==globex

# Explicit cross-tenant filters return no rows.
a=next(iter(acme)); g=next(iter(globex))
for name,oid in [('uma',g),('vic',g),('wen',a)]:
    s,j=req('/rest/v1/tickets?select=*&org_id=eq.'+oid,bearer=tokens[name]); assert s==200 and j==[],(name,s,j)
    s,j=req('/rest/v1/org_members?select=*&org_id=eq.'+oid,bearer=tokens[name]); assert s==200 and j==[],(name,s,j)

# Anonymous role sees no protected rows.
for table in ('tickets','org_members'):
    s,j=req('/rest/v1/'+table+'?select=*'); assert s==200 and j==[],(table,s,j)

# A normal member cannot insert (request is denied and therefore changes no data).
s,j=req('/rest/v1/tickets',bearer=tokens['vic'],method='POST',body={'org_id':a,'title':'RLS denial probe','status':'open'},extra={'Prefer':'return=representation'})
assert s in (401,403),(s,j)
# Outsider cannot update/delete Acme; nonexistent visibility means no changed rows.
for method in ('PATCH','DELETE'):
    body={'status':'closed'} if method=='PATCH' else None
    s,j=req('/rest/v1/tickets?org_id=eq.'+a,bearer=tokens['wen'],method=method,body=body,extra={'Prefer':'return=representation'})
    assert s==200 and j==[],(method,s,j)

print(json.dumps({k:{'ticket_count':len(v['tickets']),'member_count_visible':len(v['members']),'org_id':next(iter({x['org_id'] for x in v['tickets']}))} for k,v in results.items()},indent=2))
print('anonymous: 0 tickets, 0 org_members; cross-tenant reads: 0; member insert denied; outsider writes affected 0')
PY
python3 /tmp/test_rls.py
rm /tmp/test_rls.py

{
  "uma": {
    "ticket_count": 3,
    "member_count_visible": 2,
    "org_id": "d6c56af2-417e-4d27-b869-c2e3b44db6d2"
  },
  "vic": {
    "ticket_count": 3,
    "member_count_visible": 2,
    "org_id": "d6c56af2-417e-4d27-b869-c2e3b44db6d2"
  },
  "wen": {
    "ticket_count": 2,
    "member_count_visible": 1,
    "org_id": "bdbffbd7-bfa2-4bf8-9ba9-04737d546370"
  }
}
anonymous: 0 tickets, 0 org_members; cross-tenant reads: 0; member insert denied; outsider writes affected 0