> On May 13, 2021, at 10:41 AM, Stephen Frost <sfr...@snowman.net> wrote:
>
> Greetings,
>
> * Mark Dilger (mark.dil...@enterprisedb.com) wrote:
>>> On May 12, 2021, at 12:58 PM, Robert Haas <robertmh...@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 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.
>
> 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'd need 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_role and 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 SET on any GUC variable, you'd need
to be a member of pg_admin_role. If the GUC variable also impacts host
security (file permissions, etc.) you'd have to also be a member of
pg_host_admin, or if it impacts security of connections to the server, 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. We can quibble over how fine
grained the themes should be. There is a simplicity argument to having them be
one-to-one with the config_group.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
