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: Andrew Dunstan <andrew@dunslane.net>
Cc: Michael Banck <michael.banck@credativ.de>, Stephen Frost <sfrost@snowman.net>, Robert Haas <robertmhaas@gmail.com>, "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-02-01T22:27:35Z
Lists: pgsql-hackers 

> On Feb 1, 2022, at 1:10 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> 
> The whole 'NOFOO WITH ADMIN OPTION'
> thing seems to me a bit like a POLA violation. Nevertheless I can
> probably live with it as long as it's *really* well documented. Even so
> I suspect it would be too complex for many, and they will just continue
> to use superusers to create and manage roles if possible.

I agree with the sentiment, but it might help to distinguish between surprising behavior vs. surprising grammar.

In existing postgresql releases, having CREATEROLE means you can give away most attributes, including ones you yourself don't have (createdb, login).  So we already have the concept of NOFOO WITH ADMIN OPTION, we just don't call it that.  In pre-v8 patches on this thread, I got rid of that; you *must* have the attribute to give it away.  But maybe that was too restrictive, and we need a way to specify, attribute by attribute, how this works.  Is this just a problem of surprising grammar?  Is it surprising behavior?  If the latter, I'm inclined to give up this WIP as having been a bad move.  If the former, I'll try to propose some less objectionable grammar.
 

—
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