How to back up PostgreSQL roles, users and permissions

What pg_dump saves and what it leaves out

We restored a pg_dump archive of a database called shop into a fresh server that had none of its roles. The data loaded, but every owner and grant failed:

pg_restore: error: could not execute query: ERROR:  role "app_owner" does not exist
Command was: ALTER SCHEMA app OWNER TO app_owner;
...
pg_restore: warning: errors ignored on restore: 11

The tables ended up owned by postgres, with none of the original grants.

Dump roles and tablespaces

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql

sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgresql/globals.sql

• --globals-only (-g): roles, memberships, role settings, parameter grants and tablespaces.
• --roles-only (-r): the same without tablespaces.
• --tablespaces-only (-t): tablespaces only.
• --no-role-passwords: leave out password hashes. Restored roles have no password until you set one.
• -f: the output file. The output is always plain SQL, restored with psql.

The file is readable SQL. These are the lines for one user, its memberships and a tablespace:

sudo -u postgres grep -E '^(CREATE|ALTER) ROLE alice|^GRANT|^CREATE TABLESPACE' /var/backups/postgresql/globals.sql

CREATE ROLE alice;
ALTER ROLE alice WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:erB1XNIDn1uqQfDIJd1ZOA==$M9erOqs567gcVr8Ajr6gtDB5XuncrsifkErTCAj60aA=:9JYDuKnAGD8jPYG1cKtJspcKQKGNn5+I6aFXnl54sgk=';
GRANT app_ro TO app_rw WITH INHERIT TRUE GRANTED BY postgres;
GRANT app_ro TO reporting WITH INHERIT TRUE GRANTED BY postgres;
GRANT app_rw TO alice WITH INHERIT TRUE GRANTED BY postgres;
GRANT pg_monitor TO reporting WITH INHERIT TRUE GRANTED BY postgres;
GRANT SET ON PARAMETER log_min_duration_statement TO app_owner;
CREATE TABLESPACE fastdisk OWNER postgres LOCATION '/srv/pg-fastdisk';

pg_dumpall 16.15 and 18.6 wrote identical files apart from a random key on the \restrict line at the top. psql releases older than August 2025 reject that line with invalid command \restrict, so restore with a current psql.

Password hashes are secrets

Each PASSWORD 'SCRAM-SHA-256$…' is the stored hash, not the password. With SCRAM the hash alone does not let someone log in, but anyone holding the file can test password guesses against it offline. Older md5 hashes are weaker: the PostgreSQL docs say MD5 gives no protection if the hash is stolen, and PostgreSQL 18 deprecates MD5 passwords, warning whenever one is set.

pg_dumpall does not restrict the file's permissions. Under a umask of 0002 our globals.sql came out -rw-rw-r--, readable by every account on the server. Write it into a mode 700 directory as above, or set umask 077 in the script that creates it, and encrypt it before it leaves the server.

If you would rather not store hashes at all, dump with --no-role-passwords and reset each login role's password after a restore with ALTER ROLE alice PASSWORD '…' or psql's \password alice.

Restore roles before databases

On the new server, create each tablespace directory first. It must exist, be empty and be owned by postgres. Then load the globals into the postgres database. -X skips your .psqlrc, as the docs advise:

sudo install -d -o postgres -g postgres -m 700 /srv/pg-fastdisk

sudo -u postgres psql -X -f /var/backups/postgresql/globals.sql postgres

Expect ERROR: role "postgres" already exists; the docs call it harmless, since that role exists on every new server. Do not add ON_ERROR_STOP here, or that line stops the restore. Read the output for any other ERROR.

Now restore the database from its custom-format dump (pg_dump -Fc) with -C (--create). pg_restore connects to postgres, creates shop with its original owner, and applies the database-level grants and per-database role settings:

sudo -u postgres pg_restore -C -d postgres /var/backups/postgresql/shop.dump

Restoring into a database made with createdb, without -C, brought back the tables and grants, but REVOKE CONNECT … FROM PUBLIC, GRANT CONNECT … TO app_ro and ALTER ROLE alice IN DATABASE shop SET search_path were missing. With -C all three were there. The restored alice logged in with her old password, and this check gave the same result on both servers:

sudo -u postgres psql -d shop -c "SELECT rolname, has_table_privilege(rolname, 'app.orders', 'SELECT') AS can_read, has_table_privilege(rolname, 'app.orders', 'INSERT') AS can_write FROM pg_roles WHERE rolname IN ('alice', 'reporting')"

  rolname  | can_read | can_write 
-----------+----------+-----------
 alice     | t        | t
 reporting | t        | f
(2 rows)

Restore into a managed database without superuser

Managed PostgreSQL services give you an admin role that can create roles and databases but is not a superuser. We reproduced that on PostgreSQL 16 with a role created LOGIN CREATEDB CREATEROLE, and ran the unedited globals.sql as it. Every ALTER ROLE … WITH line failed, because pg_dumpall spells out NOSUPERUSER even for ordinary roles:

psql:globals.sql:17: ERROR:  permission denied to alter role
DETAIL:  Only roles with the SUPERUSER attribute may change the SUPERUSER attribute.

So the roles were created with no LOGIN and no password, and the memberships failed on GRANTED BY postgres. This edit removes the superuser-only attributes, the GRANTED BY clauses and the postgres role:

sed -E -e 's/ (NO)?SUPERUSER//; s/ (NO)?REPLICATION//; s/ (NO)?BYPASSRLS//' -e 's/ GRANTED BY [^;]*;/;/' -e '/^(CREATE|ALTER) ROLE postgres[ ;]/d' globals.sql > globals-managed.sql

psql -h db.example.com -U admin -d postgres -X -f globals-managed.sql

Roles, passwords and memberships then loaded. Three errors were left, for things the admin may not do: granting pg_monitor, GRANT SET ON PARAMETER, and CREATE TABLESPACE. Ask the provider for the equivalents, or drop those lines. The edit also turns any superuser into an ordinary login role. Where you are not a superuser on the source either, pg_dumpall --globals-only fails with permission denied for table pg_authid; add --no-role-passwords and set the passwords again.

Restoring the database as the admin failed five times with must be able to SET ROLE "app_owner". Pick one of these:

• Keep the owners: run GRANT app_owner TO admin; as the admin first (it may, having created the role). Our restore then ran with no errors and kept every owner, grant and default privilege.
• --no-owner (-O): the admin owns everything. Grants still restore, but ALTER DEFAULT PRIVILEGES FOR ROLE app_owner failed.
• --no-owner --no-privileges (-O -x): no ownership or GRANT commands at all. Use it when the roles do not exist on the target, then grant access yourself.

In each case the admin first creates the empty database, then restores into it. The last option looks like this:

createdb -h db.example.com -U admin shop

pg_restore -h db.example.com -U admin --no-owner --no-privileges -d shop shop.dump

Back up roles every night

Roles change less often than data, but a role added last week and missing from the backup still breaks a restore. Dump the globals in the same run as the databases:

#!/usr/bin/env bash
# Nightly: roles and tablespaces, then every database, then prune.
set -euo pipefail
umask 077

DIR="/var/backups/postgresql"
KEEP_DAYS=7
STAMP=$(date +%Y-%m-%d_%H%M)

pg_dumpall --globals-only -f "$DIR/globals-$STAMP.sql"

psql -X -Atc "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate" postgres |
while read -r db; do
  pg_dump -Fc -f "$DIR/$db-$STAMP.dump" "$db"
done

find "$DIR" -type f \( -name '*.sql' -o -name '*.dump' \) -mtime +"$KEEP_DAYS" -delete

30 2 * * * postgres /usr/local/bin/pg-backup-all >> /var/backups/postgresql/backup.log 2>&1

set -o pipefail makes a failed pg_dump inside the loop fail the whole run. Every file came out -rw------- because of umask 077. To check a globals file, compare its role count with the server's. Ours matched, 9 and 9:

sudo -u postgres grep -c '^CREATE ROLE' /var/backups/postgresql/globals-2026-10-03_2027.sql

sudo -u postgres psql -X -Atc "SELECT count(*) FROM pg_roles WHERE rolname !~ '^pg_'"

The real proof is loading the globals and a dump into a scratch server, as above. See testing restores for a routine.

Common errors

Frequently asked questions

Does pg_dump back up users and roles?

No. It saves the GRANTs and ownership inside one database, which name roles, but not the roles themselves. Use pg_dumpall --globals-only for those.

Can I move PostgreSQL users with their passwords to a new server?

Yes. The dump carries the password hashes, and our restored user logged in with the same password on the new server. Keep the file private.

How do I back up just one PostgreSQL user?

pg_dumpall has no option for a single role. Dump --roles-only and copy that role's CREATE ROLE, ALTER ROLE and GRANT lines.

Does a pg_basebackup include roles?

Yes. A physical backup copies the whole cluster, roles included. It only restores to the same major version, though.