PostgreSQL's row-level security error hides six different causes

A survey tested eleven types of writes against five RLS policy configurations in PostgreSQL 17.11 and showed that the same error message covers quite distinct causes, from a missing tenant to a RETURNING clause that reads before writing.

One message, six causes

"new row violates row-level security policy for table" is the phrase PostgreSQL returns for practically every write rejected by row-level security (RLS). The problem is that it doesn't say which policy failed, which expression rejected the row, or why. A survey published on Planet PostgreSQL, measured on October 9, 2026 against PostgreSQL 17.11, ran eleven types of writes across five policy configurations (plus a few more with a restrictive policy) to separate what this single phrase hides.

The result: the same sentence covers six distinct causes, and the full message has four variations, not always easy to tell apart in an application that only looks at SQLSTATE 42501 (the same code as permission denied for table).

The four forms of the message

The table below summarizes what each variation indicates, according to the survey:

The form with a policy name only appears when a restrictive policy is involved, whether for insert or select. In the test, inserting without RETURNING passed, but the same insert with RETURNING id named the restrictive select policy, because RETURNING reads the row just written.

The decision tree

The survey proposes a simple flow to go from the message to the cause, with the tenant stored in a session variable (app.tenant):

1. Does the message carry a policy name? A restrictive policy rejected the row. Read its WITH CHECK, or the USING if there's no WITH CHECK. It can be a select policy: RETURNING and ON CONFLICT with a conflict target also apply it to the new row.
2. Does it come as (USING expression), without a name? An upsert hit a row the role can't update: in the test, a row from another tenant, or its own row without an update policy.
3. Does it have neither a name nor (USING expression)? Check whether the same row goes in with a plain INSERT, without RETURNING and without ON CONFLICT. If it does, no select policy lets the new row pass through RETURNING or ON CONFLICT.
4. Does it also fail on the plain insert? Check, in the same transaction, the value of current_setting('app.tenant', true) right before the write. Empty or wrong: the tenant isn't set where the write runs. Correct: there's no insert policy for the role, or the row really does belong to another tenant.

One point the tree highlights: the check needs to run in the same transaction and before any earlier error, because after an error the transaction is aborted and any diagnostic SELECT fails too.

Why an upsert fails even when the row already exists

The most counterintuitive cause in the survey: an INSERT ... ON CONFLICT DO UPDATE can fail with the RLS message even when the final action would only be an update, because the insert policy is evaluated before PostgreSQL knows whether there's a conflict. In a configuration with select and update policies but no insert policy, trying to write a row that collides with an existing one failed even when the actual result would be zero changes.

PostgreSQL's documentation confirms the behavior:

an INSERT with ON CONFLICT DO NOTHING/UPDATE will check the INSERT policies' WITH CHECK expressions for all rows proposed for insertion, regardless of whether or not they end up being inserted

PostgreSQL 17 documentation, CREATE POLICY

MERGE behaves differently: since there's no policy specific to MERGE, PostgreSQL applies each action's policy as it executes. In the test, a MERGE that only updates an existing row worked in a configuration without an insert policy, because no new row went through the insert action. Including a single new row in the same MERGE was enough for the entire command to fail with the same generic message.

RETURNING and ON CONFLICT read before writing

Another practical cause, and probably the most common one in production: RETURNING and ON CONFLICT with a conflict target make the statement read the row it's writing, and reading goes through select policies, not insert or update ones. In a configuration with an insert policy but no select policy, a plain INSERT worked, and the same INSERT ... RETURNING id failed.

This directly affects ORM users: many add RETURNING by default to inserts to retrieve the generated key. The result is an insert that works perfectly in a manual psql session and fails only when triggered by the application, because the application asks for the id back and the manual test doesn't.

The case of a UNIQUE column outside the tenant-composed primary key illustrates the risk: when trying to write a slug that already belongs to another tenant, the behavior changes depending on the statement:

DO NOTHING is the most dangerous case: it reports success with zero rows affected, and that same zero also shows up when the tenant already has that slug. The application has no way to distinguish "already exists, it was yours" from "already exists, it's someone else's tenant" just by looking at the statement's return.

What doesn't trigger the error

Six behaviors from the survey show that the absence of the error can be just as telling as its presence:

• COPY FROM as the application role fails before looking at any row, with the message COPY FROM not supported with row-level security. Bulk import under RLS needs to become an INSERT, or use a role that bypasses the policy.
• A missing GRANT returns permission denied for table, with the same SQLSTATE 42501 as the RLS error: the code alone doesn't distinguish the two cases.
• The table owner, without FORCE ROW LEVEL SECURITY, writes outside its own tenant with no error at all. If the application connects as the table owner, the absence of the error is the real problem.
• An UPDATE or DELETE filtered out by the policy simply reports UPDATE 0 or DELETE 0, without throwing any exception.
• A MERGE that sees the row but can't update it throws a sibling message, target row violates row-level security policy, different from the insert message.
• A BEFORE INSERT trigger that rewrites tenant_id changes the outcome of the check, because WITH CHECK runs after the BEFORE ROW trigger.

How to diagnose it in your own database

The proposed diagnostic script comes down to two queries, run in the same transaction as the write that failed:

select current_user, current_setting('app.tenant', true);

select policyname, cmd, permissive, roles, qual, with_check
from pg_policies
where tablename = 'notes';

With that in hand, the order of checking is: is the tenant correctly set right now, on this connection? Is there a policy for the command and for this role (column cmd equal to INSERT or ALL, with the role, a role it's a member of, or {public} in roles)? And if the statement uses RETURNING or ON CONFLICT with a target, is there also a SELECT or ALL policy? Only after that does it make sense to compare the row's tenant_id with the session's value.

What's left out

The survey covers only PostgreSQL 17.11; it didn't test other versions, MERGE with a DELETE action, security-barrier views, partitioned tables, or foreign tables. Hosted platforms that put their own roles in front of the table introduce additional causes outside the tested scope. For anyone modeling multi-tenant RLS, the practical point is this: the generic message isn't a readability bug, it's a design decision that treats insert, update, and select rejection the same way. Treating 42501 as a single error in application code, without checking pg_policies first, is technical debt disguised as error handling.