Horton

Re: role self-revocation

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, "David G. Johnston" <david.g.johnston@gmail.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:46:55Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Mar 11, 2022 at 10:27 AM Stephen Frost <sfrost@snowman.net> wrote:
>> I agree that there would be a recorded relationship (that is, one that
>> we write into the catalog and keep around until and unless it's removed
>> by an admin) between creating and created roles and that's probably the
>> default when CREATE ROLE is run but, unlike tables or such objects in
>> the system, I don't agree that we should require this to exist at
>> absolutely all times for every role (what would it be for the bootstrap
>> superuser..?).  At least today, that's distinct from how ownership in
>> the system works.  I also don't believe that this is necessarily an
>> issue for Robert's use-case, as long as there are appropriate
>> restrictions around who is allowed to remove or modify these
>> relationships.

> I agree.

The bootstrap superuser clearly must be a special case in some way.
I'm not convinced that that means there should be other special
cases.  Maybe there is a use-case for other "unowned" roles, but in
exactly what way would that be different from deeming such roles
to be owned by the bootstrap superuser?

			regards, tom lane

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