claude-opus-4-8 · d1_rls_owner · rep 2

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 jq || echo "checking curl/jq"

/usr/bin/psql
/usr/bin/curl
/usr/bin/jq

psql "$SUPABASE_DB_URL" -c "\d public.notes" 2>&1; echo "---RLS STATUS---"; psql "$SUPABASE_DB_URL" -t -c "select relname, relrowsecurity, relforcerowsecurity from pg_class where relname='notes';" 2>&1; echo "---POLICIES---"; psql "$SUPABASE_DB_URL" -c "select * from pg_policies where tablename='notes';" 2>&1

                            Table "public.notes"
 Column  |  Type  | Collation | Nullable |             Default              
---------+--------+-----------+----------+----------------------------------
 id      | bigint |           | not null | generated by default as identity
 user_id | uuid   |           | not null | 
 title   | text   |           | not null | 
 body    | text   |           | not null | ''::text
Indexes:
    "notes_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "notes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE

---RLS STATUS---
 notes   | f              | f

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

psql "$SUPABASE_DB_URL" -c "select grantee, privilege_type from information_schema.role_table_grants where table_name='notes' and table_schema='public' order by grantee, privilege_type;" 2>&1; echo "---DATA COUNT---"; psql "$SUPABASE_DB_URL" -t -c "select user_id, count(*) from public.notes group by user_id;" 2>&1

    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)

---DATA COUNT---
 622e07b1-b747-46bd-bc77-046600880b6e |     2
 5a4f674d-90e9-4810-b4b8-220f028eded5 |     2

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
BEGIN;

ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS notes_select_own ON public.notes;
DROP POLICY IF EXISTS notes_insert_own ON public.notes;
DROP POLICY IF EXISTS notes_update_own ON public.notes;
DROP POLICY IF EXISTS notes_delete_own ON public.notes;

CREATE POLICY notes_select_own ON public.notes
  FOR SELECT TO authenticated
  USING ( (SELECT auth.uid()) = user_id );

CREATE POLICY notes_insert_own ON public.notes
  FOR INSERT TO authenticated
  WITH CHECK ( (SELECT auth.uid()) = user_id );

CREATE POLICY notes_update_own ON public.notes
  FOR UPDATE TO authenticated
  USING ( (SELECT auth.uid()) = user_id )
  WITH CHECK ( (SELECT auth.uid()) = user_id );

CREATE POLICY notes_delete_own ON public.notes
  FOR DELETE TO authenticated
  USING ( (SELECT auth.uid()) = user_id );

COMMIT;
SQL

BEGIN
ALTER TABLE
NOTICE:  policy "notes_select_own" for relation "public.notes" does not exist, skipping
DROP POLICY
NOTICE:  policy "notes_insert_own" for relation "public.notes" does not exist, skipping
DROP POLICY
NOTICE:  policy "notes_update_own" for relation "public.notes" does not exist, skipping
DROP POLICY
NOTICE:  policy "notes_delete_own" for relation "public.notes" does not exist, skipping
DROP POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
COMMIT

ALICE_TOKEN=$(curl -s "$SUPABASE_URL/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H "Content-Type: application/json" -d '{"email":"alice@cari.dev","password":"Passw0rd-alice-42"}' | jq -r .access_token)
BOB_TOKEN=$(curl -s "$SUPABASE_URL/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H "Content-Type: application/json" -d '{"email":"bob@cari.dev","password":"Passw0rd-bob-58"}' | jq -r .access_token)
echo "alice token len: ${#ALICE_TOKEN}"; echo "bob token len: ${#BOB_TOKEN}"
echo "$ALICE_TOKEN" > /tmp/alice.jwt
echo "$BOB_TOKEN" > /tmp/bob.jwt
# decode alice sub
echo "alice sub:"; echo "$ALICE_TOKEN" | cut -d. -f2 | base64 -d 2>/dev/null | jq -r .sub
echo "bob sub:"; echo "$BOB_TOKEN" | cut -d. -f2 | base64 -d 2>/dev/null | jq -r .sub

alice token len: 791
bob token len: 788
alice sub:
622e07b1-b747-46bd-bc77-046600880b6e
bob sub:
5a4f674d-90e9-4810-b4b8-220f028eded5

ALICE=$(cat /tmp/alice.jwt); BOB=$(cat /tmp/bob.jwt)
echo "=== 1. ANON select (should be empty []) ==="
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title" -H "apikey: $SUPABASE_ANON_KEY"

echo; echo "=== 2. ALICE select (should see only her 2 notes) ==="
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE"

echo; echo "=== 3. BOB select (should see only his 2 notes) ==="
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $BOB"

=== 1. ANON select (should be empty []) ===
[]
=== 2. ALICE select (should see only her 2 notes) ===
[{"id":1,"user_id":"622e07b1-b747-46bd-bc77-046600880b6e","title":"Groceries"}, 
 {"id":2,"user_id":"622e07b1-b747-46bd-bc77-046600880b6e","title":"Call plumber"}]
=== 3. BOB select (should see only his 2 notes) ===
[{"id":3,"user_id":"5a4f674d-90e9-4810-b4b8-220f028eded5","title":"Gym plan"}, 
 {"id":4,"user_id":"5a4f674d-90e9-4810-b4b8-220f028eded5","title":"Trip ideas"}]

ALICE=$(cat /tmp/alice.jwt); BOB=$(cat /tmp/bob.jwt)
ALICE_ID="622e07b1-b747-46bd-bc77-046600880b6e"; BOB_ID="5a4f674d-90e9-4810-b4b8-220f028eded5"

echo "=== 4. ALICE insert own row (should succeed) ==="
curl -s -X POST "$SUPABASE_URL/rest/v1/notes" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE" -H "Content-Type: application/json" -H "Prefer: return=representation" -d "{\"user_id\":\"$ALICE_ID\",\"title\":\"test-own\",\"body\":\"x\"}"

echo; echo "=== 5. ALICE insert with BOB's user_id (should be rejected) ==="
curl -s -w "\nHTTP %{http_code}\n" -X POST "$SUPABASE_URL/rest/v1/notes" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE" -H "Content-Type: application/json" -H "Prefer: return=representation" -d "{\"user_id\":\"$BOB_ID\",\"title\":\"spoof\",\"body\":\"x\"}"

echo; echo "=== 6. ALICE update BOB's note id=3 (should affect 0 rows) ==="
curl -s -w "\nHTTP %{http_code}\n" -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.3" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE" -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"title":"hacked"}'

echo "=== 7. ALICE delete BOB's note id=3 (should affect 0 rows) ==="
curl -s -w "\nHTTP %{http_code}\n" -X DELETE "$SUPABASE_URL/rest/v1/notes?id=eq.3" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE" -H "Prefer: return=representation"

echo "=== 8. ANON insert (should be rejected) ==="
curl -s -w "\nHTTP %{http_code}\n" -X POST "$SUPABASE_URL/rest/v1/notes" -H "apikey: $SUPABASE_ANON_KEY" -H "Content-Type: application/json" -d "{\"user_id\":\"$ALICE_ID\",\"title\":\"anon\",\"body\":\"x\"}"

=== 4. ALICE insert own row (should succeed) ===
[{"id":5,"user_id":"622e07b1-b747-46bd-bc77-046600880b6e","title":"test-own","body":"x"}]
=== 5. ALICE insert with BOB's user_id (should be rejected) ===
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}
HTTP 403

=== 6. ALICE update BOB's note id=3 (should affect 0 rows) ===
[]
HTTP 200
=== 7. ALICE delete BOB's note id=3 (should affect 0 rows) ===
[]
HTTP 200
=== 8. ANON insert (should be rejected) ===
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}
HTTP 401

ALICE=$(cat /tmp/alice.jwt)
echo "=== 9. ALICE update her own note id=1 (should succeed, then revert) ==="
curl -s -w "\nHTTP %{http_code}\n" -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.1" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE" -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"body":"temp-edit"}'
# revert body back to original ''
curl -s -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.1" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE" -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"body":""}' >/dev/null

echo "=== 10. ALICE delete her own test note id=5 (cleanup, should succeed) ==="
curl -s -w "\nHTTP %{http_code}\n" -X DELETE "$SUPABASE_URL/rest/v1/notes?id=eq.5" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE" -H "Prefer: return=representation"

echo "=== Final data check (should be exactly the original 4 notes, ids 1-4, bodies intact) ==="
psql "$SUPABASE_DB_URL" -c "select id, user_id, title, body from public.notes order by id;"

=== 9. ALICE update her own note id=1 (should succeed, then revert) ===
[{"id":1,"user_id":"622e07b1-b747-46bd-bc77-046600880b6e","title":"Groceries","body":"temp-edit"}]
HTTP 200
=== 10. ALICE delete her own test note id=5 (cleanup, should succeed) ===
[{"id":5,"user_id":"622e07b1-b747-46bd-bc77-046600880b6e","title":"test-own","body":"x"}]
HTTP 200
=== Final data check (should be exactly the original 4 notes, ids 1-4, bodies intact) ===
 id |               user_id                |    title     |         body         
----+--------------------------------------+--------------+----------------------
  1 | 622e07b1-b747-46bd-bc77-046600880b6e | Groceries    | 
  2 | 622e07b1-b747-46bd-bc77-046600880b6e | Call plumber | kitchen sink leaks
  3 | 5a4f674d-90e9-4810-b4b8-220f028eded5 | Gym plan     | push pull legs
  4 | 5a4f674d-90e9-4810-b4b8-220f028eded5 | Trip ideas   | mountains in october
(4 rows)

psql "$SUPABASE_DB_URL" -c "select relrowsecurity as rls_enabled from pg_class where relname='notes';"
psql "$SUPABASE_DB_URL" -c "select policyname, cmd, roles, qual, with_check from pg_policies where tablename='notes' order by policyname;"

 rls_enabled 
-------------
 t
(1 row)

    policyname    |  cmd   |      roles      |                  qual                   |               with_check                
------------------+--------+-----------------+-----------------------------------------+-----------------------------------------
 notes_delete_own | DELETE | {authenticated} | (( SELECT auth.uid() AS uid) = user_id) | 
 notes_insert_own | INSERT | {authenticated} |                                         | (( SELECT auth.uid() AS uid) = user_id)
 notes_select_own | SELECT | {authenticated} | (( SELECT auth.uid() AS uid) = user_id) | 
 notes_update_own | UPDATE | {authenticated} | (( SELECT auth.uid() AS uid) = user_id) | (( SELECT auth.uid() AS uid) = user_id)
(4 rows)