Fix

pg_dump: permission denied for table "orders"

You created a dedicated backup role so the nightly job would not run as superuser. It worked. Then a migration added a table, and the dump has been failing — or worse, quietly shrinking — ever since.

The error

pg_dump: error: query failed: ERROR:  permission denied for table orders
pg_dump: error: query was: LOCK TABLE public.orders IN ACCESS SHARE MODE

Or, one level up, when the role cannot even see into the schema:

pg_dump: error: query failed: ERROR:  permission denied for schema billing

Note where it fails. pg_dump takes an ACCESS SHARE lock on every table before it reads a single row, so the error lands in the first seconds of the run, not forty minutes into a 200 GB dump. That is the one merciful thing about it.

What pg_dump actually requires

Reading a database is not one privilege, it is four, and they are granted in different places:

The definitions themselves — column types, indexes, constraints — come from the system catalogs, which any role can read. That is why --schema-only is not the escape hatch people expect it to be: the table locks are still taken, so a role without SELECT fails on exactly the same line.

Fix 1 — on Postgres 14 and later, use the predefined role

Postgres ships a role designed for this exact job:

GRANT pg_read_all_data TO backup;

It confers SELECT on all tables, views and sequences, and USAGE on all schemas, including ones created after you granted it. That last part is the whole point — it is a standing capability, not a snapshot, so it cannot drift out of date the way the explicit grants below do.

Two things it deliberately does not do. It grants nothing on the write side, so the same role cannot be reused to restore. And it does not bypass row-level security — see below, because that is the case that fails silently rather than loudly.

Fix 2 — explicit grants, and the two ways they rot

On Postgres 13 or older, or when you want the backup role scoped to one schema:

GRANT CONNECT ON DATABASE appdb TO backup;
GRANT USAGE ON SCHEMA public TO backup;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup;

ON ALL TABLES is a loop, not a rule. It expands once, at the moment you run it, over the tables that exist right then. Every table a migration creates tomorrow is not covered. This is why the dump worked for three months and then broke on a Tuesday — nothing about the backup changed, a deploy did.

The standing version is a default privilege:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO backup;

And here is the part that catches almost everyone. Default privileges apply only to objects created by one specific role — by default, whoever runs the ALTER DEFAULT PRIVILEGES. If you run it as postgres but your migrations connect as app_user, tables created by those migrations get nothing, and you are back to a dump that breaks on the next deploy with the fix apparently already applied. Name the creating role:

ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public
  GRANT SELECT ON TABLES TO backup;

Second rot: default privileges are set per schema. A migration that introduces a brand-new schema is outside every statement above — no USAGE, no defaults, nothing. If your schema list is not fixed, prefer Fix 1.

Fix 3 — dump as the owner

The unglamorous answer, and the right one on managed Postgres where you cannot create roles freely: run the dump as the role that owns the objects. On Supabase, Neon or Railway that is the administrative role in the connection string they give you. A table's owner has full rights on it by definition, so ownership can never fall behind a migration the way a grant can.

The cost is that this role can also drop everything it can read, so the credential in your backup cron is no longer read-only. Pick deliberately: a least-privilege role you have to maintain, or an owner role you have to protect.

The silent case: row-level security

This is the one worth reading twice, because it does not always announce itself.

If the dumping role is subject to an RLS policy, pg_dump refuses rather than guess:

pg_dump: error: query failed: ERROR:  query would be affected by row-level security policy for table "orders"

pg_dump connects with row_security off, which makes the server raise that error instead of quietly filtering rows. The flag that makes the error go away, --enable-row-security, does not grant anything — it turns the filtering back on. The dump then succeeds, exits zero, and contains only the rows that role was allowed to see. A multi-tenant table can come out with one tenant in it, and nothing in the output says so.

Who is exempt from RLS:

• Superusers, and any role with the BYPASSRLS attribute.
• The table owner — unless the table was declared ALTER TABLE ... FORCE ROW LEVEL SECURITY, which is common precisely on tables holding tenant data.

pg_read_all_data is not on that list. A backup role that reads everything on a database without RLS can read a filtered subset on a database with it. Granting BYPASSRLS requires superuser, which you do not have on managed Postgres — so there, dump as the owner and check that FORCE is not set on the tables you care about:

psql -d "$SOURCE_URL" -Atc \
  "SELECT relname, relrowsecurity, relforcerowsecurity
     FROM pg_class WHERE relrowsecurity ORDER BY 1"

Check the dump, not the exit code

Two habits close the loop. First, make sure a failure is actually seen. In the classic one-liner, the shell reports the exit status of the last command in the pipe, so a pg_dump that died on a permission error and a gzip that happily compressed the truncated stream give you a successful job and a 400-byte file:

# without this, $? is gzip's, not pg_dump's
set -o pipefail
pg_dump -Fc -d "$SOURCE_URL" -f appdb.dump

Second, count what landed. A custom-format archive lists its own contents without restoring:

pg_restore -l appdb.dump | grep -c 'TABLE DATA'

Compare that with the source:

psql -d "$SOURCE_URL" -Atc \
  "SELECT count(*) FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog','information_schema')"

Equal numbers mean every table is present. They say nothing about the rows inside them — which is the RLS case above, and the reason the drill compares counts against a manifest instead of trusting the archive's table of contents.

The part worth keeping

"Permission denied" is the good outcome. It is loud, it happens in the first seconds, and it stops the job. The same misconfiguration one notch further along — a role with SELECT everywhere but subject to RLS — produces a dump that looks completely normal and is missing rows. Privileges are not a setup step you do once; they are part of what a backup has to prove every night.

Related fixes: pg_dump hangs — and takes your application with it pg_restore: role "…" does not exist pg_dumpall vs pg_dump: what a per-database dump leaves behind pg_dump formats: custom, plain, directory or tar? How to actually test a Postgres backup