z-ai/glm-5.2 · d1_rls_owner · rep 1

echo "SUPABASE_URL=$SUPABASE_URL"; echo "PROJECT_REF=$SUPABASE_PROJECT_REF"; echo "DB_URL=$SUPABASE_DB_URL"; echo "ANON_KEY length=${#SUPABASE_ANON_KEY}"; echo "SERVICE_KEY length=${#SUPABASE_SERVICE_ROLE_KEY}"; echo "DB_PASSWORD length=${#SUPABASE_DB_PASSWORD}"

SUPABASE_URL=https://<ref>.supabase.co
PROJECT_REF=<ref>
DB_URL=<redacted:SUPABASE_DB_URL>
ANON_KEY length=208
SERVICE_KEY length=219
DB_PASSWORD length=48

pip install supabase-py httpx 2>/dev/null | tail -1

python3 -c "
import os, requests
url = os.environ['SUPABASE_URL']
anon = os.environ['SUPABASE_ANON_KEY']
# Check current anon access
r = requests.get(f'{url}/rest/v1/notes?select=*', headers={'apikey': anon})
print('Anon GET status:', r.status_code)
print('Anon GET data:', r.json()[:2] if isinstance(r.json(), list) else r.json())
print('Total rows visible to anon:', len(r.json()) if isinstance(r.json(), list) else 'N/A')
"

Traceback (most recent call last):
  File "<string>", line 2, in <module>
ModuleNotFoundError: No module named 'requests'

pip install requests 2>&1 | tail -1

bash: line 1: pip: command not found

pip3 install requests 2>&1 | tail -3

bash: line 1: pip3: command not found

which python3; python3 -c "import httpx; print('httpx ok')" 2>&1; python3 -c "import urllib.request; print('urllib ok')" 2>&1

/usr/bin/python3
Traceback (most recent call last):
  File "<string>", line 1, in <module>
ModuleNotFoundError: No module named 'httpx'
urllib ok

apt-get update -qq && apt-get install -y -qq python3-requests 2>&1 | tail -3

Setting up python3-idna (3.3-1+deb12u1) ...
Setting up python3-urllib3 (1.26.12-1+deb12u4) ...
Setting up python3-requests (2.28.1+dfsg-1) ...

python3 -c "
import os, requests, json
url = os.environ['SUPABASE_URL']
anon = os.environ['SUPABASE_ANON_KEY']

r = requests.get(f'{url}/rest/v1/notes?select=*', headers={'apikey': anon})
print('=== Anon GET ===')
print('Status:', r.status_code)
data = r.json()
print('Rows visible:', len(data) if isinstance(data, list) else data)
if isinstance(data, list):
    for row in data:
        print(json.dumps(row))
"

=== Anon GET ===
Status: 200
Rows visible: 4
{"id": 1, "user_id": "2f5e8643-78f0-4319-829e-01964164db05", "title": "Groceries", "body": "milk, eggs, coffee"}
{"id": 2, "user_id": "2f5e8643-78f0-4319-829e-01964164db05", "title": "Call plumber", "body": "kitchen sink leaks"}
{"id": 3, "user_id": "2b48b320-489a-4437-982b-b52a6e57ddf1", "title": "Gym plan", "body": "push pull legs"}
{"id": 4, "user_id": "2b48b320-489a-4437-982b-b52a6e57ddf1", "title": "Trip ideas", "body": "mountains in october"}

python3 << 'PYEOF'
import os
# Use the DB URL with the password
db_url = os.environ['SUPABASE_DB_URL']
print("DB URL:", db_url)
PYEOF

DB URL: <redacted:SUPABASE_DB_URL>

which psql 2>&1; apt list --installed 2>/dev/null | grep postgres 2>&1

/usr/bin/psql
postgresql-client-18/bookworm-pgdg,now 18.4-1.pgdg12+1 amd64 [installed]
postgresql-client-common/bookworm-pgdg,now 293.pgdg12+1 all [installed,automatic]

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres" -c "
SELECT c.relrowsecurity, c.relforcerowsecurity 
FROM pg_class c WHERE c.relname='notes';
"

 relrowsecurity | relforcerowsecurity 
----------------+---------------------
 f              | f
(1 row)

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres" -c "
SELECT pol.polname, pol.polcmd, pol.polroles::regrole, 
       pg_get_expr(pol.polqual, pol.polrelid) as using_expr,
       pg_get_expr(pol.polwithcheck, pol.polrelid) as with_check_expr
FROM pg_policy pol
JOIN pg_class c ON c.oid = pol.polrelid
WHERE c.relname = 'notes';
"

ERROR:  cannot cast type oid[] to regrole
LINE 2: SELECT pol.polname, pol.polcmd, pol.polroles::regrole, 
                                                    ^

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres" -c "
SELECT polname, polcmd, 
       array_agg(r.rolname) as roles,
       pg_get_expr(polqual, polrelid) as using_expr,
       pg_get_expr(polwithcheck, polrelid) as with_check_expr
FROM pg_policy p
JOIN pg_class c ON c.oid = p.polrelid
LEFT JOIN pg_roles r ON r.oid = ANY(p.polroles)
WHERE c.relname = 'notes'
GROUP BY polname, polcmd, polqual, polwithcheck, polrelid;
"

 polname | polcmd | roles | using_expr | with_check_expr 
---------+--------+-------+------------+-----------------
(0 rows)

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres" -c "
\d public.notes
"

ERROR:  syntax error at or near "\"
LINE 2: \d public.notes
        ^

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres" -c "
SELECT column_name, data_type, is_nullable, column_default 
FROM information_schema.columns 
WHERE table_schema='public' AND table_name='notes'
ORDER BY ordinal_position;
"

 column_name | data_type | is_nullable | column_default 
-------------+-----------+-------------+----------------
 id          | bigint    | NO          | 
 user_id     | uuid      | NO          | 
 title       | text      | NO          | 
 body        | text      | NO          | ''::text
(4 rows)

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres" -c "
SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_schema='public' AND table_name='notes'
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
 postgres      | DELETE
 postgres      | INSERT
 postgres      | REFERENCES
 postgres      | SELECT
 postgres      | TRIGGER
 postgres      | TRUNCATE
 postgres      | UPDATE
 service_role  | DELETE
 service_role  | INSERT
 service_role  | REFERENCES
 service_role  | SELECT
 service_role  | TRIGGER
 service_role  | TRUNCATE
 service_role  | UPDATE
(28 rows)

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres" << 'SQL'
-- Enable RLS
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notes FORCE ROW LEVEL SECURITY;

-- SELECT policy: authenticated users can only read their own rows
CREATE POLICY notes_select_owner ON public.notes
  FOR SELECT TO authenticated
  USING (user_id = auth.uid());

-- INSERT policy: authenticated users can only insert their own rows
CREATE POLICY notes_insert_owner ON public.notes
  FOR INSERT TO authenticated
  WITH CHECK (user_id = auth.uid());

-- UPDATE policy: authenticated users can only update their own rows
CREATE POLICY notes_update_owner ON public.notes
  FOR UPDATE TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

-- DELETE policy: authenticated users can only delete their own rows
CREATE POLICY notes_delete_owner ON public.notes
  FOR DELETE TO authenticated
  USING (user_id = auth.uid());
SQL

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

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres" -c "
SELECT polname, polcmd, 
       array_agg(r.rolname) as roles,
       pg_get_expr(polqual, polrelid) as using_expr,
       pg_get_expr(polwithcheck, polrelid) as with_check_expr
FROM pg_policy p
JOIN pg_class c ON c.oid = p.polrelid
LEFT JOIN pg_roles r ON r.oid = ANY(p.polroles)
WHERE c.relname = 'notes'
GROUP BY polname, polcmd, polqual, polwithcheck, polrelid
ORDER BY polname;
"
echo "---"
psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres" -c "
SELECT c.relrowsecurity, c.relforcerowsecurity FROM pg_class c WHERE c.relname='notes';
"

      polname       | polcmd |      roles      |       using_expr       |    with_check_expr     
--------------------+--------+-----------------+------------------------+------------------------
 notes_delete_owner | d      | {authenticated} | (user_id = auth.uid()) | 
 notes_insert_owner | a      | {authenticated} |                        | (user_id = auth.uid())
 notes_select_owner | r      | {authenticated} | (user_id = auth.uid()) | 
 notes_update_owner | w      | {authenticated} | (user_id = auth.uid()) | (user_id = auth.uid())
(4 rows)

---
 relrowsecurity | relforcerowsecurity 
----------------+---------------------
 t              | t
(1 row)

python3 << 'PYEOF'
import os, requests, json

url = os.environ['SUPABASE_URL']
anon = os.environ['SUPABASE_ANON_KEY']
service = os.environ['SUPABASE_SERVICE_ROLE_KEY']

def get_token(email, password):
    r = requests.post(f'{url}/auth/v1/token?grant_type=password',
        headers={'apikey': anon, 'Content-Type': 'application/json'},
        json={'email': email, 'password': password})
    r.raise_for_status()
    return r.json()['access_token']

alice_token = get_token('alice@cari.dev', 'Passw0rd-alice-42')
bob_token = get_token('bob@cari.dev', 'Passw0rd-bob-58')

print("=== TEST 1: Anon GET (should see 0 rows) ===")
r = requests.get(f'{url}/rest/v1/notes?select=*', headers={'apikey': anon})
print("Status:", r.status_code, "Rows:", len(r.json()) if isinstance(r.json(), list) else r.json())

print("\n=== TEST 2: Anon INSERT (should fail) ===")
r = requests.post(f'{url}/rest/v1/notes', 
    headers={'apikey': anon, 'Content-Type': 'application/json', 'Prefer': 'return=representation'},
    json={'user_id': '2f5e8643-78f0-4319-829e-01964164db05', 'title': 'test', 'body': 'test'})
print("Status:", r.status_code, "Response:", r.json())

print("\n=== TEST 3: Alice GET (should see only her 2 notes) ===")
r = requests.get(f'{url}/rest/v1/notes?select=*', 
    headers={'apikey': anon, 'Authorization': f'Bearer {alice_token}'})
print("Status:", r.status_code, "Rows:", len(r.json()))
for row in r.json():
    print(f"  id={row['id']} user_id={row['user_id']} title={row['title']}")

print("\n=== TEST 4: Bob GET (should see only his 2 notes) ===")
r = requests.get(f'{url}/rest/v1/notes?select=*',
    headers={'apikey': anon, 'Authorization': f'Bearer {bob_token}'})
print("Status:", r.status_code, "Rows:", len(r.json()))
for row in r.json():
    print(f"  id={row['id']} user_id={row['user_id']} title={row['title']}")

print("\n=== TEST 5: Alice INSERT with her own user_id (should succeed) ===")
alice_uid = '2f5e8643-78f0-4319-829e-01964164db05'
r = requests.post(f'{url}/rest/v1/notes',
    headers={'apikey': anon, 'Authorization': f'Bearer {alice_token}', 'Content-Type': 'application/json', 'Prefer': 'return=representation'},
    json={'user_id': alice_uid, 'title': 'temp_test_note', 'body': 'temp'})
print("Status:", r.status_code, "Response:", r.json())
inserted_id = r.json()[0]['id'] if r.status_code == 201 and isinstance(r.json(), list) else None
# Clean up: delete the temp note
if inserted_id:
    r2 = requests.delete(f'{url}/rest/v1/notes?id=eq.{inserted_id}',
        headers={'apikey': anon, 'Authorization': f'Bearer {alice_token}'})
    print(f"Cleanup delete: {r2.status_code}")

print("\n=== TEST 6: Alice INSERT with Bob's user_id (should fail) ===")
bob_uid = '2b48b320-489a-4437-982b-b52a6e57ddf1'
r = requests.post(f'{url}/rest/v1/notes',
    headers={'apikey': anon, 'Authorization': f'Bearer {alice_token}', 'Content-Type': 'application/json', 'Prefer': 'return=representation'},
    json={'user_id': bob_uid, 'title': 'evil', 'body': 'hacked'})
print("Status:", r.status_code, "Response:", r.json())

print("\n=== TEST 7: Alice UPDATE her own note (should succeed) ===")
r = requests.patch(f'{url}/rest/v1/notes?id=eq.1',
    headers={'apikey': anon, 'Authorization': f'Bearer {alice_token}', 'Content-Type': 'application/json', 'Prefer': 'return=representation'},
    json={'title': 'Groceries'})
print("Status:", r.status_code, "Response:", r.json())

print("\n=== TEST 8: Alice UPDATE Bob's note (should not affect any rows) ===")
r = requests.patch(f'{url}/rest/v1/notes?id=eq.3',
    headers={'apikey': anon, 'Authorization': f'Bearer {alice_token}', 'Content-Type': 'application/json', 'Prefer': 'return=representation'},
    json={'title': 'hacked'})
print("Status:", r.status_code, "Rows affected:", len(r.json()) if isinstance(r.json(), list) else r.json())

print("\n=== TEST 9: Alice DELETE Bob's note (should not delete any rows) ===")
r = requests.delete(f'{url}/rest/v1/notes?id=eq.3',
    headers={'apikey': anon, 'Authorization': f'Bea

=== TEST 1: Anon GET (should see 0 rows) ===
Status: 200 Rows: 0

=== TEST 2: Anon INSERT (should fail) ===
Status: 401 Response: {'code': '42501', 'details': None, 'hint': None, 'message': 'new row violates row-level security policy for table "notes"'}

=== TEST 3: Alice GET (should see only her 2 notes) ===
Status: 200 Rows: 2
  id=1 user_id=2f5e8643-78f0-4319-829e-01964164db05 title=Groceries
  id=2 user_id=2f5e8643-78f0-4319-829e-01964164db05 title=Call plumber

=== TEST 4: Bob GET (should see only his 2 notes) ===
Status: 200 Rows: 2
  id=3 user_id=2b48b320-489a-4437-982b-b52a6e57ddf1 title=Gym plan
  id=4 user_id=2b48b320-489a-4437-982b-b52a6e57ddf1 title=Trip ideas

=== TEST 5: Alice INSERT with her own user_id (should succeed) ===
Status: 201 Response: [{'id': 6, 'user_id': '2f5e8643-78f0-4319-829e-01964164db05', 'title': 'temp_test_note', 'body': 'temp'}]
Cleanup delete: 204

=== TEST 6: Alice INSERT with Bob's user_id (should fail) ===
Status: 403 Response: {'code': '42501', 'details': None, 'hint': None, 'message': 'new row violates row-level security policy for table "notes"'}

=== TEST 7: Alice UPDATE her own note (should succeed) ===
Status: 200 Response: [{'id': 1, 'user_id': '2f5e8643-78f0-4319-829e-01964164db05', 'title': 'Groceries', 'body': 'milk, eggs, coffee'}]

=== TEST 8: Alice UPDATE Bob's note (should not affect any rows) ===
Status: 200 Rows affected: 0

=== TEST 9: Alice DELETE Bob's note (should not delete any rows) ===
Status: 204

=== TEST 10: Bob DELETE his own note (should NOT happen - we won't actually delete) ===
Skipping to avoid data modification

=== TEST 11: Verify data is unchanged ===
Service role sees all rows:
  id=2 user_id=2f5e8643-78f0-4319-829e-01964164db05 title=Call plumber body=kitchen sink leaks
  id=3 user_id=2b48b320-489a-4437-982b-b52a6e57ddf1 title=Gym plan body=push pull legs
  id=4 user_id=2b48b320-489a-4437-982b-b52a6e57ddf1 title=Trip ideas body=mountains in october
  id=1 user_id=2f5e8643-78f0-4319-829e-01964164db05 title=Groceries body=milk, eggs, coffee
Total: 4

export PGPASSWORD='<redacted:SUPABASE_DB_PASSWORD>'
psql "postgresql://postgres.<ref>@aws-0-us-east-1.pooler.supabase.com:5432/postgres" -c "
SELECT id, user_id, title, body FROM public.notes ORDER BY id;
SELECT count(*) as total FROM public.notes;
"

 id |               user_id                |    title     |         body         
----+--------------------------------------+--------------+----------------------
  1 | 2f5e8643-78f0-4319-829e-01964164db05 | Groceries    | milk, eggs, coffee
  2 | 2f5e8643-78f0-4319-829e-01964164db05 | Call plumber | kitchen sink leaks
  3 | 2b48b320-489a-4437-982b-b52a6e57ddf1 | Gym plan     | push pull legs
  4 | 2b48b320-489a-4437-982b-b52a6e57ddf1 | Trip ideas   | mountains in october
(4 rows)

 total 
-------
     4
(1 row)