Re: Delegating superuser tasks to new security roles

On 2021-05-26 05:33, Mark Dilger wrote:
>> 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.

Thanks for working on this topic, I appreciate it!

BTW, do these patches enable non-superusers to create user with
bypassrls?
Since I failed to apply the patches and didn't test them,
I may have overlooked something but I didn't find the
corresponding codes.

Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION