Supabase security · 7 minute read

Why your Supabase RLS tests pass and your data still leaks

The pattern shows up in almost every audit I run: the test suite is green, the app works, and the anon key still reads every email in the users table. Nothing is broken. The suite was simply written as one identity — the owner — and never as the attacker. An RLS test that never runs as anon, a second tenant, or an expired session is not a security test. It is a feature test with SQL.

The asymmetry that hides the leak

RLS policies are evaluated per role per request, so a policy can be perfectly correct for one identity and wide open for another. When you test only as the signed-in owner, four failure modes are invisible:

• RLS was never enabled on the table. With no policies and no RLS, the table falls back to plain grants — and in Supabase, anon and authenticated hold table grants by default. The owner session reads its own rows and the suite passes. The anon key reads everything. A suite that only signs in never touches this path.
• The policy exists but permits everyone. create policy ... using (true)satisfies "the table has a policy" in any checklist, and behaves exactly like no policy for reads. The owner session still sees its own rows — the test passes.
• The definer function does the reading. A security definerfunction that queries a table runs with the function owner's privileges, not the caller's. Tested as the owner it returns the right rows; called by a signed-in stranger it returns theirs too — and if it takes an argument (a tenant_id, an email), it can be pointed at anyone's data.
• A view sits between the API and the table.Pre-Postgres-15 views run with the view owner's privileges, so RLS on the base table is bypassed for anyone who can select the view — and the Data API exposes views like tables. A suite that only queries base tables never sees this.

None of these are exotic. They are the four most common findings in the audits I deliver, and every one of them passes a naive owner-session test.

The five identities your suite must run as

A useful mental model: each test has two axes — which table/object and which identity. Most suites only walk the first axis. The second axis is where the leaks are:

1. anon(no JWT, the public anon key) — must get zero rows on every user-scoped table, and must not be able to insert, update, or delete anything that isn't explicitly public (a waitlist, a public catalog).
2. authenticated — the owner — sees and writes exactly its own rows. This is the only identity most suites test.
3. authenticated — a second tenant — the identity that catches cross-tenant reads and writes. Seed one row owned by tenant B, query it as tenant A, assert zero rows and a zero count through the REST path, not just the policy function.
4. an expired token— proves the failure mode is "denied", not "stale data still served".
5. service_role — only ever from your server, never presentable by a client. The test is negative too: prove no client-reachable path (the browser bundle, a NEXT_PUBLIC_ variable, an Edge Function with verify_jwt = false) can obtain or use it.

Run that as a matrix: every table × every identity. That is what turns "the queries work" into evidence an auditor (or a customer's security questionnaire) accepts.

The harness: simulate the identity in SQL, not in the app

You do not need to mint a JWT per test. PostgREST reads the caller identity from the request GUCs, so a pgTAP test can set them locally and run as any role:

-- tests/rls/tenant_isolation_test.sql
begin;
select plan(4);

-- set identity: tenant A
set local role authenticated;
select set_config('request.jwt.claims',
  '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}', true);

-- negative read: tenant B's row must not be visible
set local role anon;
select is((
  select count(*) from public.orders where tenant_id = '22222222-2222-2222-2222-222222222222'
), 0::bigint, 'anon cannot read tenant B orders');

-- negative write: anon insert must fail with RLS violation
select throws_ok(
  $$insert into public.orders (tenant_id, total) values ('22222222-2222-2222-2222-222222222222', 1)$$,
  '42501', 'anon insert into orders is denied'
);

select * from finish();
rollback;

Then run the same file twice: once through supabase test db on the local stack, and once against a clone with the production grants applied. The local stack is built from migrations; production grants drift. Both runs green is what makes it evidence.

The three checks that catch what SQL misses

Some surfaces aren't reachable from a test session, so check them directly:

• The REST path, as anon.One curl per table with the public anon key — this is the actual attacker's position, and it catches view-exposure and grant problems that SQL tests skip:
  curl "$SUPABASE_URL/rest/v1/orders?select=*&limit=1" -H "apikey: $ANON_KEY"
• The storage surfaces. A public bucket flag is one setting; storage.objects policies are the control. Test anon upload into a user-scoped folder path — upload permission on objects is where most apps accidentally allow overwrites.
• The browser bundle. grep -rE "service_role|SERVICE_ROLE" build/ and scan for NEXT_PUBLIC_ variables that carry privileged keys. No database test can catch a secret that shipped to the client.

The five-minute version

If you do nothing else this week: list every table, then for each one run the anon curl above and one two-tenant pgTAP pair. That combination catches the four invisible failure modes — no-RLS, permissive policy, definer bypass, view bypass — without any new infrastructure.

Related reading: 7 ways your Supabase app still leaks data with RLS enabled, how to test RLS policies and what your AI security reviewer structurally cannot see. For a full launch pass, a fixed-price Supabase RLS audit from $99 returns prioritized findings with reproducible evidence.