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

env | sed -n 's/^SUPABASE_[A-Z_]*=.*/&/p' | 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>

printf '%s\n' "$SUPABASE_DB_URL" | sed -E 's#(postgres(ql)?://[^:]+:)[^@]+@#\1<redacted>@#'

postgresql://postgres.<ref>:<redacted>@aws-0-us-east-1.pooler.supabase.com:5432/postgres

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "select schemaname, tablename, rowsecurity, force_row_security 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'; select column_name, data_type from information_schema.columns where table_schema='public' and table_name='notes' order by ordinal_position;"

ERROR:  column "force_row_security" does not exist
LINE 1: select schemaname, tablename, rowsecurity, force_row_securit...
                                                   ^

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "select schemaname, tablename, rowsecurity, forcerowsecurity 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'; select column_name, data_type from information_schema.columns where table_schema='public' and table_name='notes' order by ordinal_position;"

ERROR:  column "forcerowsecurity" does not exist
LINE 1: select schemaname, tablename, rowsecurity, forcerowsecurity ...
                                                   ^

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "select n.nspname, c.relname, c.relrowsecurity, c.relforcerowsecurity from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname='public' and c.relname='notes'; select policyname, cmd, roles, qual, with_check from pg_policies where schemaname='public' and tablename='notes'; select column_name, data_type from information_schema.columns where table_schema='public' and table_name='notes' order by ordinal_position;"

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

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

 column_name | data_type 
-------------+-----------
 id          | bigint
 user_id     | uuid
 title       | text
 body        | text
(4 rows)

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
ALTER TABLE public.notes ENABLE 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 ((select auth.uid()) = user_id);
CREATE POLICY notes_owner_insert ON public.notes
  FOR INSERT TO authenticated
  WITH CHECK ((select auth.uid()) = user_id);
CREATE POLICY notes_owner_update ON public.notes
  FOR UPDATE TO authenticated
  USING ((select auth.uid()) = user_id)
  WITH CHECK ((select auth.uid()) = user_id);
CREATE POLICY notes_owner_delete ON public.notes
  FOR DELETE TO authenticated
  USING ((select auth.uid()) = user_id);
COMMIT;
SQL
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "select c.relrowsecurity, c.relforcerowsecurity from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname='public' and c.relname='notes'; select policyname, cmd, roles, 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
COMMIT
 relrowsecurity | relforcerowsecurity 
----------------+---------------------
 t              | f
(1 row)

     policyname     |  cmd   |      roles      |                  qual                   |               with_check                
--------------------+--------+-----------------+-----------------------------------------+-----------------------------------------
 notes_owner_delete | DELETE | {authenticated} | (( SELECT auth.uid() AS uid) = user_id) | 
 notes_owner_insert | INSERT | {authenticated} |                                         | (( SELECT auth.uid() AS uid) = user_id)
 notes_owner_select | SELECT | {authenticated} | (( SELECT auth.uid() AS uid) = user_id) | 
 notes_owner_update | UPDATE | {authenticated} | (( SELECT auth.uid() AS uid) = user_id) | (( SELECT auth.uid() AS uid) = user_id)
(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 -sS -f "$base/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H 'Content-Type: application/json' --data "$(printf '%s' "{\"email\":\"$1\",\"password\":\"$2\"}")"; }
alice=$(login 'alice@cari.dev' 'Passw0rd-alice-42')
bob=$(login 'bob@cari.dev' 'Passw0rd-bob-58')
at=$(printf '%s' "$alice" | python3 -c 'import json,sys; print(json.load(sys.stdin)["access_token"])')
bt=$(printf '%s' "$bob" | python3 -c 'import json,sys; print(json.load(sys.stdin)["access_token"])')
get() { curl -sS -w '\nHTTP %{http_code}\n' "$base/rest/v1/notes?select=id,user_id,title,body&order=id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $1"; }
echo 'ANON:'; curl -sS -w '\nHTTP %{http_code}\n' "$base/rest/v1/notes?select=id,user_id,title,body&order=id" -H "apikey: $SUPABASE_ANON_KEY"
echo 'ALICE:'; get "$at"
echo 'BOB:'; get "$bt"
# Confirm hostile insert is rejected (no row can be created; request has no effect).
buid=$(printf '%s' "$bob" | python3 -c 'import json,sys; print(json.load(sys.stdin)["user"]["id"])')
echo 'ALICE_CROSS_USER_INSERT:'; curl -sS -w '\nHTTP %{http_code}\n' -X POST "$base/rest/v1/notes" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $at" -H 'Content-Type: application/json' -H 'Prefer: return=minimal' --data "$(printf '%s' "{\"user_id\":\"$buid\",\"title\":\"RLS test\",\"body\":\"must reject\"}")"

ANON:
[]
HTTP 200
ALICE:
[{"id":1,"user_id":"0a617f51-c64a-4ca2-b329-4136cc8882b9","title":"Groceries","body":"milk, eggs, coffee"}, 
 {"id":2,"user_id":"0a617f51-c64a-4ca2-b329-4136cc8882b9","title":"Call plumber","body":"kitchen sink leaks"}]
HTTP 200
BOB:
[{"id":3,"user_id":"7d75819d-8f7c-4c67-a92c-89295aa2a2fb","title":"Gym plan","body":"push pull legs"}, 
 {"id":4,"user_id":"7d75819d-8f7c-4c67-a92c-89295aa2a2fb","title":"Trip ideas","body":"mountains in october"}]
HTTP 200
ALICE_CROSS_USER_INSERT:
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}
HTTP 403