moonshotai/kimi-k3 · d1_rls_owner · rep 3

env | grep -i supabase | sed 's/\(KEY=\).\{10\}.*/\1<redacted>/; s/\(PASSWORD=\).*/\1<redacted>/; s|\(DB_URL=postgres://postgres:<redacted>@]*|\1<redacted>|'

SUPABASE_DB_PASSWORD=<redacted>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted>

which psql curl jq; psql --version 2>/dev/null; curl --version | head -1

/usr/bin/psql
/usr/bin/curl
/usr/bin/jq
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

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "
SELECT schemaname, tablename, rowsecurity AS rls_enabled, forcerowsecurity AS rls_forced
FROM pg_tables WHERE tablename='notes';
" -c "
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies WHERE tablename='notes' ORDER BY policyname;
" -c "
SELECT grantee, privilege_type FROM information_schema.role_table_grants
WHERE table_schema='public' AND table_name='notes' ORDER BY grantee, privilege_type;
" -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;
"

ERROR:  column "forcerowsecurity" does not exist
LINE 2: ...chemaname, tablename, rowsecurity AS rls_enabled, forcerowse...
                                                             ^

echo '--- anon GET /rest/v1/notes (before fix) ---'
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,title,user_id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY" | jq '.'

--- anon GET /rest/v1/notes (before fix) ---
[
  {
    "id": 1,
    "title": "Groceries",
    "user_id": "37ba4a7c-9597-4be3-8304-db4aefb26e38"
  },
  {
    "id": 2,
    "title": "Call plumber",
    "user_id": "37ba4a7c-9597-4be3-8304-db4aefb26e38"
  },
  {
    "id": 3,
    "title": "Gym plan",
    "user_id": "c27c5886-2160-41ab-b5d8-5882c59f6bc3"
  },
  {
    "id": 4,
    "title": "Trip ideas",
    "user_id": "c27c5886-2160-41ab-b5d8-5882c59f6bc3"
  }
]

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "
SELECT n.nspname AS schema, c.relname AS table, c.relrowsecurity AS rls_enabled, c.relforcerowsecurity AS rls_forced
FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname='public' AND c.relname='notes';
" -c "
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies WHERE tablename='notes' ORDER BY policyname;
" -c "
SELECT grantee, privilege_type FROM information_schema.role_table_grants
WHERE table_schema='public' AND table_name='notes' ORDER BY grantee, privilege_type;
" -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;
"

 schema | table | rls_enabled | rls_forced 
--------+-------+-------------+------------
 public | notes | f           | f
(1 row)

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

    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)

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

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;

-- 1. Enforce row-level security on the table
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;

-- 2. Owner-only policies for authenticated users (anon role gets none -> sees/does nothing)
CREATE POLICY "notes_select_own" ON public.notes
    FOR SELECT TO authenticated
    USING (user_id = auth.uid());

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

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

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

COMMIT;
SQL

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

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "
SELECT c.relrowsecurity AS rls_enabled FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname='public' AND c.relname='notes';
" -c "
SELECT policyname, cmd, roles, qual, with_check FROM pg_policies
WHERE schemaname='public' AND tablename='notes' ORDER BY policyname;
" -c "
SELECT id, email FROM auth.users ORDER BY email;
"

 rls_enabled 
-------------
 t
(1 row)

    policyname    |  cmd   |      roles      |          qual          |       with_check       
------------------+--------+-----------------+------------------------+------------------------
 notes_delete_own | DELETE | {authenticated} | (user_id = auth.uid()) | 
 notes_insert_own | INSERT | {authenticated} |                        | (user_id = auth.uid())
 notes_select_own | SELECT | {authenticated} | (user_id = auth.uid()) | 
 notes_update_own | UPDATE | {authenticated} | (user_id = auth.uid()) | (user_id = auth.uid())
(4 rows)

                  id                  |     email      
--------------------------------------+----------------
 37ba4a7c-9597-4be3-8304-db4aefb26e38 | alice@cari.dev
 c27c5886-2160-41ab-b5d8-5882c59f6bc3 | bob@cari.dev
(2 rows)

echo '--- anon GET after fix (expect []) ---'
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,title,user_id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY" | jq '.'
echo '--- anon INSERT attempt (expect error) ---'
curl -s -X POST "$SUPABASE_URL/rest/v1/notes" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY" -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"user_id":"37ba4a7c-9597-4be3-8304-db4aefb26e38","title":"anon-hack","body":"x"}' | jq '.'

--- anon GET after fix (expect []) ---
[]
--- anon INSERT attempt (expect error) ---
{
  "code": "42501",
  "details": null,
  "hint": null,
  "message": "new row violates row-level security policy for table \"notes\""
}

ALICE_TOKEN=$(curl -s -X POST "$SUPABASE_URL/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H "Content-Type: application/json" -d '{"email":"alice@cari.dev","password":"Passw0rd-alice-42"}' | jq -r '.access_token')
BOB_TOKEN=$(curl -s -X POST "$SUPABASE_URL/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H "Content-Type: application/json" -d '{"email":"bob@cari.dev","password":"Passw0rd-bob-58"}' | jq -r '.access_token')
echo "alice token len: ${#ALICE_TOKEN}, bob token len: ${#BOB_TOKEN}"
echo "$ALICE_TOKEN" > /tmp/alice_token; echo "$BOB_TOKEN" > /tmp/bob_token

echo '=== 1) Alice SELECT (expect only ids 1,2) ==='
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,title,user_id&order=id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE_TOKEN" | jq -c '.'

echo '=== 2) Alice tries to read Bob'"'"'s notes via explicit filter (expect []) ==='
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,title&user_id=eq.c27c5886-2160-41ab-b5d8-5882c59f6bc3" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $ALICE_TOKEN" | jq -c '.'

echo '=== 3) Bob SELECT (expect only ids 3,4) ==='
curl -s "$SUPABASE_URL/rest/v1/notes?select=id,title,user_id&order=id" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $BOB_TOKEN" | jq -c '.'

alice token len: 791, bob token len: 788
=== 1) Alice SELECT (expect only ids 1,2) ===
[{"id":1,"title":"Groceries","user_id":"37ba4a7c-9597-4be3-8304-db4aefb26e38"},{"id":2,"title":"Call plumber","user_id":"37ba4a7c-9597-4be3-8304-db4aefb26e38"}]
=== 2) Alice tries to read Bob's notes via explicit filter (expect []) ===
[]
=== 3) Bob SELECT (expect only ids 3,4) ===
[{"id":3,"title":"Gym plan","user_id":"c27c5886-2160-41ab-b5d8-5882c59f6bc3"},{"id":4,"title":"Trip ideas","user_id":"c27c5886-2160-41ab-b5d8-5882c59f6bc3"}]

ALICE_TOKEN=$(cat /tmp/alice_token); BOB_TOKEN=$(cat /tmp/bob_token)
API=(-H "apikey: $SUPABASE_ANON_KEY" -H "Content-Type: application/json")
ALICE_ID="37ba4a7c-9597-4be3-8304-db4aefb26e38"; BOB_ID="c27c5886-2160-41ab-b5d8-5882c59f6bc3"

echo '=== 4) Alice INSERT with her own user_id (expect success; will delete after) ==='
NEW_ID=$(curl -s -X POST "$SUPABASE_URL/rest/v1/notes" "${API[@]}" -H "Authorization: Bearer $ALICE_TOKEN" -H "Prefer: return=representation" -d "{\"user_id\":\"$ALICE_ID\",\"title\":\"__rls_test__\",\"body\":\"tmp\"}" | jq -r '.[0].id // empty')
echo "inserted id: $NEW_ID"

echo '=== 5) Alice INSERT with Bob'"'"'s user_id (expect RLS error) ==='
curl -s -X POST "$SUPABASE_URL/rest/v1/notes" "${API[@]}" -H "Authorization: Bearer $ALICE_TOKEN" -d "{\"user_id\":\"$BOB_ID\",\"title\":\"spoof\",\"body\":\"x\"}" | jq -c '{code,message}'

echo '=== 6) Alice UPDATE her own temp note (expect 1 row updated) ==='
[ -n "$NEW_ID" ] && curl -s -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.$NEW_ID" "${API[@]}" -H "Authorization: Bearer $ALICE_TOKEN" -H "Prefer: return=representation" -d '{"body":"tmp2"}' | jq -c '.'

echo '=== 7) Alice UPDATE Bob'"'"'s note id=3 (expect 0 rows, unchanged) ==='
curl -s -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.3" "${API[@]}" -H "Authorization: Bearer $ALICE_TOKEN" -H "Prefer: return=representation" -d '{"body":"hacked"}' | jq -c '.'

echo '=== 8) Alice UPDATE own note setting user_id to Bob (expect 0 rows via WITH CHECK) ==='
[ -n "$NEW_ID" ] && curl -s -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.$NEW_ID" "${API[@]}" -H "Authorization: Bearer $ALICE_TOKEN" -H "Prefer: return=representation" -d "{\"user_id\":\"$BOB_ID\"}" | jq -c '.'

echo '=== 9) Alice DELETE Bob'"'"'s note id=3 (expect 0 rows) ==='
curl -s -X DELETE "$SUPABASE_URL/rest/v1/notes?id=eq.3" "${API[@]}" -H "Authorization: Bearer $ALICE_TOKEN" -H "Prefer: return=representation" | jq -c '.'

echo '=== 10) Cleanup: Alice DELETE her temp note (expect 1 row) ==='
[ -n "$NEW_ID" ] && curl -s -X DELETE "$SUPABASE_URL/rest/v1/notes?id=eq.$NEW_ID" "${API[@]}" -H "Authorization: Bearer $ALICE_TOKEN" -H "Prefer: return=representation" | jq -c '.'

echo '=== 11) Bob INSERT with Alice'"'"'s user_id (expect RLS error) ==='
curl -s -X POST "$SUPABASE_URL/rest/v1/notes" "${API[@]}" -H "Authorization: Bearer $BOB_TOKEN" -d "{\"user_id\":\"$ALICE_ID\",\"title\":\"spoof2\",\"body\":\"x\"}" | jq -c '{code,message}'

=== 4) Alice INSERT with her own user_id (expect success; will delete after) ===
inserted id: 6
=== 5) Alice INSERT with Bob's user_id (expect RLS error) ===
{"code":"42501","message":"new row violates row-level security policy for table \"notes\""}
=== 6) Alice UPDATE her own temp note (expect 1 row updated) ===
[{"id":6,"user_id":"37ba4a7c-9597-4be3-8304-db4aefb26e38","title":"__rls_test__","body":"tmp2"}]
=== 7) Alice UPDATE Bob's note id=3 (expect 0 rows, unchanged) ===
[]
=== 8) Alice UPDATE own note setting user_id to Bob (expect 0 rows via WITH CHECK) ===
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}
=== 9) Alice DELETE Bob's note id=3 (expect 0 rows) ===
[]
=== 10) Cleanup: Alice DELETE her temp note (expect 1 row) ===
[{"id":6,"user_id":"37ba4a7c-9597-4be3-8304-db4aefb26e38","title":"__rls_test__","body":"tmp2"}]
=== 11) Bob INSERT with Alice's user_id (expect RLS error) ===
{"code":"42501","message":"new row violates row-level security policy for table \"notes\""}

export PGPASSWORD="$SUPABASE_DB_PASSWORD"; psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "
SELECT n.id, u.email, n.title, n.body FROM public.notes n
JOIN auth.users u ON u.id = n.user_id ORDER BY n.id;
" -c "SELECT count(*) AS total_rows FROM public.notes;"; rm -f /tmp/alice_token /tmp/bob_token

 id |     email      |    title     |         body         
----+----------------+--------------+----------------------
  1 | alice@cari.dev | Groceries    | milk, eggs, coffee
  2 | alice@cari.dev | Call plumber | kitchen sink leaks
  3 | bob@cari.dev   | Gym plan     | push pull legs
  4 | bob@cari.dev   | Trip ideas   | mountains in october
(4 rows)

 total_rows 
------------
          4
(1 row)