Обсуждение: BUG #14009: pg_restore not restoring privilegs when one login/group role is missing

The following bug has been logged on the website:

Bug reference:      14009
Logged by:          Alexander Spiteri
Email address:      alexander@spiteri.org
PostgreSQL version: 9.5.1
Operating system:   CentOS Linux release 7.2.1511 (Core)
Description:

I have just installed a new instance of postgres 9.5.1 and have an issue
with restoring the database, specifically privilges of the login/group
roles.

In the past I was working with postgres 8.4 and when I took a backup from
the live server to the testing server pg_restore would grant all privilages
to login/group roles which existed on the testing server and give an error
for those that do not exist.

On the new 9.5.1 server when I try to restore a database, if one login/group
role does not exist pg_restore gives an error about it and then does not
grant any rights to all the other login/group roles that do exist. No errors
/ notices are issued about the grants that are not applied.

This behaviour is puzzling. I expected to get errors for users that are not
found but why aren't privilges assigned to users that do exist?

I reproduced this behaviour on different installations running version 9.5.1
and 9.3.11.

alexander@spiteri.org writes:
> I have just installed a new instance of postgres 9.5.1 and have an issue
> with restoring the database, specifically privilges of the login/group
> roles.

> In the past I was working with postgres 8.4 and when I took a backup from
> the live server to the testing server pg_restore would grant all privilages
> to login/group roles which existed on the testing server and give an error
> for those that do not exist.

> On the new 9.5.1 server when I try to restore a database, if one login/group
> role does not exist pg_restore gives an error about it and then does not
> grant any rights to all the other login/group roles that do exist. No errors
> / notices are issued about the grants that are not applied.

That's fairly hard to believe.  Can you provide a self-contained test
case?

            regards, tom lane

Alexander Spiteri <alexander@spiteri.org> writes:
> Please find a test case attached. I tried to put as much detail as i could.

Okay, I see what's going on.  I really doubt that there is a regression
here from 8.4 though.  I will bet that the difference is you used plain
text output from pg_dump back in 8.4, and now in 9.5 you are trying to
use pg_restore direct-to-database mode.

The proximate cause of the problem is that pg_dump bundles up GRANT/REVOKE
statements for any object into a single "ACL" entry in the archive file.
If you dump to SQL text this doesn't matter because it just looks like a
stream of separate statements.  But if pg_restore is trying to restore
direct to database then it issues the whole text of each archive file
entry as a single PQexec() operation, which means that an error in one
statement in that string causes the rest of 'em to be skipped.

As a short-term workaround you could try restoring in this way:

   pg_restore foo.dump | psql

To really fix it, I think we'd need either to expand "ACL" archive entries
into a separate entry for each target user, or to change pg_restore to
parse the entry contents into separate statements.  The latter seems
messy and bug-prone.  The former solution wouldn't fix the problem
for dumps from existing pg_dump versions; but on the other hand, it might
provide additional flexibility for selective restores, so I'm not sure
that it's a bad idea going forward.

            regards, tom lane

I tried the command as you suggested but still had the same issue.

[postgres@server4 ~]$ pg_restore -p 5432 -d stgsample04
/var/lib/pgsql/data/dumps/stgsample01_schema.dump | psql

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3067; 0 0 ACL country
stgsamplelogin01
pg_restore: [archiver (db)] could not execute query: ERROR:  role
"sample_read_role" does not exist
    Command was: REVOKE ALL ON TABLE country FROM PUBLIC;
REVOKE ALL ON TABLE country FROM stgsamplelogin01;
GRANT ALL ON TABLE country TO st...
pg_restore: [archiver (db)] Error from TOC entry 3068; 0 0 ACL
country_alias stgsamplelogin01
pg_restore: [archiver (db)] could not execute query: ERROR:  role
"sample_read_role" does not exist
    Command was: REVOKE ALL ON TABLE country_alias FROM PUBLIC;
REVOKE ALL ON TABLE country_alias FROM stgsamplelogin01;
GRANT ALL ON TABLE c...
pg_restore: [archiver (db)] Error from TOC entry 3069; 0 0 ACL log
stgsamplelogin01
pg_restore: [archiver (db)] could not execute query: ERROR:  role
"sample_read_role" does not exist
    Command was: REVOKE ALL ON TABLE log FROM PUBLIC;
REVOKE ALL ON TABLE log FROM stgsamplelogin01;
GRANT ALL ON TABLE log TO stgsamplelogin...
pg_restore: [archiver (db)] Error from TOC entry 3070; 0 0 ACL result
stgsamplelogin01
pg_restore: [archiver (db)] could not execute query: ERROR:  role
"sample_read_role" does not exist
    Command was: REVOKE ALL ON TABLE result FROM PUBLIC;
REVOKE ALL ON TABLE result FROM stgsamplelogin01;
GRANT ALL ON TABLE result TO stgsa...
pg_restore: [archiver (db)] Error from TOC entry 3071; 0 0 ACL tariff
stgsamplelogin01
pg_restore: [archiver (db)] could not execute query: ERROR:  role
"sample_read_role" does not exist
    Command was: REVOKE ALL ON TABLE tariff FROM PUBLIC;
REVOKE ALL ON TABLE tariff FROM stgsamplelogin01;
GRANT ALL ON TABLE tariff TO stgsa...
WARNING: errors ignored on restore: 5

[postgres@server4 ~]$ psql
psql (9.5.1)
Type "help" for help.

postgres=# \connect stgsample04 stgsamplelogin01
You are now connected to database "stgsample04" as user "stgsamplelogin01".
stgsample04=> \dp
                                   Access privileges
 Schema |       Name        | Type  | Access privileges | Column privileges
| Policies
--------+-------------------+-------+-------------------+-------------------+----------
 public | country           | table |                   |
|
 public | country_alias     | table |                   |
|
 public | log               | table |                   |
|
 public | result            | table |                   |
|
 public | tariff            | table |                   |
|
 public | tariff_bk20140630 | table |                   |
|
(6 rows)


Regards,
Alexander Spiteri

On 10 March 2016 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alexander Spiteri <alexander@spiteri.org> writes:
> > Please find a test case attached. I tried to put as much detail as i
> could.
>
> Okay, I see what's going on.  I really doubt that there is a regression
> here from 8.4 though.  I will bet that the difference is you used plain
> text output from pg_dump back in 8.4, and now in 9.5 you are trying to
> use pg_restore direct-to-database mode.
>
> The proximate cause of the problem is that pg_dump bundles up GRANT/REVOKE
> statements for any object into a single "ACL" entry in the archive file.
> If you dump to SQL text this doesn't matter because it just looks like a
> stream of separate statements.  But if pg_restore is trying to restore
> direct to database then it issues the whole text of each archive file
> entry as a single PQexec() operation, which means that an error in one
> statement in that string causes the rest of 'em to be skipped.
>
> As a short-term workaround you could try restoring in this way:
>
>    pg_restore foo.dump | psql
>
> To really fix it, I think we'd need either to expand "ACL" archive entries
> into a separate entry for each target user, or to change pg_restore to
> parse the entry contents into separate statements.  The latter seems
> messy and bug-prone.  The former solution wouldn't fix the problem
> for dumps from existing pg_dump versions; but on the other hand, it might
> provide additional flexibility for selective restores, so I'm not sure
> that it's a bad idea going forward.
>
>                         regards, tom lane
>

Always used pg_dump and pg_restore when using v8.3.7, v8.3.23 and v8.4.5.

I never had this issue on 8.4.5 however I have a particular server running
v8.3.23 which has this exact issue. Could this behaviour be the result of
patch applied between 8.3.7 and 8.3.23 ?

Regards,
Alexander Spiteri

On 14 March 2016 at 09:18, Alexander Spiteri <alexander@spiteri.org> wrote:

> I tried the command as you suggested but still had the same issue.
>
> [postgres@server4 ~]$ pg_restore -p 5432 -d stgsample04
> /var/lib/pgsql/data/dumps/stgsample01_schema.dump | psql
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 3067; 0 0 ACL country
> stgsamplelogin01
> pg_restore: [archiver (db)] could not execute query: ERROR:  role
> "sample_read_role" does not exist
>     Command was: REVOKE ALL ON TABLE country FROM PUBLIC;
> REVOKE ALL ON TABLE country FROM stgsamplelogin01;
> GRANT ALL ON TABLE country TO st...
> pg_restore: [archiver (db)] Error from TOC entry 3068; 0 0 ACL
> country_alias stgsamplelogin01
> pg_restore: [archiver (db)] could not execute query: ERROR:  role
> "sample_read_role" does not exist
>     Command was: REVOKE ALL ON TABLE country_alias FROM PUBLIC;
> REVOKE ALL ON TABLE country_alias FROM stgsamplelogin01;
> GRANT ALL ON TABLE c...
> pg_restore: [archiver (db)] Error from TOC entry 3069; 0 0 ACL log
> stgsamplelogin01
> pg_restore: [archiver (db)] could not execute query: ERROR:  role
> "sample_read_role" does not exist
>     Command was: REVOKE ALL ON TABLE log FROM PUBLIC;
> REVOKE ALL ON TABLE log FROM stgsamplelogin01;
> GRANT ALL ON TABLE log TO stgsamplelogin...
> pg_restore: [archiver (db)] Error from TOC entry 3070; 0 0 ACL result
> stgsamplelogin01
> pg_restore: [archiver (db)] could not execute query: ERROR:  role
> "sample_read_role" does not exist
>     Command was: REVOKE ALL ON TABLE result FROM PUBLIC;
> REVOKE ALL ON TABLE result FROM stgsamplelogin01;
> GRANT ALL ON TABLE result TO stgsa...
> pg_restore: [archiver (db)] Error from TOC entry 3071; 0 0 ACL tariff
> stgsamplelogin01
> pg_restore: [archiver (db)] could not execute query: ERROR:  role
> "sample_read_role" does not exist
>     Command was: REVOKE ALL ON TABLE tariff FROM PUBLIC;
> REVOKE ALL ON TABLE tariff FROM stgsamplelogin01;
> GRANT ALL ON TABLE tariff TO stgsa...
> WARNING: errors ignored on restore: 5
>
> [postgres@server4 ~]$ psql
> psql (9.5.1)
> Type "help" for help.
>
> postgres=# \connect stgsample04 stgsamplelogin01
> You are now connected to database "stgsample04" as user "stgsamplelogin01".
> stgsample04=> \dp
>                                    Access privileges
>  Schema |       Name        | Type  | Access privileges | Column
> privileges | Policies
>
> --------+-------------------+-------+-------------------+-------------------+----------
>  public | country           | table |
> |                   |
>  public | country_alias     | table |
> |                   |
>  public | log               | table |
> |                   |
>  public | result            | table |
> |                   |
>  public | tariff            | table |
> |                   |
>  public | tariff_bk20140630 | table |
> |                   |
> (6 rows)
>
>
> Regards,
> Alexander Spiteri
>
> On 10 March 2016 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Alexander Spiteri <alexander@spiteri.org> writes:
>> > Please find a test case attached. I tried to put as much detail as i
>> could.
>>
>> Okay, I see what's going on.  I really doubt that there is a regression
>> here from 8.4 though.  I will bet that the difference is you used plain
>> text output from pg_dump back in 8.4, and now in 9.5 you are trying to
>> use pg_restore direct-to-database mode.
>>
>> The proximate cause of the problem is that pg_dump bundles up GRANT/REVOKE
>> statements for any object into a single "ACL" entry in the archive file.
>> If you dump to SQL text this doesn't matter because it just looks like a
>> stream of separate statements.  But if pg_restore is trying to restore
>> direct to database then it issues the whole text of each archive file
>> entry as a single PQexec() operation, which means that an error in one
>> statement in that string causes the rest of 'em to be skipped.
>>
>> As a short-term workaround you could try restoring in this way:
>>
>>    pg_restore foo.dump | psql
>>
>> To really fix it, I think we'd need either to expand "ACL" archive entries
>> into a separate entry for each target user, or to change pg_restore to
>> parse the entry contents into separate statements.  The latter seems
>> messy and bug-prone.  The former solution wouldn't fix the problem
>> for dumps from existing pg_dump versions; but on the other hand, it might
>> provide additional flexibility for selective restores, so I'm not sure
>> that it's a bad idea going forward.
>>
>>                         regards, tom lane
>>
>
>

Alexander Spiteri <alexander@spiteri.org> writes:
> I tried the command as you suggested but still had the same issue.
> [postgres@server4 ~]$ pg_restore -p 5432 -d stgsample04
> /var/lib/pgsql/data/dumps/stgsample01_schema.dump | psql

Uh, no, you need to drop the -d switch from the pg_restore call ...
(and the -p switch is useless as well)

            regards, tom lane

I need the -d to specify the target database, without it restore is made to
the current user which in my case was "postgres".

moving -d to psql worked fine :

pg_restore /var/lib/pgsql/data/dumps/stgsample01_schema.dump | psql -d
stgsample05



---- On SERVER_5 ----

-- transfer dump from server 1

-- sample_read_role not created on purpose

CREATE ROLE sample_write_role
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE ROLE stgsamplelogin01 LOGIN
  ENCRYPTED PASSWORD 'md5ea60c5e12ef0bd0e1344eb53e0078a67'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT sample_write_role TO stgsamplelogin01;

CREATE DATABASE stgsample05
  WITH OWNER = stgsamplelogin01
       TEMPLATE=TEMPLATE0
       ENCODING = 'SQL_ASCII'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = 20;

[postgres@server5 ~]$ pg_restore
/var/lib/pgsql/data/dumps/stgsample01_schema.dump | psql -d stgsample05

SET
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
ERROR:  role "sample_read_role" does not exist
GRANT
REVOKE
REVOKE
GRANT
ERROR:  role "sample_read_role" does not exist
GRANT
REVOKE
REVOKE
GRANT
ERROR:  role "sample_read_role" does not exist
GRANT
REVOKE
REVOKE
GRANT
ERROR:  role "sample_read_role" does not exist
GRANT
REVOKE
REVOKE
GRANT
ERROR:  role "sample_read_role" does not exist
GRANT

[postgres@server5 ~]$ psql
psql (9.5.1)
Type "help" for help.

postgres=# \connect stgsample05 stgsamplelogin01
You are now connected to database "stgsample05" as user "stgsamplelogin01".
stgsample05=> \dp
                                               Access privileges
 Schema |       Name        | Type  |             Access
privileges              | Column privileges | Policies
--------+-------------------+-------+--------------------------------------------+-------------------+----------
 public | country           | table |
stgsamplelogin01=arwdDxt/stgsamplelogin01 +|                   |
        |                   |       |
sample_write_role=arwdDxt/stgsamplelogin01 |                   |
 public | country_alias     | table |
stgsamplelogin01=arwdDxt/stgsamplelogin01 +|                   |
        |                   |       |
sample_write_role=arwdDxt/stgsamplelogin01 |                   |
 public | log               | table |
stgsamplelogin01=arwdDxt/stgsamplelogin01 +|                   |
        |                   |       |
sample_write_role=arwdDxt/stgsamplelogin01 |                   |
 public | result            | table |
stgsamplelogin01=arwdDxt/stgsamplelogin01 +|                   |
        |                   |       |
sample_write_role=arwdDxt/stgsamplelogin01 |                   |
 public | tariff            | table |
stgsamplelogin01=arwdDxt/stgsamplelogin01 +|                   |
        |                   |       |
sample_write_role=arwdDxt/stgsamplelogin01 |                   |
 public | tariff_bk20140630 | table
|                                            |                   |
(6 rows)

Regards,
Alexander Spiteri

On 15 March 2016 at 03:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alexander Spiteri <alexander@spiteri.org> writes:
> > I tried the command as you suggested but still had the same issue.
> > [postgres@server4 ~]$ pg_restore -p 5432 -d stgsample04
> > /var/lib/pgsql/data/dumps/stgsample01_schema.dump | psql
>
> Uh, no, you need to drop the -d switch from the pg_restore call ...
> (and the -p switch is useless as well)
>
>                         regards, tom lane
>