In a row-security policy definition, what is the difference between the USING predicate and the WITH CHECK predicate, and what goes wrong if you specify only USING?
answer
- USING = rows read; WITH CHECK = rows written
- SELECT/DELETE: USING only
- INSERT: WITH CHECK only
- UPDATE: both — eligible row and resulting value
- Read denial is silent; write denial errors
basics
~20 sUSING filters rows that already exist — what SELECT returns and what UPDATE or DELETE may match. WITH CHECK validates row values being written by INSERT or produced by UPDATE. With only USING, a session can insert or update rows into another tenant, writing data it cannot then read.
solid answer
~50 sThey guard opposite directions of data flow. - **USING** is applied to rows the engine reads: which rows SELECT returns, and which rows UPDATE and DELETE are allowed to match. Rows failing it are invisible, not rejected. - **WITH CHECK** is applied to rows the statement produces: the new row of an INSERT, and the post-image of an UPDATE. Rows failing it raise a policy-violation error. An UPDATE goes through both: `USING` decides which rows you may modify, `WITH CHECK` decides what they may become. Without `WITH CHECK`, nothing stops `INSERT ... (tenant_id = 'other')` or an UPDATE that moves a row to another tenant — data escapes into a tenant you cannot see, which is worse than a read leak because it is silent and hard to detect. When `WITH CHECK` is omitted on a policy that has `USING`, engines commonly default the write check to the `USING` expression for that policy — but relying on that default is fragile. I write both explicitly.
code
sql · 8 linesCREATE POLICY doc_tenant ON document
FOR ALL TO app_user
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);
-- With WITH CHECK omitted, this would silently succeed:
UPDATE document SET tenant_id = '00000000-0000-0000-0000-000000000009'
WHERE id = 42;go deeper
Know the one-line split: USING for rows you read, WITH CHECK for rows you write, and that INSERT is only governed by the latter.
Walk through all four commands, explain that UPDATE uses both, and name the silent-insert leak that comes from omitting WITH CHECK.
Add the asymmetric error semantics and why they exist, a real asymmetric-policy use case, and how you test the write side explicitly in CI.
Treat the write side as the harder guarantee: cross-tenant writes are silent and corrupt data, so make explicit dual predicates a schema-review rule rather than a convention.
## Two directions of data flow Every statement against a protected table either *reads existing rows* or *produces new row values*, and often both. Row policies have one predicate for each direction. **USING** is the read-side filter. It is evaluated against rows as they already exist in the table. The engine silently drops rows for which it is false. That silence matters: a SELECT returns fewer rows, a DELETE deletes nothing, an UPDATE reports zero rows affected. You get no error, because signalling an error would itself disclose that a hidden row exists. **WITH CHECK** is the write-side filter. It is evaluated against the row value the statement wants to store: the new row for INSERT, the post-image for UPDATE. Failure here *does* raise an error — a row-security violation — because the caller already has the values in hand, so refusing loudly leaks nothing. ## How each command uses them - **SELECT** — `USING` only. - **DELETE** — `USING` only: you may only delete rows you can see. - **INSERT** — `WITH CHECK` only: there is no pre-existing row to filter. - **UPDATE** — both. `USING` decides which rows are eligible to be modified; `WITH CHECK` decides what values they are allowed to end up with. That UPDATE case is the one interviewers press on. A policy with `USING (tenant_id = current_tenant())` and no write check lets a session take one of its own rows and set `tenant_id` to somebody else's — a legal update that moves the row out of view. The row is gone from the owner's perspective and has appeared, unbidden, inside another tenant's data. ## The INSERT hole The more common mistake is a policy written only with `USING`, applied `FOR ALL`. Reads are correctly isolated, so tests pass. But inserts are unconstrained on the tenant column: an application bug, or an attacker with the ability to influence the inserted values, writes rows tagged with another tenant. Symptoms are strange — records "disappear", another customer sees data they never created, auditing shows the write came from your app. Because nothing errors, nobody notices for weeks. Most engines mitigate this by defaulting the write check to the `USING` expression when `WITH CHECK` is omitted for a policy that has one. Two reasons not to lean on that: it is an engine-specific convenience rather than something obvious to a reader, and it is wrong whenever the two predicates should genuinely differ. ## When the two predicates should differ Asymmetry is a feature, not just a safety net. - **Append-only / write-forward**: `USING (author_id = current_user_id())` so you only read your own rows, but `WITH CHECK (author_id = current_user_id() AND status = 'draft')` so you can only create rows in a state you are allowed to create. - **Read broadly, write narrowly**: a support role may read every row in its region (`USING (region = current_region())`) but only write rows it owns (`WITH CHECK (owner = current_user_id())`). - **Immutable classification**: `WITH CHECK` can forbid a row from being written with an elevated classification even though `USING` allows reading rows at that level. ## Interaction with ordinary constraints `WITH CHECK` is not a `CHECK` constraint. A table `CHECK` constraint applies to *every* writer, is part of the table's data integrity contract, and is visible in the schema as a universal invariant. A policy's `WITH CHECK` is per-role and per-command, evaluated only for roles the policy applies to, and is an authorization rule. Enforcing tenancy via a CHECK constraint is impossible anyway, since it cannot depend on session context in most engines. ## Practical rules 1. Write both predicates explicitly on any `FOR ALL` or `FOR UPDATE` policy, even when identical. 2. Test the write side deliberately: attempt an INSERT with a foreign tenant id and an UPDATE that moves a row across tenants, and assert both fail. 3. Remember the asymmetric error behaviour — invisible on read, loud on write — when writing integration tests; a zero-row result is the expected "denied" signal for reads.
- Why does a row that fails USING produce an empty result while a row that fails WITH CHECK produces an error?Erroring on the read side would itself be a disclosure: the caller would learn that a hidden row exists with that key. Returning nothing keeps invisible rows genuinely invisible. On the write side the caller already supplied the values, so refusing loudly reveals nothing new and gives the application a clear signal that its write was rejected.
- Give a case where USING and WITH CHECK should deliberately be different expressions.A support role that may read every row in its region but may only create or modify rows it personally owns: USING checks the region, WITH CHECK checks ownership. Another is an append-only workflow where you can read rows in any status but may only write rows in the initial status, so WITH CHECK adds a status predicate that USING does not have.
saying these in an interview costs you the question
- Thinking USING also constrains INSERT
- Assuming a failed read raises a permission error instead of returning no rows
- Confusing a policy's WITH CHECK with a table CHECK constraint
- Believing UPDATE is governed by only one of the two predicates
- Relying silently on the engine defaulting WITH CHECK to USING