A comment on my last article was better than the article. The subject was single-database multi-tenancy, and @to21as argued that the predicate shou...
For further actions, you may consider blocking this person and/or reporting abuse
Really enjoyed this one. The part that stood out to me is that ENABLE ROW LEVEL SECURITY can give you a completely misleading security signal when the application role also owns the tables.
The testing point is probably the most important takeaway for me. A single-tenant fixture can pass whether RLS is actually enforcing the policy or completely bypassed. The negative cross-tenant assertion is what turns the security claim into something falsifiable.
I also like that you don't stop at the owner/FORCE RLS issue. The SET vs SET LOCAL distinction and connection reuse introduce a second, very different class of failure: the policy may be enforced correctly while the tenant context itself is stale.
That's a great example of why I prefer testing the security invariant at the actual enforcement boundary, rather than testing that the configuration merely exists.
Thanks for sharing the measurements and the concrete queries. This is exactly the kind of database security footgun that's easy to review as “configured correctly” and still ship broken. 🔐
Testing the invariant at the enforcement boundary rather than testing that the configuration exists is the sentence I would keep, and it has a sharper edge than it looks.
A configuration assertion is at least honest about being one. The trap I walked into this week is a test that does sit on the boundary and still goes hollow, because the fixture stops being a violation.
Different codebase, nothing to do with RLS. A guard test dropped a deliberately malformed content file and asserted the rest of the site still rendered. It had been watched failing by hand first, so it was a real test. Then a fix landed on the parser that produced that malformation, and the fix made the fixture valid. The test kept passing, on nothing at all: green, boundary-level, and worthless, without a line of it changing.
What repairs it is the same shape as the double run: assert both sides in the same test. Assert that the malformed file breaks the pages it is supposed to break, and then that it leaves the others alone. A test that only asserts the positive side cannot notice that its own fixture went stale.
The same failure is available here.
owner run and serving-role run return the same setis the assertion that survivesFORCElanding, but it only means something while a second tenant's rows are present and never returned. The day someone trims that fixture, the assertion stays green and stops saying anything about the policy. So the second tenant is not fixture furniture, it is half of the claim, and the suite should say so out loud rather than rely on it quietly.Exactly. The fixture is part of the security claim, not just test data.
If removing the violating state doesn't make the test fail, then the test was never actually proving the invariant.
I particularly like the “assert both sides” formulation because it makes the test self-defending: prove the allowed path, prove the forbidden path, and make the fixture contain the evidence required for both. Otherwise a perfectly green suite can slowly become a test of nothing.
Removing the violating state was the half I had not measured, so I ran it, and it caught my own advice out.
Five worlds, tenant 1 queried, PostgreSQL 18.3, three assertions side by side. Equality is the one I gave earlier in this thread: the owner run and the serving run return the same set. Scoped is the ordinary one: the serving role returns only its own rows. Disjoint is yours, made executable: set the context to the second tenant, assert that set is non-empty and does not intersect the first.
Equality is green in four rows and three of them are broken. Two runs that both return everything are still equal, so it never noticed the world where there is no policy at all. It is red in exactly one row, the one the article was about.
Scoped does not rescue it. Both single-tenant rows are green on both assertions, and one of those rows has the owner bypass sitting in it, waiting for a second customer to sign up.
Only the disjointness line 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.
The part I did not expect was how to write it without privilege. Under FORCE the owner is subject to the policy too, so there is no unprivileged connection left that can read the whole table, and reaching for a superuser to check the fixture puts the check back outside the enforcement boundary you named. Querying as the second tenant is what stays inside it: same role, same policy, nothing the application could not do itself.
So the double run needs both halves stated. Equality proves nobody is above the policy. Disjointness proves there is a policy, and that the fixture still contains something for it to refuse. I only had the first one written down.
This is an excellent result. The 1-tenant cases are exactly the edge case I had in mind: the negative control can disappear simply because the fixture no longer contains another tenant to separate from.
I especially like that you kept the check inside the enforcement boundary by querying as the second tenant rather than using a privileged connection. Otherwise we'd be proving the policy from outside the policy.
And the equality/disjointness split is much clearer now: equality tells us whether two views coincide; disjointness tells us whether the boundary actually separates what it is supposed to separate.
That's a much stronger two-part check than either assertion alone. 🔐
Staying inside the boundary was the part I nearly got wrong, so I am glad it is the part you picked out. The whole exchange became the follow-up article, and you are in it by name.
Then I went looking for the next place the pair goes hollow, and there is one: all three assertions only read. Same fixture, same
FORCE, three shapes of policy, tenant 1 writing towards tenant 2.The bottom row is a database where tenant 1 can file an invoice into tenant 2's books, and all three of our assertions are green on it.
WITH CHECK (true)is not a strawman. Seed a table that already hasFORCEon, with the wrong context, and Postgres saysnew row violates row-level security policy for table "invoice". That is a loud error whose obvious repair is to relax the check, and it is the same shape as theNULLIFtrap on the read side: the tempting fix turns a loud failure into a silent one.What I did not expect is the two refusals sitting next to it on that row.
UPDATE ... WHERE id = 1is blocked, and not byWITH CHECK, which istrue. It is the SELECT policy'sUSINGexpression being applied to the new row, and only because the statement reads a column. Footnote [a] of "Policies Applied by Command Type" inCREATE POLICY: "If read access is required to either the existing or new row (for example, a WHERE or RETURNING clause that refers to columns from the relation)." Same reasonINSERT ... RETURNING idis refused while the identicalINSERTwithout it goes through.So on that database the write side is guarded by the read policy, incidentally, and only while the statement reads something. Take the read away and it is gone:
No
WHERE, nothing read, nothing checked.USINGstill filters on the way in, so tenant 1 only touches its own rows, and then hands all of them to tenant 2. The tenant that lost its data cannot see it, and the tenant that received it never asked for it.Same lesson as the fixture one, one level up. Equality and disjointness say the boundary separates what is already in the table. Neither of them says anything about what a tenant can put there. The fourth assertion is one line and it belongs in the same test: as tenant 1, attempt a write labelled tenant 2, and assert that it raises.
PostgreSQL 18.3, one permissive
ALLpolicy, nothing else in the way.This is exactly the kind of security control that needs a negative test, not just a catalog assertion. I’d run the same cross-tenant query suite twice: once as the serving role and once as the owner/migration role, with the owner run expected to fail the build unless FORCE RLS is deliberate and verified. For pooled connections, add a request-boundary canary too: begin transaction, SET LOCAL tenant, query, rollback, then prove the next checkout has no tenant context. That catches both policy bypass and context leakage—the two failure modes that otherwise hide behind a green test suite.
The double run is the right shape; the assertion inside it is the part I would word carefully. I re-measured on 18.3 while writing this: once
ALTER TABLE ... FORCE ROW LEVEL SECURITYis in place, the owner run returns exactly what the serving role returns, same rows, no error. So "the owner run must fail the build" turns red the day someone applies the correct fix. The invariant that survives both worlds is owner run and serving-role run return the same set: it fails loudly today, and it keeps passing after FORCE lands.The other half of the negative test is the fixtures. The failure direction is extra rows, never zero: with a single organisation in the fixture, both runs return that one row whether the policy applies or is completely inert. A second tenant's data has to be present and never returned.
On the canary, one detail is in our favour.
SET LOCALis reverted at the transaction boundary whichever way it ends, and what comes back is the empty string, not NULL. So the next query does not quietly return zero rows, it throws22P02 invalid input syntax for type integer: "". The two "no context" states fail differently, and only the never-set one is silent.The leak I would actually hunt with that canary is the missing
LOCAL. A plainSETthat commits survives the whole session: the next transaction on the same connection still reads the previous tenant. A rollback undoes it, a commit does not. Under PHP-FPM the connection normally dies with the request, so it never shows; behind pgbouncer in transaction mode, or inside a long-running Messenger worker, the next unit of work inherits it.The migration-role bypass is exactly the kind of RLS footgun teams miss. Policies feel comprehensive until one privileged operational path silently sits outside the model everyone is reasoning about.
What surprised me while measuring it is how little privilege it takes: not a superuser, not
BYPASSRLS, just owning the table. And that is the roledoctrine:migrations:migrateruns as in a stock deployment, since the sameDATABASE_URLboth creates the schema and serves the requests.The other half of why it hides is the direction of the failure: extra rows, never zero. A fixture with a single organisation returns that one row whether the policy applies or is completely inert, so the suite stays green either way.