On 1/14/19 12:04 PM, Sherrylyn Branchaw wrote:
The above needs more information:
1) Are the dev_* databases on a different cluster?
2) If so did you run:
CREATE ROLE prod_user;
CREATE ROLE prod_and_dev_user;
on that cluster first?
I happened to put them all on the same cluster for my test case, in
order to reproduce the unexpected behavior I encountered in the wild,
where the prod and dev dbs happened to live on different clusters. In
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
short, as long as you make sure the /prod_user/ exists on
the source
cluster at the time when the dump is taken, and doesn't exist on the
target cluster when the restore is carried out, you get the behavior I saw.
Also if so:
In the restores below are you sure you are pointed at the same cluster
in each case?
Yes, I am sure. Both for the test case I was creating for the mailing
list, and for the script where I first encountered this in the wild.
Worked like a charm when I used /psql/, didn't do what I expected when I
used /pg_restore/.
What do you see if you do:
pg_restore -f prod_dump_restore.sql prod_dump.bin
and look in prod_dump_res?tore.sql?
This is exactly what I did when I was first trying to figure out what
was going on. I see
GRANT USAGE ON SCHEMA test TO prod_and_dev_user;
GRANT ALL ON SCHEMA test TO prod_user;
If I then use /psql/ to load /prod_dump_restore.sql/ to a cluster that
doesn't have the /prod_user /role, I get the expected behavior
(/prod_and_dev_user/ has usage on the schema /test/), because /psql/
treats each of those statements as a separate command. /pg_restore/
seems to treat them as a single command, judging by the error message
and the behavior.
Best,
Sherrylyn
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
