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