Re: Delegating superuser tasks to new security roles (Was: Granting control of SUSET gucs to non-superusers)

Greetings,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Mon, Aug 23, 2021 at 2:13 PM Stephen Frost <sfrost(at)snowman(dot)net>
> wrote:> This I have to object to pretty strongly- we have got to get
> away from
> > the idea that just because X isn't a superuser or isn't owned by a
> > superuser that it's fine to allow some non-superuser to mess with it.
> > In particlar, just because a role isn't explicitly marked as a superuser
> > doesn't mean that the role can't *become* a superuser, or that it hasn't
> > got privileged access to the system in other ways, such as by being a
> > member of other predefined roles that perhaps the role who is a member
> > of pg_manage_database_objects doesn't have. Such a check against
> > modifying of "superuser owned" objects implies that it's providing some
> > kind of protection against the role being able to become a superuser
> > when it doesn't actually provide that protection in any kind of reliable
> > fashion and instead ends up fooling the user.
>
> I think you make a good point here, but it seems to me that we need
> *something*. We need a way to create a "lead tenant" role that can
> create other tenant roles and then, err, boss them around. Not only
> drop the roles again, but also drop or alter or change the owner of
> their objects, or really bypass any security those roles would like to
> assert as against the lead tenant. If we can't see a way to create
> some sort of role of that sort, then I don't think we can really say
> we've solved anything much.

Sure, but we can't just use the "superuser" flag for that, we need
something better. The "better" in my mind here would be akin to what
we're thinking about doing for event triggers, but for roles which
actually already have a distinction between becoming a role vs. being
able to GRANT that role to another role, and that's the 'admin' option.

In other words, the user we imagine being GRANT'd this hypothetical
pg_manage_database_objects role wouldn't actually need that role to
explicitly give them access to be able to modify the objects of other
roles- it would be able to do that by virtue of just being a member of
those roles. The roles who are allowed to modify existing role
membership should have the 'admin' right on those roles, and what we
just need is a new predefined role that's basically "allow roles to be
created or dropped" but where the only roles which can be GRANT'd by a
user with that ability are the ones that they have admin rights on, and
the only roles that they're allowed to drop they also have to have admin
rights on.

> > This is the issue with CREATEROLE and we definitely shouldn't be
> > doubling-down on that mistake, and also brings up the point that I, at
> > least, had certainly hoped that part of this effort would include a way
> > for roles to be created by a user with an appropriate predefined role,
> > and w/o CREATEROLE (which would then be deprecated or, ideally, just
> > outright removed). I get that this doesn't have to be in the first
> > patch or even patch set going down this road but the lack of discussion
> > or of any coordination between this effort and the other one that is
> > trying to address the CREATEROLE issue seems likely to land us in a bad
> > place with two distinct approaches being used.
>
> Is there an active effort to do something about CREATEROLE? Do you
> have a link to the thread? I feel like this is one of those things
> that has occasioned discussion over the years but I am not aware of an
> active project or a specific proposal to do something about this.

Hrmpf, I had been thinking of this:

https://www.postgresql.org/message-id/flat/c2ee39152957af339ae6f3e851aef09930dd2faf(dot)camel(at)credativ(dot)de

registered in the CF here: https://commitfest.postgresql.org/34/2918/

though I see now that it isn't trying to explicitly deal with the
CREATEROLE bit (which I had understood from some other discussion was a
topic of interest to the author), but is definitely caught up in the
discussion about who is allowed to set what GUCs, and therefore still
seems rather related to me.

> Maybe this can be solved from the other end? Like, as opposed to
> working by exception and saying, "well, everything but superusers,"
> maybe we need to explicitly declare who is included. Like, perhaps we
> could somehow represent the fact that role A has super-powers with
> respect to roles B, C, D, and any future roles that A may create, but
> not other roles that exist on the system, or something of that sort?

Isn't this exactly what having the 'admin' option on a role is? You're
GRANT'd that role and further are allowed to then GRANT that role to
other roles. Being a member of that role means you're considered to
have 'ownership' level rights for all the objects that that role owns
too.

Maybe also need to have some condition around "you can only set
attributes on roles which you already have", or maybe we need to invent
'admin' options for each of the role attributes if we think it needs to
be more granular. The only other thing I can think of is that we should
also contemplate what to do with things like CONNECTION LIMIT, VALID
UNTIL, perhaps others. Those aren't currently allowed to be modified by
a role who has 'admin' rights on another role and so maybe we make those
require the new 'pg_manage_roles' predefined role and the 'admin' option
on a given role to be set.

I'm not against the idea of inventing something new here too... but the
'admin' option sure looks like what we're talking about here.

Thanks,

Stephen