Re: Granting control of SUSET gucs to non-superusers

> On May 12, 2021, at 12:58 PM, Robert Haas <robertmhaas(at)gmail(dot)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 patch authors 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, running external 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 prefix of the config_group such that, for example, QUERY_TUNING_METHOD, QUERY_TUNING_COST, QUERY_TUNING_GEQO, and QUERY_TUNING_OTHER could 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 the security roles

The Theme+Security approach might be insufficient for extensibility, given that 3rd-party custom GUCs might not have a corresponding theme. The Admin+Security approach appears better in this regard.

Admin+Security seems sufficient, in conjunction with Chapman's idea of extensible check_hooks.

Thoughts?

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