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: Robert Haas <robertmhaas@gmail.com>
Cc: 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-07T19:29:31Z
Lists: pgsql-hackers 
On Mon, Mar 7, 2022 at 11:18 AM Robert Haas <robertmhaas@gmail.com> wrote:

> In terms of how
>
things work today, see Joshua Brindle's email about the use of groups
> in pg_hba.conf. That is an excellent example of how removing oneself
> from a group could enable one to bypass security restrictions intended
> by the DBA.
>
>
You mean the one that was based upon your "ooops"...I discounted that
immediately because members cannot revoke their own membership in a group
unless they were given WITH ADMIN OPTION on that group.

The mere fact that the pg_hba.conf concern raised there hasn't been
reported as a live issue suggests the lack of any meaningful design flaw
here.

That isn't to say that having a LOGIN role get an automatic temporary WITH
ADMIN OPTION on itself is a good thing - but there isn't any privilege
escalation vector here to be squashed.  There is just a "DBAs should treat
LOGIN roles as leaf nodes" expectation in which case there would be no
superuser granted memberships to be removed.

David J.

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