Re: BUG #14009: pg_restore not restoring privilegs when one login/group role is missing
От | Alexander Spiteri |
---|---|
Тема | Re: BUG #14009: pg_restore not restoring privilegs when one login/group role is missing |
Дата | |
Msg-id | CAKmUXWvTOfuWFHx89b9JX0YHAiNMXkwyUg3dKLCwKnHCw6=wcA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: BUG #14009: pg_restore not restoring privilegs when one login/group role is missing (Alexander Spiteri <alexander@spiteri.org>) |
Список | pgsql-bugs |
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 >> > >
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Alexander SpiteriДата:
Сообщение: Re: BUG #14009: pg_restore not restoring privilegs when one login/group role is missing
Следующее
От: nummervet@mail.ruДата:
Сообщение: BUG #14019: Security label TAB causes unexpected EOF and client session termination