Re: role self-revocation

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Mar 4, 2022 at 4:34 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Agreed, this is not something to move on quickly. We might want
>> to think about adjusting pg_dump to use explicit GRANTED BY
>> options in GRANT/REVOKE a release or two before making incompatible
>> changes.

> Uggh. I really want to make some meaningful progress here before the
> heat death of the universe, and I'm not sure that this manner of
> proceeding is really going in that direction. That said, I do entirely
> see your point. Are you thinking we'd actually add a GRANTED BY clause
> to GRANT/REVOKE, vs. just wrapping it in SET ROLE incantations of some
> sort?

I was thinking the former ... however, after a bit of experimentation
I see that we accept "grant foo to bar granted by baz" a VERY long
way back, but the "granted by" option for object privileges is
(a) pretty new and (b) apparently restrictively implemented:

regression=# grant delete on alices_table to bob granted by alice;
ERROR: grantor must be current user

That's ... surprising. I guess whoever put that in was only
interested in pro-forma SQL syntax compliance and not in making
a usable feature.

So if we decide to extend this change into object privileges
it would be advisable to use SET ROLE, else we'd be giving up
an awful lot of backwards compatibility in dump scripts.
But if we're only talking about role grants then I think
GRANTED BY would work fine.

regards, tom lane