Horton

Re: CREATEROLE and role ownership hierarchies

Mark Dilger <mark.dilger@enterprisedb.com>

View thread View on postgr.es
From: Mark Dilger <mark.dilger@enterprisedb.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: Robert Haas <robertmhaas@gmail.com>, Andrew Dunstan <andrew@dunslane.net>, "Bossart, Nathan" <bossartn@amazon.com>, Jeff Davis <pgsql@j-davis.com>, Joshua Brindle <joshua.brindle@crunchydata.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Shinya Kato <Shinya11.Kato@oss.nttdata.com>
Date: 2022-01-24T23:18:03Z
Lists: pgsql-hackers 

> On Jan 24, 2022, at 2:21 PM, Stephen Frost <sfrost@snowman.net> wrote:
> 
> Superuser is a problem specifically because it gives people access to do absolutely anything, both for security and safety concerns. Disallowing a way to curtail that same risk when it comes to role ownership invites exactly those same problems.

Before the patch, users with CREATEROLE can do mischief.  After the patch, users with CREATEROLE can do mischief.  The difference is that the mischief that can be done after the patch is a proper subset of the mischief that can be done before the patch.  (Counter-examples highly welcome.)

Specifically, I claim that before the patch, non-superuser "bob" with CREATEROLE can interfere with *any* non-superuser.  After the patch, non-superuser "bob" with CREATEROLE can interfere with *some* non-superusers; specifically, with non-superusers he created himself, or which have had ownership transferred to him.

Restricting the scope of bob's mischief is a huge win, in my view.

The argument about whether owners should always implicitly inherit privileges from roles they own is a bit orthogonal to my point about mischief-making.  Do we at least agree on the mischief-abatement aspect of this patch set?  

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

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