Re: role self-revocation

> On Mar 10, 2022, at 7:56 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
>
> I want that because I want mini-superusers, where alice can administer
> the users that alice creates just as if she were a superuser,
> including having their permissions implicitly and dropping them when
> she wants them gone, but where alice cannot break out to the operating
> system as a true superuser could do.
>
> CREATEROLE (once the pg_* with admin rules are fixed) + Ownership and rules restricting interfering with another
role'sobjects (unless superuser) seems to handle this. 

What if one of alice's subordinates also owns roles?  Can alice interfere with *that* role's objects?  I don't see that
asimple rule restricting roles from interfering with another role's objects is quite enough.  That raises the question
ofwhether role ownership is transitive, and whether we need a concept similar to inherit/noinherit for ownership. 

There is also the problem that CREATEROLE currently allows a set of privileges to be granted to created roles, and that
setof privileges is hard-coded.  You've suggested changing the hard-coded rules to remove pg_* roles from the list of
grantableprivileges, but that's still an inflexible set of hardcoded privileges.  Wouldn't it make more sense for the
grantorto need GRANT OPTION on any privilege they give to roles they create? 

> the bot can stand up
> accounts, can grant them membership in a defined set of groups, but
> cannot exercise the privileges of those accounts (or hack superuser
> either).
>
> The bot should be provided a security definer procedure that encapsulates all of this rather than us trying to hack
thepermission system.  This isn't a user permission concern, it is an unauthorized privilege escalation concern.
Anyonewith the bot's credentials can trivially overcome the third restriction by creating a role with the desired
membershipand then logging in as that role - and there is nothing the system can do to prevent that while also allowing
theother two permissions. 

Doesn't this assume password authentication?  If the server uses ldap authentication, for example, wouldn't the bot
needvalid ldap credentials for at least one user for this attack to work?  And if CREATEROLE has been made more
configurable,wouldn't the bot only be able to grant that ldap user the limited set of privileges that the bot's
databaseuser has been granted ADMIN OPTION for? 

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company