Restore database with changed privileges

Question

We have two PostgreSQL servers. On web server1 pguser1 is the associated user who creates all database objects required to work with the web application. Let's say, all objects are in schema1.
Similarly, on server2 pguser2 creates data for Web server 2.

I have taken a schema backup (custom option in the backup window from pgadmin) from server1. When I restore to server2 I want all privileges and ownership of objects to be with pguser2 and nothing with pguser1.

How can this be achieved? If possible, an option during during restore (pg_restore command) would be preferred.

score 1 · Answer 1 · edited May 23 '17 at 12:20

It would very much simplify your task if you GRANT privileges and ownership to a non-login role (a.k.a. group). Let's name it foo_admin. On server1 you

GRANT foo_admin TO pguser1;

On server2 you

REVOKE foo_admin FROM pguser1;
GRANT foo_admin TO pguser2;

All done. Or better yet: hack the dump file and replace 1 with 2 in the line

GRANT foo_admin TO pguser1;

.. before you restore. pguser2 has to be created first, of course.

Now, just make sure, when you create anything on server1 to

ALTER TABLE wuchtel12.game OWNER TO foo_admin;

And set privileges. You can preset privileges per schema. Something like:

ALTER DEFAULT PRIVILEGES IN SCHEMA schema1
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
TO foo_admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA schema1
GRANT SELECT, UPDATE, USAGE ON SEQUENCES
TO foo_admin;

This works identically on server1 and server2.
More details and links in this related answer.

You might also be interested in the Grant Wizard of pgAdmin. Details in this related answer

Restore database with changed privileges

1 Answers1