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 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
