Horton

Re: role self-revocation

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: "David G. Johnston" <david.g.johnston@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-06T15:19:39Z
Lists: pgsql-hackers 
On Fri, Mar 4, 2022 at 5:20 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I think I disagree.  Or, at least, the superuser has full control of dictating how role membership is modified and that seems sufficient.

The point is that the superuser DOES NOT have full control. The
superuser cannot prevent relatively low-privileged users from undoing
things that the superuser did intentionally and doesn't want reversed.

The choice of names in my example wasn't accidental. If the granted
role is a login role, then the superuser's intention was to vest the
privileges of that role in some other role, and it is surely not right
for that role to be able to decide that it doesn't want it's
privileges to be so granted. That's why I chose the name "peon". In
your example, where you chose the name "admin", the situation is less
clear. If we imagine the granted role as a container for a bundle of
privileges, giving it the ability to administer itself feels more
reasonable. However, I am very much unconvinced that it's correct even
there. Suppose the superuser grants "admin" to both "joe" and "sally".
Now "joe" can SET ROLE to "admin" and revoke it from "sally", and the
superuser has no tool to prevent this.

Now you can imagine a situation where the superuser is totally OK with
either "joe" or "sally" having the ability to lock the other one out,
but I don't think it's right to say that this will be true in all
cases.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

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