google/gemini-3.1-pro-preview · d1_rls_owner · rep 2

psql "$SUPABASE_DB_URL" -c "\d public.notes"

                            Table "public.notes"
 Column  |  Type  | Collation | Nullable |             Default              
---------+--------+-----------+----------+----------------------------------
 id      | bigint |           | not null | generated by default as identity
 user_id | uuid   |           | not null | 
 title   | text   |           | not null | 
 body    | text   |           | not null | ''::text
Indexes:
    "notes_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "notes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE

psql "$SUPABASE_DB_URL" -c "SELECT relname, relrowsecurity FROM pg_class WHERE relname='notes';"

 relname | relrowsecurity 
---------+----------------
 notes   | f
(1 row)

cat << 'EOF' > update_rls.sql
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY "owner_only_all"
ON public.notes
FOR ALL
TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
EOF
psql "$SUPABASE_DB_URL" -f update_rls.sql

ALTER TABLE
CREATE POLICY

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"}'

{"access_token":"<jwt:authenticated>","token_type":"bearer","expires_in":3600,"expires_at":1784871068,"refresh_token":"lx3rdjhb4x5n","user":{"id":"373ea1ec-d576-47be-ae2e-cc5c65e59229","aud":"authenticated","role":"authenticated","email":"alice@cari.dev","email_confirmed_at":"2026-07-24T04:30:41.907546Z","phone":"","confirmed_at":"2026-07-24T04:30:41.907546Z","last_sign_in_at":"2026-07-24T04:31:08.206103327Z","app_metadata":{"provider":"email","providers":["email"]},"user_metadata":{"email_verified":true},"identities":[{"identity_id":"3f804772-e54f-4882-b2a7-b22f5f237a63","id":"373ea1ec-d576-47be-ae2e-cc5c65e59229","user_id":"373ea1ec-d576-47be-ae2e-cc5c65e59229","identity_data":{"email":"alice@cari.dev","email_verified":false,"phone_verified":false,"sub":"373ea1ec-d576-47be-ae2e-cc5c65e59229"},"provider":"email","last_sign_in_at":"2026-07-24T04:30:41.905996Z","created_at":"2026-07-24T04:30:41.90606Z","updated_at":"2026-07-24T04:30:41.90606Z","email":"alice@cari.dev"}],"created_at":"2026-07-24T04:30:41.904938Z","updated_at":"2026-07-24T04:31:08.208643Z","is_anonymous":false},"weak_password":null}

export ALICE_TOKEN="<jwt:authenticated>"
curl -s -X GET "$SUPABASE_URL/rest/v1/notes?select=*" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer $ALICE_TOKEN"

[{"id":1,"user_id":"373ea1ec-d576-47be-ae2e-cc5c65e59229","title":"Groceries","body":"milk, eggs, coffee"}, 
 {"id":2,"user_id":"373ea1ec-d576-47be-ae2e-cc5c65e59229","title":"Call plumber","body":"kitchen sink leaks"}]

curl -s -X GET "$SUPABASE_URL/rest/v1/notes?select=*" \
-H "apikey: $SUPABASE_ANON_KEY"

[]

curl -s -X POST "$SUPABASE_URL/rest/v1/notes" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer $ALICE_TOKEN" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d '{"title": "Alice test", "body": "test", "user_id": "373ea1ec-d576-47be-ae2e-cc5c65e59229"}'

{"code":"PGRST301","details":null,"hint":null,"message":"Empty JWT is sent in Authorization header"}

curl -s -X POST "$SUPABASE_URL/rest/v1/notes" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer $ALICE_TOKEN" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d '{"title": "Alice test", "body": "test"}'

{"code":"PGRST301","details":null,"hint":null,"message":"Empty JWT is sent in Authorization header"}

curl -s -X POST "$SUPABASE_URL/rest/v1/notes" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer <jwt:authenticated>" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d '{"title": "Alice test", "body": "test", "user_id": "373ea1ec-d576-47be-ae2e-cc5c65e59229"}'

[{"id":5,"user_id":"373ea1ec-d576-47be-ae2e-cc5c65e59229","title":"Alice test","body":"test"}]

psql "$SUPABASE_DB_URL" -c "DELETE FROM notes WHERE id = 5;"

DELETE 1

curl -s -X POST "$SUPABASE_URL/rest/v1/notes" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer <jwt:authenticated>" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d '{"title": "Alice malicious test", "body": "test", "user_id": "00000000-0000-0000-0000-000000000000"}'

{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"notes\""}

psql "$SUPABASE_DB_URL" -c "ALTER TABLE public.notes ALTER COLUMN user_id SET DEFAULT auth.uid();"

ALTER TABLE

curl -s -X POST "$SUPABASE_URL/rest/v1/notes" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer <jwt:authenticated>" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d '{"title": "Alice malicious test 2", "body": "test"}'

[{"id":7,"user_id":"373ea1ec-d576-47be-ae2e-cc5c65e59229","title":"Alice malicious test 2","body":"test"}]

psql "$SUPABASE_DB_URL" -c "DELETE FROM notes WHERE id = 7;"

DELETE 1