Three silent failures in Postgres row-level security
I checked a multi-tenant row-level security design on PostgreSQL 16.15 before building it, and three kinds of mistake produced no error.
Row-level security fails closed, and closed is quiet. A policy's
WITH CHECK rejects a bad new row loudly, with error 42501. But
USING only filters, so a mistake on that side looks like an empty result,
never a failure. Each trap below is a case where "nothing matched" hides a
mistake.
None of the three is a defect. Each is documented or was reported upstream. What follows is how each one looks, why it stays quiet, and what catches it.
Every behavior here was measured on PostgreSQL 16.15, as a role that is neither a
superuser nor BYPASSRLS. The SQL is in the post, so you can run it yourself,
and I link the documentation wherever it covers a behavior.
The design is for the hosted version of lontar, a memory store for AI agents on Postgres. lontar's local build runs as a Claude Code plugin and has no row-level security, and the hosted version doesn't exist yet.
The setup
You need a scratch database and a role that can create roles. I used a database called
rls_demo and a role with CREATEROLE that is not a superuser. The
SQL below makes a table, item, with one policy that keys on a session
setting, app.org_id, and puts one row in it for each of two organizations.
-- Block 0: a non-owner role, one table, one policy, one row per organization.
CREATE ROLE rls_app NOLOGIN NOSUPERUSER NOBYPASSRLS;
GRANT rls_app TO CURRENT_USER;
CREATE TABLE item (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL DEFAULT NULLIF(current_setting('app.org_id', true), '')::uuid,
key text UNIQUE,
body text
);
ALTER TABLE item ENABLE ROW LEVEL SECURITY;
ALTER TABLE item FORCE ROW LEVEL SECURITY;
CREATE POLICY org_isolation ON item
USING (organization_id = NULLIF(current_setting('app.org_id', true), '')::uuid)
WITH CHECK (organization_id = NULLIF(current_setting('app.org_id', true), '')::uuid);
GRANT SELECT, INSERT, UPDATE, DELETE ON item TO rls_app;
SET ROLE rls_app;
BEGIN;
SELECT set_config('app.org_id', '11111111-1111-1111-1111-111111111111', true);
INSERT INTO item (id, key, body) VALUES ('aaaaaaaa-0000-0000-0000-000000000001', 'a-1', 'mine');
COMMIT;
BEGIN;
SELECT set_config('app.org_id', '22222222-2222-2222-2222-222222222222', true);
INSERT INTO item (id, key, body) VALUES ('bbbbbbbb-0000-0000-0000-000000000001', 'b-1', 'theirs');
COMMIT;
RESET ROLE;
SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname = 'rls_app';
Three details matter. FORCE ROW LEVEL SECURITY is there because a table's
owner bypasses its own policies without it. The last query prints the flags of
rls_app, the role most later blocks run as, because
the documentation
says "Superusers and roles with the BYPASSRLS attribute always bypass
the row security system when accessing a table." A test run as one of them shows
every row and proves nothing. And GRANT rls_app TO CURRENT_USER is there
because, on 16.15, a role that creates another role gets ADMIN OPTION on it
but not the right to switch to it. Without that line, SET ROLE rls_app fails
with permission denied to set role "rls_app".
The policy has two halves, and the traps live in the difference between them.
Rows that fail USING
are not an error: "Typically, such rows are silently suppressed; no error is
reported". WITH CHECK is the loud half: when it is false or null,
"an error occurs".
Run each later SQL block in its own fresh psql session. The shell blocks
connect through the standard PG* environment variables, so set
PGDATABASE=rls_demo, plus your usual PGHOST and
PGUSER, before running them. Roles belong to the whole cluster, not to one
database, so to start over, drop the database and the roles too.
Trap 1: two kinds of unset
The loud case comes first, because it is the exception. In a session that has already
touched the setting, the plain ::uuid cast of an unset organization fails
with ERROR: 22P02: invalid input syntax for type uuid: "". In a
fresh session, the same cast returns NULL and no error, so a test that opens a new
connection never sees it. The reason is that "no organization set" has two
spellings.
The documentation
says that current_setting with missing_ok true returns NULL when
there is no such setting, and that is what a fresh session reads. But once anything in the
session has touched the setting, it reads as an empty string. For
set_config with is_local true, the same page says "the new
value will only apply during the current transaction." Neither entry says what the
setting reads afterward.
-- Block 1: run in a fresh psql session.
\set VERBOSITY verbose
SET ROLE rls_app;
SELECT current_setting('app.org_id', true) IS NULL AS fresh_session_is_null;
BEGIN;
SELECT set_config('app.org_id', '11111111-1111-1111-1111-111111111111', true);
COMMIT;
SELECT current_setting('app.org_id', true) = '' AS now_empty_string,
current_setting('app.org_id', true) IS NULL AS is_null;
-- the guard and the fallback both pass quietly
DO $$
BEGIN
IF current_setting('app.org_id', true) IS NULL THEN
RAISE EXCEPTION 'no organization set';
END IF;
RAISE NOTICE 'GUARD PASSED: org value is [%]', current_setting('app.org_id', true);
END $$;
SELECT COALESCE(current_setting('app.org_id', true), 'FALLBACK') AS coalesced;
-- the plain cast is the loud one
SELECT current_setting('app.org_id', true)::uuid;
-- the fix: NULLIF turns both spellings into NULL
SELECT NULLIF(current_setting('app.org_id', true), '')::uuid IS NULL AS nullif_is_null;
The block uses a committed set_config(…, true). I measured the same empty
string after a rolled-back one, after SET LOCAL in a committed transaction,
and after SET followed by RESET, so this is not only about
transactions.
The quiet cases are the ones that matter. The IS NULL guard does not raise:
it prints GUARD PASSED: org value is [], because the value is an empty string
and not NULL. A COALESCE with a fallback returns the empty string, and its
fallback never applies. Both are ordinary ways to write "stop if no organization is
set", and both pass without a sound.
This is known upstream. It was reported in 2019, against 10.4: after a rolled-back transaction, a setting read as an empty string where it had been NULL. In a 2023 thread on the same list, Tom Lane wrote: "The GUC infrastructure doesn't recognize null as a value, so you are making a distinction that the system doesn't."
The fix is to treat the two spellings as one. Wrap every read of the setting in
NULLIF(current_setting('app.org_id', true), ''), including the column
default, as the setup does. The last query in the block prints t: the wrapped
read is NULL in the touched session too.
Trap 2: an empty backup
A backup is where "nothing matched" costs the most, and
the documentation
says so: "it could be disastrous if row security silently caused some rows to be
omitted from the backup". By default, pg_dump turns
row_security off, and
its documentation says
"If the user does not have sufficient privileges to bypass row security, then an
error is thrown." That is the loud default. pg_dump is a separate
process, so the SET ROLE from the setup does not reach it, and
--role issues one after it connects. I use --inserts because the
same page says that with --enable-row-security "you probably also want
the dump be in INSERT format, as the COPY FROM during restore
does not support row security."
# Block 2a: the default dump fails loudly; the usual fix succeeds with nothing in it.
DUMP="pg_dump --role=rls_app --data-only --table=item --inserts"
$DUMP > /dev/null; echo "exit $?"
$DUMP --enable-row-security > dump.sql; echo "exit $?"
grep -c '^INSERT' dump.sql
The first dump prints
pg_dump: error: query failed: ERROR: query would be affected by row-level security
policy for table "item"
and exits 1. The obvious fix, --enable-row-security, exits 0 and prints
nothing to stderr. The file has no INSERT lines, because the flag dumps
"the parts of the contents of the table that they have access to", and with no
organization set, rls_app can see none.
I measured the same with a custom-format dump: pg_dump exits 0,
pg_restore --list exits 0 and lists the table's data, and
pg_restore --data-only exits 0 with nothing on stderr and restores zero rows.
Every exit code says success while the backup is empty. I measured this as a role that
does not own the table, as the blocks do, and as the table's owner before any backup
policy existed. Neither was a superuser or BYPASSRLS.
The fix is a policy for the backup role. This block creates a role that cannot bypass
row-level security, grants it to your own role so that --role can switch to
it, gives it SELECT, and adds a FOR SELECT … USING (true) policy
that applies only to it.
-- Block 2b: the fix is a policy for the backup role, not a flag.
CREATE ROLE rls_backup NOLOGIN NOSUPERUSER NOBYPASSRLS;
GRANT rls_backup TO CURRENT_USER;
GRANT SELECT ON item TO rls_backup;
CREATE POLICY backup_read ON item FOR SELECT TO rls_backup USING (true);
Now the flag has something to dump. The last block dumps as that role and checks the result against a count that does not pass through row-level security.
# Block 2c: dump as the backup role, then compare with a count that does not pass through RLS.
pg_dump --role=rls_backup --data-only --table=item --inserts --enable-row-security > dump.sql; echo "exit $?"
echo "dumped: $(grep -c '^INSERT' dump.sql)"
psql -At -c "SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'item'"
It prints exit 0, dumped: 2 and 2. The exit code
alone can't tell you the backup is complete, so the check compares the number of
INSERT lines with n_live_tup. A count(*) taken as
the backup role would be the wrong comparison: with the policy missing, it reads 0 beside
a dump of 0 rows, and the check would agree with the failure it exists to catch. The
statistics are not filtered by row-level security. But n_live_tup is an
estimate that lags behind recent writes (in the session that did the inserts it read 0
right after them), so keep this check for tables where an estimate is fair, and run it
once the writes have settled.
To see the check work, start over on a fresh database and run everything as before, but
leave the CREATE POLICY line out of the policy block. The last block then
prints exit 0, dumped: 0, and then 2. The mismatch
is the whole point.
Trap 3: an update that found nothing
The last trap is an answer, not a failure. In the block below, organization 1 tries to
change and then delete organization 2's row by id. Both statements succeed with no error:
UPDATE 0 and DELETE 0. That is the same answer organization 1
gets for an id that does not exist, and UPDATE … RETURNING id returns
(0 rows). Reading organization 2's data afterward shows the row untouched,
still theirs.
-- Block 3: run in a fresh psql session.
\set VERBOSITY verbose
SET ROLE rls_app;
-- organization 1 acts
BEGIN;
SELECT set_config('app.org_id', '11111111-1111-1111-1111-111111111111', true);
UPDATE item SET body = 'edited' WHERE id = 'aaaaaaaa-0000-0000-0000-000000000001';
UPDATE item SET body = 'edited' WHERE id = 'bbbbbbbb-0000-0000-0000-000000000001';
DELETE FROM item WHERE id = 'bbbbbbbb-0000-0000-0000-000000000001';
UPDATE item SET body = 'edited' WHERE id = 'cccccccc-0000-0000-0000-000000000001';
UPDATE item SET body = 'edited' WHERE id = 'bbbbbbbb-0000-0000-0000-000000000001' RETURNING id;
COMMIT;
-- the loud one, in its own transaction
BEGIN;
SELECT set_config('app.org_id', '11111111-1111-1111-1111-111111111111', true);
INSERT INTO item (organization_id, key, body) VALUES ('22222222-2222-2222-2222-222222222222', 'x-1', 'planted');
ROLLBACK;
-- the other organization's row is untouched
BEGIN;
SELECT set_config('app.org_id', '22222222-2222-2222-2222-222222222222', true);
SELECT id, body FROM item;
COMMIT;
-- aside: ON CONFLICT DO NOTHING against a row organization 1 cannot see
BEGIN;
SELECT set_config('app.org_id', '11111111-1111-1111-1111-111111111111', true);
INSERT INTO item (key, body) VALUES ('b-1', 'mine too') ON CONFLICT (key) DO NOTHING;
INSERT INTO item (key, body) VALUES ('b-1', 'mine too') ON CONFLICT DO NOTHING;
ROLLBACK;
BEGIN;
SELECT set_config('app.org_id', '11111111-1111-1111-1111-111111111111', true);
INSERT INTO item (key, body) VALUES ('b-1', 'mine too');
ROLLBACK;
That is row-level security working: it never reveals that organization 2's row exists. The mistake lives in the application, in code that treats "no error" as "it worked". An update that changed nothing looks exactly like one that changed a row, and a cross-organization mix-up shows up as nothing at all.
Compare the insert. A row stamped with another organization's id fails loudly:
ERROR: 42501: new row violates row-level security policy for table
"item". That is the WITH CHECK half from the setup. The fix for the quiet half is
to treat an affected-row count of 0 as an error wherever a row was expected. The count
can't tell "not yours" from "not there", and by design it shouldn't.
You will probably ask about INSERT … ON CONFLICT DO NOTHING.
The CREATE POLICY documentation
says that with an arbiter, the rows proposed for insertion are checked against the
SELECT policies, and "the INSERT is never silently
avoided". That is about the row you propose. I measured a different case, where the
conflicting row belongs to another organization and is invisible to the writer. An
ON CONFLICT … DO NOTHING whose conflicting row is invisible to the writer
returns INSERT 0 0, with no error, and the other organization's row is
unchanged. The plain INSERT with the same key fails with
23505 (duplicate key value violates unique constraint "item_key_key"), which tells organization 1 that the key exists. Both need a unique key that spans
organizations, as key does in this table.
What catches all three
Four checks catch all three traps, and none takes more than a few lines.
-
Normalize the setting. Wrap every read of it in
NULLIF(current_setting('app.org_id', true), ''), including the column default, so an empty string and NULL mean the same thing. - Give every backup role a read policy, and compare the dump's row count with a count that does not pass through row-level security. An exit code says the dump finished, not that it holds rows.
-
Check affected-row counts. Where a row was expected,
UPDATE 0orDELETE 0is an error. -
Run your RLS tests as a role that is not the table's owner, not a superuser and not
BYPASSRLS, and assert those flags in the test. A test run as a superuser or aBYPASSRLSrole sees every row, and so does one run as the owner of a table withoutFORCE. It passes without testing anything.
None of this is built in lontar, because the hosted version doesn't exist yet. Its design has a named test for a pooled connection reused after another organization's transaction, one for a cross-organization revise failing exactly like a missing id, one that the test suite's application role is not the owner, a superuser or able to bypass RLS, and a suite test that fails any organization-owned table without a backup policy.
Where this stands
The local build runs today as a Claude Code plugin, for one user: me. It has no row-level security. The repository is private for now, and it will be open source (AGPL-3.0). The hosted version, which this design is for, doesn't exist yet. None of what's above has run in production: I measured it on a scratch database while checking the design.
Whether the hosted version gets built depends on a waitlist: if 280 people confirm a signup at lontar.dev before its closing date, I build it, and if not, lontar stays a personal tool and the list is deleted. The page has the date and the details.
If you have hit other quiet row-level security failures, ones where "nothing matched" hid a mistake, I would like to compare notes: what you saw, why it stayed quiet, and what caught it.