Postgres row-level security: four ways it fails without an error

Feddy stores every customer’s data in one Postgres database. Every table uses FORCE ROW LEVEL SECURITY, so even the table owner goes through the policies. Writing the policies was simple. The problem was four places where RLS returned success, or no error, and the result was wrong.

Each case below is reproduced on Postgres 18.6, with the output copied from that run.

What does the setup look like?

One role owns the tables and the app connects as that role. Each request runs in a transaction that sets the tenant first, and every policy checks that setting:

ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE conversations FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant ON conversations
  USING (project_id = nullif(current_setting('app.project_id', true), '')::int);

BEGIN;
SELECT set_config('app.project_id', '1', true);  -- true: ends with the transaction
-- the request's queries
COMMIT;

The test table has 100 conversations split across two projects.

Why does my UPDATE affect 0 rows?

Rows hidden by a policy are skipped, not rejected. With no tenant set, the owner sees and changes nothing, and Postgres reports success:

SELECT count(*) FROM conversations;           -- 0
UPDATE conversations SET body = upper(body);   -- UPDATE 0
DELETE FROM conversations WHERE id = 1;        -- DELETE 0

The same happens when a tenant is set and you target an id from another tenant: UPDATE 0, and RETURNING id returns no rows. Only an insert or update that would create a forbidden row raises an error:

INSERT INTO conversations VALUES (101, 2, 'other tenant');
ERROR:  new row violates row-level security policy for table "conversations"

The PostgreSQL docs state that rows failing USING are “silently suppressed; no error is reported”. That still caught us three times in one change:

• A backfill in a migration. We ran ALTER TABLE … ADD COLUMN status, then UPDATE conversations SET status = 'open' as the owner. It returned UPDATE 0. The migration succeeded and every row stayed null. A backfill must turn FORCE off around the update:

  ALTER TABLE conversations NO FORCE ROW LEVEL SECURITY;
  UPDATE conversations SET status = 'open';   -- UPDATE 100
  ALTER TABLE conversations FORCE ROW LEVEL SECURITY;

• An update before the tenant was set. A code path updated a row before setting the context, got UPDATE 0, and continued as if the save worked.

• A test that passed for the wrong reason. Seeding left a session-level setting behind. set_config(…, false) lasts until the connection closes, so on a pooled connection it leaks into the next request:

  SELECT set_config('app.project_id', '1', false);
  -- next request, never sets a tenant
  SELECT count(*) FROM conversations;   -- 50

  Our cross-tenant tests were reading the context left by seeding, not the one they set. The test helper now sets every variable on each call (empty when unused), and the app only uses transaction-local set_config(…, true).

Now any write under RLS that depends on changing rows asserts the affected count or uses RETURNING, or sets and checks the context first.

One detail in that policy: current_setting('app.project_id', true) returns NULL when the setting has never been set in the session, but an empty string after it has been set and reset. ''::int raises invalid input syntax for type integer: "", so the policy wraps the call in nullif(…, '').

Why does my app get “permission denied” on a new table?

The role that runs CREATE TABLE owns the new table. Our migrations normally run as the app’s role, so the app owns every table. Once, we ran a migration by hand as a superuser to repair it, and the new table belonged to the superuser:

SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public';
 conversations | rls_owner
 notes         | decqxy
 projects      | rls_owner

SELECT count(*) FROM notes;   -- as rls_owner
ERROR:  permission denied for table notes

The test suite stayed green because the test database ran migrations as the app’s role. Only the database repaired by hand had the wrong owner. The fix is ALTER TABLE notes OWNER TO rls_owner;. After any migration run by hand, we check ownership with the pg_tables query above.

Why does Postgres say “infinite recursion detected in policy”?

Two policies queried each other’s tables. A user could see users in their own projects, which read teammates. A teammate row was visible if it belonged to the current user, which read users:

CREATE POLICY same_project ON users
  USING (project_id IN (SELECT project_id FROM teammates));
CREATE POLICY own_rows ON teammates
  USING (user_id IN (SELECT id FROM users WHERE id = current_setting('app.user_id')::int));

SELECT * FROM users;
ERROR:  infinite recursion detected in policy for relation "users"

Both CREATE POLICY statements succeed. The error only appears when a query touches either table. The fix is to break the cycle with a session setting, so teammates checks app.user_id directly instead of querying users:

CREATE POLICY own_rows ON teammates
  USING (user_id = nullif(current_setting('app.user_id', true), '')::int);

We break policy cycles with session settings. A SECURITY DEFINER function also works, but it adds another path that bypasses isolation.

Why is my pg_dump backup incomplete?

pg_dump sets row_security to off so it never dumps a filtered table. A role that cannot bypass RLS gets an error instead. Under FORCE, that includes the table owner:

$ pg_dump -U rls_owner -d rls_demo -f dump.sql
pg_dump: error: query failed: ERROR:  query would be affected by row-level security policy for table "conversations"
HINT:  To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
$ echo $?
1

The error is expected, but pg_dump leaves a partial file behind. Our first manual dump of the dev database came out about 6% short this way. In this reproduction the file is 2,038 bytes: the schema and a COPY public.conversations header with no rows. The same dump run as a superuser is 6,318 bytes with all five tables. A backup script that ignores the exit code, or pipes pg_dump into gzip without pipefail, uploads the partial file as a good backup.

The CREATE ROLE docs say “superusers and the owner of the table being dumped always bypass RLS”. Under FORCE the owner does not, as the hint above shows. Our backups run as a superuser under set -euo pipefail.

What do we check now?

• Writes on RLS tables assert the affected row count, or run after setting and checking the context.
• Tenant settings use set_config(…, true). Tests set every variable on each call.
• Data migrations turn FORCE off before the update and back on after.
• After any migration run by hand, check tableowner in pg_tables.
• Policies never query a table whose policy queries them back. They use session settings instead.
• Backups run as a superuser or a BYPASSRLS role, and fail loudly on a non-zero exit.
• Cross-tenant tests read and write as one tenant and expect nothing from another, including UPDATE and DELETE affecting zero rows.

Feddy is in-app support for iOS apps and websites. Customer conversations sit behind these policies, and the cross-tenant tests run on every push to main.

Questions