openai/gpt-5.6-sol · d1_rls_owner · rep 1

set -e
command -v psql || true
command -v curl || true
printf 'URL=%s\nREF=%s\nDB_URL_SET=%s\n' "$SUPABASE_URL" "$SUPABASE_PROJECT_REF" "${SUPABASE_DB_URL:+yes}"

/usr/bin/psql
/usr/bin/curl
URL=https://<ref>.supabase.co
REF=<ref>
DB_URL_SET=yes

set -e
curl -sS "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY" -w '\nHTTP:%{http_code}\n' | head -c 2000

[{"id":1,"user_id":"8afaf2e0-26e9-4c58-83e6-b5ec4532bc7c","title":"Groceries"}, 
 {"id":2,"user_id":"8afaf2e0-26e9-4c58-83e6-b5ec4532bc7c","title":"Call plumber"}, 
 {"id":3,"user_id":"907ba439-412d-47b0-a0cf-972a3c1e548a","title":"Gym plan"}, 
 {"id":4,"user_id":"907ba439-412d-47b0-a0cf-972a3c1e548a","title":"Trip ideas"}]
HTTP:200

set -euo pipefail
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "select current_user, current_database();" -c "select relrowsecurity, relforcerowsecurity from pg_class where oid='public.notes'::regclass;" -c "select policyname, permissive, roles, cmd, qual, with_check from pg_policies where schemaname='public' and tablename='notes';" -c "select grantee, privilege_type from information_schema.role_table_grants where table_schema='public' and table_name='notes' order by grantee, privilege_type;"

 current_user | current_database 
--------------+------------------
 postgres     | postgres
(1 row)

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

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

    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)

set -euo pipefail
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
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 (user_id = (SELECT auth.uid()));

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

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()));

-- Keep SELECT for anon so REST returns an empty result under RLS, but remove
-- all mutation and table-management capabilities from that role.
REVOKE INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON public.notes FROM anon;
-- Authenticated API clients need CRUD only.
REVOKE TRUNCATE, REFERENCES, TRIGGER ON public.notes FROM authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.notes TO authenticated;
GRANT SELECT ON public.notes TO anon;

COMMIT;
SQL

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off \
  -c "select relrowsecurity from pg_class where oid='public.notes'::regclass;" \
  -c "select policyname, roles, cmd, qual, with_check from pg_policies where schemaname='public' and tablename='notes' order by policyname;"

BEGIN
ALTER TABLE
DROP POLICY
DROP POLICY
DROP POLICY
DROP POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
REVOKE
REVOKE
GRANT
GRANT
COMMIT
 relrowsecurity 
----------------
 t
(1 row)

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

NOTICE:  policy "notes_select_own" for relation "public.notes" does not exist, skipping
NOTICE:  policy "notes_insert_own" for relation "public.notes" does not exist, skipping
NOTICE:  policy "notes_update_own" for relation "public.notes" does not exist, skipping
NOTICE:  policy "notes_delete_own" for relation "public.notes" does not exist, skipping

set -euo pipefail
work=$(mktemp -d); trap 'rm -rf "$work"' EXIT
login() {
  local email=$1 pass=$2 out=$3
  curl -sS --fail-with-body -X POST "$SUPABASE_URL/auth/v1/token?grant_type=password" \
    -H "apikey: $SUPABASE_ANON_KEY" -H 'Content-Type: application/json' \
    --data "{\"email\":\"$email\",\"password\":\"$pass\"}" > "$out"
}
login alice@cari.dev Passw0rd-alice-42 "$work/alice.json" &
login bob@cari.dev Passw0rd-bob-58 "$work/bob.json" &
wait
python3 - "$work" <<'PY'
import json,sys,os
w=sys.argv[1]
for n in ('alice','bob'):
 d=json.load(open(f'{w}/{n}.json'))
 open(f'{w}/{n}.token','w').write(d['access_token'])
 print(f"{n}_id={d['user']['id']}")
PY
alice_token=$(cat "$work/alice.token"); bob_token=$(cat "$work/bob.token")
alice_id=$(python3 -c "import json;print(json.load(open('$work/alice.json'))['user']['id'])")
bob_id=$(python3 -c "import json;print(json.load(open('$work/bob.json'))['user']['id'])")

request_get() { curl -sS --fail-with-body "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title&order=id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $1"; }
anon=$(request_get "$SUPABASE_ANON_KEY")
alice=$(request_get "$alice_token")
bob=$(request_get "$bob_token")
python3 - "$anon" "$alice" "$bob" "$alice_id" "$bob_id" <<'PY'
import json,sys
anon,alice,bob=map(json.loads,sys.argv[1:4]); aid,bid=sys.argv[4:]
assert anon == [], anon
assert len(alice)==2 and all(x['user_id']==aid for x in alice), alice
assert len(bob)==2 and all(x['user_id']==bid for x in bob), bob
print(f'anon rows: {len(anon)}')
print(f'alice rows: {len(alice)} (all owned by Alice)')
print(f'bob rows: {len(bob)} (all owned by Bob)')
PY

# Negative REST mutation tests: cross-owner UPDATE/DELETE affect zero rows.
patch_code=$(curl -sS -o "$work/patch" -w '%{http_code}' -X PATCH \
  "$SUPABASE_URL/rest/v1/notes?user_id=eq.$bob_id" \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $alice_token" \
  -H 'Content-Type: application/json' -H 'Prefer: return=representation' \
  --data '{"title":"MUST_NOT_APPEAR"}')
delete_code=$(curl -sS -o "$work/delete" -w '%{http_code}' -X DELETE \
  "$SUPABASE_URL/rest/v1/notes?user_id=eq.$bob_id" \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $alice_token" \
  -H 'Prefer: return=representation')
# Cross-owner insert must be rejected by WITH CHECK.
insert_code=$(curl -sS -o "$work/insert" -w '%{http_code}' -X POST \
  "$SUPABASE_URL/rest/v1/notes" \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $alice_token" \
  -H 'Content-Type: application/json' \
  --data "{\"user_id\":\"$bob_id\",\"title\":\"MUST_NOT_APPEAR\",\"body\":\"MUST_NOT_APPEAR\"}")
[ "$patch_code" = 200 ] && [ "$(cat "$work/patch")" = '[]' ]
[ "$delete_code" = 200 ] && [ "$(cat "$work/delete")" = '[]' ]
case "$insert_code" in 4*) ;; *) echo "Unexpected insert status $insert_code: $(cat "$work/insert")"; exit 1;; esac
printf 'cross-owner PATCH: HTTP %s, affected 0\ncross-owner DELETE: HTTP %s, affected 0\ncross-owner INSERT: HTTP %s, rejected\n' "$patch_code" "$delete_code" "$insert_code"

# Confirm all four original rows and contents remain, bypassing RLS as operator.
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -Atc "select 'total notes: '||count(*) from public.notes; select 'test-marker rows: '||count(*) from public.notes where title='MUST_NOT_APPEAR' or body='MUST_NOT_APPEAR';"

alice_id=8afaf2e0-26e9-4c58-83e6-b5ec4532bc7c
bob_id=907ba439-412d-47b0-a0cf-972a3c1e548a
anon rows: 0
alice rows: 2 (all owned by Alice)
bob rows: 2 (all owned by Bob)
cross-owner PATCH: HTTP 200, affected 0
cross-owner DELETE: HTTP 200, affected 0
cross-owner INSERT: HTTP 403, rejected
total notes: 4
test-marker rows: 0