Horton

Re: CREATEROLE and role ownership hierarchies

Shinya Kato <shinya11.kato@oss.nttdata.com>

View thread View on postgr.es
From: Shinya Kato <Shinya11.Kato@oss.nttdata.com>
To: Mark Dilger <mark.dilger@enterprisedb.com>
Cc: "Bossart, Nathan" <bossartn@amazon.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Andrew Dunstan <andrew@dunslane.net>
Date: 2021-10-26T05:09:09Z
Lists: pgsql-hackers 
On 2021-10-21 03:40, Mark Dilger wrote:
> These patches have been split off the now deprecated monolithic
> "Delegating superuser tasks to new security roles" thread at [1].
> 
> The purpose of these patches is to fix the CREATEROLE escalation
> attack vector misfeature.  (Not everyone will see CREATEROLE that way,
> but the perceived value of the patch set likely depends on how much
> you see CREATEROLE in that light.)

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.
2. Is it correct that REPLICATION/BYPASSRLS can be granted even if you 
are not a super user, but have CREATEROLE and REPLICATION/BYPASSRLS?
3. I think it would be better to have an "DROP ROLE [ IF EXISTS ] name 
[, ...] [CASCADE | RESTRICT]" like "DROP TABLE [ IF EXISTS ] name [, 
...] [ CASCADE | RESTRICT ]". What do you think?

-- 
Regards,

--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

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