openai/gpt-5.6-luna · d1_rls_owner · rep 2

printf 'url=%s\nref=%s\n' "${SUPABASE_URL:+set}" "${SUPABASE_PROJECT_REF:+set}"; command -v psql || true; command -v curl || true

url=set
ref=set
/usr/bin/psql
/usr/bin/curl

env | grep '^SUPABASE_' | sed -E '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>

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -c "select schemaname, tablename, rowsecurity, (select count(*) from public.notes) as note_count from pg_tables where schemaname='public' and tablename='notes'; select policyname, cmd, roles, qual, with_check from pg_policies where schemaname='public' and tablename='notes';"

 schemaname | tablename | rowsecurity | note_count 
------------+-----------+-------------+------------
 public     | notes     | f           |          4
(1 row)

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

curl -sS -o /tmp/anon.json -w 'anon_http=%{http_code}\n' "${SUPABASE_URL}/rest/v1/notes?select=id,user_id,title,body" -H "apikey: ${SUPABASE_ANON_KEY}" -H "Authorization: Bearer ${SUPABASE_ANON_KEY}"; cat /tmp/anon.json

anon_http=200
[{"id":1,"user_id":"5db26a71-0ece-4078-8934-f5bdb0bec86c","title":"Groceries","body":"milk, eggs, coffee"}, 
 {"id":2,"user_id":"5db26a71-0ece-4078-8934-f5bdb0bec86c","title":"Call plumber","body":"kitchen sink leaks"}, 
 {"id":3,"user_id":"e14695d4-9f6c-47f2-866c-2f6c5cf3221f","title":"Gym plan","body":"push pull legs"}, 
 {"id":4,"user_id":"e14695d4-9f6c-47f2-866c-2f6c5cf3221f","title":"Trip ideas","body":"mountains in october"}]

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X <<'SQL'
BEGIN;
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notes FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS notes_owner_select ON public.notes;
DROP POLICY IF EXISTS notes_owner_insert ON public.notes;
DROP POLICY IF EXISTS notes_owner_update ON public.notes;
DROP POLICY IF EXISTS notes_owner_delete ON public.notes;
CREATE POLICY notes_owner_select ON public.notes
  FOR SELECT TO authenticated
  USING (user_id = (select auth.uid()));
CREATE POLICY notes_owner_insert ON public.notes
  FOR INSERT TO authenticated
  WITH CHECK (user_id = (select auth.uid()));
CREATE POLICY notes_owner_update ON public.notes
  FOR UPDATE TO authenticated
  USING (user_id = (select auth.uid()))
  WITH CHECK (user_id = (select auth.uid()));
CREATE POLICY notes_owner_delete ON public.notes
  FOR DELETE TO authenticated
  USING (user_id = (select auth.uid()));
COMMIT;
SQL
PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -X -c "select relrowsecurity, relforcerowsecurity from pg_class where oid='public.notes'::regclass; select policyname, cmd, roles, qual, with_check from pg_policies where schemaname='public' and tablename='notes' order by policyname;"

BEGIN
ALTER TABLE
ALTER TABLE
DROP POLICY
DROP POLICY
DROP POLICY
DROP POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
COMMIT
 relrowsecurity | relforcerowsecurity 
----------------+---------------------
 t              | t
(1 row)

     policyname     |  cmd   |      roles      |                  qual                   |               with_check                
--------------------+--------+-----------------+-----------------------------------------+-----------------------------------------
 notes_owner_delete | DELETE | {authenticated} | (user_id = ( SELECT auth.uid() AS uid)) | 
 notes_owner_insert | INSERT | {authenticated} |                                         | (user_id = ( SELECT auth.uid() AS uid))
 notes_owner_select | SELECT | {authenticated} | (user_id = ( SELECT auth.uid() AS uid)) | 
 notes_owner_update | UPDATE | {authenticated} | (user_id = ( SELECT auth.uid() AS uid)) | (user_id = ( SELECT auth.uid() AS uid))
(4 rows)

NOTICE:  policy "notes_owner_select" for relation "public.notes" does not exist, skipping
NOTICE:  policy "notes_owner_insert" for relation "public.notes" does not exist, skipping
NOTICE:  policy "notes_owner_update" for relation "public.notes" does not exist, skipping
NOTICE:  policy "notes_owner_delete" for relation "public.notes" does not exist, skipping

set -u
base="$SUPABASE_URL"
login() { curl -fsS "$base/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H 'Content-Type: application/json' --data "{\"email\":\"$1\",\"password\":\"$2\"}"; }
alice=$(login alice@cari.dev Passw0rd-alice-42)
bob=$(login bob@cari.dev Passw0rd-bob-58)
python3 - "$alice" "$bob" <<'PY'
import json,sys
for name,s in [('alice',sys.argv[1]),('bob',sys.argv[2])]:
 d=json.loads(s); print(name, 'login_ok=',bool(d.get('access_token')), 'uid=',d.get('user',{}).get('id'))
 open('/tmp/'+name+'.token','w').write(d.get('access_token',''))
PY
for u in alice bob; do
 t=$(cat /tmp/$u.token)
 curl -sS -o /tmp/$u.notes -w "$u http=%{http_code} " "$base/rest/v1/notes?select=id,user_id,title&order=id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $t"
 python3 -c 'import json,sys; x=json.load(open(sys.argv[1])); print("rows=",len(x),"ids=",[r["id"] for r in x],"uids=",sorted(set(r["user_id"] for r in x)))' /tmp/$u.notes
done
# A mismatched-owner insert: must fail, and use a fixed id unlikely to conflict; no data should be changed.
bobuid=$(python3 -c 'import json; print(json.loads(open("/tmp/bob.notes").read())[0]["user_id"])')
alice_token=$(cat /tmp/alice.token)
printf 'mismatch_insert: '
curl -sS -o /tmp/insert.err -w 'http=%{http_code} body=' "$base/rest/v1/notes" -X POST -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $alice_token" -H 'Content-Type: application/json' -H 'Prefer: return=minimal' --data "{\"user_id\":\"$bobuid\",\"title\":\"SHOULD_FAIL\",\"body\":\"SHOULD_FAIL\"}"; cat /tmp/insert.err; echo

alice login_ok= True uid= 5db26a71-0ece-4078-8934-f5bdb0bec86c
bob login_ok= True uid= e14695d4-9f6c-47f2-866c-2f6c5cf3221f
alice http=200 rows= 2 ids= [1, 2] uids= ['5db26a71-0ece-4078-8934-f5bdb0bec86c']
bob http=200 rows= 2 ids= [3, 4] uids= ['e14695d4-9f6c-47f2-866c-2f6c5cf3221f']
mismatch_insert: http=403 body={"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}

curl -sS -o /tmp/anon2.json -w 'anon http=%{http_code} body=' "$SUPABASE_URL/rest/v1/notes?select=id,user_id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY"; cat /tmp/anon2.json; echo
PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -X -c 'select count(*) as note_count from public.notes;'

anon http=200 body=[]
 note_count 
------------
          4
(1 row)