Re: BUG #14009: pg_restore not restoring privilegs when one login/group role is missing

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(at)spiteri(dot)org> wrote:

> I tried the command as you suggested but still had the same issue.
>
> [postgres(at)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(at)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Alexander Spiteri <alexander(at)spiteri(dot)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
>>
>
>