Build1 publisher2 min readPublished
WITH CHECK (true) accepts the UPDATE that moves tenant 1's invoices into tenant 2's books
A fixture seed that fails under FORCE ROW LEVEL SECURITY has one tempting repair, and after it the write path is guarded only by the read policy, for exactly as long as the statement happens to read a column.
The Engineer · Build desk
What happened
- The quick repair is a policy whose WITH CHECK clause is simply true: reads stay filtered by the USING expression, the seed goes through, and the isolation suite comes back green.
- On that policy tenant 1's plain INSERT into tenant 2 and its UPDATE setting tenant_id = 2 with no WHERE clause are both accepted, while the equality, scoped and disjoint assertions still pass.
- The two cross-tenant writes that are still refused are stopped by the USING expression of the read policy applied to the new row, and only because those statements read a column.
- The author reports his own bench hit the seeding error without him seeing it, running the first two scenarios against an empty invoice table.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint A suite assembled only from SELECT assertions cannot separate a sound policy from this one, because the same nine green results come back from all three shapes and nothing in the run touches the write path.
- decision Fixture design decides policy strength here: either the seed switches app.tenant_id to match each row it inserts, or somebody relaxes the check clause to make the error go away.
- exposure Whether a tenant can actually write into another tenant's rows depends on the shape of the statements the application sends, so two codebases on the identical broken policy can have different exposure.
- cost The price of the relaxed check is two of four cross-tenant writes accepted, and the accepted pair includes the single statement that reassigns every invoice a tenant owns.
Leaving WITH CHECK off a policy still enforces a check. Postgres uses the USING expression as the check expression when WITH CHECK is absent [9]. The short form and the explicit two-clause form do the same thing, and both refuse all four cross-tenant writes on the bench [7].
The refused seed is correct behaviour. Under FORCE ROW LEVEL SECURITY the owner is subject to the policy [4]. The bench output shows an INSERT of (1,1,100) going through with app.tenant_id set to 1. Then an INSERT of (2,2,200) is refused under that same setting, because the row claims a tenant the connection does not [5]. Seeding two tenants means changing the setting between rows [3]. That is more work than typing WITH CHECK (true). The author calls the relaxed policy the same shape as the NULLIF trap from the previous article, a tempting fix that turns a loud failure into a silent one. He says this one is a hole, not a degraded mode [14].
The three assertions being run came out of a comment thread on two earlier findings in the same series. The role that runs migrations bypasses every policy [1]. A fixture holding one tenant passes with no policy at all [15]. Equality double-runs a query as the owner and as the serving role and compares the sets. Scoped checks that tenant 1 sees only its own rows. Disjoint, credited to a commenter the author names as Marco, checks that tenant 2 sees rows and that none of them are tenant 1's [3]. Across three policy shapes those three assertions return nine green results [1].
What refuses the other two writes under WITH CHECK (true) is the read side. The USING expression is applied to the new row when the statement needs read access to it. Footnote [a] of the "Policies Applied by Command Type" table in CREATE POLICY gives the case as "for example, a WHERE or RETURNING clause that refers to columns from the relation" [11]. A plain INSERT reads nothing and passes. The same INSERT with RETURNING id reads, and is refused. UPDATE ... WHERE id = 1 reads id, refused. UPDATE ... SET tenant_id = 2 with no WHERE reads nothing, and the row moves [12].
The demonstration is one invoice table with two rows, three policy shapes and four write attempts [16]. For the hole to be reachable in a running application, that application has to issue a write Postgres can execute without reading a column of the table. No WHERE clause on it, no RETURNING from it [4].
What to watch
- Whether the series adds a write assertion to the three read ones, since only a statement that reads no column of the table actually tests the WITH CHECK expression.
- A change to the footnote [a] behaviour in CREATE POLICY would remove the two accidental refusals and widen the hole to all four statements.
- More broken-database cases out of the comment thread that produced the disjoint assertion.