Test your Supabase RLS before you ship: a free red/green fixture and the 9 SQL checks a linter cannot run

If you built a Supabase app quickly – with an AI coding tool or by hand – the row-level-security policies were often written last, or generated for you. That is fine. What is not fine is shipping without knowing whether those policies actually isolate one user’s rows from another.

Supabase ships a database linter, and you should run it first – it is free and it catches the obvious cases: RLS switched off, and RLS switched on with no policy behind it. But a linter checks whether a policy exists, not whether the policy is correct. Those are different questions, and the second one is where cross-user leaks live.

The 2-second test

I put a minimal, synthetic reproduction on GitHub: supabase-rls-leak-demo. Same test suite on two branches, differing only by db/policies.sql:

• broken -> 4 failed, 1 passed (an authenticated user reads another user’s row)
• fixed -> 5 passed

npm ci
npm run test:ci

Enter fullscreen mode Exit fullscreen mode

No Docker, no Supabase project, no credentials. The tests run PostgreSQL in PGlite locally and exercise database-level row security. They do not model Supabase Auth, PostgREST, the Data API, or the network path – the result proves only the row-level gate in the fixture, which is exactly the gate people get wrong.

On broken, the failing assertion is readable on purpose:

x does not let user B read any row owned by user A
  -> user B received 1 row(s) belonging to another user:
    ["A: card ending 4471, expiry 09/29"]

Enter fullscreen mode Exit fullscreen mode

(That is synthetic seed data, not a real card.)

Run the free checks against your own database

The repo also ships audit/rls-audit.sql – nine read-only queries against the system catalogs, MIT-licensed, nothing to install and nothing to send anywhere. Every one is SELECT-only, so it is safe to paste into the Supabase SQL editor. They tell you:

• RLS coverage per table
• Every policy and the roles it actually applies to (an empty roles array means no TO clause, so the policy is evaluated for anon too)
• The effective write check, and which columns its predicate never mentions
• What anon and authenticated can INSERT / UPDATE / DELETE
• SECURITY DEFINER functions the client can call, and whether search_path is pinned
• Owner bypass, FORCE ROW LEVEL SECURITY, and roles holding BYPASSRLS

Here is the coverage query on its own, so you can try it right now:

select
  c.relname as table_name,
  c.relrowsecurity as rls_enabled,
  count(p.polname) as policy_count
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_policy p on p.polrelid = c.oid
where n.nspname = 'public' and c.relkind = 'r'
group by c.relname, c.relrowsecurity
order by c.relrowsecurity, c.relname;

Enter fullscreen mode Exit fullscreen mode

Any row with rls_enabled = false is a table where PostgreSQL applies no RLS row filter to roles subject to RLS. Investigate grants, role attributes and API/schema exposure before calling it an externally reachable leak – policy_count > 0 does not by itself prove one.

The check people get wrong most often

Testing “as a user” by hand usually goes like this:

set role authenticated;
select * from notes;   -- returns nothing, so you assume the policy is broken

Enter fullscreen mode Exit fullscreen mode

But set role authenticated on its own leaves request.jwt.claims unset, so auth.uid() returns NULL, every ownership policy filters everything away, and you conclude a correct policy is broken. The audit’s role-simulation harness wraps its probes in BEGIN ... ROLLBACK and sets the JWT claims the way the API does, so you can query as a real user without persisting anything.

What none of this can check for you

Reading a policy against the schema it guards is manual work. The failure modes that survive review and pass tests are the ones a query cannot flag:

• a permissive policy silently cancelling a restrictive one
• a membership/tenant join that is not actually isolated
• a service_role key reachable from a client code path

That reading is the audit. If you want to do it yourself, the Supabase RLS Audit Kit is seven commented SQL audits you run against your own catalogs ($29, nothing leaves your database). If you just want a plain go-live pass wider than RLS – secrets, auth, performance, SEO, reliability, backups – the Next.js + Supabase Launch Checklist is an 8-page, 60-check PDF ($19). And if you would rather have it done, I run a fixed-price Supabase RLS Security Audit from $99.

All three are review aids for projects you own or are authorized to test – not a penetration test, a certification, or a guarantee that an application is secure. But the free fixture and the nine queries above are genuinely all most people need before launch. Start there, and if they come back clean, you are done.