pg_dump on Supabase: "query would be affected by row-level security policy"
pg_dump: error: query failed: ERROR: query would be affected by row-level security policy for table "profiles"You see this when pg_dump runs as a role that is not a superuser and has no BYPASSRLS, and it reaches a table with row-level security enabled. On Supabase that is usually most tables in public and storage.objects on every project, so a dump with a separate read-only role usually stops at the first one.
pg_dump raises an error on purpose
pg_dump sets row_security to off for its session. That setting does not switch RLS off. It tells Postgres to raise an error whenever a policy would hide rows, so a backup cannot silently miss data.
Do not add --enable-row-security
With --enable-row-security the error disappears, and pg_dump dumps only the rows the role is allowed to see. Your policies are written for anon and authenticated, so a backup role matches none of them. The RLS tables come out empty, pg_dump exits with 0 and the scheduled job reports success.
Check the role
select rolname, rolsuper, rolbypassrls
from pg_roles
where rolname = current_user;Run it with the same connection string the backup uses. If rolsuper and rolbypassrls are both false, the role cannot make a complete dump of tables with RLS.
Give the backup a role that sees every row
create role backup_reader login bypassrls password '...';
grant pg_read_all_data to backup_reader;pg_read_all_data grants SELECT on every table but not BYPASSRLS, so it needs both. Creating a role with BYPASSRLS takes a superuser, or since Postgres 16 a role that has BYPASSRLS itself. Keep the password in your CI secrets and drop the role when you stop using it.
Restore one dump
Before you rely on the job, load one dump into a scratch database and compare row counts on the tables that have RLS with the live project. A table that is empty in the copy and not in the source is the sign that a role or flag is wrong. How to test a Supabase backup covers the comparison.
ReviveDB creates a role like this for each backup, removes it afterwards and restore-tests every database dump. See what is checked.