On 1/14/19 12:57 PM, Sherrylyn Branchaw wrote:
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
True. The thing is that there are a lot of moving parts to this and the
more detail you give the better the chance that someone trying to
replicate actually does replicate your setup. Removes some of the
uncertainty when the results diverge.
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).'
Well if I use two clusters and:
DROP ROLE prod_user;
on the one that has the dev_* databases then I can replicate.
I am interested in this as I have noted another divergence between a
text dump and a binary dump, which as yet remains unanswered:
https://www.postgresql.org/message-id/b7a24043-1c9c-1876-f06a-8f916293c142%40aklaver.com
Best,
Sherrylyn
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
