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

> On May 13, 2021, at 12:30 PM, Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com> wrote:
>
>
>
>> On May 13, 2021, at 12:18 PM, Jacob Champion <pchampion(at)vmware(dot)com> wrote:
>>
>> On Thu, 2021-05-13 at 11:42 -0700, Mark Dilger wrote:
>>> The distinction that Theme+Security would make is that capabilities
>>> can be categorized by the area of the system:
>>> -- planner
>>> -- replication
>>> -- logging
>>> ...
>>> but also by the security implications of what is being done:
>>> -- host
>>> -- schema
>>> -- network
>> Since the "security" buckets are being used for both proposals -- how
>> you would deal with overlap between them? When a GUC gives you enough
>> host access to bleed into the schema and network domains, does it get
>> all three attributes assigned to it, and thus require membership in all
>> three roles?
>
> Yeah, from a security standpoint, pg_host_admin basically gives everything away. I doubt service providers would give the "host" or "network" security to their tenants, but they would probably consider giving "schema" security to the tenants.
>
>> (Thanks, by the way, for this thread -- I think a "capability system"
>> for superuser access is a great idea.)
>
> I am happy to work on this, and appreciate feedback....

Please find attached five new patches each intended to reduce the number of administrative tasks that require superuser privileges.

v3-0001 adds a new pg_logical_replication role with permission to manage publications and subscriptions.

v3-0002 adds a new pg_host_security role with permission to manage extensions, event triggers and tablespaces.

v3-0003 adds a new pg_network_security role with pemission to manage foreign servers and data wrappers.

v3-0004 adds a new pg_database_security role with permission to perform many actions that would otherwise require superuser, so long as those actions do not compromise the security of the host or network. This role, along with pg_logical_replication, is intended to be safe to delegate to the tenant of a database provided as a service.

v3-0005 associates all GUC variables with security roles and allows both SET and ALTER SYSTEM SET on those variables by users belonging to the necessary security role(s). This patch extends the significance of the pg_host_security, pg_network_security, and pg_database_security roles added in the previous patches, as those roles are associated with GUC variables that implicate the same security concerns.

These patches likely still need some adjustment, as there are a large number of security relevant permission decisions in here which some hackers may debate, but I think these are mature enough to solicit feedback.

I admit right upfront that the regression tests guc_priv_admin and guc_priv_tenant in v3-0005 could be made to cover a subset of GUC variables rather than the full set of them, but I'm delaying pruning them down until I know if the rest of the patches are basically acceptable.