Fix

pg_restore: role "app_user" does not exist

Your data is in the dump. What is missing is everything around it: the roles that own your tables and appear in every GRANT.

The error

pg_restore: error: could not execute query: ERROR:  role "app_user" does not exist
pg_restore: error: Command was: ALTER TABLE public.orders OWNER TO app_user;

From a plain SQL dump replayed with psql, the same thing reads:

ERROR:  role "app_user" does not exist

On Postgres 13 and older the wording is pg_restore: [archiver (db)] Error while PROCESSING TOC, but the cause is identical.

Why it happens

A database dump describes objects and their ownership. pg_dump emits an ALTER ... OWNER TO for every table, sequence, view and function, plus the GRANT/REVOKE statements attached to them. Those statements name roles — but roles live at the cluster level, not inside a database, so pg_dump never carries them.

Restore into a fresh cluster and every one of those role names is unknown. This is why you see the error dozens of times in a row: once per object.

It bites hardest when you move a dump off a managed host. A Supabase dump, for example, references anon, authenticated, service_role and supabase_admin — none of which exist in a stock postgres:17 container.

Fix 1 — drop the ownership, if you only want the data

For a restore drill, a staging copy, or a local clone, you do not need the original roles. Tell pg_restore to skip both ownership and privileges:

pg_restore --no-owner --no-acl -d mydb backup.dump

Both flags matter. The most common mistake is passing only --no-owner (-O): ownership statements disappear, then the restore still fails on the GRANT SELECT ON ... TO app_user lines. --no-acl (an alias of --no-privileges, short form -x) removes those too.

Everything then ends up owned by the role you connect as. The same flags exist at dump time if you would rather bake it in:

pg_dump -Fc --no-owner --no-privileges -d "$SOURCE_URL" -f backup.dump

For a plain SQL dump the equivalent restore is psql -v ON_ERROR_STOP=1 -f dump.sql — you must have used the flags at dump time, since the statements are already written into the file.

Fix 2 — create the roles first, if you want a faithful copy

If the restore is a real recovery and permissions must survive, create the roles before restoring. Ownership only requires that the role exists; it does not need a password or login rights:

psql -d mydb -c 'CREATE ROLE app_user NOLOGIN;'
psql -d mydb -c 'CREATE ROLE readonly NOLOGIN;'

To capture them all from the source cluster instead of guessing, dump the globals — roles and tablespaces, no table data:

pg_dumpall --globals-only -d "$SOURCE_URL" -f globals.sql
psql -d postgres -f globals.sql

Two caveats on managed Postgres. Role passwords come from pg_authid, which most managed hosts do not let you read, so they will be missing from globals.sql. And the file usually contains attributes your target refuses (SUPERUSER, REPLICATION, provider-internal roles) — read it before replaying it, and strip what does not apply.

Which role names do you actually need?

Ask the dump, before the restore fails:

# every owner recorded in the archive's table of contents
pg_restore -l backup.dump | grep -oE '[^ ]+$' | sort -u | head -20

Or list the source cluster directly:

psql -d "$SOURCE_URL" -c '\du'

The part that actually hurts

By default pg_restore does not stop on these errors. It logs each one, keeps going, and exits with a non-zero status at the end — which a cron job piping to /dev/null will happily ignore. The tables and rows land, so the restore looks fine; the GRANTs did not, so your application role can no longer read the tables it owns.

Make failures loud:

# stop at the first error instead of limping to the end
pg_restore --exit-on-error --no-owner --no-acl -d mydb backup.dump

# plain SQL equivalent
psql -v ON_ERROR_STOP=1 --single-transaction -d mydb -f dump.sql

Then verify the permissions came back, not just the rows — \dp in psql shows the access privileges of each table. A restore that ends with correct row counts and empty ACLs is exactly the kind of half-success the drill is designed to catch.

Related fixes: How to actually test a Postgres backup pg_restore: unsupported version in file header pg_dumpall vs pg_dump: what a per-database dump leaves behind pg_dump formats: custom, plain, directory or tar?