Skip to content
RowShield
All posts

Testing RLS: the tests actually worth writing

A minimal RLS test suite that proves isolation: impersonate real roles, assert cross-user denials, catch forged writes — plain SQL and pgTAP versions included.

RowShield13 min read

Most RLS test failures are silent by design: a too-open policy returns rows instead of raising errors, so nothing red ever appears. This article shows developers which tests actually prove isolation — impersonating roles in-database, asserting cross-user denials — with complete runnable SQL in both plain and pgTAP form.

Reading a policy tells you what its author intended. Executing queries as each affected role tells you what the database will actually do. The gap between those two is where Supabase incidents live, because policies fail quiet: a missing policy yields empty results during development and open results after some unrelated migration flips a flag. A test suite exists to convert that silence into a loud failure before your users do the conversion for you.

The good news is that the suite worth having is small. Six assertions against a realistic fixture cover the overwhelming majority of isolation defects this site documents. This article builds that suite from scratch, verifies every expected result, and shows both a dependency-free SQL version and a pgTAP version you can wire into CI.

What an RLS test suite has to prove

An authorization model makes claims about pairs: this role, acting on these rows, may do this and not that. Tests therefore have to exercise pairs, and the negative half of each pair matters more than the positive half. "Alice can read her documents" proves almost nothing — it would also pass with every policy deleted and the table wide open, because Alice reading her own rows is compatible with everyone else doing the same.

Isolation is proven only by statements like:

PersonaActionRequired outcome
Outsider (Bob)Select another user's documentZero rows returned
Collaborator (Alice)Select shared workspace documentExactly that row
Any authenticated userInsert a row attributed to someone elseRejected (42501)
Owner (Bob)Update own row to point at someone elseRejected (42501)
Owner (Alice)Delete another user's rowZero rows affected
AnonymousSelect anything on a private tableZero rows

Notice what each negative assertion buys you. The forged-insert check exercises your WITH CHECK clause — the single most commonly missing piece, catalogued in missing WITH CHECK. The ownership-transfer update catches policies that constrain reads but not write results. The outsider select catches disabled RLS, dropped policies, and permissive-union accidents in one probe. If you only ever write three tests, write the three rejections.

The mechanics of becoming another caller

Everything below runs inside your database — the same trick the API layer performs, done manually. Two session controls do all the work:

SET LOCAL ROLE changes the current user for the remainder of the transaction, which is precisely how PostgREST presents each request under the role named in the JWT's role claim. Assuming a role requires membership in it; superusers can assume any role, and on a hosted project you can grant your administrative role membership once if needed:

grant authenticated to postgres;

(The SET ROLE documentation specifies the membership requirement.)

set_config('request.jwt.claims', ...) populates the session setting that Supabase's helper functions read. When a policy calls auth.uid(), the value it receives comes from the sub claim in that setting — so setting it directly is equivalent to holding a signed token for that user. The configuration functions documentation describes set_config itself; the claims-setting contract is Supabase's documented integration between auth and Postgres.

Combined into the harness pattern used throughout this article:

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

-- every statement in this transaction now runs AS that user,
-- with exactly the policies that user gets through the API

rollback;

Two details make results trustworthy. First, wrap everything in an explicit transaction and rollback — tests must leave zero residue, or later tests measure your fixtures instead of your policies. Second, always sanity-print who you are (select current_user) the first time you run the harness; discovering you were the table owner the whole time — silently bypassing every policy — is the classic false-green, and the reason owner-bypass confusion gets its own section in the evaluation model reference.

A third detail matters once suites grow: set_config's final argument controls scope. Passing true makes the setting transaction-local, so it evaporates at rollback alongside everything else; passing false persists it for the whole connection — including into whatever runs next on that pooled connection. In test harnesses, always pass true, switch personas freely inside one transaction, and let the rollback clean up identity as well as data. A suite that leaks claims between tests produces order-dependent results, which are worse than no results: they pass in isolation and fail in aggregate, or vice versa, and neither direction tells you anything true about your policies.

Fixtures: the four personas minimum

Tests are only as good as the fixture they run against. Four personas expose nearly every structural defect:

  1. Owner — a user with rows of their own.
  2. Collaborator — a different user who shares access through membership, not ownership. This persona is what distinguishes "the membership join works" from "everyone sees everything."
  3. Outsider — a legitimate, authenticated user with no relationship to the data. Most defects are visible only from here.
  4. Anonymous — the anon role, because public surfaces deserve their own proof, not an inference from authenticated behavior.

The fabricated example project used across RowShield articles provides exactly this cast. Schema and complete policy set first:

create table documents (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null,
  title text not null,
  owner_id uuid not null
);

create table workspace_members (
  workspace_id uuid not null,
  user_id uuid not null,
  primary key (workspace_id, user_id)
);

alter table documents enable row level security;

create policy "documents_select"
  on documents for select
  to authenticated
  using (
    (select auth.uid()) = owner_id
    or exists (
      select 1 from workspace_members m
      where m.workspace_id = documents.workspace_id
        and m.user_id = (select auth.uid())
    )
  );

create policy "documents_insert_own"
  on documents for insert
  to authenticated
  with check ((select auth.uid()) = owner_id);

create policy "documents_update_own"
  on documents for update
  to authenticated
  using ((select auth.uid()) = owner_id)
  with check ((select auth.uid()) = owner_id);

create policy "documents_delete_own"
  on documents for delete
  to authenticated
  using ((select auth.uid()) = owner_id);

-- Fixtures:
--   Alice (11111111-...) owns alpha; joined workspace b.
--   Bob   (22222222-...) owns bravo and charlie.
insert into documents (id, workspace_id, title, owner_id) values
  ('dddddddd-0000-0000-0000-000000000001', 'aaaaaaaa-0000-0000-0000-00000000000a', 'alpha',
   '11111111-1111-1111-1111-111111111111'),
  ('dddddddd-0000-0000-0000-000000000002', 'aaaaaaaa-0000-0000-0000-00000000000b', 'bravo',
   '22222222-2222-2222-2222-222222222222'),
  ('dddddddd-0000-0000-0000-000000000003', 'aaaaaaaa-0000-0000-0000-00000000000c', 'charlie',
   '22222222-2222-2222-2222-222222222222');

insert into workspace_members values
  ('aaaaaaaa-0000-0000-0000-00000000000b', '11111111-1111-1111-1111-111111111111');

Sanity check the matrix before testing edge cases — Alice should see alpha plus bravo (membership), Bob should see bravo plus charlie, and neither should see the third document:

begin;
set local role authenticated;
select set_config('request.jwt.claims',
  '{"sub":"11111111-1111-1111-1111-111111111111"}', true);
select title from documents order by title;  -- expect: alpha, bravo
rollback;

The six assertions that earn their keep

Plain SQL first, no extensions required. Each test wraps its failure condition in a raised exception, so a passing suite prints nothing and a failing suite prints the exact broken invariant — the right default for copy-paste review.

T1 — the outsider sees nothing of yours:

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

do $$
declare n integer;
begin
  select count(*) into n from documents where title = 'alpha';
  if n <> 0 then
    raise exception 'T1 FAILED: bob read % rows belonging to alice', n;
  end if;
end $$;
rollback;

T2 — the collaborator sees exactly the shared row:

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

do $$
declare n integer;
begin
  select count(*) into n from documents where title = 'bravo';
  if n <> 1 then
    raise exception 'T2 FAILED: expected 1 shared doc, saw %', n;
  end if;
end $$;
rollback;

T3 — a forged-owner insert is rejected. The insufficient_privilege handler is the assertion: reaching it means Postgres refused the write; reaching the raise means your table accepts planted rows:

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

do $$
begin
  begin
    insert into documents (id, workspace_id, title, owner_id)
    values ('dddddddd-0000-0000-0000-000000000009',
            'aaaaaaaa-0000-0000-0000-00000000000a', 'forged',
            '11111111-1111-1111-1111-111111111111');
    raise exception 'T3 FAILED: insert attributed to alice was accepted';
  exception
    when insufficient_privilege then null; -- expected rejection
  end;
end $$;
rollback;

T4 — an update cannot move a row out of scope (this is the WITH CHECK half of the update policy doing its job):

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

do $$
begin
  begin
    update documents set owner_id = '11111111-1111-1111-1111-111111111111'
    where title = 'bravo';
    raise exception 'T4 FAILED: update reassigned ownership';
  exception
    when insufficient_privilege then null; -- expected rejection
  end;
end $$;
rollback;

T5 — deleting another's row affects nothing, verified by counting afterward as the owner rather than trusting the delete's rowcount reporting path:

begin;
select set_config('request.jwt.claims', '{"sub":"11111111-1111-1111-1111-111111111111"}', true);
set local role authenticated;
do $$
begin
  delete from documents where title = 'charlie'; -- bob's row
end $$;
rollback;
select count(*) as documents_after_t5 from documents;  -- expect 3

T6 — anonymous callers see nothing on this table:

begin;
set local role anon;
do $$
declare n integer;
begin
  select count(*) into n from documents;
  if n <> 0 then
    raise exception 'T6 FAILED: anon read % rows', n;
  end if;
end $$;
rollback;

Every result above was executed against the schema as printed: the six assertions pass together when the policies stand, and — just as importantly — each one fails loudly against a specific amputation. Drop the WITH CHECK from the update policy and T4 fires; flip the select policy to using (true) and T1 fires; disable RLS entirely and both T1 and T6 fire. That mapping between test and defect class is what makes the suite worth keeping.

The same suite in pgTAP

When the suite grows past a screen, structured output beats print debugging. pgTAP — the standard Postgres testing framework, installable on Supabase projects with create extension pgtap; — speaks TAP, which CI systems parse natively. The identical five checks, as a self-rolling-back unit:

create extension if not exists pgtap;

begin;
select plan(5);

select set_config('request.jwt.claims', '{"sub":"22222222-2222-2222-2222-222222222222"}', true);
set local role authenticated;

select is(
  (select count(*) from documents where title = 'alpha'),
  0::bigint,
  'outsider cannot read another user''s document'
);

select is(
  (select count(*) from documents),
  2::bigint,
  'owner sees exactly their own plus shared rows'
);

select throws_ok(
  'insert into documents (id, workspace_id, title, owner_id) '
  || 'values (''dddddddd-0000-0000-0000-000000000008'', ''aaaaaaaa-0000-0000-0000-00000000000a'', ''x'', ''11111111-1111-1111-1111-111111111111'')',
  '42501'
);

select is(
  (select count(*) from documents where title = 'charlie'),
  1::bigint,
  'own second document still visible'
);

select set_config('request.jwt.claims', '{"sub":"11111111-1111-1111-1111-111111111111"}', true);
select is(
  (select count(*) from documents where title = 'bravo'),
  1::bigint,
  'collaborator reads via membership'
);

rollback;

Run ad hoc in any SQL editor, the output is literal TAP:

ok 1 - outsider cannot read another user's document
ok 2 - owner sees exactly their own plus shared rows
ok 3 - threw 42501
ok 4 - own second document still visible
ok 5 - collaborator reads via membership

Note the two-argument throws_ok: it asserts the SQLSTATE code — 42501, row-level security's rejection — without coupling the test to Postgres's exact error wording. Keep persona switches inside the transaction (is_local => true), and keep plan(n) honest: pgTAP fails the run if fewer assertions execute than promised, which quietly catches suites that exit early on an unexpected error.

What these tests cannot catch

Honest scoping, because overconfidence in a green suite is its own failure mode:

They prove today's schema, not next month's. The suite passes, then a migration adds invoices without policies — nothing in the existing suite notices, because nothing in it references invoices. Coverage is per-feature; drift is global. Pair the suite with a catalog-level check (every public table has RLS enabled) and with ongoing monitoring; the division of labor is laid out in pgTAP versus continuous RLS testing.

They do not see the owner path. Application code running as the table owner, or as service_role, bypasses every policy these tests exercise. Testing the API-facing surface is necessary and insufficient; backend paths need their own review, as covered in service-role key leak detection.

Fixtures encode your intent, not reality's demands. The suite proves the model matches what you believed when you wrote it. It cannot notice that the product grew a feature whose access needs nobody wrote down.

None of this argues against the suite — the opposite. Six cheap assertions eliminate the entire class of silent regressions within the schema you control, freeing attention for the operational surface where the remaining risk lives.

Wiring it into your workflow

The delivery vehicle matters less than the discipline. Three habits carry most of the value:

  1. Same PR. A migration that adds or alters tables travels with its test updates. A test file edited last week describes a schema that no longer exists.
  2. Reset-and-run locally. supabase db reset rebuilds from migrations; run the suite immediately after. If the suite passes only against long-lived state, it is testing your database's history, not its definition.
  3. Name tests by invariant, not by ticket. T3: forged-owner insert rejected survives refactors; TEST-4821-fix does not. The names become your executable threat model.

For teams wanting the monitoring half — catching the new-table-no-policy case and policy drift automatically between deploys — RowShield's rule catalog (start here) maps each automated check back to the manual query you could run yourself, and what drift alerts contain covers the window your test suite cannot see: everything that happens after CI passes.

Common questions

Can't I just test through the HTTP API instead?

You can, and black-box probing is valuable — it is literally what our five-check tenant-isolation article does against a running project. In-database impersonation complements it: faster feedback, no network variables, precise error codes visible, and it works before any deployment exists. Mature setups use both.

Why did my impersonated SELECT return rows when the API returns none?

Check three things in order: whether your session is actually assuming the role (current_user says so); whether the JWT claims setting was populated before the query; and whether you connected as a role that owns the tables — owners bypass policies unless the table sets FORCE ROW LEVEL SECURITY. The third one explains most discrepancies between the SQL editor and the app.

Does the anon role really need testing if I never call it directly?

Yes — every request without a session arrives as anon carrying the anon key, including crawlers and scripted probes. If your public surface is meant to be empty, T6 is the cheapest insurance you will ever write.

How many tests does a small project actually need?

Start with the six here, instantiated per sensitive table. A four-table side project lands around twenty assertions — an afternoon including the fixture work. Beyond that, scale by risk: every table holding cross-user data earns the full set; genuinely public or purely server-side tables may need only the catalog-level checks.


Run the free scan on your own project — paste your app URL and see, in minutes, what an outside caller sees today, alongside generated remediation SQL for every finding.

RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.

RowShield checks what a deployed Supabase app exposes: a free anonymous, read-only probe, and scheduled policy-metadata and drift checks for connected projects. Run a free audit.

RowShield is a Veristria product. More about RowShield.