HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Export roles *with* privileges under PostgreSQL 9.3

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlroleswithprivilegesunderexport

Problem

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?

Solution

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.

Context

StackExchange Database Administrators Q#132610, answer score: 5

Revisions (0)

No revisions yet.