Ten days ago I wrote that the Postgres role which runs your migrations bypasses every row-level security policy you wrote. Mads Hansen replied with the obvious next step, and it was better than the article: run the same cross-tenant query suite twice, once as the serving role and once as the owner role, and compare.
I did. It works, and I published the assertion. Then Marco took it apart with one question: what happens if you remove the violating state from the fixture? I measured that too, and it caught my own advice out. Equality is green in four databases out of five, and three of them are broken.
So this is both halves. If you only take the first one home, you get a test that is green on a database with row-level security switched off.
The invariant is not "the owner run must fail"
My first instinct was to assert that the owner run leaks: it sees rows from other tenants, so the assertion is assertNotSame. That assertion is wrong, and it goes red on the day someone applies the correct fix.
ENABLE ROW LEVEL SECURITY leaves the table owner exempt. FORCE ROW LEVEL SECURITY does not. After FORCE, the owner sees exactly what the serving role sees. Measured on PostgreSQL 18.3, same fixture, same query:
### 1. ENABLE only: the owner ignores the policy
tenant 1 : serving=[1] owner=[1,2] DIVERGENT <-- leak
tenant 2 : serving=[2] owner=[1,2] DIVERGENT <-- leak
### 2. After FORCE ROW LEVEL SECURITY: both runs return the same set
tenant 1 : serving=[1] owner=[1] IDENTICAL
tenant 2 : serving=[2] owner=[2] IDENTICAL
So the invariant that survives both worlds is: both runs return the same set. Not "the owner run fails". Write it as equality and it stays green when the schema is fixed, red when someone drops FORCE in a migration.
That is the assertion I gave in the thread, and it is where I would have stopped.
Equality alone is green in three databases that are broken
Marco's reply is the sentence I would keep from the whole exchange: the fixture is part of the security claim, not just test data. And its consequence, which is the part that made me go back to psql: if removing the violating state doesn't make the test fail, then the test was never actually proving the invariant.
So I built the same table five different ways and ran three assertions side by side. Tenant 1 is the one being queried. Equality is the double run above. Scoped is the ordinary one everybody writes: the serving role returns only its own rows. Disjoint is Marco's, made executable: set the context to the second tenant, assert that set is non-empty and does not intersect the first.
equality scoped disjoint
2 tenants, no RLS at all GREEN RED RED
2 tenants, ENABLE only RED GREEN GREEN
2 tenants, FORCE GREEN GREEN GREEN
1 tenant, ENABLE only GREEN GREEN RED
1 tenant, no RLS at all GREEN GREEN RED
Only one row of five is a sound database. Read the columns rather than the rows.
Equality is green in four rows and three of them are broken. Two runs that both return everything are still equal, so it never notices the world where there is no policy at all. It is red in exactly one row: the one my article was about.
Scoped does not rescue it. Look at the bottom two rows: with a single organisation in the fixture, the serving role returns its one row whether the policy applies, or is inert because the app account owns the table, or does not exist at all. Both assertions are green, and one of those databases has the owner bypass sitting in it waiting for a second customer to sign up.
That is the headline, and it is worth saying without the table: if your fixture has one tenant, your tenant-isolation test passes against a database with no row-level security on it. The failure direction here is always too many rows, never zero, so a fixture that contains nothing to refuse cannot tell a working boundary from an absent one.
Only the disjointness column notices that the fixture stopped being a violation. It is red in the three broken rows equality sleeps through, green in the sound one, and between them the pair covers all four broken worlds.
Disjointness has to stay inside the boundary it is checking
The part I did not expect was how to write it without reaching for privilege.
The obvious way to assert "there is still a second tenant in that table" is to go look with a connection that can see everything. Under FORCE there is no such connection left, which is the whole point of FORCE, and grabbing a superuser to check the fixture puts the check back outside the boundary you are trying to prove. You would be proving the policy from outside the policy.
Querying as the second tenant is what stays inside it: same role, same policy, same request-scoped setting, nothing the application could not do itself. If tenant 2 comes back empty, the fixture has gone stale and the negative control is gone. If it comes back overlapping tenant 1, the boundary is not separating anything.
function visibleIds(Connection $c, int $tenantId): array
{
$c->beginTransaction();
try {
// set_config(..., true) is SET LOCAL, but it takes a bound parameter.
$c->executeStatement('SELECT set_config(?, ?, true)', ['app.tenant_id', (string) $tenantId]);
$ids = $c->fetchFirstColumn('SELECT id FROM invoice ORDER BY id');
} finally {
$c->rollBack();
}
return array_map('intval', $ids);
}
$servingA = visibleIds($serving, 1);
$ownerA = visibleIds($owner, 1);
$servingB = visibleIds($serving, 2);
// Nobody is above the policy.
self::assertSame($servingA, $ownerA);
// There is a policy, and the fixture still contains something for it to refuse.
self::assertNotSame([], $servingB);
self::assertSame([], array_intersect($servingA, $servingB));
That set_config call is worth a line of its own. SET LOCAL app.tenant_id = ? is not parameterisable: SET takes a literal, so you end up interpolating a tenant id into SQL, in the one place where you least want to. set_config(name, value, is_local) is the same thing as a function call, and DBAL binds it like any other query.
And the run prints the ids rather than a count on purpose. serving=[1] owner=[1,2] says something. 1 !== 2 says the same thing with much less to go on when it fails in CI at 2am.
The two ways "no tenant context" fails, and they are not alike
fresh connection, no context : is_null=yes, rows=0 (silent)
reused connection, no context : SQLSTATE[22P02] invalid input syntax for type integer: "" (loud)
A connection that never had the setting returns NULL from current_setting('app.tenant_id', true), the policy matches nothing, and you get zero rows in silence. A connection that had it set with SET LOCAL inside a transaction gets the empty string back once that transaction ends, not NULL, and the cast to int raises 22P02.
The tempting fix is to make the policy tolerant:
USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::int)
Measured: it works, and it is a trap. It turns the loud failure into the silent one. Zero rows, no error, on a connection that just lost its tenant context. If you have to choose, choose the version that raises: a query that explodes is an incident, a query that quietly returns nothing is a customer email three weeks later.
The leak that hides under PHP-FPM
Last one, and it is the reason any of this matters outside the test suite. SET app.tenant_id = '2' without LOCAL, in a transaction that commits, survives the transaction. Not the request: the connection.
BEGIN; SET app.tenant_id='2'; SELECT count(*) FROM invoice; COMMIT;
SELECT current_setting('app.tenant_id', true), count(*) FROM invoice;
-- still_here | count
-- 2 | 1
A rollback undoes it, a commit does not. Under PHP-FPM this is invisible, because the connection dies with the request and the next one starts clean. Put PgBouncer in transaction mode in front of it, or run the same code in a Messenger worker that holds a connection for hours, and the next transaction on that connection reads the previous tenant. The LOCAL keyword is the entire difference, and no test that runs one request at a time will ever tell you it is missing.
Reproduce it
Two bash scripts and two PHP files: they create a throwaway database and two roles, build the table in each of the five configurations, run the assertions under both connections, and drop everything on the way out. PostgreSQL 18.3, PHP 8.5.7, Doctrine DBAL. Nothing in them is specific to my schema, and I would rather you ran them against yours than believed my output. They came out of the multi-tenant kit I work on, ShipAnvil, but there is no product in them: it is a table called invoice with two rows.
Thanks to Mads Hansen for the double run, and to Marco for the question that showed the double run was half an answer. If you are running RLS in a Symfony app, the cheapest thing you can do this week is open your isolation test and count the tenants in its fixture.
Written by Eric Mollenthiel, freelance Symfony developer in Lyon, France.
More at mollenthiel.fr.
