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: "David G. Johnston" <david.g.johnston@gmail.com>, Stephen Frost <sfrost@snowman.net>, 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-07T16:04:10Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> Hmm. I think the real issue is what David Johnson calls the session
> user exception. I hadn't quite understood how that played into this.
> According to the documentation: "If WITH ADMIN OPTION is specified,
> the member can in turn grant membership in the role to others, and
> revoke membership in the role as well. Without the admin option,
> ordinary users cannot do that. A role is not considered to hold WITH
> ADMIN OPTION on itself, but it may grant or revoke membership in
> itself from a database session where the session user matches the
> role."

> Is there some use case for the behavior described in that last
> sentence?

Good question.  You might try figuring out when that text was added
and then see if there's relevant discussion in the archives.

Just looking at it now, without having done any historical research,
I wonder why it is that we don't attach significance to WITH ADMIN
OPTION being granted to the role itself.  It seems like the second
part of that sentence is effectively saying that a role DOES have
admin option on itself, contradicting the first part.

			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