Re: Granting control of SUSET gucs to non-superusers

> On May 13, 2021, at 10:41 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
> Greetings,
>
> * Mark Dilger (mark.dilger@enterprisedb.com) wrote:
>>> On May 12, 2021, at 12:58 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> - Group things by which section of postgresql.conf they're in, and
>>> then further restrict some of them as security-sensitive. This is
>>> reasonably close to what you've got, but not exactly what you've got.
>>> One issue is that it risks separating things that are in practice not
>>> useful to separate, creating more predefined roles to manage than we
>>> really need. With your division, what are the chances that someone
>>> wants to grant pg_stats_settings but not pg_maintenance_settings or
>>> the other way around?
>>
>> I think our conversation off-list was worth enough to reiterate here....
>>
>> When classifying GUC variables, the philosophy of classification needs to be consistent and easily understandable so
that,among other considerations, all future GUC variables have a reasonable chance of be classified correctly by their
patchauthors and committers.  The patch I posted falls short in this regard.  You and I discussed two organizational
options:
>>
>> Theme+Security:
>>  - security is considered as falling into three groupings:  (a) host security, which includes files and permissions,
runningexternal commands, etc., (b) network security, which includes all connection options and authentications, and
(c)schema security, which includes database internal object security like rls, object ownership, etc. 
>>  - theme is based on the GUC config_group, either having one theme per config_group, or basing the theme on the
prefixof the config_group such that, for example, QUERY_TUNING_METHOD, QUERY_TUNING_COST, QUERY_TUNING_GEQO, and
QUERY_TUNING_OTHERcould all be in one theme named "pg_query_tuning". 
>>
>> Admin+Security
>>  - security works the same as Theme+Security
>>  - a pg_admin role is required to set all non PGC_USERSET gucs, but some of those gucs *also* require one or more of
thesecurity roles 
>>
>> The Theme+Security approach might be insufficient for extensibility, given that 3rd-party custom GUCs might not have
acorresponding theme.  The Admin+Security approach appears better in this regard. 
>>
>> Admin+Security seems sufficient, in conjunction with Chapman's idea of extensible check_hooks.
>
> I'm not entirely following what the difference here is that's being
> suggested.  At a high level, I like the idea of defining capabilities
> along the lines of "host security", "network security", "schema
> security".  I do think we should consider maybe breaking those down a
> bit more but I don't know that we'd really need to have much more.

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

So if a GUC variable is related to replication, but also impacts the security of libpq connections to the server, then
you'dneed to be a member of both pg_replication_role and pg_network_admin.  If another GUC variable is related to
logging,but also impacts the file permissions or ownership of the log file, you'd need to be a member of both
pg_logging_roleand pg_host_admin. 


The Admin+Security idea would instead say that to SET any GUC variable other than PGC_USERSET gucs, or to ALTER SYSTEM
SETon any GUC variable, you'd need to be a member of pg_admin_role.  If the GUC variable also impacts host security
(filepermissions, etc.) you'd have to also be a member of pg_host_admin, or if it impacts security of connections to
theserver, you'd have to also be a member of pg_network_admin. 

I'm just making up names like "pg_replication_role" and such for illustration.

> In general, I'm not really keen on such a generic role as 'pg_admin'.  I
> would have thought we'd have a matrix where we have categories for GUCs
> and roles which are allowed to modify those categories, with the
> additional requirement of having host/network/schema capability for
> those GUCs which imply that level of access.

Yeah, that's the Theme+Security idea, or at least it seems so to me.

> Having the low-level
> capabilities plus the GUC groups would seem likely to cover most cases
> that 3rd party extensions might wish for, in a pretty granular way,
> though we could always consider adding more in the future.

I'm imagining the security concerns splitting three ways, and the themes splitting on the order of ten different ways.
Wecan quibble over how fine grained the themes should be.  There is a simplicity argument to having them be one-to-one
withthe config_group. 

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company