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.dilger@enterprisedb.com> wrote:
>> 
>> 
>> 
>>> On May 13, 2021, at 12:18 PM, Jacob Champion <pchampion@vmware.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