Writing RLS Policy Tests for Every Role and Tenant

A hand-maintained list of roles drifts the moment someone adds a service account. Query the catalog instead. The roles you care about are the ones that can reach the table: those holding a grant, plus the special anon-style roles your platform uses. Supabase, for example, maps unauthenticated requests to anon and signed-in requests to authenticated the Supabase RLS guide.

-- roles that hold a privilege on a given table
select distinct grantee
from information_schema.role_table_grants
where table_schema = 'public'
  and table_name = 'invoices'
  and grantee not in ('postgres', 'service_role')
order by grantee;

For tenants, read the tenant table itself. If a tenant exists in production but not in the matrix, you have an untested boundary, and the cheapest fix is generating cases from data rather than from a checklist. The same idea drives generated suites that read policies from the catalog and emit one test per table, command and identity, including owner, another user, another tenant and anon the rlsautotest discussion.

A practical cap: generate a case for every distinct role, and for tenants, take all tenants if you have fewer than roughly a few dozen in the fixture, otherwise a fixed sample of two tenant ids plus any tenant flagged as sensitive. Keep the sample ids in the committed fixture so the matrix is deterministic.

The fixture is where most RLS suites quietly stop working. If tenant A has zero invoices and tenant B has three, a completely broken policy on tenant A returns nothing and the test passes. The fixture has to be built so that a missing predicate changes the result set.

Three rules make that true:

1. Every tenant owns at least two rows in every table you test.
2. Tenant A and tenant B share identical non-key values (same status, same amount, same created date) so the only distinguishing column is the tenant id. That means a missing tenant predicate returns rows that differ only by tenant, which the snapshot diff will show as extra ids.
3. Include one row that belongs to a third tenant the acting user is a member of, and one that does not. Membership-based policies are where the cross-tenant leaks live, and a two-tenant fixture cannot distinguish "my tenant" from "any tenant I am a member of."

Generated test tooling describes this as reverse-predicate seeding: work backward from each policy to the exact rows that satisfy it and the exact rows that violate it, so "another tenant cannot see this" is asserted against a row that genuinely belongs to another tenant the rlsautotest discussion. You can do the same by hand. For a policy tenant_id = current_setting('app.tenant_id')::uuid, seed rows for tenant A, tenant B, and a user who is a member of both.

Keep the fixture as SQL migrations checked into the repository, applied inside a transaction that rolls back after the run, the pattern the Supabase testing guide uses with begin;, select plan(n); and rollback; the Supabase testing overview. Rolling back keeps the suite idempotent and parallel-safe.

There are two mechanisms in wide use, and the harness should pick one per project rather than mixing them.

Role-based isolation connects (or switches) as a database user whose name or role is the tenant, and the policy compares a column to current_user Craig Kerstiens on RLS for tenants. Session-variable isolation keeps one application role and sets a per-request variable that the policy reads with current_setting the same Crunchy Data walkthrough.

For the second, SET LOCAL scopes the setting to the current transaction so a pooled connection cannot leak one request's tenant into the next the queryplane multi-tenant patterns guide. Your harness should mirror whatever the application does, and use SET LOCAL inside the test transaction so every case starts clean.

begin;
-- the subject of the test: real role, real tenant context
set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
set local app.tenant_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';

select id from public.invoices order by id;
rollback;

Two details make this reliable. First, SET LOCAL role only holds until the transaction ends, so wrap every case in its own transaction; a leaked SET ROLE in a long-lived session is exactly the cross-request bleed that SET LOCAL prevents. Second, set both the identity claim and the tenant variable the policy actually reads, even if one appears redundant today. A policy change that switches from one to the other should break the test, not silently keep passing.

If you use pgTAP, the runner drives the same statements inside a transaction, and the Supabase CLI runs the suite with supabase test db the Supabase testing overview. Helper libraries wrap user creation and authentication, for example authenticate_as and clear_authentication the Basejump pgTAP guide. Helpers are convenient, but assert what they set, because a helper that leaves you as postgres turns every test green.

Counting rows is the weakest assertion you can write. Use results_eq comparing the actual id list with a committed expected list. The Supabase example suite uses results_eq to check row counts per user and lives_ok to confirm that an allowed insert succeeds the Supabase testing overview. Counts catch some bugs; exact ids catch reordering, widening and row swaps.

select results_eq(
  $$ select id from public.invoices order by id $$,
  $$ values ('id-101'::uuid), ('id-102'::uuid) $$,
  'tenant A member sees exactly their two invoices'
);

For columns, assert the shape as well as the ids where it matters, because a policy controls rows and does not control which columns a grant exposes. A column-level leak looks like select * returning salary to a role that should only see department, and only a snapshot of the column list catches it. Record the column set once per query per role in the snapshot file.

Store snapshots next to the tests as a single data file keyed by case name, table, role and tenant. The format does not matter as long as regeneration is a deliberate command and a diff is a failure. A compact shape:

case: invoices.list.authenticated.tenant_a
rows: 101, 102
columns: id, amount, status
case: invoices.list.authenticated.tenant_b
rows: 201, 202, 203
columns: id, amount, status

The third tenant in that snapshot, with three rows where the other two have two, is not an accident. It is the row that will appear in tenant A's result if the policy widens, and the diff will say so by name.

The point of a committed snapshot is that a policy edit cannot change what a role sees without a human seeing it. When someone edits a policy file, three outcomes are possible and only one is acceptable silently.

The last row is the one that erodes the suite. Treat snapshot regeneration as a production change: require a reviewer, and put the case name and the leaked tenant id in the commit message. If regeneration is one keystroke and no one reads the diff, the snapshot becomes a rubber stamp within a month.

For teams that want the matrix generated rather than written, tooling exists that reads policies from the catalog, emits pgTAP tests plus seed data per identity, and prints a per-identity access report with a CI gate that fails on a leak or an unprotected table the rlsautotest discussion. Use it to bootstrap the matrix, then keep the committed snapshots as your contract. Generated cases catch nothing on day one that your snapshot does not catch on day two.

Run these checks as part of the suite itself, not as prose in a wiki:

• Every table in an exposed schema has row security enabled, asserted from pg_class.relrowsecurity.
• Every policy in that schema appears in at least one test case, joined from pg_policies.
• No test subject role has rolbypassrls and no test runs as the table owner PostgreSQL row security documentation.
• Views over protected tables are tested too, because views bypass RLS by default when created by the owner, so a view can hand out rows its policies were meant to withhold the Supabase RLS guide.
• Storage buckets get the same treatment: uploads to a bucket are denied without a policy on storage.objects, and the service key bypasses RLS entirely, so test with the anon and authenticated keys only the Supabase storage access control guide.
• Policies that allow writes are tested with WITH CHECK, because a USING-only policy lets a user move a row out of their own visibility, and the mutation needs its own case the queryplane multi-tenant patterns guide.
• Every case runs inside a transaction that rolls back.

Wrap the checks in one test that fails loudly and names the gap, for example table public.reports has RLS disabled. A missing policy is a default-deny hole in availability and, once someone adds a permissive grant to fix the outage, a data hole.

For performance, do not assert timings in the policy suite; they are unstable in CI. Do assert that the columns your policies filter on are indexed, since a missing index on the tenant column turns every tenant query into a scan, and an index on the policy column has shown over 100x improvement on large tables the Supabase RLS performance discussion.

If your team operates the database through a managed platform and the authorization model is spreading across services, this is a reasonable moment to book a free discovery call and get a recommendation on where RLS should stop and application authorization should begin.