Horton

Re: role self-revocation

David G. Johnston <david.g.johnston@gmail.com>

View thread View on postgr.es
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@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:58:22Z
Lists: pgsql-hackers 
On Mon, Mar 7, 2022 at 9:04 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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.
>
>
WITH ADMIN OPTION is inheritable which is really bad if the group has WITH
ADMIN OPTION on itself.  The session_user exception temporarily grants WITH
ADMIN OPTION to the group but it is done in such a way so that it is not
inheritable.

There is no possible way to even assign WITH ADMIN OPTION on a role to
itself since pg_auth_members doesn't record a self-relationship and
admin_option only exists there.

David J.

P.S. Feature request; modify \du+ to show which "Member of" roles a given
role has the WITH ADMIN OPTION privilege on.

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