Re: CREATEROLE and role ownership hierarchies

Mark Dilger <mark.dilger@enterprisedb.com>

From: Mark Dilger <mark.dilger@enterprisedb.com>

To: Shinya Kato <Shinya11.Kato@oss.nttdata.com>, "Bossart, Nathan" <bossartn@amazon.com>

Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>, Andrew Dunstan <andrew@dunslane.net>, Jeff Davis <pgsql@j-davis.com>

Date: 2021-10-27T22:21:43Z

Lists: pgsql-hackers

Attachments

v2-0001-Add-tests-of-the-CREATEROLE-attribute.patch (application/octet-stream) patch v2-0001
v2-0002-Add-owners-to-roles.patch (application/octet-stream) patch v2-0002
v2-0003-Give-role-owners-control-over-owned-roles.patch (application/octet-stream) patch v2-0003
v2-0004-Restrict-power-granted-via-CREATEROLE.patch (application/octet-stream) patch v2-0004
(unnamed) (text/plain)

>> On Oct 25, 2021, at 10:09 PM, Shinya Kato <Shinya11.Kato@oss.nttdata.com> wrote:

>> Hi! Thank you for the patch.
>> I too think that CREATEROLE escalation attack is problem.
>> 
>> I have three comments.
>> 1. Is there a function to check the owner of a role, it would be nice to be able to check with \du or pg_roles view.
> 
> No, but that is a good idea.

These two ideas are implemented in v2.  Both \du and pg_roles show the owner information.

> The current solution is to run REASSIGN OWNED in each database where the role owns objects before running DROP ROLE. At that point, the CASCADE option (not implemented) won't be needed.  Of course, I need to post the next revision of this patch set addressing the deficiencies that Nathan pointed out upthread to make that work. 

REASSIGN OWNED and ALTER ROLE..OWNER TO now work in v2.

Commits

Make role grant system more consistent with other privileges.
- ce6b672e4455 16.0 landed
Ensure that pg_auth_members.grantor is always valid.
- 6566133c5f52 16.0 landed
Remove the ability of a role to administer itself.
- 79de9842ab03 15.0 landed
Add tests of the CREATEROLE attribute
- e9d4001ec592 15.0 landed
Replace explicit PIN entries in pg_depend with an OID range test.
- a49d08123599 15.0 cited
Shore up ADMIN OPTION restrictions.
- fea164a72a7b 9.4.0 cited
Add pg_has_role() family of privilege inquiry functions modeled after the
- f9fd1764615e 8.1.0 cited
Align GRANT/REVOKE behavior more closely with the SQL spec, per discussion
- 4b2dafcc0b1a 8.0.0 cited