pg_dump on Supabase: "query would be affected by row-level security policy"

On Supabase, tables exposed to the API should have row-level security, and storage.objects has it on every project. That changes what pg_dump can read, depending on the role that runs it.

By default, the dump fails

pg_dump sets row_security = off. That does not skip RLS. It makes Postgres raise an error instead of filtering rows:

ERROR:  query would be affected by row-level security policy for table "profiles"

This happens for any role that is not a superuser and has no BYPASSRLS, unless it owns the table and the table is not forced. A failed backup is the good outcome: you notice.

The flag that hides it

The usual answer to that error is --enable-row-security. pg_dump then dumps only the rows the role can see. A backup role usually matches no policy written for anon or authenticated, so those tables come out empty. Exit code 0, no warning.

pg_read_all_data does not help

pg_read_all_data (Postgres 14+) grants SELECT on everything, but not BYPASSRLS. A role with only that grant gets the same error.

The fix

Check your backup role:

select rolname, rolsuper, rolbypassrls
from pg_roles where rolname = current_user;

If both are false, give backups their own read-only role that sees every row:

create role backup_reader login bypassrls
  password '...' valid until '2026-11-01';
grant pg_read_all_data to backup_reader;

Creating a BYPASSRLS role takes a superuser, or since Postgres 16 a role that has BYPASSRLS itself. Drop it after the backup.

Then restore the dump once and compare row counts on your RLS tables with the source. A table that comes back empty but is not empty in the source is the sign. More on that in testing a Supabase backup.

ReviveDB creates a role like this for every backup, removes it afterwards, and restore-tests the result.