On Mon, Mar 7, 2022 at 8:37 AM Robert Haas <robertmh...@gmail.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.