Re: pg_restore restores privileges differently from psql - Mailing list pgsql-general

On 1/14/19 10:15 AM, Sherrylyn Branchaw wrote:
> Hi,
> 
> I'm running two Postgres 9.6.11 databases on RHEL 6.9.
> 
> I'm restoring the schema from one database (prod) to another (dev). 
> There are users that exist in prod that don't exist in dev. When the 
> restore job tries to grant privileges to nonexistent users in dev, I 
> would like it to generate an error, which is safe to ignore, but still 
> correctly grant privileges to any user that does exist in dev.
> 
> That's the behavior I see when I dump to a plain file and restore it 
> using /psql -f/, but not the behavior I see when I do a /pg_dump 
> -Fc/ followed by /pg_restore./ /pg_restore /seems to treat all the 
> /GRANT/ statements for a single object as a single statement, and when 
> one errors out, they all error out, meaning I'm left with no privileges 
> on the object in question.
> 
> For instance, when this appears in my plaintext file:
> GRANT ALL ON SCHEMA test TO user1;
> GRANT USAGE ON SCHEMA test TO user2;
> GRANT USAGE ON SCHEMA test TO user3;
> 
> and user1 doesn't exist on the target database, user2 and user3 get the 
> expected privileges when restoring from /psql/ but not from /pg_restore/.
> 
> Here's a reproducible test case.
> 
> CREATE DATABASE prod_db;
> CREATE DATABASE dev_db_pg_restore;
> CREATE DATABASE dev_db_psql;
> CREATE ROLE prod_user;
> CREATE ROLE prod_and_dev_user;
> 
> -- in prod_db
> CREATE SCHEMA test;
> GRANT ALL ON SCHEMA test TO prod_user;
> GRANT USAGE ON SCHEMA test TO prod_and_dev_user;
> 
> pg_dump -Fc prod_db &> prod_dump.bin
> pg_dump prod_db &> prod_dump.sql
> 
> -- On database instance containing the dev dbs.
> DROP ROLE prod_user;

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?

Also if so:

In the restores below are you sure you are pointed at the same cluster 
in each case?

> 
> pg_restore prod_dump.bin -d dev_db_pg_restore
> psql -d dev_db_psql -f prod_dump.sqlu 

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?

> 
> -- In dev_db_psql
> SELECT has_schema_privilege('prod_and_dev_user', 'test', 'usage');
> Expected result: true
> Actual result: true
> 
> -- In dev_db_pg_restore
> SELECT has_schema_privilege('prod_and_dev_user', 'test', 'usage');
> Expected result: true
> Actual result: false
> 
> The behavior seems to be related to the fact that /pg_restore/ reports 
> the failed command as containing all the semicolon-delimited privilege 
> command, which get executed separately when restoring from plaintext:
> 
> pg_restore: [archiver (db)] could not execute query: ERROR:  role 
> "prod_user" does not exist
>      Command was: GRANT ALL ON SCHEMA test TO prod_user;
> GRANT USAGE ON SCHEMA test TO prod_and_dev_user;
> 
> As a workaround, I've created a unprivileged dummy user by this name on 
> the dev database, but my question is, is this a bug or feature? If a 
> feature, is the behavior documented? I didn't find any documentation, 
> but that doesn't mean it doesn't exist.
> 
> Thanks,
> Sherrylyn


-- 
Adrian Klaver
adrian.klaver@aklaver.com