Horton

Re: role self-revocation

Mark Dilger <mark.dilger@enterprisedb.com>

View thread View on postgr.es
From: Mark Dilger <mark.dilger@enterprisedb.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, "David G. Johnston" <david.g.johnston@gmail.com>, Stephen Frost <sfrost@snowman.net>, Joshua Brindle <joshua.brindle@crunchydata.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-03-07T20:09:48Z
Lists: pgsql-hackers 

> On Mar 7, 2022, at 12:03 PM, Mark Dilger <mark.dilger@enterprisedb.com> wrote:
> 
> Right, but with a reflexive self-admin-option, we could document that it works in a non-inherited way.  We'd just be saying the current hard-coded behavior is an option which can be revoked rather than something you're stuck with.

We could also say that the default is to not have admin option on yourself, with that being something grantable, but that is a larger change from the historical behavior and might have more consequences for dump/restore, etc.

My concern about just nuking self-admin is that there may be sites which use self-admin and we'd be leaving them without a simple work-around after upgrade, because they couldn't restore the behavior by executing a grant.  They'd have to more fundamentally restructure their role relationships to not depend on self-admin, something which might be harder for them to do.  Perhaps nobody is using self-admin, or very few people are using it, and I'm being overly concerned.

—
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