0
Challenge · 54RLS: The Core

The failure-mode matrix: what throws, what silently returns zero

Securityused in ~95% of apps

Concept 53 handed you a table and asked you to memorize it. Fair warning: memorizing a table and being able to diagnose a live incident from it are different skills. In production you never get to see the table -- you get exactly one signal, a row count or an error code, and from that signal alone you have to say which clause fired and why. This concept is where you stop reciting the matrix and start reading it off a real, running database, including the two corners that memorizing prose never prepares you for: a write that throws after it already happened, and a relationship that leaks through a door RLS was never watching.

Core idea. Every RLS failure reduces to one of exactly two signals: a row count (possibly zero) with no error, or a thrown 42501. USING failures are always the first kind; WITH CHECK failures are always the second. But the matrix has edges past the four commands: INSERT ... RETURNING adds an implicit SELECT check on the row you just wrote, count(*) reports only what you can see and never announces that it's incomplete, and a foreign key constraint checks only that the referenced row exists -- never whether your policies would let you read it. Diagnosing RLS in production means working backwards from one of those two signals to the exact clause and exact table that produced it.
Playground, this isn't a screenshot
existing row
the row already in the table
USING
may you reach it?
update
WITH CHECK
may it become that?
new row
the values you are proposing

BOTH run, at different moments. USING asks 'may you touch this row?' before the change — fail it and zero rows are affected, silently. WITH CHECK asks 'may it become that?' after — fail it, by giving your row away, and it throws 42501. Omit WITH CHECK and Postgres reuses USING.

The asymmetry that catches everyone: a failing USING clause silently filters — zero rows, no error. A failing WITH CHECK clause throws 42501.
So it is not true that "only INSERT throws": an UPDATE that tries to move a row out of your ownership fails WITH CHECK and raises too. And because three of the four commands can fail in total silence, a broken policy and a working one look identical from the client. Test by asserting on the emptiness.
↑ play a little first, then dig in