pg_dump errors on Supabase, and the one that does not error
Dump as postgres, through the session
pooler, with a pg_dump of your project's major version. If you dump as a role
you created yourself, give it BYPASSRLS first.
Without it, the usual fix for the row-level security error is
--enable-row-security. With that flag, pg_dump wrote 0 of our
5,000 orders and 250 of our 1,000 posts, printed nothing and exited 0.
Most pg_dump errors on Supabase are loud: the dump stops and the exit code is 1.
The flag people add to get past one of them is the exception, so this page starts with it.
Every error below comes from a run in Supabase's own Postgres image, except the IPv6 one,
which is quoted from Supabase.
The dump that exits 0 with rows missing
We made two tables in supabase/postgres:17.6.1.178, each with row-level security
and one policy of the kind Supabase's docs show. Then we made a read-only role for backups,
as you might to keep the postgres password out of CI.
-- 5,000 rows. Only signed-in users see their own.
create policy "own orders" on public.orders
for select to authenticated using (user_id = auth.uid());
-- 1,000 rows, 250 of them published. Anyone sees the published ones.
create policy "published posts are public" on public.posts
for select using (published);
create role backup_user login password '...';
grant usage on schema public to backup_user;
grant select on all tables in schema public to backup_user;
grant select on all sequences in schema public to backup_user;enable row level security. The full setup also creates the tables and fills them.
A dump as that role fails, which is what you want. The error names the table. Search for it
and the answer you find is --enable-row-security, which makes the error go away:
$ pg_dump -h localhost -U backup_user -d postgres -n public -Fc -f public.dump
pg_dump: error: query failed: ERROR: query would be affected by row-level security policy for table "orders"
pg_dump: detail: Query was: COPY public.orders (id, user_id, total_cents) TO stdout;
$ echo $?
1
$ pg_dump -h localhost -U backup_user -d postgres -n public --enable-row-security -Fc -f public.dump
$ echo $?
0We restored each dump into a new database and counted the rows:
| Dumped as | Flag | Exit code | orders | posts |
|---|---|---|---|---|
postgres | none | 0 | 5,000 of 5,000 | 1,000 of 1,000 |
backup_user | none | 1 | no dump written | |
backup_user | --enable-row-security | 0 | 0 of 5,000 | 250 of 1,000 |
backup_user with BYPASSRLS | none | 0 | 5,000 of 5,000 | 1,000 of 1,000 |
pg_dump write what the policies leave.
By default pg_dump turns row security off for its session, so a role that cannot
bypass it gets an error instead of part of a table. The
pg_dump docs
say --enable-row-security turns it back on and dumps "the parts of the contents
of the table that they have access to". That is what happened here. orders
came out empty because its policy only applies to authenticated, and
backup_user is not a member. posts came out with the 250
published rows, because its policy applies to every role.
The fix
alter role backup_user bypassrls;
Run it as postgres in the SQL editor. In the image, postgres has
CREATEROLE and BYPASSRLS, so it can give the attribute away, and
the next dump as backup_user held all 6,000 rows without the flag. The role
still cannot write. Then drop --enable-row-security from the script.
To see whether a role is subject to row-level security, connect as it and run:
select rolname, rolsuper, rolbypassrls from pg_roles where rolname = current_user;
If both columns are f, row-level security applies to this role on every table
it does not own. Under the flag, each of those tables dumps only the rows its policies show
the role.
The other errors, and what fixes each
permission denied for sequence orders_id_seq
pg_dump: error: query failed: ERROR: permission denied for sequence orders_id_seq
pg_dump: detail: Query was: SELECT last_value, is_called FROM public.orders_id_seqbackup_user before it was granted the sequences. Exit code 1.
pg_dump reads each sequence's current value, so that new rows after a restore
do not reuse ids. An identity column has a sequence behind it, and
grant select on all tables does not cover it. Grant the sequences as well:
grant select on all sequences in schema public to backup_user;permission denied for schema auth
pg_dump: error: query failed: ERROR: permission denied for schema auth
pg_dump: detail: Query was: LOCK TABLE auth.users, auth.refresh_tokens, auth.instances, auth.audit_log_entries, auth.schema_migrations, public.orders, public.posts IN ACCESS SHARE MODEbackup_user. Exit code 1. The same dump as postgres exited 0.
A dump without -n includes Supabase's schemas, and pg_dump locks
every table it will read before it reads any. A role you created has no access to
auth, so the first lock fails and nothing is written.
You can add -n public to dump only your own schema. That leaves out
auth.users, which is your list of users. A restore without it gives you an app
nobody can sign in to. Dump as postgres if you want them, and see
restoring a Supabase dump for where to load it.
aborting because of server version mismatch
$ pg_dump --version
pg_dump (PostgreSQL) 16.15 (Debian 16.15-1.pgdg13+2)
$ pg_dump -h localhost -U postgres -d postgres -Fc -f x.dump
pg_dump: error: aborting because of server version mismatch
pg_dump: detail: server version: 17.6; pg_dump version: 16.15 (Debian 16.15-1.pgdg13+2)postgres:16 image's client against the Supabase 17 container. Exit code 1.
pg_dump refuses a server newer than itself. Run show server_version;
in the SQL editor to see your project's. The
postgresql-client package on a CI runner or an older laptop is often an older
major version. Install the client for your project's major version from the PostgreSQL apt
repository, or run the dump from the postgres:17 image. A newer
pg_dump than the server is fine.
Network is unreachable on db.<ref>.supabase.co
The direct host has only an IPv6 address unless the project has the IPv4 add-on. From a
network or a Docker container without IPv6, the connection fails before
pg_dump starts. Supabase's
IPv4 changelog
lists the messages you get, including these two:
connect to db.example.supabase.co (2001:db8:3333:4444:5555:6666:7777:8888) port 5432 (tcp) failed: Network is unreachable
could not translate host name "db.example.supabase.co" to address: nodename nor servname provided, or not known
Use the session pooler string from Connect in the dashboard. Its host
ends in pooler.supabase.com, the port is 5432 and the user is
postgres.<ref>. It answers over IPv4. Supabase's
backup and restore guide
names it as the fallback when you cannot reach the direct host.
Port 6543
Port 6543 is the transaction pooler, meant for serverless functions that open many short
connections. Supabase's
connection guide
lists pg_dump and backups under the direct connection, and says the shared
pooler does not support prepared statements in transaction mode. We did not run
pg_dump through it. If your string has 6543 in it, switch to the session
pooler on 5432 before you debug anything else.
Check the dump, not the exit code
The exit code caught every error on this page except the one that matters most. To catch that one too, restore the dump somewhere and compare row counts with the project, table by table. Restoring a Supabase dump has the setup that restores with no errors and the query that counts every table. A table at 0 in the restore that has rows in the project is the sign to look for.
How SafeGrd handles this
Before a PostgreSQL backup, SafeGrd checks the role it connects as. If row-level security
hides rows from that role, the backup prints a warning that names the tables and gives the
ALTER ROLE ... BYPASSRLS fix. Tables the role cannot read at all are left out
and listed in the snapshot, instead of failing the whole backup on the first lock. The
Supabase backup page has the setup.
Postgres behavior from the documentation for pg_dump and row security policies. Supabase's connection details from its docs and changelog, read on 31 October 2026. Dumps run on 31 October 2026 on macOS with Docker 29.