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

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.

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

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?

--
Robert Haas
EDB: http://www.enterprisedb.com