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@aklaver.com