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

env | grep -i supabase | sed 's/\(KEY=\).*/\1***/; s/\(PASSWORD=\).*/\1***/; s/\(DB_URL=\).*/\1***/'

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

which psql || echo "no psql"; which curl || echo "no curl"; which jq || echo "no jq"

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

psql "$SUPABASE_DB_URL" -c "\d public.notes" 2>&1; echo "---RLS---"; psql "$SUPABASE_DB_URL" -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---
 relname | relrowsecurity | relforcerowsecurity 
---------+----------------+---------------------
 notes   | f              | f
(1 row)

---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';" 2>&1; echo "---DATA---"; psql "$SUPABASE_DB_URL" -c "SELECT id, user_id, title FROM public.notes ORDER BY id;" 2>&1

    grantee    | privilege_type 
---------------+----------------
 postgres      | INSERT
 postgres      | SELECT
 postgres      | UPDATE
 postgres      | DELETE
 postgres      | TRUNCATE
 postgres      | REFERENCES
 postgres      | TRIGGER
 anon          | INSERT
 anon          | SELECT
 anon          | UPDATE
 anon          | DELETE
 anon          | TRUNCATE
 anon          | REFERENCES
 anon          | TRIGGER
 authenticated | INSERT
 authenticated | SELECT
 authenticated | UPDATE
 authenticated | DELETE
 authenticated | TRUNCATE
 authenticated | REFERENCES
 authenticated | TRIGGER
 service_role  | INSERT
 service_role  | SELECT
 service_role  | UPDATE
 service_role  | DELETE
 service_role  | TRUNCATE
 service_role  | REFERENCES
 service_role  | TRIGGER
(28 rows)

---DATA---
 id |               user_id                |    title     
----+--------------------------------------+--------------
  1 | 58a0d373-1349-462a-9400-1ee158ab06e7 | Groceries
  2 | 58a0d373-1349-462a-9400-1ee158ab06e7 | Call plumber
  3 | 29d628d5-d9cb-4a70-ac3d-1263c54d66c2 | Gym plan
  4 | 29d628d5-d9cb-4a70-ac3d-1263c54d66c2 | Trip ideas
(4 rows)

echo "=== anon REST before fix ==="; curl -s "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY" | jq .

=== anon REST before fix ===
[
  {
    "id": 1,
    "user_id": "58a0d373-1349-462a-9400-1ee158ab06e7",
    "title": "Groceries"
  },
  {
    "id": 2,
    "user_id": "58a0d373-1349-462a-9400-1ee158ab06e7",
    "title": "Call plumber"
  },
  {
    "id": 3,
    "user_id": "29d628d5-d9cb-4a70-ac3d-1263c54d66c2",
    "title": "Gym plan"
  },
  {
    "id": 4,
    "user_id": "29d628d5-d9cb-4a70-ac3d-1263c54d66c2",
    "title": "Trip ideas"
  }
]

psql "$SUPABASE_DB_URL" <<'SQL' 2>&1
-- Enable Row Level Security (default-deny once enabled)
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;

-- Drop any pre-existing policies with these names (idempotency)
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;

-- SELECT: only own rows, only for authenticated users
CREATE POLICY notes_select_own ON public.notes
  FOR SELECT TO authenticated
  USING (user_id = (SELECT auth.uid()));

-- INSERT: only rows where user_id is your own id
CREATE POLICY notes_insert_own ON public.notes
  FOR INSERT TO authenticated
  WITH CHECK (user_id = (SELECT auth.uid()));

-- UPDATE: only your rows, and can't reassign to someone else
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()));

-- DELETE: only your rows
CREATE POLICY notes_delete_own ON public.notes
  FOR DELETE TO authenticated
  USING (user_id = (SELECT auth.uid()));
SQL

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

psql "$SUPABASE_DB_URL" -c "SELECT policyname, cmd, roles, qual, with_check FROM pg_policies WHERE tablename='notes' ORDER BY policyname;" 2>&1

    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)

get_token() {
  curl -s "$SUPABASE_URL/auth/v1/token?grant_type=password" \
    -H "apikey: $SUPABASE_ANON_KEY" -H "Content-Type: application/json" \
    -d "{\"email\":\"$1\",\"password\":\"$2\"}" | jq -r .access_token
}
ALICE=$(get_token alice@cari.dev Passw0rd-alice-42)
BOB=$(get_token bob@cari.dev Passw0rd-bob-58)
echo "$ALICE" > /tmp/alice.jwt; echo "$BOB" > /tmp/bob.jwt
echo "alice token len: ${#ALICE}"; echo "bob token len: ${#BOB}"

echo "=== 1) anon SELECT (expect []) ==="
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,title" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY"

echo; echo "=== 2) alice SELECT (expect her 2 rows) ==="
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 (expect his 2 rows) ==="
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $BOB"

alice token len: 791
bob token len: 788
=== 1) anon SELECT (expect []) ===
[]
=== 2) alice SELECT (expect her 2 rows) ===
[{"id":1,"user_id":"58a0d373-1349-462a-9400-1ee158ab06e7","title":"Groceries"}, 
 {"id":2,"user_id":"58a0d373-1349-462a-9400-1ee158ab06e7","title":"Call plumber"}]
=== 3) bob SELECT (expect his 2 rows) ===
[{"id":3,"user_id":"29d628d5-d9cb-4a70-ac3d-1263c54d66c2","title":"Gym plan"}, 
 {"id":4,"user_id":"29d628d5-d9cb-4a70-ac3d-1263c54d66c2","title":"Trip ideas"}]

ALICE=$(cat /tmp/alice.jwt); BOB=$(cat /tmp/bob.jwt)
ALICE_ID=58a0d373-1349-462a-9400-1ee158ab06e7
BOB_ID=29d628d5-d9cb-4a70-ac3d-1263c54d66c2

echo "=== 4) alice INSERT with her own id (expect success) ==="
curl -s -w "\nHTTP:%{http_code}\n" "$SUPABASE_URL/rest/v1/notes" -X POST \
  -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-alice-own\",\"body\":\"x\"}"

echo "=== 5) alice INSERT with BOB's id (expect 403/policy violation) ==="
curl -s -w "\nHTTP:%{http_code}\n" "$SUPABASE_URL/rest/v1/notes" -X POST \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE" \
  -H "Content-Type: application/json" -H "Prefer: return=representation" \
  -d "{\"user_id\":\"$BOB_ID\",\"title\":\"TEST-alice-spoof\",\"body\":\"x\"}"

echo "=== 6) alice UPDATE bob's row id=3 (expect 0 rows affected) ==="
curl -s -w "\nHTTP:%{http_code}\n" "$SUPABASE_URL/rest/v1/notes?id=eq.3" -X PATCH \
  -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 row id=3 (expect 0 rows affected) ==="
curl -s -w "\nHTTP:%{http_code}\n" "$SUPABASE_URL/rest/v1/notes?id=eq.3" -X DELETE \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE" \
  -H "Prefer: return=representation"

echo "=== 8) alice UPDATE own row via reassign to bob (expect blocked by WITH CHECK) ==="
curl -s -w "\nHTTP:%{http_code}\n" "$SUPABASE_URL/rest/v1/notes?id=eq.1" -X PATCH \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE" \
  -H "Content-Type: application/json" -H "Prefer: return=representation" \
  -d "{\"user_id\":\"$BOB_ID\"}"

echo "=== 9) anon INSERT (expect blocked) ==="
curl -s -w "\nHTTP:%{http_code}\n" "$SUPABASE_URL/rest/v1/notes" -X POST \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d "{\"user_id\":\"$ALICE_ID\",\"title\":\"anon\",\"body\":\"x\"}"

=== 4) alice INSERT with her own id (expect success) ===
[{"id":5,"user_id":"58a0d373-1349-462a-9400-1ee158ab06e7","title":"TEST-alice-own","body":"x"}]
HTTP:201
=== 5) alice INSERT with BOB's id (expect 403/policy violation) ===
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}
HTTP:403
=== 6) alice UPDATE bob's row id=3 (expect 0 rows affected) ===
[]
HTTP:200
=== 7) alice DELETE bob's row id=3 (expect 0 rows affected) ===
[]
HTTP:200
=== 8) alice UPDATE own row via reassign to bob (expect blocked by WITH CHECK) ===
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}
HTTP:403
=== 9) anon INSERT (expect blocked) ===
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}
HTTP:401

psql "$SUPABASE_DB_URL" -c "DELETE FROM public.notes WHERE title='TEST-alice-own';" 2>&1
echo "=== final data (should be original 4 rows, unchanged) ==="
psql "$SUPABASE_DB_URL" -c "SELECT id, user_id, title FROM public.notes ORDER BY id;" 2>&1
psql "$SUPABASE_DB_URL" -c "SELECT setval(pg_get_serial_sequence('public.notes','id'), (SELECT MAX(id) FROM public.notes));" 2>&1

DELETE 1
=== final data (should be original 4 rows, unchanged) ===
 id |               user_id                |    title     
----+--------------------------------------+--------------
  1 | 58a0d373-1349-462a-9400-1ee158ab06e7 | Groceries
  2 | 58a0d373-1349-462a-9400-1ee158ab06e7 | Call plumber
  3 | 29d628d5-d9cb-4a70-ac3d-1263c54d66c2 | Gym plan
  4 | 29d628d5-d9cb-4a70-ac3d-1263c54d66c2 | Trip ideas
(4 rows)

 setval 
--------
      4
(1 row)