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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com> writes:
> > On Tue, Jul 24, 2018 at 5:14 AM Moshe Jacobson <moshe(at)neadwerx(dot)com>
> wrote:
> >> On Tue, Jul 17, 2018 at 12:02 PM Moshe Jacobson <moshe(at)neadwerx(dot)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> <http://facebook.com/NeadWerx/>
<http://goo.gl/maps/7SL9gn5fBi62>