We found this on our own fleet, which is the only reason we found it quickly: three production databases had been dumping fine for months, and in one afternoon all three stopped. No change to the backup job, no change to credentials, no change to the storage target.
The error is unusually clear, which helps:
pg_dump: error: query failed: ERROR: query would be affected by row-level security policy for table "applications"
What had changed was the schema. A tenant-isolation migration had enabled row-level security on forty tables, and every database that received it became undumpable by the role doing the backing up.
Why pg_dump refuses rather than dumping less
This is the part worth internalising, because the behaviour is deliberate and it is the right one.
When pg_dump connects it sets row_security = off. That setting does not mean "ignore policies" — it means "fail if policies would apply". PostgreSQL then errors on any table whose policies are in force for the connecting role, regardless of what those policies would have allowed.
The alternative would be far worse. If pg_dump quietly honoured the policies, it would write a backup containing only the rows that role can see, and that file would look completely normal: right format, plausible size, restores without complaint. You would discover the missing rows during a restore, on the worst day of the year. Refusing is the safe failure.
pg_read_all_data is not enough
Most backup roles are built the sensible way: a login role granted pg_read_all_data, so it can read everything without being a superuser. That role does exactly what it says — SELECT on every table, USAGE on every schema — and it does not include BYPASSRLS.
PostgreSQL's own documentation says as much: if you use row-level security, consider setting BYPASSRLS on roles granted pg_read_all_data. It is easy to read that as advice about convenience. It is not. Without it, the day someone adds a policy is the day your backups stop.
Two more things that look like workarounds and are not:
- A bypass token in the policy. Many tenant policies include an escape hatch — a session variable that, when set to a magic value, allows everything. It makes no difference here. pg_dump fails because policies apply, not because of what they evaluate to.
- The --enable-row-security flag. This one works, and that is the danger: it tells pg_dump to proceed under the policies, producing a partial backup that looks entirely healthy. Never give this flag to a backup job.
FORCE row-level security closes the last door: with it, even the table owner is filtered, so "dump as the owner instead" stops working too.
The fix is one statement
Grant the backup role the attribute it needs:
ALTER ROLE backup_role BYPASSRLS;
It is a read-widening change and nothing else — no write access, no ownership, no superuser. If your backup role is already permitted to read every row in every table, BYPASSRLS grants it nothing it did not have before the policies existed.
While you are there, check the other half of the same problem. A database that revokes CONNECT from PUBLIC — a good idea for isolation between applications on a shared server — also locks out a backup role that was never granted it explicitly. pg_read_all_data covers tables. It does not cover connecting to the database in the first place.
How long would you have gone without noticing?
This is the question the incident actually raises. Our backup script reports per database: seven succeeded, one failed, run finished. The run did finish. The summary was mostly green. The failure lived in a status file on the server, and nothing carried it to a person.
One of those databases had been failing for five consecutive nights before anyone looked. It was not a bad script or a careless team — it was a report nobody had a reason to open.
So the durable fixes are not about RLS at all:
- Alert on a per-database failure, not on the job exiting non-zero. Most jobs exit zero with one database missing.
- Judge by what exists, not by what ran. The useful question is "does every database have a backup from last night", and it is answered by listing the bucket, not by reading a log.
- Restore one on a schedule. A restore is the only check that reads the inside of the file.
Row-level security is worth having. So is a backup. Making them coexist is one grant and one alert, and the cost of discovering that the hard way is measured in the distance between the last good backup and today.