Log inStart free

pg_dump fails with "query would be affected by row-level security policy"

Why pg_dump stops on tables with RLS when the dumping role can't bypass it, what --enable-row-security really does, and the simplest way to get a full dump of a SupaHost project.

What you see

pg_dump: error: Dumping the contents of table "messages" failed: PQgetResult() failed.
pg_dump: detail: Error message from server: ERROR:  query would be affected by row-level security policy for table "messages"
pg_dump: detail: Command was: COPY public.messages (id, channel_id, author_id, body, created_at) TO stdout;

Why it happens

pg_dump turns row security off for its session (row_security = off) so that it copies every row, not just the rows its role is allowed to see. With row security off, Postgres refuses to read a table with RLS enabled unless the role:

  • owns the table (and the table doesn't use force row level security), or
  • has the BYPASSRLS attribute, or is a superuser.

If neither is true, Postgres stops with this error rather than hand back a silently incomplete dump. You usually hit it when dumping with a custom role you created, such as a read-only reporting user, or with a tool that connects as anon/authenticated.

Check your role:

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

How to fix it

On SupaHost, use the built-in export. Dashboard → your project → Backups tab → Create pg_dump. SupaHost runs pg_dump for you as an admin role, so RLS doesn't get in the way, and you get a custom-format .dump file with a download link that lasts 24 hours. Only the project owner can create exports. This works on every plan.

Restore it anywhere with pg_restore:

pg_restore --no-owner --no-privileges -d "$TARGET_DATABASE_URL" app-one-20261111-0930.dump

On Business, if you run pg_dump yourself over the SSH tunnel:

  1. Open the tunnel: ssh -N -L 5432:127.0.0.1:5432 [email protected].
  2. Get the connection string from Dashboard → your project → Connect tab → Database → Show connection string. It connects as postgres to p_<project>.
  3. Run pg_dump 17 or newer (the server is Postgres 17):
pg_dump "postgresql://postgres:[email protected]:5432/p_app_one" \
  -Fc --schema=public -f app-one.dump

Dump as postgres, not as a custom role; in the Supabase Postgres image postgres normally has BYPASSRLS (check with the query above). If you must use a role that can't bypass RLS, the error still tells you exactly which table it couldn't read.

About --enable-row-security

This flag makes the error go away, but it doesn't give you a full backup. It tells pg_dump to leave row security on, so each table is dumped with your role's policies applied: you get only the rows that role can see. For anon, that is often nothing. Use it only when a filtered dump is what you want, for example to share one tenant's data, and never as a backup.

Updated 11 November 2026 · View as Markdown · Something wrong or missing? Tell us