Supabase database

Supabase RLS policies explained, with owner and stranger tests

A row level security (RLS) policy is a rule Postgres attaches to a table that decides, row by row, who may read, add, change or delete data. In a Supabase app it is the only thing between your public key and your users’ rows, so the way to know it works is to test it as three people: a logged-out visitor, a signed-in stranger and the owner.

By Manraj Singh Dhillon, technical founderChecked 10 Nov 20266 min read

Why a Supabase app depends on RLS

A Supabase frontend talks to the database directly. The browser sends queries to the project’s REST API with the project’s anon key (or, on newer projects, a sb_publishable_… key), and the API runs them as one of two Postgres roles: anon for logged-out visitors and authenticated for signed-in users. The key is in your JavaScript bundle, so anyone can copy it and send their own queries.

That is fine, and intended, as long as row level security decides what those roles can see. Postgres evaluates your policies on every query, adding their conditions to it, so a visitor asking for select * from profiles gets back only the rows a policy allows. The server-side service role key skips RLS entirely, which is why it must never reach the browser (see PL-010).

Anatomy of a policy

create policy "Users read own notes"   -- a name, for humans
  on public.notes                      -- the table
  for select                           -- the command it covers
  to authenticated                     -- the roles it applies to
  using ((select auth.uid()) = user_id); -- which rows pass

Each part matters:

• Command. select, insert, update, delete or all. A table needs a policy for each thing your app does with it.
• Roles. Leave out to and the policy applies to every role, including anon. This is the most common cause of leaks to logged-out visitors.
• USING filters existing rows: the ones a user can read, or pick to update or delete.
• WITH CHECK validates new rows: what an insert adds, or what a row looks like after an update.

Three rules explain most surprises:

1. Policies add up. Ordinary (permissive) policies are combined with OR: if any policy for the command lets a row through, it’s through. One loose policy undoes five careful ones. Restrictive policies (as restrictive) are combined with AND and narrow what the permissive ones allow.
2. RLS on, no policy: nothing. Postgres denies everything through the API. Safe, but your app breaks (PL-003), and the quick fix people reach for is usually a leak.
3. RLS off: grants decide. If anon or authenticated have been granted access to a table without RLS, they can read and change every row (PL-008).

An update with a filter also needs the row to pass your select policies, because Postgres has to read the row to find it. That is why “I added an update policy and it still updates 0 rows” usually means the select policy is missing.

The three identities to test

Reading a policy tells you what it says. Running a query as a real role tells you what it does, across every policy on the table at once. Test each table holding personal data as three people:

The owner test matters as much as the other two. A “fix” that locks everyone out closes the leak and breaks the app, which is how you end up back at using (true) next week.

How to test a table as each identity

The safest place to test is a local copy of your database built from your migrations: run supabase start, then connect with psql postgresql://postgres:postgres@127.0.0.1:54322/postgres (the CLI’s default local database). You can also use your project’s SQL editor; it shows one result per run, so run one block at a time. Every block is a transaction that is rolled back, so nothing changes.

Supabase’s auth.uid() reads the user id from the request’s JWT claims, so you can act as any user by setting those claims for the transaction.

-- 0. Pick an owner: someone who has rows in the table
select user_id from public.notes limit 1;

-- 1. Anonymous visitor
begin;
set local role anon;
select count(*) as anon_rows from public.notes;
rollback;

-- 2. Signed-in stranger: a random id that owns nothing
begin;
set local role authenticated;
select set_config('request.jwt.claims',
  json_build_object('sub', gen_random_uuid(), 'role', 'authenticated')::text, true);
select count(*) as stranger_rows from public.notes;
rollback;

-- 3. The owner: should see their rows, and only theirs
begin;
set local role authenticated;
select set_config('request.jwt.claims',
  json_build_object('sub', '<owner id from step 0>', 'role', 'authenticated')::text, true);
select count(*) as owner_rows,
       count(*) filter (where user_id <> '<owner id from step 0>') as not_theirs
from public.notes;
rollback;

The result you want: anon_rows = 0, stranger_rows = 0, owner_rows above 0 and not_theirs = 0. If the table is meant to be public (a product list), anon and the stranger seeing rows is correct; what matters is that personal columns aren’t among them.

Writes are tested the same way. As the stranger, try update public.notes set body = body where user_id <> '<owner id>' returning id; inside the transaction. Any id returned is a row a stranger can change (PL-012).

Policies for the common cases

Rows that belong to one user

alter table public.notes enable row level security;

create policy "Owners read own notes" on public.notes
  for select to authenticated using ((select auth.uid()) = user_id);
create policy "Owners add own notes" on public.notes
  for insert to authenticated with check ((select auth.uid()) = user_id);
create policy "Owners edit own notes" on public.notes
  for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);
create policy "Owners delete own notes" on public.notes
  for delete to authenticated using ((select auth.uid()) = user_id);

Rows shared by a team

create policy "Members read their team's projects" on public.projects
  for select to authenticated
  using (team_id in (
    select team_id from public.team_members
    where user_id = (select auth.uid())
  ));

The membership table needs its own policies too, or a member could add themselves to any team.

Public to read, owner to write

create policy "Anyone can read published posts" on public.posts
  for select to anon, authenticated using (published);
create policy "Authors write own posts" on public.posts
  for update to authenticated
  using ((select auth.uid()) = author_id)
  with check ((select auth.uid()) = author_id);

If a public table also holds a private column (an author’s email), withhold the column rather than the rows: revoke select on public.posts from anon; grant select (id, title, body, published) on public.posts to anon;. The app then has to name its columns instead of using select('*').

Mistakes that leak

• using (true) with no to clause on a table with personal data: anyone with the key reads it (PL-001).
• to authenticated using (true), or auth.uid() is not null: looks restricted, but anyone who signs up reads everything (PL-002).
• An update policy without WITH CHECK on a row with a role, plan or credits column: users edit their way to admin (PL-004, PL-013).
• “Enable all for all users” on writes: anyone can change or delete every row (PL-005).
• Roles read from user_metadata, which users can edit (PL-019). See user_metadata vs app_metadata.
• A view over a protected table, which runs as its owner and skips the policies (PL-014). See views and security_invoker.

Keep policies fast

Write (select auth.uid()) rather than auth.uid(). Wrapped in a sub-select, Postgres evaluates it once per query instead of once per row, which matters on large tables. Index the column you compare against: create index on public.notes (user_id);. Supabase’s performance advisor flags the unwrapped form.

A short checklist

• Every table in public has RLS enabled.
• Every policy has a to clause.
• No policy on a table with personal data is using (true) for anon or authenticated, unless the table is meant to be public.
• Every update policy has WITH CHECK, and columns users mustn’t set are not granted to them.
• Each table passes the three-identity test above.
• All of it lives in supabase/migrations, not only in the dashboard, so it can be rebuilt and re-tested.

Sources

• Supabase: Row Level Security
• PostgreSQL: Row Security Policies
• PostgreSQL: CREATE POLICY
• Supabase: database advisors (security and performance lints)

Related findings

The checks behind this guide, each with example code, a self-check and the fix.

Written by Manraj Singh Dhillon. Last checked 10 Nov 2026. Code samples are synthetic and run against your own project; nothing here names a real app. Spotted a mistake? Tell us.