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>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, 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-10T22:01:43Z
Lists: pgsql-hackers 
On Thu, Mar 10, 2022 at 4:00 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I dislike changing the documented behavior of CREATEROLE to the degree suggested here.  However, there are three choices here, only one of which can be chosen:
>
> 1. Leave CREATEROLE alone entirely
> 2. Make it so CREATEROLE cannot assign membership to the predefined roles or superuser (inheritance included), but leave the rest alone.  This would be the hard-coded version, not the role attribute one.
> 3. Make it so CREATEROLE can only assign membership to roles for which it has been made an admin; as well as the other things mentioned
>
> Moving forward I'd prefer options 1 or 2, leaving the ability to create/alter/drop a role to be vested via predefined roles.

It sounds like you prefer a behavior where CREATEROLE gives power over
all non-superusers, but that seems pretty limiting to me. Why can't
someone want to create a user with power over some users but not
others? For example, the superuser might want to give alice the
ability to set up new users in the accounting department, but NOT give
alice the right to tinker with the backup user (who is not a
superuser, but doesn't have the replication privilege). How would they
accomplish that in your view?

-- 
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