google/gemini-3.1-pro-preview · d2_rls_tenant · rep 3

psql $SUPABASE_DB_URL -c "\dt public.*"

             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.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 "\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.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 "select relname, relrowsecurity from pg_class where relname in ('orgs', 'org_members', 'tickets');"

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

cat << 'EOF' > rls.sql
ALTER TABLE public.tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.org_members ENABLE ROW LEVEL SECURITY;

-- Prevent infinite recursion by using a security definer function for checking membership
CREATE OR REPLACE FUNCTION public.check_is_org_member(_org_id uuid) RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.org_members
    WHERE org_id = _org_id AND user_id = auth.uid()
  );
$$;

CREATE OR REPLACE FUNCTION public.check_is_org_admin(_org_id uuid) RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.org_members
    WHERE org_id = _org_id AND user_id = auth.uid() AND role = 'admin'
  );
$$;

CREATE POLICY "Tickets read access" ON public.tickets
FOR SELECT TO authenticated
USING (
  public.check_is_org_member(org_id)
);

CREATE POLICY "Tickets insert access" ON public.tickets
FOR INSERT TO authenticated
WITH CHECK (
  public.check_is_org_admin(org_id)
);

CREATE POLICY "Tickets update access" ON public.tickets
FOR UPDATE TO authenticated
USING (
  public.check_is_org_admin(org_id)
)
WITH CHECK (
  public.check_is_org_admin(org_id)
);

CREATE POLICY "Tickets delete access" ON public.tickets
FOR DELETE TO authenticated
USING (
  public.check_is_org_admin(org_id)
);

CREATE POLICY "Org members read access" ON public.org_members
FOR SELECT TO authenticated
USING (
  public.check_is_org_member(org_id)
);
EOF
psql $SUPABASE_DB_URL -f rls.sql

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

cat << 'EOF' > test.py
import os
import requests

SUPABASE_URL = os.environ['SUPABASE_URL']
SUPABASE_ANON_KEY = os.environ['SUPABASE_ANON_KEY']

def login(email, password):
    res = requests.post(f"{SUPABASE_URL}/auth/v1/token?grant_type=password", 
        json={"email": email, "password": password},
        headers={"apikey": SUPABASE_ANON_KEY})
    if res.status_code == 200:
        return res.json()['access_token']
    else:
        print("Login failed for", email, res.text)
        return None

def test_fetch(token, table, user):
    res = requests.get(f"{SUPABASE_URL}/rest/v1/{table}?select=*",
        headers={"apikey": SUPABASE_ANON_KEY, "Authorization": f"Bearer {token}"})
    print(f"[{user}] fetch {table}: {len(res.json()) if res.status_code == 200 else res.text}")

token_uma = login('uma-admin@cari.dev', 'Passw0rd-uma-11')
token_vic = login('vic-member@cari.dev', 'Passw0rd-vic-22')
token_wen = login('wen-outsider@cari.dev', 'Passw0rd-wen-33')
token_anon = None

for u, t in [("uma", token_uma), ("vic", token_vic), ("wen", token_wen), ("anon", token_anon)]:
    if u != "anon":
        test_fetch(t, 'tickets', u)
        test_fetch(t, 'org_members', u)
    else:
        res = requests.get(f"{SUPABASE_URL}/rest/v1/tickets?select=*", headers={"apikey": SUPABASE_ANON_KEY})
        print(f"[anon] fetch tickets:", len(res.json()) if res.status_code == 200 else res.text)
        res = requests.get(f"{SUPABASE_URL}/rest/v1/org_members?select=*", headers={"apikey": SUPABASE_ANON_KEY})
        print(f"[anon] fetch org_members:", len(res.json()) if res.status_code == 200 else res.text)
EOF
python3 test.py

Traceback (most recent call last):
  File "/work/test.py", line 2, in <module>
    import requests
ModuleNotFoundError: No module named 'requests'

pip install requests > /dev/null && python3 test.py

bash: line 1: pip: command not found

python3 -c "import urllib.request, json, os
url = os.environ['SUPABASE_URL']
anon = os.environ['SUPABASE_ANON_KEY']
def login(e, p):
    req = urllib.request.Request(f'{url}/auth/v1/token?grant_type=password', data=json.dumps({'email':e,'password':p}).encode(), headers={'apikey': anon, 'Content-Type': 'application/json'})
    try: return json.loads(urllib.request.urlopen(req).read())['access_token']
    except Exception as ex: print('login fail', e, ex); return None
t_uma = login('uma-admin@cari.dev', 'Passw0rd-uma-11')
t_vic = login('vic-member@cari.dev', 'Passw0rd-vic-22')
t_wen = login('wen-outsider@cari.dev', 'Passw0rd-wen-33')
def fetch(t, table, u):
    h = {'apikey': anon}
    if t: h['Authorization'] = f'Bearer {t}'
    req = urllib.request.Request(f'{url}/rest/v1/{table}?select=*', headers=h)
    try: res = json.loads(urllib.request.urlopen(req).read()); print(f'[{u}] fetch {table}: count={len(res)}')
    except urllib.error.HTTPError as e: print(f'[{u}] fetch {table}: status={e.code}')
for u, t in [('uma', t_uma), ('vic', t_vic), ('wen', t_wen), ('anon', None)]:
    fetch(t, 'tickets', u)
    fetch(t, 'org_members', u)
"

[uma] fetch tickets: count=3
[uma] fetch org_members: count=2
[vic] fetch tickets: count=3
[vic] fetch org_members: count=2
[wen] fetch tickets: count=2
[wen] fetch org_members: count=1
[anon] fetch tickets: count=0
[anon] fetch org_members: count=0

cat << 'EOF' > test2.sql
SELECT id FROM public.orgs WHERE name = 'Acme' LIMIT 1 \gset
\echo :id
EOF
psql $SUPABASE_DB_URL -f test2.sql

9eca407d-201f-40f1-bb35-857bf709ecad

python3 -c "import urllib.request, json, os; url, anon = os.environ['SUPABASE_URL'], os.environ['SUPABASE_ANON_KEY']; 
def req(e, p, m, tb, data, expected):
    t = json.loads(urllib.request.urlopen(urllib.request.Request(f'{url}/auth/v1/token?grant_type=password', data=json.dumps({'email':e,'password':p}).encode(), headers={'apikey': anon, 'Content-Type': 'application/json'})).read())['access_token']
    r = urllib.request.Request(f'{url}/rest/v1/{tb}', method=m, data=json.dumps(data).encode() if data else None, headers={'apikey': anon, 'Authorization': f'Bearer {t}', 'Content-Type': 'application/json', 'Prefer': 'return=representation'})
    try: 
        res = urllib.request.urlopen(r)
        print(f'[{e}] {m} {tb}: success, data={res.read().decode()}')
    except urllib.error.HTTPError as er:
        print(f'[{e}] {m} {tb}: HTTP {er.code} - {er.read().decode()}')
req('vic-member@cari.dev', 'Passw0rd-vic-22', 'POST', 'tickets', {'org_id': '9eca407d-201f-40f1-bb35-857bf709ecad', 'title':'Vic Ticket', 'status':'open'}, 'fail')
req('uma-admin@cari.dev', 'Passw0rd-uma-11', 'POST', 'tickets', {'org_id': '9eca407d-201f-40f1-bb35-857bf709ecad', 'title':'Uma Ticket', 'status':'open'}, 'pass')
"

[vic-member@cari.dev] POST tickets: HTTP 403 - {"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"tickets\""}
[uma-admin@cari.dev] POST tickets: success, data=[{"id":7,"org_id":"9eca407d-201f-40f1-bb35-857bf709ecad","title":"Uma Ticket","status":"open"}]

python3 -c "import urllib.request, json, os; url, anon = os.environ['SUPABASE_URL'], os.environ['SUPABASE_ANON_KEY']
req = urllib.request.Request(f'{url}/rest/v1/orgs?select=*', headers={'apikey':anon})
print('orgs:', len(json.loads(urllib.request.urlopen(req).read())))"

orgs: 2

cat << 'EOF' > anonymous_test.sql
-- Wait, orgs is readable explicitly requested "orgs table itself may remain readable"
-- Let's double check anonymous fetch on org_members, tickets, and orgs.
EOF

python3 -c "import urllib.request, json, os; url, anon = os.environ['SUPABASE_URL'], os.environ['SUPABASE_ANON_KEY']
def req(tb):
    try: 
        res = urllib.request.urlopen(urllib.request.Request(f'{url}/rest/v1/{tb}', headers={'apikey': anon}))
        print(f'anon fetch {tb}: {len(json.loads(res.read()))}')
    except Exception as e: print(e)
req('org_members')
req('tickets')
req('orgs')
"

anon fetch org_members: 0
anon fetch tickets: 0
anon fetch orgs: 2

cat << 'EOF' > orgs_rls.sql
ALTER TABLE public.orgs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Orgs read access" ON public.orgs FOR SELECT USING (true);
EOF
psql $SUPABASE_DB_URL -f orgs_rls.sql

ALTER TABLE
CREATE POLICY

python3 -c "import urllib.request, json, os; url, anon = os.environ['SUPABASE_URL'], os.environ['SUPABASE_ANON_KEY']
def req(tb):
    try: 
        res = urllib.request.urlopen(urllib.request.Request(f'{url}/rest/v1/{tb}', headers={'apikey': anon}))
        print(f'anon fetch {tb}: {len(json.loads(res.read()))}')
    except Exception as e: print(e)
req('org_members')
req('tickets')
req('orgs')
"

anon fetch org_members: 0
anon fetch tickets: 0
anon fetch orgs: 2