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

> On Aug 23, 2021, at 11:13 AM, 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.

I thought we were trying to create a set of roles which could cumulatively do everything *inside the sandbox* that superuser can do, but which cannot escape the sandbox. I would think this pg_manage_database_objects role would be reasonable in the context of that effort.

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

The implementation does not allow pg_manage_database_objects to mess with objects that are owned by a role which satisfies superuser_arg(). If you are renting out a database to a tenant and change the ownership of stuff to a non-superuser, then you get what you get. But why would you do that?

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

Please provide steps to reproduce this issue. Assume that a database is initialized and that everything is owned by the system. A "tenant" role is created and granted pg_manage_database_objects, and other non-superuser roles are created. Now, what exactly can "tenant" do that you find objectionable?

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

Well, pg_manage_database_objects has no special ability to create or drop roles. I thought separating those powers made more sense than grouping them together. We can have a new role for doing what you say, but that seems redundant with CREATEROLE. I didn't want this patch set to be bogged down waiting for a consensus on how to change the CREATEROLE privilege.

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

I'm confused. This patch set doesn't come within a country mile of CREATEROLE. Why should this patch set have to coordinate with that one? I'm not arguing with you -- merely asking what I'm misunderstanding?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company