Re: pg_auth_members.grantor is bunk

On Mon, Jun 6, 2022 at 7:41 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Thankfully, at least from my reading, the spec isn't all that
> complicated on this particular point. The spec talks about "role
> authorization descriptor"s and those are "created with role name,
> grantee, and grantor" and then further says "redundant duplicate role
> authorization descriptors are destroyed", presumably meaning that the
> entire thing has to be identical. In other words, yeah, the PK should
> include the grantor. There's a further comment that the 'set of
> involved grantees' is the union of all the 'grantees', clearly
> indicating that you can have multiple GRANT 'foo' to 'bar's with
> distinct grantees.
>
> In terms of how that's then used, yeah, it's during REVOKE because a
> REVOKE is only able to 'find' role authorization descriptors which match
> the triple of role revoked, grantee, grantor (though there's a caveat in
> that the 'grantor' role could be the current role, or the current user).

What is supposed to happen if someone tries to execute DROP ROLE on a
role that has previously been used as a grantor?

Consider:

create role foo;
create role bar;
create role baz;
grant foo to bar granted by baz;
drop role baz;

Upthread, I proposed that "drop role baz" should fail here, but
there's at least one other option: it could silently remove the grant,
as we would do if either foo or bar were dropped. The situation is not
quite comparable, though: a grant from foo to bar makes no logical
sense if either of those roles cease to exist, but it does make at
least some sense if baz ceases to exist. Therefore I think someone
could argue either for an error or for removing the grant -- or
possibly even for some other behavior, though the other behaviors that
I can think of don't make much sense in a world where the primary key
of pg_auth_members is (roleid, member, grantor).

--
Robert Haas
EDB: http://www.enterprisedb.com