Re: pg_restore: All GRANTs on table fail when any one role is missing - Mailing list pgsql-bugs
On Thu, Jul 12, 2018 at 6:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Moshe Jacobson <moshe@neadwerx.com> writes:
> Examining pg_restore sql output shows two separate grant commands, however
> the problem persists even if I pipe pg_restore output into psql. Very
> strange.
Hm. I can understand why this would happen if you do pg_restore direct to
the server: the GRANTs will all be part of the same "TOC entry" in the
dump file, and I'm pretty sure pg_restore issues the entire contents of
a TOC entry in one PQexec, so that it's effectively a single transaction.
However, if you tell pg_restore to emit text and then pass the text to
psql, each SQL command should be processed separately. Are you sure about
the failure still occurring in that case?
Hi Tom,
Yes, I'm sure. Here is a reproduction of the problem exclusively on 10.4. An important note is that if there are valid permissions on the table before the invalid one, I think those will be restored successfully. Only those after the invalid permission are not restored.
(0)(0j)[jehsom@athena ~]$ psql -c '\dp' mzj_test
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------+-------+-------------------+-------------------+----------
public | table1 | table | user1=a/postgres +| |
| | | =a/postgres | |
(1 row)
(0)(0j)[jehsom@athena ~]$ pg_dump mzj_test -Fc -f /tmp/mzj_test.dump
(0)(0j)[jehsom@athena ~]$ dropdb mzj_test; dropuser user1;
(0)(0j)[jehsom@athena ~]$ createdb mzj_test;
(0)(0j)[jehsom@athena ~]$ pg_restore /tmp/mzj_test.dump | psql mzj_test
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
COPY 0
REVOKE
ERROR: role "user1" does not exist
Time: 0.134 ms
(3)(0j)[jehsom@athena ~]$ psql -c '\dp' mzj_test
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------+-------+-------------------+-------------------+----------
public | table1 | table | | |
(1 row)
(0)(0j)[jehsom@athena ~]$ pg_restore /tmp/mzj_test.dump | grep 'GRANT|REVOKE'
REVOKE ALL ON TABLE public.table1 FROM postgres;
GRANT INSERT ON TABLE public.table1 TO user1;
GRANT INSERT ON TABLE public.table1 TO PUBLIC;
(0)(0j)[jehsom@athena ~]$
|
pgsql-bugs by date: