Re: pg_restore restores privileges differently from psql

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(dot)klaver(at)aklaver(dot)com