Supabase Row Level Security: how to turn it on and get it right

Why does Supabase need RLS more than a normal backend?

Supabase exposes your tables through an auto-generated REST and GraphQL API. Your frontend calls that API directly with the anon (or publishable) key. There is no server code in between to check who may read what, so the database itself must do it. RLS is that check.

With RLS on and no policies, the table returns no rows to the anon and authenticated roles. That is a safe default: you open access one policy at a time.

How do I turn RLS on?

Tables you create in the Table Editor have RLS on by default. Tables you create with SQL, including SQL an AI tool writes for you, do not. Turn it on in every migration.

alter table public.notes enable row level security;

-- Optional: make the table owner obey RLS too.
alter table public.notes force row level security;

-- Find every table in public without RLS.
select c.relname
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r' and not c.relrowsecurity;

How do I write a policy that only lets users see their own rows?

Write one policy per command (select, insert, update, delete). Name the role with `to authenticated`, so anonymous visitors never run the policy. Use `using` for rows the user may see or change, and `with check` for the values a new or updated row may hold.

create policy "notes: owner reads" on public.notes
  for select to authenticated
  using ((select auth.uid()) = user_id);

create policy "notes: owner inserts" on public.notes
  for insert to authenticated
  with check ((select auth.uid()) = user_id);

create policy "notes: owner updates" on public.notes
  for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

create policy "notes: owner deletes" on public.notes
  for delete to authenticated
  using ((select auth.uid()) = user_id);

What are the most common RLS mistakes?

• `using (true)` on a table with private data. It looks like a policy, but it lets every signed-in user read every row.
• An update policy with `using` but no `with check`. A user can then move their row to another user by changing `user_id`.
• Trusting `auth.jwt() -> 'user_metadata'`. Users can edit their own user metadata. Put roles and team IDs in `app_metadata`, which only your server can set.
• Checking the owner on select but not on update or delete.
• Tables in `public` that the app never meant to expose. Move internal tables to a schema the API does not expose.

How do I test a policy?

Test as the role the browser uses, not in the SQL editor as `postgres`, which bypasses RLS. Inside a transaction, switch role and set the JWT claims, then query.

begin;
set local role authenticated;
select set_config('request.jwt.claims', '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}', true);
select count(*) from public.notes;  -- only this user's rows
rollback;