Get a free check
Get my free check
  • Free
  • No card
  • Your signed OK first
Articles

Supabase RLS: the two-account test that proves it holds

Nov 7, 2026 · 9 min read · Supabase

Row Level Security (RLS) in Supabase is the Postgres feature that decides, row by row, what each request is allowed to read or change. Turning it on is the start. You only know your policies hold when you sign in as user B, try to read, edit and delete user A’s rows, and get nothing back. That is the two-account test. Below is a version you can run on your own project.

On this page9 sections

What is RLS in Supabase?

RLS is a set of SQL rules attached to a table that Postgres applies to every query against it. Supabase’s docs describe a policy as adding a WHERE clause to every query, so a policy like auth.uid() = user_id quietly filters each select down to the caller’s own rows.

Supabase maps each request to a Postgres role. A request with the publishable key (the old anon key) and no signed-in user runs as anon. The same key with a signed-in user runs as authenticated. A request with the secret key (the old service_role key) runs as service_role, which has the BYPASSRLS attribute and skips every policy. If you’re unsure which key is which, the anon key vs service_role key guide covers it.

Policies sit behind a second check. Postgres evaluates table grants first: grants decide whether a role can run an operation on the table at all, and policies decide which rows it applies to. You need both set correctly.

Why “RLS enabled” doesn’t mean your data is safe

A table can have RLS switched on and still hand out rows it shouldn’t, because the policies themselves can be too loose. Enabling RLS with no policies blocks every publishable-key request through the API, which makes a quick permissive policy tempting when the app stops working.

Supabase’s own docs list several ways this goes wrong:

  • A policy that reads to anon using (true) gives every signed-out visitor every row the role can already reach. The docs say to use it only for data meant to be public.
  • On existing projects, a new table in public starts with every privilege granted to anon and authenticated. Policies don’t revoke those grants, so a table with only a select policy can still leave an insert path open for anon.
  • An update policy with no with check clause uses the using expression for both checks. Supabase points out that with check is what stops a user from reassigning user_id to someone else.
  • Views bypass RLS by default, because Postgres creates them as security definer under the postgres user. A view over a protected table can return every row the policies were written to hide.
  • raw_user_meta_data can be updated by the signed-in user, so a policy that trusts a role stored there trusts whatever the user wrote.

A check that only confirms RLS is enabled won’t catch any of these. A policy that filters on the wrong column looks the same as a correct one until a second user tries it.

Outside research shows the result at scale. Crissy Field examined 159,041 Lovable projects from December 2025 to September 2026, of which 34,545 used Supabase. Among 24,664 sites that sent Supabase records on first load, it found tables containing email addresses on 7,403 of them (30%) using further read-only requests. Crissy Field cautions that the 30% “should therefore not be read as a confirmed data leak rate”, since some addresses may be public contacts or test data. UpGuard’s September 2026 study, covering about 300,000 domains with signs of Supabase use, identified 16,326 databases exposing readable tables.

The two-account test, step by step

The two-account test checks whether one signed-in user can touch another signed-in user’s data. You create user A and user B, give A a row, then act as B and try to read, update, delete and forge rows that belong to A. Every attempt should come back empty or with an error.

The full pass, per table that holds user data:

  1. As A, create a row.
  2. As B, select it by its ID. Expect zero rows.
  3. As B, update it with returning. Expect zero rows.
  4. As B, delete it with returning. Expect zero rows.
  5. As B, insert a new row with A’s user_id. Expect error 42501.
  6. As A, read the row again. Expect it unchanged.
  7. Signed out (anon), select from the table. Expect zero rows unless the data is meant to be public.

Step 6 catches a false pass. Supabase’s testing guide warns against proving a write with lives_ok, because that assertion “passes when the write matched zero rows.” Reading the row back as A is how you confirm B’s update really did nothing.

For tables shared between users, such as team workspaces, add a third account: a member who isn’t the owner should be allowed what the policies allow, and a non-member should get nothing. Supabase’s RLS guide asks for both cases.

A two-account RLS test in SQL (pgTAP)

The quickest way to run the test is a pgTAP file against your local Supabase stack. The file below is an example written for a hypothetical notes table with a user_id column. Adapt the table and column names to yours. It runs inside one transaction and rolls back, so nothing persists.

First, the table and policies it tests (example migration):

SQL
-- supabase/migrations/<timestamp>_create_notes.sql (example)
create table public.notes (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users,
  body text not null
);

alter table public.notes enable row level security;

revoke all on public.notes from anon, authenticated;
grant select, insert, update, delete on public.notes to authenticated;

create policy "notes_select_own" on public.notes
  for select to authenticated using ((select auth.uid()) = user_id);
create policy "notes_insert_own" on public.notes
  for insert to authenticated with check ((select auth.uid()) = user_id);
create policy "notes_update_own" on public.notes
  for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);
create policy "notes_delete_own" on public.notes
  for delete to authenticated using ((select auth.uid()) = user_id);

create index on public.notes (user_id);

Then the test (create it with supabase test new notes_rls):

SQL
-- supabase/tests/notes_rls_test.sql (example)
begin;
create extension if not exists pgtap with schema extensions;
select plan(6);

-- Two users. These UUIDs are illustrative.
insert into auth.users (id, email) values
  ('11111111-1111-1111-1111-111111111111', 'a@example.test'),
  ('22222222-2222-2222-2222-222222222222', 'b@example.test');

insert into public.notes (id, user_id, body) values
  ('aaaaaaaa-0000-0000-0000-000000000001',
   '11111111-1111-1111-1111-111111111111', 'A private note');

-- Act as user B.
set local role authenticated;
set local request.jwt.claim.sub = '22222222-2222-2222-2222-222222222222';

select is_empty(
  $$ select id from public.notes where id = 'aaaaaaaa-0000-0000-0000-000000000001' $$,
  'B cannot read A''s note');

select is_empty(
  $$ update public.notes set body = 'changed by B'
     where id = 'aaaaaaaa-0000-0000-0000-000000000001' returning id $$,
  'B cannot update A''s note');

select is_empty(
  $$ delete from public.notes
     where id = 'aaaaaaaa-0000-0000-0000-000000000001' returning id $$,
  'B cannot delete A''s note');

select throws_ok(
  $$ insert into public.notes (user_id, body)
     values ('11111111-1111-1111-1111-111111111111', 'forged by B') $$,
  '42501', null,
  'B cannot create a note owned by A');

-- Back to user A: the note must be intact.
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
select results_eq(
  $$ select body from public.notes where id = 'aaaaaaaa-0000-0000-0000-000000000001' $$,
  $$ values ('A private note'::text) $$,
  'A still sees the original note');

-- Signed-out visitor.
set local role anon;
select throws_ok(
  $$ select id from public.notes $$,
  '42501', null,
  'anon has no grant on notes');

select * from finish();
rollback;

Run it with supabase test db. Supabase’s guide uses the same pattern: set local role and request.jwt.claim.sub set the role and user each assertion runs as, the way a signed-in user’s request does.

The last assertion expects 42501 because the example migration revoked every grant from anon. If your table is meant to be readable while signed out, replace it with an is_empty or results_eq check that matches what visitors should see.

Before trusting a green run, break a policy on purpose (change user_id to id in the select policy, say) and confirm the test fails. Supabase’s own agent prompt for writing these tests asks for exactly that: show each test fails when the policy it covers is broken.

Running the test through the API with two real sessions

You can also run the two-account test from outside the database, with two real user sessions against your own staging project. This catches problems the SQL test can miss, such as an RPC function or a view the frontend calls instead of the table.

The request shape: the publishable key goes on the apikey header and the signed-in user’s access token goes on Authorization: Bearer. Example, acting as user B against a note owned by user A:

Terminal
# Example only. Run against a project you own, ideally staging.
# USER_B_JWT is B's access token from a normal sign-in.

# Read A's note as B: expect []
curl -s "$SUPABASE_URL/rest/v1/notes?id=eq.$A_NOTE_ID&select=id,body" \
  -H "apikey: $SUPABASE_PUBLISHABLE_KEY" \
  -H "Authorization: Bearer $USER_B_JWT"

# Try to change it as B: expect []
curl -s -X PATCH "$SUPABASE_URL/rest/v1/notes?id=eq.$A_NOTE_ID" \
  -H "apikey: $SUPABASE_PUBLISHABLE_KEY" \
  -H "Authorization: Bearer $USER_B_JWT" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{"body":"changed by B"}'

Then read the note as A and confirm the body is unchanged. Supabase’s testing guide has a Vitest version of the same idea using @supabase/supabase-js. It warns that application-level tests can’t roll back a transaction, so give each test suite its own user IDs instead of relying on a clean database.

Only run this against projects you own or have written permission to test.

Why your Supabase RLS policy blocks a request it should allow

When a request your policy should allow fails with error 42501, check the table grant before the policy. Supabase’s docs say a missing grant raises 42501 before any policy runs.

The denials look different depending on what stopped them, which also helps you read test results:

What denied it What you see
Missing grant Error 42501
with check violation on insert or update Error 42501
using clause filtered the row out No error, zero rows

A select that returns an empty list usually means the using clause filtered the rows. An empty result for a user who should see data means the policy matches on the wrong column or the user’s ID isn’t what you think.

Policies that read another table can also fail with 42P17, infinite recursion. Supabase’s guide shows this with a lists table and a list_members table whose policies check each other, and recommends a security definer function, with search_path = '', to break the loop.

What to check besides tables

The two-account test covers tables, and data also leaks through views, functions and storage. Run the same A-versus-B pass on each of them.

  • Views: on Postgres 15 and later, set security_invoker = true so the view follows the underlying table’s policies. On older versions, revoke access from anon and authenticated or move the view to a schema the API doesn’t expose.
  • Functions: new functions in public are callable by every role by default, according to Supabase’s API security guide. A security definer function runs as its owner, which on Supabase is postgres with bypassrls, so it needs its own checks.
  • Grants: run Supabase’s default-privileges query to see what new tables will hand to anon and authenticated, and revoke what you don’t need.

When to run the two-account test

Run it before launch, and again after any change to tables, policies or auth. Crissy Field gives the same advice: review access permissions and the separation between user accounts before an app handles real customer data, and repeat after significant changes.

Putting the pgTAP file in supabase/tests/ means supabase test db reruns it every time, so a later change that opens a table fails the suite.

If you’d like someone else to run it, Shipmate’s Launch Lockdown runs the two-account test across your tables, views and functions and delivers the fixes as a tested pull request. The free check is a lighter, passive look at what your live app already exposes, and it needs your written permission first.

Sources