Re: role self-revocation

> On Mar 10, 2022, at 7:56 AM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)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's objects (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 a simple rule restricting roles from interfering with another role's objects is quite enough. That raises the question of whether 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 set of privileges is hard-coded. You've suggested changing the hard-coded rules to remove pg_* roles from the list of grantable privileges, but that's still an inflexible set of hardcoded privileges. Wouldn't it make more sense for the grantor to 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 the permission system. This isn't a user permission concern, it is an unauthorized privilege escalation concern. Anyone with the bot's credentials can trivially overcome the third restriction by creating a role with the desired membership and then logging in as that role - and there is nothing the system can do to prevent that while also allowing the other two permissions.

Doesn't this assume password authentication? If the server uses ldap authentication, for example, wouldn't the bot need valid 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 database user has been granted ADMIN OPTION for?

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