Your pgTAP RLS testing suite is green. Every assertion passes, your CI pipeline shows a clean checkmark, and you ship the migration with confidence. Then a support ticket comes in: a tenant is asking why they can see another company's invoices.
This happens more often than most pgTAP tutorials for RLS testing let on. The typical guide shows you how to write is_empty() and results_eq() assertions, then stops there. What it doesn't tell you is that the exact same test suite proving your row-level security policies work can pass while the policy underneath is wide open, or while a routine migration quietly drops the one setting that makes RLS apply to your own database owner.
This guide covers what most pgTAP RLS testing tutorials skip: how PostgreSQL actually evaluates row-level security under the hood, why your default test role can silently defeat the entire point of testing, and five specific failure patterns that let a broken policy sail through a green test suite. If you're building a multi-tenant B2B SaaS product and want your tests to actually prove something, keep reading. And if your schema isn't locked down yet, start with our complete guide to multi-tenant Supabase architecture before layering tests on top of it.
is_empty(), isnt_empty(), and throws_ok() inside a transaction you roll back, so nothing you insert survives the test. The catch: PostgreSQL skips RLS entirely for the table owner and any role with BYPASSRLS unless you run ALTER TABLE ... FORCE ROW LEVEL SECURITY, which is why a green test suite can still leak data once code runs under a privileged role in production.
Table of Contents
- What Is pgTAP and Why Does RLS Need Database-Level Tests?
- Why Does My pgTAP Test Pass When My RLS Policy Is Broken?
- Getting Started with pgTAP RLS Testing in Supabase
- Writing Your First RLS Test: A Complete Walkthrough
- Why Does USING (true) Still Pass My pgTAP Tests?
- The FORCE ROW LEVEL SECURITY Trap That Table Owners Create
- The Silent UPDATE and DELETE Leak Your SELECT Tests Miss
- Why the TO Clause Matters: The PUBLIC Pseudo-Role Trap
- Column-Level Security: What Row-Level Security Doesn't Protect
- Avoiding Decorative Tests: Seeding Data That Actually Proves Isolation
- Quick Answers About pgTAP RLS Testing
- Frequently Asked Questions
What Is pgTAP and Why Does RLS Need Database-Level Tests?
Simply put, pgTAP is a unit testing framework that runs entirely inside PostgreSQL, using SQL functions like ok(), is(), and results_eq() to assert facts about your database, from schema structure to runtime query results. Instead of testing RLS through your application layer (an API call, a Supabase client SDK, a middleware chain), pgTAP lets you query the database directly as different Postgres roles and check exactly what each one can see.
That distinction matters more than it sounds. Application-level testing can only tell you what your API returns, which conflates your RLS policy with your ORM, your caching layer, and everything in between. A pgTAP test isolates the one variable that actually enforces tenant isolation: the policy itself. If a policy is broken, you find out at the database layer, not three services downstream after a customer already noticed.
The trade-off is that pgTAP tests only prove what you explicitly assert. A test suite that never switches roles, never checks row counts, and never queries the Postgres catalog directly can still report 100% green while your isolation model is fundamentally broken. The rest of this guide walks through exactly how that happens, and how to write assertions that actually close the gap.
Why Does My pgTAP Test Pass When My RLS Policy Is Broken?
Because pgTAP runs as the postgres superuser by default, and PostgreSQL never enforces row-level security against a role with the BYPASSRLS attribute or against a table's owner. If your test file never explicitly switches to a lower-privileged role, every query in it sees every row in the table, whether your policy is airtight or nonexistent.
This is the single most common source of false confidence in RLS testing, and it's also the least discussed. Developers land on the official pgTAP or Supabase documentation, copy a basic assertion pattern, and never notice their test is running with superuser privileges the entire time.
-- This "test" will pass whether RLS works, is misconfigured, or is
-- disabled outright, because it never leaves the postgres role
begin;
select plan(1);
insert into documents (id, tenant_id, title) values (99, 'tenant_a', 'test doc');
select isnt_empty(
$$ select * from documents where tenant_id = 'tenant_a' $$,
'a document is visible' -- true for ANY role run as postgres; meaningless as an RLS test
);
select * from finish();
rollback;
The fix is procedural, not clever: every RLS assertion has to run after an explicit role switch, using set local role authenticated (or your equivalent non-superuser role) and a mocked identity. If a test file skips that step, it isn't testing RLS at all, no matter how many assertions it contains.
Getting Started with pgTAP RLS Testing in Supabase
Setting up pgTAP RLS testing takes three steps: enable the extension, decide how you'll mock an authenticated user, and wrap every test file in a transaction you roll back so nothing you insert during testing persists.
-- Enable pgTAP once per database
create extension if not exists pgtap;
The part that trips people up is mocking auth.uid() so a query behaves as if a specific tenant user is logged in. There are two common approaches, and the guides currently ranking for this topic are oddly split on which one to recommend.
| Approach | How It Works | Trade-off |
|---|---|---|
| Manual JWT injection | Run set local request.jwt.claim.sub to '<uuid>' before each query block |
No extra dependency, but verbose and easy to get wrong across a large suite |
| Community test helpers | A helper extension like basejump-supabase_test_helpers wraps the same call into one function, e.g. authenticate_as('member') |
Cuts boilerplate a lot, but adds a versioned extension you now have to track |
Neither approach is objectively better. Manual injection keeps your test suite dependency-free, which matters if you're strict about what runs in CI. Helper extensions save real time once you're writing dozens of role-switching tests. Pick one and use it consistently, since mixing both patterns across a codebase is how the default-role-bypass mistake from the last section creeps back in.
Writing Your First RLS Test: A Complete Walkthrough
A complete RLS test has three parts: seed data as a privileged role, switch to an authenticated role and mock the tenant identity, then assert what that role can and can't see. Here's a full example testing tenant isolation on a documents table.
begin;
select plan(3);
-- Seed data as postgres, bypassing RLS entirely for setup
set local role postgres;
insert into documents (id, owner_id, tenant_id, title)
values
(1, 'a1111111-1111-1111-1111-111111111111', 'tenant_a', 'Tenant A doc'),
(2, 'b2222222-2222-2222-2222-222222222222', 'tenant_b', 'Tenant B doc');
-- Switch to an authenticated user belonging to tenant_a
set local role authenticated;
set local request.jwt.claim.sub to 'a1111111-1111-1111-1111-111111111111';
select is_empty(
$$ select * from documents where tenant_id = 'tenant_b' $$,
'tenant_a user cannot see tenant_b documents'
);
select isnt_empty(
$$ select * from documents where tenant_id = 'tenant_a' $$,
'tenant_a user can see their own tenant documents'
);
select results_eq(
$$ select count(*)::int from documents where tenant_id = 'tenant_a' $$,
$$ values (1) $$,
'exactly one row is visible, not silently filtered to zero or leaked to all'
);
select * from finish();
rollback;
Notice the final assertion checks an exact count rather than just "something came back." That distinction is the difference between a test that looks reassuring and one that actually catches a broken policy, which is the entire subject of the next section.
One detail worth flagging: because the whole file runs inside a single begin...rollback block, the rows inserted by the postgres role earlier in the same transaction are immediately visible to the authenticated-role queries that follow. Nothing needs to be committed first. Cross-transaction visibility, like waiting on a trigger or a background job in a separate connection, is a different problem entirely, and pgTAP alone won't help you test it.
Why Does USING (true) Still Pass My pgTAP Tests?
Because a naive existence check can't tell the difference between a correctly filtered row and a completely open table. If a policy body is using (true), whether from a typo or from real logic that got commented out during debugging and never restored, every row in the table becomes visible to every role. An assertion like isnt_empty() still passes, because rows genuinely do come back. It just doesn't check whether the right rows came back.
begin;
select plan(2);
set local role authenticated;
set local request.jwt.claim.sub to 'stranger-0000-0000-0000-000000000000';
-- Misleading: true whether RLS is working correctly or wide open
select isnt_empty(
$$ select * from documents $$,
'stranger sees something'
);
-- The check that actually catches a tautological policy
select results_eq(
$$ select count(*)::int from documents $$,
$$ values (0) $$,
'stranger sees exactly zero rows across all tenants'
);
select * from finish();
rollback;
This is exactly the kind of gap we cataloged in our rundown of five silent RLS mistakes that leave your database exposed. The rule that follows is simple: every negative RLS test needs an exact count(*) assertion, not just an existence check, or a wide-open policy can hide behind a passing test suite indefinitely.
The FORCE ROW LEVEL SECURITY Trap That Table Owners Create
PostgreSQL does not apply row-level security to a table's owner, or to any role with the SUPERUSER or BYPASSRLS attribute, unless you explicitly run ALTER TABLE tablename FORCE ROW LEVEL SECURITY. This is documented behavior in PostgreSQL's own row security documentation, and it's the single most consequential gap across the pgTAP tutorials currently ranking for this topic. None of them mention it.
Here's why it matters in production even when your pgTAP suite is spotless. Migrations, background jobs, and connection poolers frequently connect as the database owner or a service account with elevated privileges, not as an ordinary authenticated user. If a schema refactor recreates a table and the FORCE ROW LEVEL SECURITY flag doesn't get reapplied, every policy on that table is still technically "enabled," but silently inert for any privileged connection. Your pgTAP suite, which correctly authenticates as a regular user, keeps passing. Meanwhile a backend job running as the owner reads or writes across every tenant without a single check firing.
-- Assert the flag directly against the Postgres system catalog,
-- rather than assuming it survived the last migration
select ok(
(select relforcerowsecurity from pg_class where relname = 'documents'),
'documents table has FORCE ROW LEVEL SECURITY enabled'
);
Add this single assertion to every table's test file and you've closed a gap that, as far as we could find, isn't addressed anywhere else currently ranking for pgTAP RLS testing.
The Silent UPDATE and DELETE Leak Your SELECT Tests Miss
PostgreSQL combines multiple permissive policies on the same table with OR logic, per command. That means a correctly scoped SELECT policy and an overly permissive UPDATE policy can coexist on the same table without either one being individually wrong on paper, while the table as a whole is fully exposed to unauthorized writes.
Picture a table where the SELECT policy correctly limits a user to their own tenant's rows, but the UPDATE policy uses a tautology like using (true), maybe left over from an early prototype. A targeted pgTAP test that reads a row, updates it, and reads it back only ever touches rows the SELECT policy already allows, so it passes. A blind bulk update with no WHERE clause bypasses that visibility scoping completely and rewrites every row in the table.
begin;
select plan(1);
set local role postgres;
insert into documents (id, tenant_id, title) values
(1, 'tenant_a', 'original'),
(2, 'tenant_b', 'original');
set local role authenticated;
set local request.jwt.claim.sub to 'tenant-a-user-0000-0000-000000000000';
-- A blind write with no tenant scoping at all
update documents set title = 'overwritten';
select results_eq(
$$ select title from documents where tenant_id = 'tenant_b' order by id $$,
$$ values ('original') $$,
'a tenant_a user cannot modify tenant_b rows with a blind update'
);
select * from finish();
rollback;
Run at least one blind, unscoped write test per table alongside your targeted ones. If it only modifies the rows you expect, your policies are consistent across commands, not just individually well-intentioned.
Why the TO Clause Matters: The PUBLIC Pseudo-Role Trap
If a policy definition omits an explicit TO clause, PostgreSQL applies it to the PUBLIC pseudo-role by default, which includes anonymous requests and any newly created role that hasn't been scoped otherwise. A policy meant only for logged-in users can end up silently covering unauthenticated ones too, simply because nobody typed to authenticated when the policy was written.
select policy_roles_are(
'public', 'documents', 'tenant_isolation', ARRAY['authenticated'],
'tenant_isolation policy is scoped to authenticated only, not PUBLIC'
);
This assertion checks the policy's actual role scope against the catalog rather than trusting the SQL file that created it. Run it for every policy that's supposed to require authentication. It's a one-line check that catches a mistake most teams only discover during a security review.
Column-Level Security: What Row-Level Security Doesn't Protect
RLS controls which rows a role can access, not which columns within a row it can modify. A user who passes every row-level check imaginable can still rewrite any column on their own row, including sensitive fields like a balance, an internal status flag, or a permissions field, unless you separately restrict those columns.
-- Restrict which columns an authenticated user can write to,
-- independent of whatever the RLS policy allows at the row level
revoke update (ssn, internal_notes) on user_profiles from authenticated;
begin;
select plan(1);
set local role authenticated;
set local request.jwt.claim.sub to 'tenant-a-user-0000-0000-000000000000';
select throws_ok(
$$ update user_profiles set ssn = '000-00-0000' where id = 'tenant-a-user-0000-0000-000000000000' $$,
'42501',
null,
'authenticated user cannot write to ssn even on their own row'
);
select * from finish();
rollback;
None of the pgTAP tutorials we reviewed test this. RLS and column privileges answer different questions, and a table can pass every row-level test you write while still exposing a column it should never have.
Avoiding Decorative Tests: Seeding Data That Actually Proves Isolation
A test that manually invents "tenant A" and "tenant B" data only proves the policy works against the data you imagined, not against the policy as it's actually defined. If seed data doesn't genuinely map to a different tenant under the policy's real logic, a pgTAP test can pass while the underlying policy is fundamentally broken. Community discussions on generating pgTAP tests directly from policy definitions have called this pattern out as tests that degrade into decoration rather than proof.
The more rigorous approach works backward from the policy itself, rather than forward from an assumption about what it checks.
-- Read the actual USING expression from the catalog instead of
-- trusting your memory of what the policy is supposed to do
select polname, pg_get_expr(polqual, polrelid) as using_expression
from pg_policy
where polrelid = 'documents'::regclass;
Use that output to design seed data that exactly satisfies the predicate on one side and exactly violates it on the other. For a simple tenant_id = current_tenant() policy this is trivial. For a compound policy combining ownership, team membership, and a published flag, it's the only way to be sure your test data exercises every branch of the condition instead of accidentally testing the same branch three times.
Quick Answers About pgTAP RLS Testing
What Causes RLS Tests to Pass While the Policy Stays Broken?
Simply put, a pgTAP test passes falsely when it never leaves a privileged role, checks for "any row" instead of an exact count, or never queries the Postgres catalog to confirm settings like FORCE ROW LEVEL SECURITY and a policy's TO clause actually survived the last migration. Each gap lets a broken policy produce a green checkmark, and it matters most on tables where more than one tenant shares storage.
pgTAP RLS Testing at a Glance
| Aspect | Details |
|---|---|
| Test should run as | An explicitly mocked authenticated role, never postgres or a BYPASSRLS role |
| Most common false pass | Existence checks (isnt_empty) instead of exact count(*) checks |
| Table owner bypass fix | ALTER TABLE ... FORCE ROW LEVEL SECURITY, asserted against pg_class |
| Cross-command leak | Test a blind UPDATE/DELETE with no WHERE clause, not just scoped reads |
| Applies to | Any PostgreSQL database using RLS, tested via pgTAP in Supabase or self-managed Postgres |
When Does This Level of RLS Testing Matter?
It matters most for multi-tenant B2B SaaS products storing more than one customer's rows in shared tables, where RLS is the primary isolation boundary. It also matters for teams facing SOC 2 or GDPR-style compliance audits, where auditors increasingly expect proof that access controls are tested, not just configured.
Frequently Asked Questions
What is pgTAP used for?
pgTAP is a unit testing framework for PostgreSQL that runs as SQL functions inside the database itself. It lets you assert facts about your schema, like table or column existence, and about runtime behavior, like whether a row-level security policy actually filters the data it's supposed to.
How do I test RLS policies in Supabase?
Install the pgTAP extension, wrap assertions in a transaction you roll back, seed data as the postgres role to bypass RLS, then switch to the authenticated role and mock a user identity before running is_empty(), isnt_empty(), or results_eq() checks against the policy you're testing.
Why does my pgTAP test pass as the postgres role even when RLS is broken?
The postgres role is a superuser with the BYPASSRLS attribute, so PostgreSQL skips row-level security for it entirely. Any test that never switches to an authenticated role is testing your schema, not your policy, and will pass regardless of what that policy actually does.
Do I need basejump-supabase_test_helpers to test RLS with pgTAP?
No. You can mock a user manually by setting a JWT claim yourself, but community helper extensions wrap that boilerplate into a single function call and are easier to maintain across a large suite. Either approach works as long as you actually switch roles first.
Does an insert need to be committed before I can test it with pgTAP?
No. pgTAP wraps an entire test file in one uncommitted transaction, so inserts made earlier in that same transaction are visible to later statements in the same file, even though nothing is written to disk. Cross-transaction visibility is a separate, unrelated problem.
Can a policy with USING (true) really pass an RLS test?
Yes, if your test only checks that a row comes back. An existence check returns true whether a policy correctly filtered one row or leaked every row in the table. Use a count(*) assertion with an exact expected value to catch a tautological policy that leaks everything.
Does row-level security protect specific columns?
No. RLS controls which rows a role can see or write, not which columns within them. A user who passes every row-level check can still update a column like a balance or an internal flag on their own row, unless you separately revoke column privileges.
How do I run pgTAP tests automatically in CI/CD?
The Supabase CLI's test db command runs your pgTAP suite against a local database and can be wired into a GitHub Actions workflow on every pull request. We're covering the full pipeline setup in a dedicated post soon.
Final Thoughts
pgTAP RLS testing only proves what you actually asked it to check. A green suite that never leaves the postgres role, never counts rows, and never asserts FORCE ROW LEVEL SECURITY against the catalog isn't proof your multi-tenant schema is safe. It's decoration. Add explicit role switching, exact count(*) assertions, and a couple of catalog-level checks to your existing suite, and "the tests pass" starts meaning something you can defend in a security review or a SOC 2 audit.
If your policies are already slow enough that engineers are tempted to loosen them just to make queries run faster, that's worth resolving before you write another test. Reading EXPLAIN ANALYZE output will tell you whether a slow policy is a testing problem or a query planner problem before you touch a single line of SQL. Once these tests exist, the next step is making sure they run correctly on every pull request, not just on your laptop. See automating pgTAP RLS tests in GitHub Actions for the CI setup, including the role-switching step that most tutorials skip. Bookmark this guide, and run the FORCE ROW LEVEL SECURITY check against your own schema today. It takes thirty seconds and it's the one gap none of the existing pgTAP tutorials mention.
