Re: replacing role-level NOINHERIT with a grant-level option

On Mon, Jun 13, 2022 at 2:42 PM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> Agreed. Moving the inherit flag to the many-to-many join relation provides flexibility, while representing the present behavior is trivial - every row for a given member role has the same value for said flag.

Precisely.

> One seemingly missing feature is to specify for a role that its privileges cannot be inherited. In this case associations where it is the group role mustn't be flagged inherit. Symmetrically, "inherit only" seems like a plausible option for pre-defined group roles.

Yeah, I was kind of wondering about that, although I hadn't thought so
much of making it mandatory as having some kind of way of setting the
default. One could do either, but I think that can be left for a
future patch that builds on what I am proposing here. No sense trying
to do too many things all at once.

> I agree that granting membership makes the pg_auth_members record appear and revoking membership makes it disappear.

Great.

> I dislike having GRANT do stuff when membership is already established.
>
> ALTER MEMBER role IN group ALTER [SET | ASSUME] [TO | =] [TRUE | FALSE]

I thought about this, too. We could definitely do something like that.
I imagine it would be called ALTER GRANT rather than ALTER MEMBER, but
other than that I don't see an issue. However, there's existing
precedent for the way I proposed it: if you repeat the same GRANT
command but write WITH ADMIN OPTION only the second time, it modifies
the existing grant and adds the admin option to it. If you repeat it
verbatim the second time, it instead gives you a warning that your
command was redundant. That to me establishes the precedent that the
way you modify the options associated with a GRANT is to issue a new
GRANT command. I don't find changing that existing behavior very
appealing, even if we might not pick the same thing if we were doing
it over. We could add something else alongside that to provide another
way of accomplishing the same thing, but that seemed more complicated
for not much benefit.

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