Re: pg_restore restores privileges differently from psql

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