Re: pg_restore restores privileges differently from psql

[Sherrylyn Branchaw <sbranchaw@xxxxxxxxx>]

I don't see how that can work:

test=# \c prod_db
You are now connected to database "prod_db" as user "postgres".
prod_db=# CREATE SCHEMA test;
CREATE SCHEMA
prod_db=# GRANT ALL ON SCHEMA test TO prod_user; 


GRANT 


prod_db=# GRANT USAGE ON SCHEMA test TO prod_and_dev_user; 


GRANT

prod_db=# \c dev_db_psql
You are now connected to database "dev_db_psql" as user "postgres". 


dev_db_psql=# DROP ROLE prod_user;
ERROR:  role "prod_user" cannot be dropped because some objects depend 
on it 

DETAIL:  1 object in database prod_db

Yes, if you're going to put all your databases on the same cluster, you first have to remove dependent objects before dropping the role. There are multiple ways of going about that: dropping the database, revoking the privileges on the objects in question, etc. If you put the databases on different clusters and make sure you create only the prod_and_dev_user on the second cluster, you won't run into this issue.

The goal is to make sure the prod_user role exists when the dump is taken and doesn't exist when the restore is done. You can do this by putting the databases on separate clusters and creating the appropriate roles, or by dropping the user on the single cluster.

Sorry, I considered spelling all this out in the original post, because there are two different ways of going about making sure the user isn't present for the restore, but it seemed unnecessarily complicated, and I thought I would let people decide what makes sense in their own environment for testing. Sorry if that led to more confusion in the end. Just make sure the user exists when you need it to exist and doesn't exist when you need it not to exist, and test both the pg_restore and psql methods, and I expect you'll see the same behavior I did (and if not, I'll be very curious).

Best,

Sherrylyn