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

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

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

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -Atc "select current_user, current_database(); 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 polname, polcmd, polroles::regrole[], pg_get_expr(polqual,polrelid), pg_get_expr(polwithcheck,polrelid) from pg_policy where polrelid='public.notes'::regclass order by polname;"

postgres|postgres
f|f

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -X -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 ((SELECT auth.uid()) = user_id);

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

CREATE POLICY notes_update_own
ON public.notes
FOR UPDATE
TO authenticated
USING ((SELECT auth.uid()) = user_id)
WITH CHECK ((SELECT auth.uid()) = user_id);

CREATE POLICY notes_delete_own
ON public.notes
FOR DELETE
TO authenticated
USING ((SELECT auth.uid()) = user_id);
COMMIT;
SQL

BEGIN
ALTER TABLE
DROP POLICY
DROP POLICY
DROP POLICY
DROP POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
COMMIT

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
command -v jq
ALICE_JSON=$(curl -sS --fail-with-body "$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"}')
BOB_JSON=$(curl -sS --fail-with-body "$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"}')
ALICE_TOKEN=$(jq -r .access_token <<<"$ALICE_JSON"); ALICE_ID=$(jq -r .user.id <<<"$ALICE_JSON")
BOB_TOKEN=$(jq -r .access_token <<<"$BOB_JSON"); BOB_ID=$(jq -r .user.id <<<"$BOB_JSON")

rest_get() { curl -sS --fail-with-body "$SUPABASE_URL/rest/v1/notes?select=id,user_id,title,body&order=id" -H "apikey: $SUPABASE_ANON_KEY" "$@"; }
ANON=$(rest_get)
ALICE=$(rest_get -H "Authorization: Bearer $ALICE_TOKEN")
BOB=$(rest_get -H "Authorization: Bearer $BOB_TOKEN")

# Attempt a forbidden cross-owner insert. It must fail and therefore cannot alter data.
set +e
INSERT_OUT=$(curl -sS -w '\n%{http_code}' "$SUPABASE_URL/rest/v1/notes" \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE_TOKEN" \
  -H 'Content-Type: application/json' -H 'Prefer: return=representation' \
  --data "{\"user_id\":\"$BOB_ID\",\"title\":\"RLS forbidden probe\",\"body\":\"must not be inserted\"}")
set -e
INSERT_CODE=$(tail -n1 <<<"$INSERT_OUT"); INSERT_BODY=$(sed '$d' <<<"$INSERT_OUT")

# Cross-owner update/delete filters should affect no visible rows (and preserve data).
BOB_NOTE_ID=$(jq -r '.[0].id' <<<"$BOB")
PATCH=$(curl -sS --fail-with-body -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.$BOB_NOTE_ID" \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE_TOKEN" \
  -H 'Content-Type: application/json' -H 'Prefer: return=representation' \
  --data '{"title":"RLS forbidden probe"}')
DELETE=$(curl -sS --fail-with-body -X DELETE "$SUPABASE_URL/rest/v1/notes?id=eq.$BOB_NOTE_ID" \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE_TOKEN" \
  -H 'Prefer: return=representation')

# Verify all invariants and unchanged counts.
test "$(jq length <<<"$ANON")" -eq 0
test "$(jq length <<<"$ALICE")" -eq 2
test "$(jq length <<<"$BOB")" -eq 2
jq -e --arg uid "$ALICE_ID" 'all(.[]; .user_id == $uid)' >/dev/null <<<"$ALICE"
jq -e --arg uid "$BOB_ID" 'all(.[]; .user_id == $uid)' >/dev/null <<<"$BOB"
test "$INSERT_CODE" -ge 400
test "$(jq length <<<"$PATCH")" -eq 0
test "$(jq length <<<"$DELETE")" -eq 0
AFTER=$(rest_get -H "Authorization: Bearer $BOB_TOKEN")
test "$(jq length <<<"$AFTER")" -eq 2
jq -e --arg id "$BOB_NOTE_ID" --arg title "$(jq -r '.[0].title' <<<"$BOB")" 'any(.[]; ((.id|tostring) == $id and .title == $title))' >/dev/null <<<"$AFTER"
printf 'anon_rows=%s alice_rows=%s bob_rows=%s cross_insert_http=%s cross_patch_rows=%s cross_delete_rows=%s data_unchanged=yes\n' \
  "$(jq length <<<"$ANON")" "$(jq length <<<"$ALICE")" "$(jq length <<<"$BOB")" "$INSERT_CODE" "$(jq length <<<"$PATCH")" "$(jq length <<<"$DELETE")"

/usr/bin/jq
anon_rows=0 alice_rows=2 bob_rows=2 cross_insert_http=403 cross_patch_rows=0 cross_delete_rows=0 data_unchanged=yes

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$SUPABASE_DB_URL" -X -v ON_ERROR_STOP=1 -P pager=off -c "SELECT c.relrowsecurity AS rls_enabled, p.polname, p.polcmd, p.polroles::regrole[] AS roles, pg_get_expr(p.polqual,p.polrelid) AS using_expr, pg_get_expr(p.polwithcheck,p.polrelid) AS check_expr FROM pg_class c LEFT JOIN pg_policy p ON p.polrelid=c.oid WHERE c.oid='public.notes'::regclass ORDER BY p.polcmd;" -c "SELECT privilege_type FROM information_schema.role_table_grants WHERE table_schema='public' AND table_name='notes' AND grantee='authenticated' ORDER BY privilege_type;"

 rls_enabled |     polname      | polcmd |      roles      |               using_expr                |               check_expr                
-------------+------------------+--------+-----------------+-----------------------------------------+-----------------------------------------
 t           | notes_insert_own | a      | {authenticated} |                                         | (( SELECT auth.uid() AS uid) = user_id)
 t           | notes_delete_own | d      | {authenticated} | (( SELECT auth.uid() AS uid) = user_id) | 
 t           | notes_select_own | r      | {authenticated} | (( SELECT auth.uid() AS uid) = user_id) | 
 t           | notes_update_own | w      | {authenticated} | (( SELECT auth.uid() AS uid) = user_id) | (( SELECT auth.uid() AS uid) = user_id)
(4 rows)

 privilege_type 
----------------
 DELETE
 INSERT
 REFERENCES
 SELECT
 TRIGGER
 TRUNCATE
 UPDATE
(7 rows)