"new row violates row-level security policy" — the INSERT-side check you forgot

verbatim errorERROR: 42501: new row violates row-level security policy for table "documents"

Problem

With RLS enabled on a documents table, SELECTs worked fine for the app role, but every INSERT failed with:

ERROR: 42501: new row violates row-level security policy for table "documents"

The policy clearly allowed owner_id = auth.uid() — and I was inserting rows with the correct owner. The "fix" I inherited: swap INSERTs to the service role in the app, which silently disables row security for that path. Do not do that.

Root cause

A policy without an explicit WITH CHECK clause defaults its WITH CHECK to the USING expression — that part is fine. The real trap: auth.uid() returned NULL. When the JWT claims are not set on the connection (SET LOCAL request.jwt.claims missing, or you connected through a pooler that does not inject them), owner_id = NULL is never true, so the WITH CHECK fails for every row. A NULL that makes a predicate false is indistinguishable from "wrong user", and the error message does not tell you which.

FORCE ROW LEVEL SECURITY is the second trap: it applies the policies to the table owner too, which is the classic "works in psql (as superuser), fails in app" split.

Fix

1. Be explicit instead of relying on the USING default:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY documents_rw ON documents
  FOR ALL
  USING (owner_id = auth.uid())
  WITH CHECK (owner_id = auth.uid());   -- INSERTs are checked HERE

2. Prove what the policy sees. Inside the failing transaction:

SELECT auth.uid();                          -- NULL is your smoking gun
INSERT INTO documents(owner_id, title) VALUES (auth.uid(), 'x') RETURNING owner_id;

If auth.uid() is NULL, fix the connection (set the JWT claims before the INSERT, e.g. SET LOCAL request.jwt.claims TO '<token>'), not the policy.

3. Check the role, not the row:

SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relname = 'documents';
SELECT polname, polroles::regrole FROM pg_policy WHERE polrelid = 'documents'::regclass;

If relforcerowsecurity is true and you are connecting as the owner, either drop FORCE or connect as a non-owner role. When the diagnostic queries disagree with your mental model, trust the queries — RLS failures are almost always "the policy is being evaluated by/for someone you did not expect".