Test Supabase RLS Policies Before You Ship

There is a specific way RLS bugs reach production, and it is almost never that
the developer forgot to write a policy. It is that the policy was tested in a
context that does not enforce it.

Three contexts, all of them defaults:

• The Supabase SQL editor runs your query as a privileged role.
• The service key you use in server-side code carries BYPASSRLS.
• The owner of the table — the role your migrations run as — is exempt from its own policies unless you explicitly say otherwise.

In all three, select * from documents returns every row whether your policy
is correct, inverted, or absent. Then the same query runs from a browser with
an anon key and returns nothing, or worse, returns everything.

Why Postgres skips your policy

Row level security has two exemptions built into the engine, and they exist for
good operational reasons — backups and migrations would be miserable otherwise.

The first is the superuser exemption: any role with rolsuper, and any role
with the BYPASSRLS attribute, skips policy evaluation entirely. Supabase
creates service_role with exactly that attribute, which is the whole reason
server-side code with the service key can read across all tenants. It is also
why "it works with the service key" is not a passing test — see
service_role bypasses RLS for what
that key can reach and how to contain it.

The second is the owner exemption: the role that owns a table is not subject
to that table's policies by default. This one surprises people, because it means
the connection your migration tool uses, and the connection most SQL consoles
use, sees straight through RLS.

You turn the owner exemption off per table:

alter table public.documents force row level security;

FORCE is a table-level setting, not a session one. It does not affect
superusers or BYPASSRLS roles — nothing does. To escape those, you have to
stop being them.

The procedure

Four statements. Everything else is detail.

begin;

-- 1. Feed the JWT the policy will read. `true` = transaction-local.
select set_config(
  'request.jwt.claims',
  '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}',
  true
);

-- 2. Stop being the owner.
set local role authenticated;

-- 3. Run the exact query your client runs.
select id, title from documents order by id;

rollback;

The rollback matters more than it looks. It discards the role change, the
claims, and anything the statement wrote — which is what lets you test an
INSERT repeatedly against identical seed data instead of accumulating junk
rows between runs.

auth.uid() is not magic. In a Supabase project it is a small SQL function that
reads request.jwt.claims and pulls sub out of it. Setting that GUC by hand is
precisely what PostgREST does for you when a request arrives with a bearer token,
which is why this test reproduces the real path rather than approximating it.

Run it as more than one person

This is the step almost everyone skips, and it is the one that catches actual
leaks.

A policy like using (true) passes a single-user test perfectly: you are signed
in, you asked for your rows, you got rows. The test only fails when a second
identity runs the same query and gets the same rows back.

So run the block above at least three times — once with no claims and
set local role anon, once as user A, once as user B — and compare:

If A and B see the same non-empty set, you have a cross-tenant leak. If both see
zero, you have the silent failure covered in
why RLS returns zero rows with no error.

Doing all four at once

Typing that transaction four times per policy change is why the step gets
skipped, so I built the tool I wanted: the
Supabase RLS Playground boots a real
PostgreSQL engine compiled to WebAssembly inside the browser tab, applies your
schema and your CREATE POLICY statements, and runs one query four times — as
anon, as two different signed-in users, and as service_role — showing the
four result sets side by side.

It does the two things this article is about, because otherwise it would lie to
you the same way the SQL editor does: every persona query runs under SET LOCAL
ROLE, and every RLS-enabled table gets FORCE ROW LEVEL SECURITY before the
first query runs. The auth schema is shimmed the way Supabase defines it —
auth.uid(), auth.jwt(), auth.role(), auth.email() — so a policy pasted
straight out of your project runs verbatim.

Nothing is uploaded; there is no server component to upload it to. Once the
WebAssembly build is cached the page works offline.

What the measurements look like

Two experiments against a real Postgres engine, because the two exemptions
behave differently and the difference decides which flag you need.

The owner exemption, isolated. A table owned by an ordinary non-superuser
role, RLS enabled, and a policy deliberately written to match nothing —
using (user_id = 'nobody'). Queried by the owner:

A policy that blocks everything returns everything, until FORCE.

The four identities, on a normal policy. Three rows in documents, two
owned by user A and one by user B, with the textbook rule
for select to authenticated using ((select auth.uid()) = user_id):

Same policy, same data, five different answers depending only on who asked. The
last two rows of that table are the ones you get by default from a SQL console
and from server-side code holding the service key — and they are the two that
tell you nothing, because neither of them ever consulted the policy.

Note which flag fixes which: FORCE removes the owner exemption, and nothing
removes the superuser exemption. To escape that one you have to stop being a
superuser, which is what SET LOCAL ROLE is for. You need both.

Studio impersonation is closer, but it is still not production

Supabase Studio has a role-impersonation feature in the SQL editor, and it is a
genuine improvement over running as the owner. Treat it as a good approximation
rather than a proof, because it is reproducing production behaviour rather than
being it.

A concrete example: supabase/supabase#27841
reported that impersonation did not invoke the project's Custom Access Token
Hook, so claims the hook adds to app_metadata were missing from
select auth.jwt() during impersonation while being present in production. That
specific gap was addressed in a later pull request — the durable lesson is not
that one bug existed, but that the JWT your test sees and the JWT your users
carry are assembled by different code paths, and any policy keyed on a custom
claim is only as trustworthy as the claim you fed it.

Which is also the argument for setting request.jwt.claims explicitly, as above:
you know exactly what the policy read, because you wrote it.

Put it in CI

Everything here runs in plain SQL, so it belongs in a migration test rather than
in your memory. The shape that works:

begin;
  select set_config('request.jwt.claims', '{"sub":"...A...","role":"authenticated"}', true);
  set local role authenticated;

  -- Fails the migration if the policy ever stops isolating users.
  do $$
  declare visible int;
  begin
    select count(*) into visible from documents where user_id <> '...A...'::uuid;
    if visible > 0 then
      raise exception 'RLS leak: user A can see % foreign rows', visible;
    end if;
  end
  $$;
rollback;

A policy change that reintroduces a leak then fails a deploy instead of failing a
customer. For the wider set of things worth asserting before a release, the
RLS debug checklist
covers the GRANTs, the session plumbing and the cache behaviour that sit around
the policies, and
RLS policy design patterns covers
the shapes that survive multi-tenancy.

The short version

Testing an RLS policy means answering one question: what does someone who is not
me get back? Any context that answers with your own privileges — the SQL editor,
the service key, the table owner — has not answered it.

Two flags and one transaction fix that. And if the policy you are testing throws
infinite recursion detected in policy for relation, that is a different
problem with a known shape:
fix infinite recursion in a Supabase policy.
For everything else that goes wrong on the way,
debugging Supabase RLS issues walks the
full failure catalogue.

Originally published at https://www.iloveblogs.blog