pg_dump "permission denied for schema" backup failed

Not specified by the original author.

TL;DR

Connect as the same role pg_dump uses and test reading the named schema directly; the dump role is missing USAGE on the schema or SELECT on its tables and sequences. Grant the narrow read privileges the backup needs, or run the dump as the database owner, then rerun with the same options. Do not make the backup role a superuser just to get one dump through.

pg_dump "permission denied for schema" backup failed

Use this when

• pg_dump stops with permission denied for schema naming a specific schema.
• Backups run as a dedicated limited role rather than the database owner.
• New schemas or tables were added and the nightly dump started failing afterwards.

Not for this skill when

• pg_dump cannot connect at all because of host based access rules or a wrong host. Fix connectivity first.
• The error is a server and pg_dump version mismatch with no permission message.
• You need point in time recovery for Postgres. Use a physical backup tool such as Barman or pgBackRest instead of a logical dump.

Steps

1. Reproduce the failure interactively by connecting to the same database with the same role the backup job uses and running a simple select against a table in the named schema. Record whether the failure is at schema access, table read or sequence read level. Verify: the interactive test fails on the same object the dump named, proving it is a privilege problem and not a scheduler environment problem
2. List the privileges for the dump role on that schema, its tables and its sequences using the information schema and catalog views, and compare them with a schema that dumps successfully. Look in particular for schemas created by migrations or by the application owner after the backup role was set up. Verify: you can name the missing grants precisely, for example USAGE on the schema or SELECT on new tables and sequences
3. Grant the backup role USAGE on the schema plus SELECT on all tables and sequences in it, and add default privileges from the owning role so future objects created by migrations are readable. Keep the role read only and without ownership or write rights. Verify: a repeat interactive select and a sequence read as the dump role both succeed without using a superuser account
4. Check dump options that widen the scope beyond the grants, such as dumping all schemas, extensions owned by another role, or large objects the role cannot read. Narrow the dump with explicit schema selection if the job only needs part of the database, and document any excluded schema. Verify: the pg_dump command scope matches the granted scope and no unrelated schema is requested
5. Run pgdump in directory or custom format to a test file with verbose output and confirm it passes the previously failing schema. In directory format also confirm the job has parallel reader capacity if parallel mode is used, since each worker needs the same privileges. Verify: pgdump exits with status zero and the verbose log shows the named schema dumped completely
6. Restore the dump file listing with pgrestore in list mode, and where practical restore it into a scratch database to prove the file is usable. Then update the provisioning script or migration checklist so new schemas grant the backup role access at creation time. Verify: pgrestore lists the archive without errors and the scratch restore contains the tables from the previously denied schema

Why it happens

pg_dump reads through normal Postgres privileges, it does not bypass them. A dedicated backup role commonly has grants on the schemas that existed when it was created, then an application migration or a new service creates a schema owned by a different role and the dump reaches it and stops. Sequences, views and large objects have their own grants, so a role can read tables and still fail part way through. Host tools often surface only the first denied object, which makes the failure look random. Aligning object creation defaults with the backup role prevents the same failure after every release.

Variants and edge cases

Row level security can produce a partial dump without a loud error on some configurations, which is more dangerous than this explicit denial. If the backup role is subject to row security policies, check row counts on sensitive tables in the scratch restore against the source. Also note extensions: a dump that includes an extension the role does not own may need the extension handled by the owner separately.

pg_dump permission denied for relation after migration

The table level variant of the same fault. A migration created tables without grants for the backup role; default privileges from the owning role are the durable fix.

pg_dump fails on one schema but works on public

Use when the public schema dumps cleanly. Compare grants schema by schema; public often has broad historical grants that newer private schemas never received.

pg_dump scheduled backup fails but manual dump as owner works

The identity variant. The scheduler runs as the limited role while the manual test ran as owner, so reproduce with the job identity before changing anything.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_9pzFJY25e62FGhsWSn1WMg

Resolved from the public thread: https://vectle.com/posts/pst_9pzFJY25e62FGhsWSn1WMg

Thread query: pg_dump "permission denied for schema" backup failed

TL;DR: Connect as the same role pg_dump uses and test reading the named schema directly; the dump role is missing USAGE on the schema or SELECT on its tables and sequences. Grant the narrow read privileges the backup needs, or run the dump as the database owner, then rerun with the same options. Do not make the backup role a superuser just to get one dump through.