RLS in PostgreSQL: USING vs WITH CHECK on writes

A row-level security policy has two expressions. USING decides which existing rows a statement can see, update or delete; WITH CHECK decides which new rows it may write. Get them wrong and a multi-tenant application does one of two things, neither of which shows up in a test that only reads: it writes rows into another tenant, or its updates and deletes silently do nothing.

We measured ten writes against six common policy setups, as tenant 2 of three. With USING alone, every attempt to write a row for another tenant failed with an error, and updates and deletes aimed at another tenant’s rows affected 0 rows. With WITH CHECK (true) added, tenant 2 inserted a row for tenant 3 without any complaint, and an UPDATE that read no columns moved all five of its rows to tenant 3. And in every setup that lacked the policy for the command, or lacked a select policy while the statement filtered on a column, updates and deletes of tenant 2’s own rows reported UPDATE 0 and DELETE 0 — no error.

The setup

PostgreSQL 17.10 in a throwaway container, measured on 4 October 2026.

• A table notes (tenant_id, id, body), primary key (tenant_id, id), three tenants with five rows each, row-level security enabled. The application role app is not the owner and has SELECT, INSERT, UPDATE and DELETE on the table.
• The tenant is a session variable: every policy below uses tenant_id = nullif(current_setting('app.tenant', true), '')::int, written here as own tenant.
• Each write ran as app, with set_config('app.tenant', '2', true), in its own transaction that was rolled back, so every write started from the same fifteen rows. Write 9 ran without a tenant set.
• The tests of moving rows further down ran in a separate database with only tenants 1 and 2, so tenant 3 was empty. With five rows already in tenant 3, the same move stopped on the primary key (duplicate key value violates unique constraint "notes_pkey"), not on the policy.

The six setups:

What happened to each write?

Numbers are rows affected; error is new row violates row-level security policy for table "notes". Rows 5 and 6 are the protection doing its job: other tenants’ rows are invisible, so nothing happens to them. The numbers in italics are the problem — an own row that should have been updated or deleted, and a statement that reported success anyway.

Why does WITH CHECK (true) let a tenant write into another?

Because for the write itself, WITH CHECK is what PostgreSQL checks a new row against. Give only USING, and the documentation’s rule applies: “If only a USING clause is specified, then that clause will be used for both USING and WITH CHECK cases” — that is why setup A refused write 2 and write 9. WITH CHECK (true) replaces that with “any new row is fine”, so tenant 2 could insert a row for tenant 3, and a connection with no tenant at all could insert too.

Moving a row is subtler. In setup B, write 4 — UPDATE … SET tenant_id = 3 WHERE tenant_id = 2 AND id = 1 — failed, but not because of WITH CHECK. When an UPDATE reads columns, “in a WHERE clause or a RETURNING clause, or in an expression on the right hand side of the SET clause”, the select policies apply as well, and the documentation’s table of policies per command adds that the new row is then checked against them. Whether the move worked depended only on that:

So the select policy saved the move only when the statement happened to name a column. A policy for writes needs a WITH CHECK on the tenant, or no WITH CHECK at all so that USING is reused. Never true.

Why do updates and deletes silently do nothing?

Because rows that a policy does not let through are filtered, not refused. The documentation calls these rows “silently suppressed; no error is reported”, and its table of policies per command lists the exceptions that raise an error instead: new rows against WITH CHECK, new rows against select policies when the statement reads columns, and existing rows under ON CONFLICT DO UPDATE and MERGE. Everything else is a filter.

• No policy for the command (C and D for updates and deletes, F for deletes): with row-level security on and no policy for a command, PostgreSQL assumes “default deny”, so no row is updatable or deletable. Write 3 reported UPDATE 0, write 7 DELETE 0. An application that does not check the row count tells the user the change was saved.
• No select policy (E): an UPDATE or DELETE with a WHERE clause needs to read the rows, and reading goes through select policies. With none, the WHERE matched nothing: write 3 and write 7 affected 0 rows, while write 10, an update without a WHERE, updated all five own rows. Write 8 failed because RETURNING id reads a column, so the new row must pass a select policy — and that one is an error, not a filter.
• An update policy without WITH CHECK (F) behaves like A for updates: the USING expression checks the new row too. Write 4 in F also had a WHERE, so we checked it separately: with only FOR UPDATE USING (own tenant) and no other policy, UPDATE notes SET tenant_id = 3 failed with the policy error, while UPDATE notes SET body = 'q' updated five rows.

What we would build

The read side of the same policy — the table owner, the empty context, the connection that changes tenant, foreign keys and the table that came later — is in row-level security in PostgreSQL: where tenants leak. What a duplicate-key error tells one tenant about another, and how ON CONFLICT behaves under a policy, is in unique per tenant in PostgreSQL.

What we did not measure

MERGE, INSERT … ON CONFLICT DO UPDATE (the documentation says it raises an error where a plain UPDATE would filter), RESTRICTIVE policies combined with permissive ones, policies that check a membership table instead of a session variable, and COPY FROM. The test used fifteen rows; which writes succeed follows from the policies, not from the size of the table — though rows already in the target tenant can stop a move with a key error, as above.

This belongs to the decisions that are cheap early and expensive later; the rest of that list is in multi-tenant SaaS on PostgreSQL: six decisions.

Sources: the PostgreSQL 17 documentation on CREATE POLICY (USING and WITH CHECK, policies per command, default deny, RETURNING, the table of policies applied by command type), read on 4 October 2026. All measurements are our own, on PostgreSQL 17.10, on 4 October 2026.