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

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

command -v psql && command -v curl

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

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "select schemaname, tablename, rowsecurity, forcerowsecurity from pg_tables where schemaname='public' and tablename='notes'; select policyname, permissive, roles, cmd, qual, with_check from pg_policies where schemaname='public' and tablename='notes';"

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

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "select grantee, privilege_type from information_schema.role_table_grants where table_schema='public' and table_name='notes' order by grantee, privilege_type;"

    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)

curl -sS -o /tmp/anon_notes.json -w '%{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"; head -c 500 /tmp/anon_notes.json

200
[{"id":1,"user_id":"d0be17e7-4036-49ed-935f-db5f876c6441","title":"Groceries","body":"milk, eggs, coffee"}, 
 {"id":2,"user_id":"d0be17e7-4036-49ed-935f-db5f876c6441","title":"Call plumber","body":"kitchen sink leaks"}, 
 {"id":3,"user_id":"1a28313b-8259-45e6-adc5-5fc056b84290","title":"Gym plan","body":"push pull legs"}, 
 {"id":4,"user_id":"1a28313b-8259-45e6-adc5-5fc056b84290","title":"Trip ideas","body":"mountains in october"}]

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "select schemaname, tablename, rowsecurity, tablespace from pg_tables where schemaname='public' and tablename='notes'; select policyname, permissive, roles, cmd, qual, with_check from pg_policies where schemaname='public' and tablename='notes';"

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

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

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "select column_name,data_type,is_nullable,column_default from information_schema.columns where table_schema='public' and table_name='notes' order by ordinal_position;"

 column_name | data_type | is_nullable | column_default 
-------------+-----------+-------------+----------------
 id          | bigint    | NO          | 
 user_id     | uuid      | NO          | 
 title       | text      | NO          | 
 body        | text      | NO          | ''::text
(4 rows)

curl -sS "$SUPABASE_URL/auth/v1/settings" -H "apikey: $SUPABASE_ANON_KEY" | head -c 1000

{"external":{"anonymous_users":false,"apple":false,"azure":false,"bitbucket":false,"discord":false,"facebook":false,"snapchat":false,"figma":false,"fly":false,"github":false,"gitlab":false,"google":false,"keycloak":false,"kakao":false,"linkedin":false,"linkedin_oidc":false,"notion":false,"spotify":false,"slack":false,"slack_oidc":false,"workos":false,"twitch":false,"twitter":false,"email":true,"phone":false,"zoom":false},"disable_signup":false,"mailer_autoconfirm":false,"phone_autoconfirm":false,"sms_provider":"twilio","saml_enabled":false,"saml_private_key_next_configured":true,"passkeys_enabled":false}

psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can view their own notes" ON public.notes;
DROP POLICY IF EXISTS "Users can insert their own notes" ON public.notes;
DROP POLICY IF EXISTS "Users can update their own notes" ON public.notes;
DROP POLICY IF EXISTS "Users can delete their own notes" ON public.notes;
CREATE POLICY "Users can view their own notes"
  ON public.notes FOR SELECT TO authenticated
  USING ((select auth.uid()) = user_id);
CREATE POLICY "Users can insert their own notes"
  ON public.notes FOR INSERT TO authenticated
  WITH CHECK ((select auth.uid()) = user_id);
CREATE POLICY "Users can update their own notes"
  ON public.notes FOR UPDATE TO authenticated
  USING ((select auth.uid()) = user_id)
  WITH CHECK ((select auth.uid()) = user_id);
CREATE POLICY "Users can delete their own notes"
  ON public.notes FOR DELETE TO authenticated
  USING ((select auth.uid()) = user_id);
COMMIT;
SQL
psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -c "select schemaname,tablename,rowsecurity from pg_tables where schemaname='public' and tablename='notes'; 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
COMMIT
 schemaname | tablename | rowsecurity 
------------+-----------+-------------
 public     | notes     | t
(1 row)

            policyname            |      roles      |  cmd   |                  qual                   |               with_check                
----------------------------------+-----------------+--------+-----------------------------------------+-----------------------------------------
 Users can delete their own notes | {authenticated} | DELETE | (( SELECT auth.uid() AS uid) = user_id) | 
 Users can insert their own notes | {authenticated} | INSERT |                                         | (( SELECT auth.uid() AS uid) = user_id)
 Users can update their own notes | {authenticated} | UPDATE | (( SELECT auth.uid() AS uid) = user_id) | (( SELECT auth.uid() AS uid) = user_id)
 Users can view their own notes   | {authenticated} | SELECT | (( SELECT auth.uid() AS uid) = user_id) | 
(4 rows)

NOTICE:  policy "Users can view their own notes" for relation "public.notes" does not exist, skipping
NOTICE:  policy "Users can insert their own notes" for relation "public.notes" does not exist, skipping
NOTICE:  policy "Users can update their own notes" for relation "public.notes" does not exist, skipping
NOTICE:  policy "Users can delete their own notes" for relation "public.notes" does not exist, skipping

curl -sS -w '\nHTTP %{http_code}\n' "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title,body&order=id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY"

[]
HTTP 200

curl -sS "$SUPABASE_URL/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H 'Content-Type: application/json' --data '{"email":"alice@cari.dev","password":"Passw0rd-alice-42"}' > /tmp/alice.json; python3 - <<'PY'
import json
x=json.load(open('/tmp/alice.json')); print('alice token:', bool(x.get('access_token')), 'user:', x.get('user',{}).get('id'))
PY

alice token: True user: d0be17e7-4036-49ed-935f-db5f876c6441

curl -sS "$SUPABASE_URL/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H 'Content-Type: application/json' --data '{"email":"bob@cari.dev","password":"Passw0rd-bob-58"}' > /tmp/bob.json; python3 - <<'PY'
import json
x=json.load(open('/tmp/bob.json')); print('bob token:', bool(x.get('access_token')), 'user:', x.get('user',{}).get('id'))
PY

bob token: True user: 1a28313b-8259-45e6-adc5-5fc056b84290

set -u
alice=$(python3 -c 'import json; print(json.load(open("/tmp/alice.json"))["access_token"])')
bob=$(python3 -c 'import json; print(json.load(open("/tmp/bob.json"))["access_token"])')
echo 'Alice own rows:'
curl -sS -w '\nHTTP %{http_code}\n' "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title&order=id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $alice"
echo 'Alice filtered to Bob rows (must be empty):'
curl -sS -w '\nHTTP %{http_code}\n' "$SUPABASE_URL/rest/v1/notes?select=id,user_id&user_id=eq.1a28313b-8259-45e6-adc5-5fc056b84290" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $alice"
echo 'Alice malicious insert (must fail):'
curl -sS -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=minimal' --data '{"user_id":"1a28313b-8259-45e6-adc5-5fc056b84290","title":"should not insert","body":"should not insert"}'
echo 'Bob own rows:'
curl -sS -w '\nHTTP %{http_code}\n' "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title&order=id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $bob"

Alice own rows:
[{"id":1,"user_id":"d0be17e7-4036-49ed-935f-db5f876c6441","title":"Groceries"}, 
 {"id":2,"user_id":"d0be17e7-4036-49ed-935f-db5f876c6441","title":"Call plumber"}]
HTTP 200
Alice filtered to Bob rows (must be empty):
[]
HTTP 200
Alice malicious insert (must fail):
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}
HTTP 403
Bob own rows:
[{"id":3,"user_id":"1a28313b-8259-45e6-adc5-5fc056b84290","title":"Gym plan"}, 
 {"id":4,"user_id":"1a28313b-8259-45e6-adc5-5fc056b84290","title":"Trip ideas"}]
HTTP 200