Re: Granting control of SUSET gucs to non-superusers

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.

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.  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.

Thanks,

Stephen