Re: role self-revocation - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: role self-revocation |
| Date | |
| Msg-id | CAKFQuwaTD6UoEjmqvaLwMih3Qegg-EY8B2F+V5gv6og7xe7R9g@mail.gmail.com Whole thread Raw |
| In response to | Re: role self-revocation (Stephen Frost <sfrost@snowman.net>) |
| Responses |
Re: role self-revocation
|
| List | pgsql-hackers |
On Thu, Mar 10, 2022 at 9:19 AM Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* David G. Johnston (david.g.johnston@gmail.com) wrote:
> 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".
This doesn't strike me as going in the right direction. Falling back to
the bootstrap superuser is generally a hack and not a great one. I'll
also point out that the SQL spec hasn't got a concept of role ownership
either.
> > 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.
No, CREATEROLE having admin option on every role in the system is broken
and always has been. It's not just an issue for predefined roles like
pg_execute_server_program,
it's an issue for any role that could become
a superuser either directly or indirectly and that extends beyond the
predefined ones.
The only indirect way for a role to become superuser is to have been granted membership in a superuser group, then SET ROLE. Non-superusers cannot do this. If a superuser does this I consider the outcome to be no different than if they go and do:
SET allow_system_table_mods TO true;
DROP pg_catalog.pg_class;
In short, having a CREATEROLE user issuing:
GRANT pg_read_all_stats TO davidj;
should result in the same outcome as them issuing:
GRANT postgres TO davidj;
-- ERROR: must be superuser to alter superusers
Superusers can break their system and we don't go to great effort to stop them. I see no difference here, so arguments of this nature aren't all that compelling to me.
CREATEROLE shouldn't be
the determining factor in the question of if a role can GRANT a
predefined (or any other role) to some other role- that should be
governed by the admin option on that role, and that should work exactly
the same for predefined roles as it does for any other.
Never granting the CREATEROLE attribute to anyone will give you this outcome today.
ADMIN option
without membership isn't something the catalogs today can understand
Today, they don't need to in order for the system to function within its existing design specs.
> 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.
I'm not following this in general or how it helps. Surely we don't want
to limit WITH ADMIN to superusers.
Today a non-superuser cannot "grant postgres to someuser;"
The point of this attribute is to allow the superuser to apply that rule to other roles that aren't superuser. In particular, the predefined pg_* roles. But it could extend to any other role the superuser would like to limit. It means, for that for named role, ADMIN privileges cannot be delegated to other roles - thus all administration of that role's membership roster must happen by a superuser.
In particular, this means CREATEROLE roles cannot assign membership in the marked roles; just like they cannot assign membership in superuser roles today.
For me, because the SUPERUSER cannot have its role become a group without a superuser making that choice, and by default the default pg_* roles will all have this property as well, and any newly superuser created roles that may be members of either superuser or pg_* can have the property defined as well, gives full control to the superuser as to how superuser abilities are doled out and so the design itself allows for what many of you are considering to be "safe usage". That "unsafe configurations" are possible is due to the policy that superusers are unrestricted in what they can do, including making unsafe and destructive choices.
In short, removing the self-administration rule solves the "login roles should not be automatically considered groups administered by themselves" problem - or at least a feature we really don't need.
And defining a "superuser administration only" attribute to a role solves the indirect superuser privileges and assignment thereof by non-superusers problem.
I can see value in adding a feature whereby we allow the DBA to define a group as a schema-like container and then assign roles to that group with a fine-grained permissions model. My take is this proposal is a new feature while the two problems noted above can be solved more readily and with less risk with the two suggested changes.
David J.
pgsql-hackers by date: