Re: pg_restore restores privileges differently from psql

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