Horton

Re: CREATEROLE and role ownership hierarchies

Andrew Dunstan <andrew@dunslane.net>

View thread View on postgr.es
From: Andrew Dunstan <andrew@dunslane.net>
To: Mark Dilger <mark.dilger@enterprisedb.com>, "Bossart, Nathan" <bossartn@amazon.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2021-10-26T18:50:49Z
Lists: pgsql-hackers 
On 10/21/21 19:21, Mark Dilger wrote:
>>  Also, are we just going to strip
>> the current CREATEROLE roles of much of their powers?  Maybe it's
>> worth keeping a legacy CREATEROLE role attribute for upgraded clusters
>> that could eventually be removed down the road.
> The patch as written drastically reduces the power of the CREATEROLE attribute, in a non-backwards compatible way.  I wondered if there would be complaints about that.  If so, we could instead leave CREATEROLE alone, and create some other privileged role for the same thing, but it does start to look funny having a CREATEROLE privilege bit and also a privileged role named, perhaps, pg_can_create_roles.


Give that CREATEROLE currently just about amounts to being a superuser,
maybe there should be a pg_upgrade option to convert CREATEROLE to
SUPERUSER. I don't want to perpetuate the misfeature though, so let's
just bring it to an end.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Commits

  1. Make role grant system more consistent with other privileges.

  2. Ensure that pg_auth_members.grantor is always valid.

  3. Remove the ability of a role to administer itself.

  4. Add tests of the CREATEROLE attribute

  5. Replace explicit PIN entries in pg_depend with an OID range test.

  6. Shore up ADMIN OPTION restrictions.

  7. Add pg_has_role() family of privilege inquiry functions modeled after the

  8. Align GRANT/REVOKE behavior more closely with the SQL spec, per discussion