Re: role self-revocation

On Mon, Mar 7, 2022 at 8:37 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> A role is not considered to hold WITH
> ADMIN OPTION on itself, but it may grant or revoke membership in
> itself from a database session where the session user matches the
> role."
>
> Is there some use case for the behavior described in that last
> sentence?

I can imagine, in particular combined with CREATEROLE, that this allows for
any user to delegate their personal permissions to a separate newly created
user. Like an assistant. I'm not all that sure whether CREATEROLE is
presently safe enough to give to a normal user in order to make this use
case work but it seems reasonable.

I would be concerned about changing the behavior at this point. But I
would be in favor of at least removing the hard-coded exception and linking
it to a role attribute. That attribute can default to "SELFADMIN" to match
the existing behavior but then "NOSELFADMIN" would exist to disable that
behavior on the per-role basis. Still tied to session_user as opposed to
current_user.

David J.

P.S.

create role selfadmin admin selfadmin; -- ERROR: role "selfadmin" is a
member of role "selfadmin"

create role selfadmin;
grant selfadmin to selfadmin with admin option; -- ERROR: role "selfadmin"
is a member of role "selfadmin"

The error message seems odd. I tried this because instead of a "SELFADMIN"
attribute adding a role to itself WITH ADMIN OPTION could be defined to
have the same effect. You cannot change WITH ADMIN OPTION independently of
the adding of the role to the group.