Explain Supabase RLS code

What this Supabase RLS sample shows

enabling RLS

enable row level security makes every query from the anon and authenticated roles see only the rows a policy allows. Without that line the policies did nothing: Ana and a signed-out visitor both read every todo, hers and Sam's. With it on and no policies at all, Ana read 0 rows, and adding a todo failed with new row violates row-level security policy.

using and with check

using decides which existing rows a query can see or touch, and with check decides what a new or changed row may look like. Ana read her 2 todos and Sam read his 1. Ana adding a todo with Sam's user_id failed the with check test. Her update to Sam's todo changed 0 rows, with no error, because using hid it from her.

auth.uid() and roles

auth.uid() returns the id of the signed-in user making the request, read from their login token. to authenticated limits a policy to signed-in requests, so a signed-out visitor, the anon role, read 0 rows and couldn't add one. Supabase's docs recommend (select auth.uid()) over a bare auth.uid() so Postgres works it out once per query, not once per row.

missing policies

There is no delete policy, so nobody signed in can delete: Ana deleting her own todo changed 0 rows. The update policy also leans on the select policy: with the select policy taken out, Ana could still add a todo with a plain insert but read 0 rows, and marking it done changed 0 rows.

Good to know

• Run on PostgreSQL 18.6 through PGlite 0.5.15, with a small stand-in for Supabase's auth schema and its anon and authenticated roles, and auth.uid() copied from Supabase's own source. Each request set the role and the login token's claims in a transaction, the way Supabase's Data API does.
• The table's owner skips these policies, and in this run postgres, which owns the table and is a superuser, read all 3 todos. Supabase's service_role key skips them too, so it belongs on a server, never in the browser.
• Taking out the update policy's with check line didn't open a hole: Postgres then tests the changed row with using, so Ana giving all her todos to Sam failed. With with check (true) instead, the same update went through and the todo became Sam's.

Watch out: A view can quietly skip every policy. A view over todos, created by the table owner and granted to signed-in users, showed Sam his own todo and Ana's. After alter view ... set (security_invoker = true), which needs Postgres 15 or later, the view applied the policies and Sam saw only his own.