Re: role self-revocation

On Thu, Mar 10, 2022 at 9:19 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
> > On Thu, Mar 10, 2022 at 7:46 AM Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
> > > On Wed, Mar 9, 2022 at 4:31 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.