> On Jul 22, 2021, at 1:01 PM, Robert Haas <robertmhaas@gmail.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
canthen set the variable, including via "alter system set". The patch stops short of creating any new roles or
assigningany 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
ableto write to arbitrary locations on the server. Remaining guc variables not in these four categories continue to
belongto the superuser.
Patches v5-0006 and v5-0007 allow non-superusers to own event triggers, and limit the event triggers to only running
forevents triggered by roles that the event trigger owner belongs to. This is backward compatible, because event
triggershave 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
workersto only work on tables that the subscription owner has permissions on. This is almost backward compatible,
becausesubscriptions have historically belonged only to superusers, as above, except for unlikely scenarios where
superusershave given ownership to non-superusers. In those cases, the new code will refuse to apply in situations
wherethe 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
forv5-0012.
Patch v5-0012 creates a new role, pg_manage_database_objects, which can do anything with an object that the owner could
dowith it, as long as the owner is not a superuser. This role is intended as a "tenant" role, and is in some sense a
lesspowerful 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
justsynonymous 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
publicationsand subscriptions work in patches v5-0008 through v5-0010 may be enough. In any event, I'd like feedback
onthose patches before designing one or more additional roles for this.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company