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

> On Jul 22, 2021, at 1:01 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> It's awkward. I think that we can't afford to create a separate
> predefined role for every single thing that someone could
> theoretically want to sever, because then we'll have a zillion of them
> and it will be unmaintainable. So the idea was to try to break up
> everything someone might want to do either via DDL or by setting GUCs
> into one of three categories: internal to the database
> (pg_database_security), facing outward toward the network
> (pg_network_security), and facing inward toward the host
> (pg_host_security). If we didn't have any predefined security-related
> roles already, this would still have complications, but as things
> stand it has more, because as you point out, pg_read_server_files
> overlaps with pg_host_security. But what do we do about that?

I gave up on the idea of splitting all superuser functions into three roles.

Patch v5-0001 refactors the guc code to allow non-superuser roles to be associated with guc variables. Any such role can then set the variable, including via "alter system set". The patch stops short of creating any new roles or assigning any roles to any guc variable.

Patches v5-0002 through v5-0005 create four new roles for managing host resource settings, vacuum settings, autovacuum settings, and logging settings. That last one excludes "where to log" settings, because we don't want the role to be able to write to arbitrary locations on the server. Remaining guc variables not in these four categories continue to belong to the superuser.

Patches v5-0006 and v5-0007 allow non-superusers to own event triggers, and limit the event triggers to only running for events triggered by roles that the event trigger owner belongs to. This is backward compatible, because event triggers have historically belonged only to superusers, and superusers have implicit membership in all groups.

Patches v5-0008 through v5-0010 allow non-superusers to own subscriptions while restricting the tablesync and apply workers to only work on tables that the subscription owner has permissions on. This is almost backward compatible, because subscriptions have historically belonged only to superusers, as above, except for unlikely scenarios where superusers have given ownership to non-superusers. In those cases, the new code will refuse to apply in situations where the old code would blindly apply changes. Does anybody see a problem with this?

Patch v5-0011 is a bug fix posted elsewhere that hasn't been committed yet but which must be committed in preparation for v5-0012.

Patch v5-0012 creates a new role, pg_manage_database_objects, which can do anything with an object that the owner could do with it, as long as the owner is not a superuser. This role is intended as a "tenant" role, and is in some sense a less powerful replacement for the pg_database_security role previously proposed.

I doubt that I will create any replacement for the pg_host_security role previously proposed, as I think that role is just synonymous with "superuser", so it serves no purpose.

I am uncertain about creating a role similar to the pg_network_security role previously proposed, as the changes to how publications and subscriptions work in patches v5-0008 through v5-0010 may be enough. In any event, I'd like feedback on those patches before designing one or more additional roles for this.