Re: pg_restore: All GRANTs on table fail when any one role is missing - Mailing list pgsql-bugs

From Moshe Jacobson
Subject Re: pg_restore: All GRANTs on table fail when any one role is missing
Date
Msg-id CAJ4CxLmmOfZ=zy0HeOxkDyZpU3j1EuJpqv+-Ox6XYhig44PFwA@mail.gmail.com
Whole thread Raw
In response to Re: pg_restore: All GRANTs on table fail when any one role is missing  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
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
--

photo
Moshe Jacobson
Principal Architect, Nead Werx, Inc.

www.neadwerx.com

2323 Cumberland Pkwy SE #201, Atlanta GA 30339

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #15306: Use pkg-config for searching libxml2 header
Next
From: Tom Lane
Date:
Subject: Re: BUG #15306: Use pkg-config for searching libxml2 header