claude-opus-5 · d1_rls_owner · rep 2

env | grep -i supabase | sed 's/\(KEY=.\{20\}\).*/\1.../' 

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIs...
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIs...

which psql curl jq python3 2>&1; psql --version 2>&1

/usr/bin/psql
/usr/bin/curl
/usr/bin/jq
/usr/bin/python3
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

cd /tmp && psql "$SUPABASE_DB_URL" -c "\d+ public.notes" 2>&1 | head -50

                                                       Table "public.notes"
 Column  |  Type  | Collation | Nullable |             Default              | Storage  | Compression | Stats target | Description 
---------+--------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
 id      | bigint |           | not null | generated by default as identity | plain    |             |              | 
 user_id | uuid   |           | not null |                                  | plain    |             |              | 
 title   | text   |           | not null |                                  | extended |             |              | 
 body    | text   |           | not null | ''::text                         | extended |             |              | 
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
Access method: heap

cd /tmp && psql "$SUPABASE_DB_URL" -c "select relname, relrowsecurity, relforcerowsecurity from pg_class where oid='public.notes'::regclass;" -c "select * from pg_policies where schemaname='public' and tablename='notes';" 2>&1

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

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

cd /tmp && psql "$SUPABASE_DB_URL" -c "select grantee, string_agg(privilege_type,',' order by privilege_type) from information_schema.role_table_grants where table_schema='public' and table_name='notes' group by grantee;" 2>&1

    grantee    |                       string_agg                        
---------------+---------------------------------------------------------
 anon          | DELETE,INSERT,REFERENCES,SELECT,TRIGGER,TRUNCATE,UPDATE
 authenticated | DELETE,INSERT,REFERENCES,SELECT,TRIGGER,TRUNCATE,UPDATE
 postgres      | DELETE,INSERT,REFERENCES,SELECT,TRIGGER,TRUNCATE,UPDATE
 service_role  | DELETE,INSERT,REFERENCES,SELECT,TRIGGER,TRUNCATE,UPDATE
(4 rows)

cd /tmp && psql "$SUPABASE_DB_URL" -c "select id, user_id, title from public.notes order by id;" -c "select id, email from auth.users order by email;" 2>&1

 id |               user_id                |    title     
----+--------------------------------------+--------------
  1 | dfc9a7bf-47a0-42d0-a2eb-ca56003419e9 | Groceries
  2 | dfc9a7bf-47a0-42d0-a2eb-ca56003419e9 | Call plumber
  3 | 8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9 | Gym plan
  4 | 8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9 | Trip ideas
(4 rows)

                  id                  |     email      
--------------------------------------+----------------
 dfc9a7bf-47a0-42d0-a2eb-ca56003419e9 | alice@cari.dev
 8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9 | bob@cari.dev
(2 rows)

cd /tmp && psql "$SUPABASE_DB_URL" -c "
select c.relname, c.relkind, pg_get_userbyid(c.relowner) as owner
from pg_class c join pg_namespace n on n.oid=c.relnamespace
where n.nspname='public' and c.relkind in ('r','v','m','f','p');" 2>&1

 relname | relkind |  owner   
---------+---------+----------
 notes   | r       | postgres
(1 row)

cd /tmp && psql "$SUPABASE_DB_URL" -c "
select p.proname, p.prosecdef, pg_get_userbyid(p.proowner) owner
from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='public';" 2>&1

 proname | prosecdef | owner 
---------+-----------+-------
(0 rows)

cd /tmp && echo "--- anon SELECT (before) ---" && curl -s "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title" -H "apikey: $SUPABASE_ANON_KEY" | jq -c .

--- anon SELECT (before) ---
[{"id":1,"user_id":"dfc9a7bf-47a0-42d0-a2eb-ca56003419e9","title":"Groceries"},{"id":2,"user_id":"dfc9a7bf-47a0-42d0-a2eb-ca56003419e9","title":"Call plumber"},{"id":3,"user_id":"8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9","title":"Gym plan"},{"id":4,"user_id":"8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9","title":"Trip ideas"}]

cd /tmp && for u in alice bob; do
  if [ $u = alice ]; then p=Passw0rd-alice-42; else p=Passw0rd-bob-58; fi
  curl -s "$SUPABASE_URL/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H "Content-Type: application/json" \
    -d "{\"email\":\"$u@cari.dev\",\"password\":\"$p\"}" | jq -r '.access_token' > tok_$u.txt
  echo "$u token len: $(wc -c < tok_$u.txt)"
done

alice token len: 792
bob token len: 789

cd /tmp && cat > lockdown.sql <<'SQL'
begin;

-- 1. Turn on row level security.
alter table public.notes enable row level security;
-- Also apply RLS to the table owner (defence in depth; superuser/bypassrls still exempt,
-- which keeps service_role working for admin tasks).
alter table public.notes force row level security;

-- 2. Drop any pre-existing policies so this script is idempotent.
do $$
declare p record;
begin
  for p in select policyname from pg_policies
           where schemaname='public' and tablename='notes'
  loop
    execute format('drop policy %I on public.notes', p.policyname);
  end loop;
end $$;

-- 3. Least-privilege grants: anon needs nothing at all on this table.
revoke all on table public.notes from anon;
-- authenticated only needs plain DML, not TRUNCATE/REFERENCES/TRIGGER.
revoke all on table public.notes from authenticated;
grant select, insert, update, delete on table public.notes to authenticated;
-- The identity sequence must stay usable for inserts.
grant usage, select on sequence public.notes_id_seq to authenticated;
revoke all on sequence public.notes_id_seq from anon;

-- 4. Owner-only policies, one per command, scoped to the authenticated role.
--    (select auth.uid()) is wrapped so the planner evaluates it once per query.

create policy notes_select_own on public.notes
  for select to authenticated
  using (user_id = (select auth.uid()));

-- WITH CHECK pins the new row's user_id to the caller: you cannot insert
-- a row owned by somebody else.
create policy notes_insert_own on public.notes
  for insert to authenticated
  with check (user_id = (select auth.uid()));

-- USING limits which rows you may target; WITH CHECK stops you from
-- reassigning a row to another user_id.
create policy notes_update_own on public.notes
  for update to authenticated
  using (user_id = (select auth.uid()))
  with check (user_id = (select auth.uid()));

create policy notes_delete_own on public.notes
  for delete to authenticated
  using (user_id = (select auth.uid()));

commit;
SQL
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f lockdown.sql 2>&1

BEGIN
ALTER TABLE
ALTER TABLE
DO
REVOKE
REVOKE
GRANT
GRANT
REVOKE
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
COMMIT

cd /tmp && psql "$SUPABASE_DB_URL" -c "select relrowsecurity, relforcerowsecurity from pg_class where oid='public.notes'::regclass;" -c "select policyname, cmd, roles, qual, with_check from pg_policies where tablename='notes' order by cmd;" -c "select grantee, string_agg(privilege_type,',' order by privilege_type) from information_schema.role_table_grants where table_schema='public' and table_name='notes' group by grantee order by grantee;" 2>&1

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

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

    grantee    |                       string_agg                        
---------------+---------------------------------------------------------
 authenticated | DELETE,INSERT,SELECT,UPDATE
 postgres      | DELETE,INSERT,REFERENCES,SELECT,TRIGGER,TRUNCATE,UPDATE
 service_role  | DELETE,INSERT,REFERENCES,SELECT,TRIGGER,TRUNCATE,UPDATE
(3 rows)

cd /tmp && A=$(cat tok_alice.txt); B=$(cat tok_bob.txt)
AID=dfc9a7bf-47a0-42d0-a2eb-ca56003419e9; BID=8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9
echo "=== 1. anon SELECT (want: no rows) ==="
curl -s "$SUPABASE_URL/rest/v1/notes?select=*" -H "apikey: $SUPABASE_ANON_KEY" | jq -c .
echo "=== 2. alice SELECT (want: only her 2) ==="
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,title" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $A" | jq -c .
echo "=== 3. bob SELECT (want: only his 2) ==="
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,title" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $B" | jq -c .
echo "=== 4. alice reads bob's note id=3 directly (want: empty) ==="
curl -s "$SUPABASE_URL/rest/v1/notes?id=eq.3&select=*" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $A" | jq -c .

=== 1. anon SELECT (want: no rows) ===
{"code":"42501","details":null,"hint":"Grant the required privileges to the current role with: GRANT SELECT ON public.notes TO anon;","message":"permission denied for table notes"}
=== 2. alice SELECT (want: only her 2) ===
[{"id":1,"title":"Groceries"},{"id":2,"title":"Call plumber"}]
=== 3. bob SELECT (want: only his 2) ===
[{"id":3,"title":"Gym plan"},{"id":4,"title":"Trip ideas"}]
=== 4. alice reads bob's note id=3 directly (want: empty) ===
[]

cd /tmp && psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL' 2>&1
-- Give anon only SELECT back so PostgREST returns an empty set instead of a
-- 401/403-style permission error. RLS is the thing denying the data: there is
-- no policy for the anon role, so zero rows are visible. No INSERT/UPDATE/
-- DELETE grant, so writes are still refused outright.
grant select on table public.notes to anon;
SQL
psql "$SUPABASE_DB_URL" -c "select grantee, string_agg(privilege_type,',' order by privilege_type) from information_schema.role_table_grants where table_schema='public' and table_name='notes' and grantee in ('anon','authenticated') group by grantee order by grantee;" 2>&1

GRANT
    grantee    |         string_agg          
---------------+-----------------------------
 anon          | SELECT
 authenticated | DELETE,INSERT,SELECT,UPDATE
(2 rows)

cd /tmp && A=$(cat tok_alice.txt); B=$(cat tok_bob.txt)
AID=dfc9a7bf-47a0-42d0-a2eb-ca56003419e9; BID=8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9
AK="apikey: $SUPABASE_ANON_KEY"
echo "=== 1. anon SELECT (want: [] not an error) ==="
curl -s "$SUPABASE_URL/rest/v1/notes?select=*" -H "$AK" | jq -c .
echo "=== 2. anon INSERT (want: denied) ==="
curl -s -o /dev/null -w '  http=%{http_code} ' -X POST "$SUPABASE_URL/rest/v1/notes" -H "$AK" -H "Content-Type: application/json" -d "{\"user_id\":\"$AID\",\"title\":\"anon hack\",\"body\":\"x\"}"; echo
echo "=== 3. anon DELETE all (want: denied, 0 rows removed) ==="
curl -s -o /dev/null -w '  http=%{http_code} ' -X DELETE "$SUPABASE_URL/rest/v1/notes?id=gt.0" -H "$AK"; echo
echo "=== 4. alice INSERT own row (want: 201 success) ==="
curl -s -X POST "$SUPABASE_URL/rest/v1/notes" -H "$AK" -H "Authorization: Bearer $A" -H "Content-Type: application/json" -H "Prefer: return=representation" -d "{\"user_id\":\"$AID\",\"title\":\"tmp-alice\",\"body\":\"selftest\"}" | jq -c .
echo "=== 5. alice INSERT spoofing bob's user_id (want: 42501 blocked) ==="
curl -s -X POST "$SUPABASE_URL/rest/v1/notes" -H "$AK" -H "Authorization: Bearer $A" -H "Content-Type: application/json" -d "{\"user_id\":\"$BID\",\"title\":\"spoof\",\"body\":\"x\"}" | jq -c .

=== 1. anon SELECT (want: [] not an error) ===
[]
=== 2. anon INSERT (want: denied) ===
  http=401 
=== 3. anon DELETE all (want: denied, 0 rows removed) ===
  http=401 
=== 4. alice INSERT own row (want: 201 success) ===
[{"id":5,"user_id":"dfc9a7bf-47a0-42d0-a2eb-ca56003419e9","title":"tmp-alice","body":"selftest"}]
=== 5. alice INSERT spoofing bob's user_id (want: 42501 blocked) ===
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}

cd /tmp && A=$(cat tok_alice.txt); B=$(cat tok_bob.txt)
BID=8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9
AK="apikey: $SUPABASE_ANON_KEY"
echo "=== 6. alice UPDATE own note id=5 (want: success) ==="
curl -s -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.5" -H "$AK" -H "Authorization: Bearer $A" -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"title":"tmp-alice-edited"}' | jq -c .
echo "=== 7. alice UPDATE bob's note id=3 (want: [] no rows affected) ==="
curl -s -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.3" -H "$AK" -H "Authorization: Bearer $A" -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"title":"HACKED"}' | jq -c .
echo "=== 8. alice reassigns own note id=5 to bob (want: 42501 blocked) ==="
curl -s -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.5" -H "$AK" -H "Authorization: Bearer $A" -H "Content-Type: application/json" -d "{\"user_id\":\"$BID\"}" | jq -c .
echo "=== 9. alice DELETE bob's note id=4 (want: [] no rows affected) ==="
curl -s -X DELETE "$SUPABASE_URL/rest/v1/notes?id=eq.4" -H "$AK" -H "Authorization: Bearer $A" -H "Prefer: return=representation" | jq -c .
echo "=== 10. alice DELETE own note id=5 (want: success, cleanup) ==="
curl -s -X DELETE "$SUPABASE_URL/rest/v1/notes?id=eq.5" -H "$AK" -H "Authorization: Bearer $A" -H "Prefer: return=representation" | jq -c .

=== 6. alice UPDATE own note id=5 (want: success) ===
[{"id":5,"user_id":"dfc9a7bf-47a0-42d0-a2eb-ca56003419e9","title":"tmp-alice-edited","body":"selftest"}]
=== 7. alice UPDATE bob's note id=3 (want: [] no rows affected) ===
[]
=== 8. alice reassigns own note id=5 to bob (want: 42501 blocked) ===
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}
=== 9. alice DELETE bob's note id=4 (want: [] no rows affected) ===
[]
=== 10. alice DELETE own note id=5 (want: success, cleanup) ===
[{"id":5,"user_id":"dfc9a7bf-47a0-42d0-a2eb-ca56003419e9","title":"tmp-alice-edited","body":"selftest"}]

cd /tmp && B=$(cat tok_bob.txt); AK="apikey: $SUPABASE_ANON_KEY"
echo "=== 11. bob full CRUD round-trip on his own data ==="
NEW=$(curl -s -X POST "$SUPABASE_URL/rest/v1/notes" -H "$AK" -H "Authorization: Bearer $B" -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"user_id":"8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9","title":"tmp-bob","body":"y"}')
echo "  insert: $(echo $NEW | jq -c .)"
NID=$(echo "$NEW" | jq -r '.[0].id')
echo "  update: $(curl -s -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.$NID" -H "$AK" -H "Authorization: Bearer $B" -H "Content-Type: application/json" -H 'Prefer: return=representation' -d '{"body":"y2"}' | jq -c .)"
echo "  select: $(curl -s "$SUPABASE_URL/rest/v1/notes?select=id,title" -H "$AK" -H "Authorization: Bearer $B" | jq -c .)"
echo "  delete: $(curl -s -X DELETE "$SUPABASE_URL/rest/v1/notes?id=eq.$NID" -H "$AK" -H "Authorization: Bearer $B" -H 'Prefer: return=representation' | jq -c .)"

=== 11. bob full CRUD round-trip on his own data ===
  insert: [{"id":7,"user_id":"8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9","title":"tmp-bob","body":"y"}]
  update: [{"id":7,"user_id":"8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9","title":"tmp-bob","body":"y2"}]
  select: [{"id":3,"title":"Gym plan"},{"id":4,"title":"Trip ideas"},{"id":7,"title":"tmp-bob"}]
  delete: [{"id":7,"user_id":"8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9","title":"tmp-bob","body":"y2"}]

cd /tmp && echo "=== 12. service_role still has admin access (backend jobs unbroken) ===" && curl -s "$SUPABASE_URL/rest/v1/notes?select=id,title" -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" | jq -c .

=== 12. service_role still has admin access (backend jobs unbroken) ===
[{"id":1,"title":"Groceries"},{"id":2,"title":"Call plumber"},{"id":3,"title":"Gym plan"},{"id":4,"title":"Trip ideas"}]

cd /tmp && echo "=== 13. Final data integrity check (want: original 4 rows, unmodified) ===" && psql "$SUPABASE_DB_URL" -c "select id, user_id, title, body from public.notes order by id;" -c "select count(*) as total from public.notes;" 2>&1; rm -f tok_alice.txt tok_bob.txt

=== 13. Final data integrity check (want: original 4 rows, unmodified) ===
 id |               user_id                |    title     |         body         
----+--------------------------------------+--------------+----------------------
  1 | dfc9a7bf-47a0-42d0-a2eb-ca56003419e9 | Groceries    | milk, eggs, coffee
  2 | dfc9a7bf-47a0-42d0-a2eb-ca56003419e9 | Call plumber | kitchen sink leaks
  3 | 8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9 | Gym plan     | push pull legs
  4 | 8f78f7c4-53d8-4e61-a035-3dfe21ff8bd9 | Trip ideas   | mountains in october
(4 rows)

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