pg_restore restores privileges differently from psql

From: Sherrylyn Branchaw <sbranchaw(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_restore restores privileges differently from psql
Date: 2019-01-14 18:15:22
Message-ID: CAB_myF5_+Ok=VRkHPyxJ_TfgrG8AHdNjQs2ke5B+hKvTCzmmPQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

pg_restore prod_dump.bin -d dev_db_pg_restore
psql -d dev_db_psql -f prod_dump.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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michel Pelletier 2019-01-14 18:56:10 Re: Question about MemoryContextRegisterResetCallback
Previous Message Andreas Joseph Krogh 2019-01-14 15:23:57 Sv: Re: sha512sum (program) gives different result than sha512 in PG11