Re: pg_restore restores privileges differently from psql

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