Horton

Re: CREATEROLE and role ownership hierarchies

Joshua Brindle <joshua.brindle@crunchydata.com>

View thread View on postgr.es
From: Joshua Brindle <joshua.brindle@crunchydata.com>
To: Mark Dilger <mark.dilger@enterprisedb.com>
Cc: Andrew Dunstan <andrew@dunslane.net>, Shinya Kato <Shinya11.Kato@oss.nttdata.com>, "Bossart, Nathan" <bossartn@amazon.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Jeff Davis <pgsql@j-davis.com>
Date: 2022-01-07T14:51:41Z
Lists: pgsql-hackers 
On Wed, Jan 5, 2022 at 7:05 PM Mark Dilger <mark.dilger@enterprisedb.com> wrote:

> > On Jan 4, 2022, at 12:47 PM, Joshua Brindle <joshua.brindle@crunchydata.com> wrote:
> >
> >> I was able to reproduce that using REASSIGN OWNED BY to cause a user to own itself.  Is that how you did it, or is there yet another way to get into that state?
> >
> > I did:
> > ALTER ROLE brindle OWNER TO brindle;
>
> Ok, thanks.  I have rebased, fixed both REASSIGN OWNED BY and ALTER ROLE .. OWNER TO cases, and added regression coverage for them.
>
> The last patch set to contain significant changes was v2, with v3 just being a rebase.  Relative to those sets:
>
> 0001 -- rebased.
> 0002 -- rebased; extend AlterRoleOwner_internal to disallow making a role its own immediate owner.
> 0003 -- rebased; extend AlterRoleOwner_internal to disallow cycles in the role ownership graph.
> 0004 -- rebased.
> 0005 -- new; removes the broken pg_auth_members.grantor field.
>

LGTM +1

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