Re: role self-revocation

On Thu, Mar 10, 2022 at 4:00 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I dislike changing the documented behavior of CREATEROLE to the degree suggested here.  However, there are three
choiceshere, 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
leavethe 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
otherthings 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.

It sounds like you prefer a behavior where CREATEROLE gives power over
all non-superusers, but that seems pretty limiting to me. Why can't
someone want to create a user with power over some users but not
others? For example, the superuser might want to give alice the
ability to set up new users in the accounting department, but NOT give
alice the right to tinker with the backup user (who is not a
superuser, but doesn't have the replication privilege). How would they
accomplish that in your view?

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