Re: role self-revocation - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: role self-revocation |
| Date | |
| Msg-id | CAKFQuwYpakynjF+QROwwqCkpz+MOy5Pkq58syAextAAcf1BNnQ@mail.gmail.com Whole thread Raw |
| In response to | Re: role self-revocation (Robert Haas <robertmhaas@gmail.com>) |
| Responses |
Re: role self-revocation
(Stephen Frost <sfrost@snowman.net>)
Re: role self-revocation (Mark Dilger <mark.dilger@enterprisedb.com>) |
| List | pgsql-hackers |
On Thu, Mar 10, 2022 at 7:46 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Wed, Mar 9, 2022 at 4:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I don't think we need syntax to describe it. As I just said in my
> other reply, we have a perfectly good precedent for this already
> in ordinary object permissions. That is: an object owner always,
> implicitly, has GRANT OPTION for all the object's privileges, even
> if she revoked the corresponding plain privilege from herself.
>
> Yeah, this does mean that we're effectively deciding that the creator
> of a role is its owner. What's the problem with that?
I don't think that's entirely the wrong concept, but it doesn't make a
lot of sense in a world where the creator has to be a superuser. If
alice, bob, and charlie are superusers who take turns creating new
users, and then we let charlie go due to budget cuts, forcing alice
and bob to change the owner of all the users he created to some other
superuser as a condition of dropping his account is a waste of
everyone's time. They can do exactly the same things to every account
on the system after we change the role owner as before.
Then maybe we should just implement the idea that if a superuser would become the owner we instead substitute in the bootstrap user. Or give the DBA the choice whether they want to retain knowledge of specific roles - and thus are willing to accept the "waste of time".
But wait, I hear you cry, what about CREATEROLE? Well, CREATEROLE is
generally agreed to be broken right now, and if you don't agree with
that, consider that it can grant pg_execute_server_programs to a
newly-created account and then explain to me how it's functionally
different from superuser.
CREATEROLE has long been defined as basically having "with admin option" on every role in the system. The failure to special-case the roles that grant different aspects of superuser-ness to its members doesn't make CREATEROLE itself broken, it makes the implementation of pg_execute_server_programs broken. Only superusers should be considered to have with admin option on these roles. They can delegate through the usual membership+admin mechanism to a CREATEROLE role if they desire.
The whole area needs a rethink. I believe
everyone involved in the discussion on the other threads agrees that
some reform of CREATEROLE is necessary, and more generally with the
idea that it's useful for non-superusers to be able to create roles.
As the documentation says, using SUPERUSER for day-to-day administration is contrary to good security practices. Role management is considered to be a day-to-day administration activity. I agree with this principle. It was designed to neither be a superuser nor grant superuser, so removing the ability to grant the pg_* role memberships remains consistent with its original intent.
I want that because I want mini-superusers, where alice can administer
the users that alice creates just as if she were a superuser,
including having their permissions implicitly and dropping them when
she wants them gone, but where alice cannot break out to the operating
system as a true superuser could do.
CREATEROLE (once the pg_* with admin rules are fixed) + Ownership and rules restricting interfering with another role's objects (unless superuser) seems to handle this.
the bot can stand up
accounts, can grant them membership in a defined set of groups, but
cannot exercise the privileges of those accounts (or hack superuser
either).
The bot should be provided a security definer procedure that encapsulates all of this rather than us trying to hack the permission system. This isn't a user permission concern, it is an unauthorized privilege escalation concern. Anyone with the bot's credentials can trivially overcome the third restriction by creating a role with the desired membership and then logging in as that role - and there is nothing the system can do to prevent that while also allowing the other two permissions.
And that's why I'm not sure it's really the right idea to say that we
don't need syntax for this admin-without-member concept.
We already have this syntax in the form of CREATEROLE. But we do need a fix, just on the group side. We need a way to define a group as having no ADMINS.
ALTER ROLE pg_superuser WITH [NO] ADMIN;
Then adding a role membership including the WITH ADMIN OPTION can be rejected, as can the non-superuser situation. Setting WITH NO ADMIN should fail if any existing members have admin. You must be a superuser to execute WITH ADMIN (maybe WITH NO ADMIN as well...). And possibly even a new pg_* role that grants this ability (and maybe some others) for use by a backup/restore user.
Or just special-case pg_* roles.
The advantage of exposing this to the DBA is that they can then package pg_* roles into a custom group and still have the benefit of superuser only administration. In the special-case implementation the presence of a pg_* role in a group hierarchy would then preclude a non-superuser from having admin on the entire tree (the pg_* roles are all roots, or in the case of pg_monitor, directly emanate from a root role).
David J.
David J.
pgsql-hackers by date: