Re: BUG #17346: pg_upgrade fails with role granted by other role

> On 27 Dec 2021, at 17:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
>> After the commit:
>
>> commit 371087d006e04991080bf17cf2287db38d3ea92e
>> Author: Daniel Gustafsson <dgustafsson@postgresql.org>
>> Date:   Fri Nov 26 14:02:01 2021 +0100
>>    Fix GRANTED BY support in REVOKE ROLE statements
>
>> pg_upgrade for example from 10.19 version causes the error:
>
> Yeah, you don't even need pg_upgrade.  Just do
>
> regression=# CREATE ROLE user1; CREATE ROLE user2; GRANT user1 TO user2 GRANTED BY user1;
> CREATE ROLE
> CREATE ROLE
> ERROR:  grantor must be current user
>
> A superuser, or really anyone who's a member of the user1 role,
> ought to be able to do that (especially since it used to be allowed).
> So it seems the permissions check was coded incorrectly.

Reading the SQL spec for GRANT and REVOKE, and specifically the "Grantor
Determination" subsection, it's not clear to me that this is wrong *per spec*
and that any value except CURRENT_USER and CURRENT_ROLE is supported (which is
what 6aaaa76bb implemented and the above referenced commit amended).  Given the
time of day I'm undercaffeinated for spec reading so I might be missing
something though.  Is <grantor> really handled differently for GRANT/REVOKE
ROLE to PRIVILEGE?

That being said, *iff* my spec reading is right, since this is something that
was working, and the benefit in supporting this is slim, reverting might be the
best (only) course.  Question is then how far that revert should stretch?  Is
there value in being spec compliant for PRIVILEGE and not ROLE?

If my spec reading is wrong then reverting is pretty obvious, but I would
appreciate a second pair of eyes on this before ripping it out.

--
Daniel Gustafsson        https://vmware.com/