DEV Community

Roni Yusuf
Roni Yusuf

Posted on Originally published at lontar.dev

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';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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'"
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

  1. 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.
  2. 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.
  3. Check affected-row counts. Where a row was expected, UPDATE 0 or DELETE 0 is an error.
  4. 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 a BYPASSRLS role sees every row, and so does one run as the owner of a table without FORCE. 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.

Top comments (0)