PostgreSQL – Export Roles with Privileges in PostgreSQL 9.3

backuppostgresqlpostgresql-9.3

New to backups under PostgreSQL. I've been backing up my database with the general commands, and it works well. Except no users or privileges are stored with it. I understand that I need to do that separately.

I've been using the following command to dump the users on the cluster:

pg_dumpall -U backups -r > /tmp/globals.only.dump

Either the -r or the -g return the same thing. What I end up with in the file is just:

CREATE ROLE backups;
ALTER ROLE backups WITH SUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD '0000000000000000000000000000000000';

It seems that all the privileges are missing, but there are 15 of them that I had setup through NaviCat.

Recreating all the privileges by hand is obviously not viable for production. How do you force the privileges to be exported with the role? Subsequently, how might I import them into another PostgreSQL server?

Best Answer

Documentation says: "Database roles are global across a database cluster installation (and not per individual database)." Hovewer, privileges are object based, therefore their definition is stored in databases separately. If you backup your database with a command like; "pg_dump .. --schema-only" you will see the privileges in there.

Related Question