Horton

Re: role self-revocation

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Joshua Brindle <joshua.brindle@crunchydata.com>, Mark Dilger <mark.dilger@enterprisedb.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-03-11T15:58:18Z
Lists: pgsql-hackers 
On Fri, Mar 11, 2022 at 10:37 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I largely agree and am perfectly fine with going with the majority on this point.  My vote would just fall on the conservative side.  But as so far no one else seems to be overly concerned, nerfing CREATEROLE seems to be the path forward.

This kind of thing is always a judgement call. If we were talking
about breaking 'SELECT * from table', I'm sure it would be hard to
convince anybody to agree to do that at all, let alone with no
deprecation period. Fortunately, CREATEROLE is less used, so breaking
it will inconvenience fewer people. Moreover, unlike 'SELECT * FROM
table', CREATEROLE is kinda broken, and it's less scary to make
changes to behavior that sucks in the first place than it is to make
changes to the behavior of things that are working well. For all of
that, there's no hard-and-fast rule that we couldn't keep the existing
behavior around, introduce a substitute, and eventually drop the old
thing. I'm just not clear that it's really worth it in this case. It'd
certainly be interesting to hear from anyone who is finding some
utility in the current system. It looks pretty crap to me, but it's
easy to bring too much of one's own bias to such judgements.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

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