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

Moshe Jacobson <moshe@neadwerx.com> writes:

I take a backup using pg_dump -Fc, and then restore on the second cluster
(where "user1" does not exist).
The restore process says it ignored the error for the missing user, but the
restored table now has no perms at all. Not even for user public.
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?

regards, tom lane

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 ~]$*
--

[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&gt; <http://facebook.com/NeadWerx/&gt;
<http://goo.gl/maps/7SL9gn5fBi62&gt;

On Tue, Jul 17, 2018 at 12:02 PM Moshe Jacobson <moshe@neadwerx.com> wrote:

Here is a reproduction of the problem exclusively on 10.4.

Can anyone validate this bug?

--

[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&gt; <http://facebook.com/NeadWerx/&gt;
<http://goo.gl/maps/7SL9gn5fBi62&gt;

On Tue, Jul 24, 2018 at 5:14 AM Moshe Jacobson <moshe@neadwerx.com> wrote:

On Tue, Jul 17, 2018 at 12:02 PM Moshe Jacobson <moshe@neadwerx.com>
wrote:

Here is a reproduction of the problem exclusively on 10.4.

Can anyone validate this bug?

I am not able to reproduce the issue.

REVOKE
ERROR: role "user1" does not exist
Time: 0.134 ms

The process of grant doesn't execute, because of this reason, there are no
permissions
on that table. In my test, after failure of user1, the next grant is
successful.

Try generating the sql file with pg_restore output and pass it to psql and
check.

Regards,
Haribabu Kommi
Fujitsu Australia

Haribabu Kommi <kommi.haribabu@gmail.com> writes:

On Tue, Jul 24, 2018 at 5:14 AM Moshe Jacobson <moshe@neadwerx.com> wrote:

On Tue, Jul 17, 2018 at 12:02 PM Moshe Jacobson <moshe@neadwerx.com>
wrote:

Here is a reproduction of the problem exclusively on 10.4.

Can anyone validate this bug?

I am not able to reproduce the issue.

I got around to trying this today, and I can't reproduce it either.
I get the expected behavior when running pg_restore's output through
psql --- all three commands execute, with the middle one failing:

...
REVOKE
ERROR: role "user1" does not exist
GRANT

mzj_test=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------+-------+-------------------+-------------------+----------
public | table1 | table | =a/postgres | |
(1 row)

while if I try to run the pg_restore direct to database, all three
commands fail because they're implicitly grouped into one transaction:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3058; 0 0 ACL TABLE table1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: role "user1" does not exist
Command was: REVOKE ALL ON TABLE public.table1 FROM postgres;
GRANT INSERT ON TABLE public.table1 TO user1;
GRANT INSERT ON TABLE public.table1 TO PUBLIC;

WARNING: errors ignored on restore: 1

mzj_test=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------+-------+-------------------+-------------------+----------
public | table1 | table | | |
(1 row)

The latter behavior is not great, probably, but it's acting as designed.

As for Moshe's different results when going through psql, the only
theory that comes to mind is that he's got it configured with
ON_ERROR_STOP turned on, probably in ~/.psqlrc.

regards, tom lane

Thanks Tom & Haribabu,

I did indeed have ON_ERROR_STOP in my .psqlrc and that was why the piping
to psql was producing the results shown.

I understand why the pg_restore method is not working, as the GRANTs are
grouped into a single transaction. I'm just not sure I understand WHY they
are grouped into one transaction.

Thanks.

On Sat, Jul 28, 2018 at 1:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Haribabu Kommi <kommi.haribabu@gmail.com> writes:

On Tue, Jul 24, 2018 at 5:14 AM Moshe Jacobson <moshe@neadwerx.com>

wrote:

On Tue, Jul 17, 2018 at 12:02 PM Moshe Jacobson <moshe@neadwerx.com>
wrote:

Here is a reproduction of the problem exclusively on 10.4.

Can anyone validate this bug?

I am not able to reproduce the issue.

I got around to trying this today, and I can't reproduce it either.
I get the expected behavior when running pg_restore's output through
psql --- all three commands execute, with the middle one failing:

...
REVOKE
ERROR: role "user1" does not exist
GRANT

mzj_test=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies

--------+--------+-------+-------------------+-------------------+----------
public | table1 | table | =a/postgres | |
(1 row)

while if I try to run the pg_restore direct to database, all three
commands fail because they're implicitly grouped into one transaction:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3058; 0 0 ACL TABLE
table1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: role "user1"
does not exist
Command was: REVOKE ALL ON TABLE public.table1 FROM postgres;
GRANT INSERT ON TABLE public.table1 TO user1;
GRANT INSERT ON TABLE public.table1 TO PUBLIC;

WARNING: errors ignored on restore: 1

mzj_test=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies

--------+--------+-------+-------------------+-------------------+----------
public | table1 | table | | |
(1 row)

The latter behavior is not great, probably, but it's acting as designed.

As for Moshe's different results when going through psql, the only
theory that comes to mind is that he's got it configured with
ON_ERROR_STOP turned on, probably in ~/.psqlrc.

regards, tom lane

--

[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&gt; <http://facebook.com/NeadWerx/&gt;
<http://goo.gl/maps/7SL9gn5fBi62&gt;