Re: role self-revocation

On Thu, Mar 10, 2022 at 12:58 PM Stephen Frost <sfrost@snowman.net> wrote:

> I don't think we're that far from having all of these though.  To start
> with, we remove from CREATEROLE the random things that it does which go
> beyond what folks tend to expect- remove the whole 'grant any role to
> any other' stuff, remove the 'drop role' exception, remove the
> 'alter role' stuff.  Do make it so that when you create a role, however,
> the above GRANT is effectively done.  Now, for the items above where we
> removed the checks against have_createrole_privilege() we go back and
> add in checks using is_admin_of_role().  Of course, also remove the role
> self-administration bug.
>
> That's step #1, but it gets us more-or-less what you're looking for, I
> think, and brings us a lot closer to what the spec has.
>

That still leaves attribute specification in place: e.g., REPLICATION,
CREATEROLE, CREATEDB, etc... (I see BYPASSRLS already is SUPERUSER only)

I dislike changing the documented behavior of CREATEROLE to the degree
suggested here.  However, there are three choices here, only one of which
can be chosen:

1. Leave CREATEROLE alone entirely
2. Make it so CREATEROLE cannot assign membership to the predefined roles
or superuser (inheritance included), but leave the rest alone.  This would
be the hard-coded version, not the role attribute one.
3. Make it so CREATEROLE can only assign membership to roles for which it
has been made an admin; as well as the other things mentioned

Moving forward I'd prefer options 1 or 2, leaving the ability to
create/alter/drop a role to be vested via predefined roles.

The rest seems fine at an initial glance.

David J.