How I test Postgres row-level security for tenant isolation

The most natural way to test row-level security passes green and proves nothing. You seed two tenants, query as one, assert you only see that tenant's rows, and the test goes green whether or not your policies are correct. PI6 is an agent-native CRM I built from scratch, with row-level security on all 27 tenant-scoped tables, and the isolation test harness is designed against that false green from the start. This is how I test row-level security in multi-tenant Postgres so that a passing run actually means a tenant cannot reach another tenant's data.

Why the obvious test proves nothing

Row-level security is enforced by the database, so the test has to reach the database through the same enforcement the application does. The reason the naive test lies is that the most convenient connection to write it with is usually the one that bypasses the policy entirely.

Everyone knows a superuser bypasses row-level security. The part that catches people is that a plain, non-superuser table owner bypasses it too, by default. If your test seeds fixtures and reads them back through a single Prisma client, and that client connects as the role that owns the tables (which is often the same role your migrations run as), every query sees every row. The policy is never consulted. The suite passes, and it would pass if you deleted every policy in the schema. That is the trap, and it is quiet, because nothing errors and the assertions are satisfied.

So the first thing the harness has to guarantee is that the connection under test is subject to the policy at all. Until that is true, none of the isolation assertions mean anything.

FORCE, and a role that cannot bypass

Two changes close the owner hole, and PI6 uses both.

The first is FORCE ROW LEVEL SECURITY on every tenant-scoped table, not just ENABLE. ENABLE turns policies on for normal roles but still lets the table owner through. FORCE makes the policy apply even to the owner, so that after it, only true superusers and roles with the BYPASSRLS attribute are exempt. The migration that turns on isolation runs FORCE across all 27 tables for exactly this reason.

The second is that the application never connects as the owner. Production and the test harness both connect as a restricted role, pi6_app, created with NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS. That role does not own the tables it queries, and it cannot grant itself an exemption. The restricted role is not a hardening extra bolted on later. It is a prerequisite, because row-level security enforces nothing against an owner or superuser connection, so the isolation only becomes real once the app is talking to Postgres as a role that the policy can actually constrain. The role and the policies were designed together for that reason.

Once both are in place, a query that runs as pi6_app without the right tenant context returns no rows, and that is the behavior the test can finally trust.

Seed as one role, test as another

The harness keeps two database clients, and the split is the point. A privileged client, connected as a superuser role, exists only to set up and tear down fixtures. It seeds two tenants and their rows without fighting the policy, which is what you want during setup. The client under test connects as pi6_app, the same restricted role production uses, and every assertion about what a tenant can and cannot see runs through it.

This mirrors production exactly. The real application seeds nothing as an owner at request time; it reads and writes as pi6_app, under a tenant context, against forced policies. Because the test exercises the same role and the same enforcement path, a green result is evidence about the code that actually ships, not about a privileged shortcut that only exists in the test file.

The failure I am guarding against here is subtle precisely because it is structural. If you seed and assert through one owner-privileged client, the test is not wrong so much as vacuous. Splitting the clients is what converts a vacuous green into a meaningful one.

Test the test's own assumption

A harness that depends on the app role lacking BYPASSRLS should verify that the app role actually lacks it. So there is a guard test whose only job is to assert that the role under test is not a superuser and does not have BYPASSRLS. It does not test any policy. It tests the assumption every other test in the suite is built on.

This matters more than it looks. Role attributes drift. Someone grants a permission to fix an unrelated problem, a migration gets edited, a local database is set up by hand and the app role quietly ends up as a superuser. Any of those turns the entire isolation suite green while proving nothing again, and none of them touch a line of policy SQL. Asserting the role's attributes directly is how you find out that your proof rests on a foundation that is still there.

The tenant context usually fails the safe way

The other thing people expect to be a silent trap is the tenant context, and in practice it mostly is not. PI6 sets the current tenant with a GUC and the policies match on it, roughly tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid. If that setting is missing or wrong, the row simply does not match the policy, so the query returns zero rows.

Zero rows is a false red, not a false green. A test that forgets to set the tenant tends to fail loudly, with an empty result where it expected data, which is the failure mode you want. Fail closed is the correct default for isolation, and the policy is written so that the absence of context denies access rather than granting it.

There is one place the tenant context does bite, and it is worth stating plainly because it is a real production hazard rather than a test artifact. When you set the tenant with a transaction-local set_config('app.tenant_id', ..., true), that value lives on one connection for the length of one transaction. Behind a connection pool, a sibling operation that lands on a different pooled connection does not see it. The result is a query that returns nothing, or a write that fails a policy check, so it surfaces as a visible error rather than a leak. The lesson is that the tenant GUC has to be set inside the same transaction as the work it governs, and you cannot assume it carries across pooled connections. PI6's data layer is deliberate about that boundary, and the tests run the same way so they exercise it.

Green is only proof if it runs the real path

Row-level security is a strong control, and it is also easy to convince yourself you have tested when you have not. The failure mode to design against first is a test that passes while proving nothing, and every guard in this harness exists to remove one way that can happen. Force the policy so the owner cannot slip past it. Connect as a role that cannot bypass, the same role production uses. Seed with a separate privileged client so the assertions run through the restricted one. Assert directly that the restricted role never gained an exemption. After all of that, a green run is a real statement: under a given tenant context, a tenant could not read another tenant's rows.

What this does not catch is anything above the database. Row-level security will not save you from an endpoint that leaks another tenant's ID in an error message, a background job that runs with the wrong tenant context set, or a query that scopes correctly by tenant and still returns a field it should not. Those are application bugs, and they belong in code review and in tests written against the API, not in the policy layer. The database can prove that a tenant cannot read another tenant's rows. It cannot prove that your code always asks it the right question. Postgres official documentation on row-level security is precise about where that line sits, and staying on the right side of it is most of the work.

Related reading

Architecture

Stripe Connect escrow for a marketplace: the payout guards that actually hold

AI Engineering

How I build MCP servers