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-11T16:34:36Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> If we implement the link between the creating role and the created
> role as role ownership, then we are surely just going to add a
> rolowner column to pg_authid, and when the role is owned by nobody, I
> think we should always just store a valid OID in it, rather than
> sometimes storing 0. It just seems simpler. Any time we would store 0,
> store the bootstrap superuser's pg_authid.oid value instead. That way
> the OID is always valid, which probably lets us get by with fewer
> special cases in the code.

+1.

Note that either case would also involve making entries in pg_shdepend;
although for the case of roles owned by/granted to the bootstrap
superuser, we could omit those on the usual grounds that we don't need
to record dependencies on pinned objects.

			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