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

set -e
printf 'project=%s\n' "$SUPABASE_PROJECT_REF"
psql --version
curl --version | head -1

project=<ref>
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
curl 7.88.1 (x86_64-pc-linux-gnu) libcurl/7.88.1 OpenSSL/3.0.20 zlib/1.2.13 brotli/1.0.9 zstd/1.5.4 libidn2/2.3.3 libpsl/0.21.2 (+libidn2/2.3.3) libssh2/1.10.0 nghttp2/1.52.0 librtmp/2.3 OpenLDAP/2.5.13

set -e
# Inspect current note counts without exposing note contents
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -Atc "select count(*) as total, count(distinct user_id) as owners from public.notes;"

4|2

set -euo pipefail
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;

ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;

-- Remove any pre-existing policies so no permissive policy can widen access.
DO $do$
DECLARE p record;
BEGIN
  FOR p IN
    SELECT policyname
    FROM pg_policies
    WHERE schemaname = 'public' AND tablename = 'notes'
  LOOP
    EXECUTE format('DROP POLICY %I ON public.notes', p.policyname);
  END LOOP;
END
$do$;

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

-- Preserve the normal authenticated REST access path; RLS applies per row.
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.notes TO authenticated;

COMMIT;
SQL

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "SELECT c.relrowsecurity, p.policyname, p.roles, p.cmd, p.qual, p.with_check FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace LEFT JOIN pg_policies p ON p.schemaname=n.nspname AND p.tablename=c.relname WHERE n.nspname='public' AND c.relname='notes' ORDER BY p.policyname;"

BEGIN
ALTER TABLE
DO
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
GRANT
COMMIT
 relrowsecurity |    policyname    |      roles      |  cmd   |                  qual                   |               with_check                
----------------+------------------+-----------------+--------+-----------------------------------------+-----------------------------------------
 t              | notes_delete_own | {authenticated} | DELETE | (( SELECT auth.uid() AS uid) = user_id) | 
 t              | notes_insert_own | {authenticated} | INSERT |                                         | (( SELECT auth.uid() AS uid) = user_id)
 t              | notes_select_own | {authenticated} | SELECT | (( SELECT auth.uid() AS uid) = user_id) | 
 t              | notes_update_own | {authenticated} | UPDATE | (( SELECT auth.uid() AS uid) = user_id) | (( SELECT auth.uid() AS uid) = user_id)
(4 rows)

set -euo pipefail
api="$SUPABASE_URL"
key="$SUPABASE_ANON_KEY"
login() {
  curl -fsS "$api/auth/v1/token?grant_type=password" \
    -H "apikey: $key" -H 'Content-Type: application/json' \
    --data "{\"email\":\"$1\",\"password\":\"$2\"}"
}
alice_json=$(login 'alice@cari.dev' 'Passw0rd-alice-42')
bob_json=$(login 'bob@cari.dev' 'Passw0rd-bob-58')
alice_token=$(jq -r .access_token <<<"$alice_json")
bob_token=$(jq -r .access_token <<<"$bob_json")
alice_id=$(jq -r .user.id <<<"$alice_json")
bob_id=$(jq -r .user.id <<<"$bob_json")

rest_get() {
  curl -fsS "$api/rest/v1/notes?select=id,user_id,title,body" \
    -H "apikey: $key" -H "Authorization: Bearer $1"
}
anon=$(curl -fsS "$api/rest/v1/notes?select=id" -H "apikey: $key")
alice=$(rest_get "$alice_token")
bob=$(rest_get "$bob_token")

# Cross-owner reads must return no rows.
alice_bob=$(curl -fsS "$api/rest/v1/notes?select=id&user_id=eq.$bob_id" -H "apikey: $key" -H "Authorization: Bearer $alice_token")
bob_alice=$(curl -fsS "$api/rest/v1/notes?select=id&user_id=eq.$alice_id" -H "apikey: $key" -H "Authorization: Bearer $bob_token")

# Attempt a forbidden foreign-owner insert. This must fail and cannot change data.
status=$(curl -sS -o /tmp/foreign_insert_response -w '%{http_code}' "$api/rest/v1/notes" \
  -X POST -H "apikey: $key" -H "Authorization: Bearer $alice_token" \
  -H 'Content-Type: application/json' -H 'Prefer: return=minimal' \
  --data "{\"user_id\":\"$bob_id\",\"title\":\"RLS forbidden test\",\"body\":\"must not insert\"}")

jq -e 'length == 0' <<<"$anon" >/dev/null
jq -e --arg uid "$alice_id" 'length == 2 and all(.user_id == $uid)' <<<"$alice" >/dev/null
jq -e --arg uid "$bob_id" 'length == 2 and all(.user_id == $uid)' <<<"$bob" >/dev/null
jq -e 'length == 0' <<<"$alice_bob" >/dev/null
jq -e 'length == 0' <<<"$bob_alice" >/dev/null
case "$status" in 4*) ;; *) echo "Foreign insert unexpectedly returned HTTP $status"; cat /tmp/foreign_insert_response; exit 1;; esac

# Confirm untouched row count remains four.
count=$(psql "$SUPABASE_DB_URL" -Atc 'select count(*) from public.notes')
[ "$count" = 4 ]
printf 'anon_rows=0 alice_owned_rows=2 bob_owned_rows=2 cross_reads=0 foreign_insert_http=%s final_rows=%s\n' "$status" "$count"

anon_rows=0 alice_owned_rows=2 bob_owned_rows=2 cross_reads=0 foreign_insert_http=403 final_rows=4