Re: pg_restore: All GRANTs on table fail when any one role is missing

On Thu, Jul 12, 2018 at 6:07 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Moshe Jacobson <moshe(at)neadwerx(dot)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(at)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(at)athena ~]$ pg_dump mzj_test -Fc -f /tmp/mzj_test.dump*
*(0)(0j)[jehsom(at)athena ~]$ dropdb mzj_test; dropuser user1;*
*(0)(0j)[jehsom(at)athena ~]$ createdb mzj_test;*
*(0)(0j)[jehsom(at)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(at)athena ~]$ psql -c '\dp' mzj_test*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------+-------+-------------------+-------------------+----------
public | table1 | table | | |
(1 row)

*(0)(0j)[jehsom(at)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(at)athena ~]$*
--

[image: photo]
*Moshe Jacobson*
Principal Architect, Nead Werx, Inc.

www.neadwerx.com
2323 Cumberland Pkwy SE #201, Atlanta GA 30339
<http://linkedin.com/in/jehsom> <http://facebook.com/NeadWerx/>
<http://goo.gl/maps/7SL9gn5fBi62>