Postgres ENABLE ROW LEVEL SECURITY Returns Zero Rows, Not an Error

Row level security is default-deny, not a filter. The moment ALTER TABLE ... ENABLE ROW LEVEL SECURITY commits, any role that is not the table owner, is not a superuser, and does not hold BYPASSRLS gets an implicit policy that matches nothing. SELECT returns zero rows. INSERT fails. UPDATE and DELETE report that they changed zero rows. Nothing on the read path logs an error, because as far as Postgres is concerned the rows were filtered out exactly as instructed.

That is the whole answer. If a table went empty right after a migration, confirm it in one paste, as the role your application connects with and not as the role that ran the migration:

-- Run as the application role. Not the migration role, not a superuser.
BEGIN;
SET LOCAL row_security = off;
SELECT count(*) FROM orders;
-- ERROR:  query would be affected by row-level security policy for table "orders"
ROLLBACK;

row_security = off asks Postgres to raise instead of silently filtering, so the error is the confirmation, not a second problem. If you get it, row level security is the cause and the rest of this page is the fix. If you get a number back, policies are not filtering this role and the empty result is coming from somewhere else.

Why the table looks empty instead of raising a permission error

The instinct is to look for a permission denied in the logs, because that is what every other access control mechanism in Postgres does. Revoke SELECT and you get ERROR: permission denied for table orders. Row level security does not work that way, and the reason is visible in the rewriter.

Policies are not a permission check applied to the statement. They are a predicate appended to the query. add_security_quals() in src/backend/rewrite/rowsecurity.c gathers the permissive policies that apply to the current role and ORs them into a single expression. When there are none, the comment in that function states the outcome plainly: “A permissive policy must exist for rows to be visible at all. Therefore, if there were no permissive policies found, return a single always-false clause.”

An always-false WHERE clause is not an error condition. It is a query that matched nothing, which is the same thing your application sees every time a customer has no orders yet. The documentation for CREATE POLICY describes the same behaviour from the outside:

If row-level security is enabled for a table, but no applicable policies exist, a “default deny” policy is assumed, so that no rows will be visible or updatable.

Writes split into two different symptoms because they run through two different code paths. SELECT, UPDATE and DELETE all have the always-false qual appended, so they match nothing and report zero rows with no error. INSERT is checked by add_with_check_options() in the same file, which adds an always-false WithCheckOption with a null policy name. That one does raise, and it is the only noisy part of the failure.

Quick reference: what the seven RLS statements lock

Every statement in this family takes ACCESS EXCLUSIVE and holds it until the transaction commits, and that uniformity is the useful fact. There is no CONCURRENTLY variant, no weaker form, and no version where one of them is cheaper than the others. In every case the lock is a catalog flip rather than a scan, and both halves of that are sourced two sections down.

Statement	Effect the moment it commits
ALTER TABLE ... ENABLE ROW LEVEL SECURITY	Non-owner roles with no applicable policy see zero rows
ALTER TABLE ... DISABLE ROW LEVEL SECURITY	Every hidden row becomes visible to anyone holding SELECT
ALTER TABLE ... FORCE ROW LEVEL SECURITY	The table owner becomes subject to policies too
ALTER TABLE ... NO FORCE ROW LEVEL SECURITY	The table owner bypasses policies again
CREATE POLICY	Nothing, until RLS is enabled on the table
ALTER POLICY	The row set every affected role can reach changes
DROP POLICY	Roles that policy covered lose or gain rows

The migration that does this

orders holds 40 million rows across about 1,200 tenants. order_items holds 210 million. The team is retrofitting tenant isolation, and the reviewer asked for both tables to be locked down in one change so the release is atomic:

-- migrations/0231_tenant_isolation.sql
SET lock_timeout = '2s';
SET statement_timeout = '5min';

BEGIN;

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id', true)::bigint);

COMMIT;

order_items never got its policy. The diff is 8 lines, it reads as a single coherent idea, and the missing line is an absence rather than a mistake, which is the hardest kind of thing to catch in a review. The migration succeeded, the deploy was green, and the order list page rendered fine. Every order detail page showed an order with no line items.

Does ALTER TABLE ENABLE ROW LEVEL SECURITY lock the table?

Yes, ACCESS EXCLUSIVE, the strongest lock Postgres has. Per the PostgreSQL documentation for ALTER TABLE: “An ACCESS EXCLUSIVE lock is acquired unless explicitly noted.” The row security forms are not among the noted exceptions.

The source is more specific. AlterTableGetLockLevel() in src/backend/commands/tablecmds.c groups AT_EnableRowSecurity, AT_DisableRowSecurity, AT_ForceRowSecurity and AT_NoForceRowSecurity into a single case block that assigns AccessExclusiveLock, under a comment that reads: “These subcommands affect write operations only. XXX Theoretically, these could be ShareRowExclusiveLock.” The weaker lock is an acknowledged possibility that nobody has implemented. Checked against REL_14_STABLE through REL_18_STABLE and master: same case block, same lock, in every branch.

The policy statements take the same lock from a different file. CreatePolicy() and AlterPolicy() in src/backend/commands/policy.c both open the target table with RangeVarGetRelidExtended(stmt->table, AccessExclusiveLock, ...). RemovePolicyById(), the DROP POLICY path, calls table_open(relid, AccessExclusiveLock) under a comment explaining why: “We need exclusive lock to lock out queries that might otherwise depend on the set of policies the rel has; furthermore we’ve got to hold the lock till commit.”

Translated into statements, while any of these is held:

Waits behind it, and vice versa:

• SELECT (ACCESS SHARE), so reads on this table stop
• INSERT, UPDATE, DELETE (ROW EXCLUSIVE)
• VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY (SHARE UPDATE EXCLUSIVE)
• Every other ALTER TABLE subcommand, DROP TABLE, TRUNCATE (ACCESS EXCLUSIVE)

Keeps running:

• Anything touching a different table

Nothing at all runs against this relation while the lock is held. The full conflict matrix is in the Postgres lock mode cheat sheet.

The duration is the good news. ATExecSetRowSecurity() in tablecmds.c fetches the pg_class row, sets relrowsecurity, writes it back, and returns. No table scan, no rewrite, no validation. It takes the same few milliseconds on a 210 million row table as on an empty one. What costs you is acquiring the lock, not holding it: ACCESS EXCLUSIVE has to wait for every in-flight transaction touching the table, and every query that arrives during that wait queues behind it. That is the lock_timeout death spiral, and it is why the example above sets lock_timeout before it starts.

There is one lock cost the example gets wrong, and it is worth naming. Both ALTER TABLE statements sit inside the same BEGIN, and a lock is held until the transaction ends. So orders stays locked while order_items is being locked, and both stay locked through the CREATE POLICY that follows. Two tables, both fully blocked, for the duration of the whole transaction rather than for a few milliseconds each.

Why code review and staging both passed

The migration role owns the tables. Table owners bypass row level security. That single fact hides this bug from every check the team ran.

From the documentation: “Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE … FORCE ROW LEVEL SECURITY.” The implementation is check_enable_rls() in src/backend/utils/misc/rls.c, which returns early for the owner unless relforcerowsecurity is set, and earlier still for anyone with BYPASSRLS, noting that “superusers are always considered to have BYPASSRLS.”

The application connects as a role that owns nothing, and it is the only party in the whole pipeline that sees the real behaviour.

Watch both halves in one session:

-- Scratch database. Run as a superuser or any role that can CREATE ROLE.
CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  tenant_id   bigint NOT NULL,
  status      text   NOT NULL DEFAULT 'pending',
  total_cents bigint NOT NULL DEFAULT 0
);

INSERT INTO orders (tenant_id, status, total_cents)
SELECT (i % 1200) + 1, 'shipped', i * 100
FROM generate_series(1, 100000) AS i;

CREATE ROLE app_user LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;

-- The migration, exactly as it shipped
SET lock_timeout = '2s';
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Still the owner. Nothing looks wrong.
SELECT count(*) FROM orders;                                  -- 100000

-- Become the role the application actually connects with
SET ROLE app_user;

SELECT count(*) FROM orders;                                  -- 0
UPDATE orders SET status = 'cancelled' WHERE tenant_id = 7;   -- UPDATE 0
DELETE FROM orders WHERE tenant_id = 7;                       -- DELETE 0
INSERT INTO orders (tenant_id, total_cents) VALUES (7, 1999);
-- ERROR:  new row violates row-level security policy for table "orders"

RESET ROLE;

SET ROLE is the entire verification story. It changes the effective user id that check_enable_rls() reads, so the session becomes subject to exactly what the application is subject to. The role you switch to has to be the right kind of role: not a superuser, not a BYPASSRLS holder, and not a member of the table’s owning role. Switch to any of those three and you get the same misleading pass the migration role gave you.

What it looks like in production

The read path never turns red. Every endpoint that lists rows returns HTTP 200 with an empty array, on time, with normal latency, because an always-false qual is the cheapest predicate in the planner. Error rate dashboards stay flat. Latency percentiles stay flat. Nothing pages anyone.

The write path is where the signal is, and it is narrow. INSERT raises ERROR: new row violates row-level security policy for table "order_items" with SQLSTATE 42501, insufficient_privilege, which most application stacks surface as an authorization failure rather than a schema problem, sending whoever investigates toward the wrong system. UPDATE and DELETE raise nothing at all and report zero rows affected. An ORM that treats zero affected rows as “record not found” turns that into a 404, which looks like a routing bug.

The real alarm is a business metric, not an infrastructure one: orders per minute goes to zero while the service reports itself healthy. Time to detection is however long it takes someone to notice that number.

The window before the commit fails differently. While the migration is still acquiring ACCESS EXCLUSIVE on a busy table, every query arriving on that table queues behind a statement that has not started work yet, which is its own incident with its own first query to run. If the pool saturates before the lock is granted, the outage starts before the migration does and looks like a completely different failure from the one that starts at commit.

How to stop it right now

Two ways to end it, and the choice is about the isolation requirement, not about the database.

Option A, turn enforcement back off. One statement, and every hidden row is readable again the moment it commits:

SET lock_timeout = '2s';
ALTER TABLE order_items DISABLE ROW LEVEL SECURITY;

This is the fastest way back to a working read path, and it is also a data-exposure change: it re-exposes every row to every role holding table-level SELECT, which is the state the table was in before the migration. Take it when the isolation requirement can be off for the time it takes to write and review a real policy, and tell whoever owns that requirement that you took it.

Option B, ship the policy that is missing. Keeps enforcement on and closes the gap directly:

-- Correct only if order_items carries its own tenant_id column. If the tenant
-- is reachable only by joining back to orders, the predicate is a subquery and
-- you are writing it mid-incident, which is an argument for option A.
SET lock_timeout = '2s';
CREATE POLICY order_items_tenant_isolation ON order_items
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id', true)::bigint);

This ships an unreviewed predicate straight into production, so run the SET ROLE check from the reproduction above before you call the incident over. A predicate that matches nothing leaves the table exactly as empty as it was, with a successful migration on top of it.

Both statements take ACCESS EXCLUSIVE on the table, so both queue behind whatever is still running against it, and lock_timeout belongs on both for the reason the lock section gives. Neither is the fix. The fix is the ordering, and that is two sections down.

Version gates

Row level security arrived in PostgreSQL 9.5, and FORCE ROW LEVEL SECURITY shipped in that same release: it is documented on the 9.5 ALTER TABLE page.

Restrictive policies came later. CREATE POLICY ... AS RESTRICTIVE is absent from the 9.5 and 9.6 documentation and present from PostgreSQL 10 onward. The distinction matters here because restrictive policies cannot rescue you from a default deny. Per the CREATE POLICY documentation: “Note that there needs to be at least one permissive policy to grant access to records before restrictive policies can be usefully used to reduce that access. If only restrictive policies exist, then no records will be accessible.” A table with three carefully written restrictive policies and no permissive one produces the identical empty result this article is about.

The lock level has not moved. The four ALTER TABLE row security subcommands sit in the same AccessExclusiveLock case block in AlterTableGetLockLevel() from REL_14_STABLE through REL_18_STABLE and master, and the three policy statements have taken AccessExclusiveLock in policy.c across the same range. Nothing in the currently maintained branches makes any of these cheaper.

The policies in this article use the two-argument form of current_setting, documented from PostgreSQL 9.6 onward, which returns NULL for a setting that has never been set instead of raising unrecognized configuration parameter. The one-argument form inside a policy turns every query from a session that forgot to set the variable into an error rather than an empty result.

Create every policy first, enable second, verify third

The ordering fix works because of a property stated directly in the ALTER TABLE documentation: “Note that policies can exist for a table even if row-level security is disabled. In this case, the policies will not be applied and the policies will be ignored.” A policy sitting on a table with row level security off is inert. That makes the intermediate state between the two migrations completely safe, which is what allows them to be separated by hours or days.

-- Migration 1, runs in milliseconds. No behaviour change: RLS is still off.
SET lock_timeout = '2s';
CREATE POLICY orders_tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id', true)::bigint);

Before enforcing anything, check that the predicate actually matches rows for the role that will be subject to it. The policy is ignored at this point, so run its USING expression as an ordinary WHERE clause. This takes no lock and touches no catalog:

-- As the application role, with the session variable the application will set.
-- The policy is inert right now, so this is just a normal query.
SET ROLE app_user;
SET app.tenant_id = '7';

SELECT count(*) FROM orders
  WHERE tenant_id = current_setting('app.tenant_id', true)::bigint;   -- 84 here, 33412 on the real table

RESET ROLE;

That count is what the application will see once row level security is on. A zero here means the predicate is wrong, or the variable name does not match what the application sets, and you have found it before enforcement rather than after.

One caveat on that SET. It is session-scoped, and a session-scoped setting survives the connection being returned to a pool. A pooled application has to set the tenant variable per transaction with SET LOCAL, or reset it explicitly on checkin, or the next request served by that connection inherits the previous request’s tenant id and the policy hands it somebody else’s rows.

-- Migration 2, separate deploy, separate transaction.
SET lock_timeout = '2s';
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Then repeat both migrations for order_items, in their own transactions, for the reason given above.

After migration 2 commits, run the SET ROLE check from the reproduction above one more time. It is the only check in this sequence that exercises the real code path, and it is three lines.

What pgfence does with this

pgfence flags all seven of these statements, because none of them can be judged from the statement alone: the lock is uniform and brief, and the damage is entirely in what the catalog change means for roles the migration file never mentions. enable-rls is HIGH and carries the ordering recipe directly.

npx @flvmnt/pgfence explain "ALTER TABLE order_items ENABLE ROW LEVEL SECURITY"

Statement:
  ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;

[HIGH] enable-rls
  ALTER TABLE "order_items" ENABLE ROW LEVEL SECURITY: affected non-owner roles need matching policies. Without an applicable policy, reads return no rows and writes fail.
  Lock: ACCESS EXCLUSIVE
  Blocks: reads, writes, other DDL

  Safe rewrite:
  Define policies before enabling RLS. The order matters: with RLS on and no matching policy, affected roles are denied.
    -- 1. Create the policies first
    CREATE POLICY <name> ON order_items FOR SELECT USING (<condition>);
    -- 2. Then enable RLS
    ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;
    -- 3. Test as the target role
    SET ROLE <app_role>; SELECT count(*) FROM order_items;

Running analyze on the original migration file also catches the lock overlap, through two policy rules that read the shape of the transaction rather than the statements in it. Statement table and the two further warnings the file earns for its missing session settings trimmed here for width:

npx @flvmnt/pgfence analyze migrations/0231_tenant_isolation.sql

  Policy Violations:
  WARNING Multiple statements holding ACCESS EXCLUSIVE lock in same transaction: "ALTER TABLE order_items ENABLE ROW LEVEL SECURITY" runs while ACCESS EXCLUSIVE is already held from "ALTER TABLE orders ENABLE ROW LEVEL SECURITY". This compounds the lock duration, blocking all reads and writes for the entire transaction.
    → Split into separate transactions so each ACCESS EXCLUSIVE lock is held for the minimum time
  WARNING Wide lock window: ACCESS EXCLUSIVE locks held on multiple tables ("orders" and "order_items") in the same transaction. This multiplies the blast radius of lock contention.
    → Split operations on different tables into separate transactions to minimize lock overlap

What pgfence cannot do is tell you whether the policy you wrote matches any rows. It reads SQL text, not your session variables or your role graph, so it can insist on the ordering and on the SET ROLE verification step, and the verification itself stays yours to run. Row level security is one of the operations covered in Five Postgres migration footguns that no linter catches today.

Review checklist

1. Every ALTER TABLE ... ENABLE ROW LEVEL SECURITY in the diff has a matching CREATE POLICY for that exact table, created in an earlier migration and not in the same file.
2. Each table gets its own transaction.
3. The policy set includes at least one permissive policy. Restrictive policies alone deny everything.
4. The verification step is SET ROLE <app_role> followed by a count, run as a role that is subject to the policies, per the rule above. A count run as the migration role proves nothing.
5. Any DISABLE ROW LEVEL SECURITY, NO FORCE ROW LEVEL SECURITY or DROP POLICY in the diff is treated as a data-exposure change and reviewed by whoever owns the isolation requirement, not as routine schema cleanup.

Related

• ENABLE ROW LEVEL SECURITY lock reference
• FORCE ROW LEVEL SECURITY lock reference
• Postgres ALTER TABLE hangs: find the blocker, then decide who to cancel
• Five Postgres migration footguns that no linter catches today
• REPLICA IDENTITY FULL is the silent CDC killer
• Lock and safety checks