Re: Granting control of SUSET gucs to non-superusers

Greetings,

On Fri, Apr 30, 2021 at 19:19 Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com>
wrote:

> PostgreSQL defines a number of GUCs that can only be set by superusers. I
> would like to support granting privileges on subsets of these to
> non-superuser roles, inspired by Stephen Frost's recent work on
> pg_read_all_data and pg_write_all_data roles.

There’s been some effort started in this direction which I was working on
(see the patches about an “admin” role and set of GUCs). I have been
meaning to get back to that but the specific concern I had was about coming
up with how to define the proper set of GUCs.

The specific use case motivating this work is that of a PostgreSQL service
> provider. The provider guarantees certain aspects of the service, such as
> periodic backups, replication, uptime, availability, etc., while making no
> guarantees of other aspects, such as performance associated with the design
> of the schema or the queries executed. The provider should be able to
> grant to the tenant privileges to set any GUC which cannot be used to
> "escape the sandbox" and interfere with the handful of metrics being
> guaranteed. Given that the guarantees made by one provider may differ from
> those made by another, the exact set of GUCs which the provider allows the
> tenant to control may differ.
>
> By my count, there are currently 50 such GUCs, already broken down into 15
> config groups. Creating a single new role pg_set_all_gucs seems much too
> coarse a control, but creating 50 new groups may be excessive. We could
> certainly debate which GUCs could be used to escape the sandbox vs. which
> ones could not, but I would prefer a design that allows the provider to
> make that determination. The patch I would like to submit would only give
> the provider the mechanism for controlling these things, but would not make
> the security choices for them.
>
> Do folks think it would make sense to create a role per config group?
> Adding an extra 15 default roles seems high to me, but organizing the
> feature this way would make the roles easier to document, because there
> would be a one-to-one correlation between the roles and the config groups.

New predefined roles are relatively inexpensive. That said, whatever sets
we define need to have some meaning to them- one which is reasonably
future-proofed so that we have some idea what category a new GUC would fit
into.

“Can’t be used to gain superuser” may be a sufficiently clear grouping, as
was more or less contemplated by the “admin” approach. If that doesn’t
work though then we need an understanding of what the limits on these
groups are, so we can competently fit new GUCs into these groups (or invent
new ones if a new GUC truly falls outside all existing but I would expect
that to be a rather rare case..).

We may also wish to keep some GUCs superuser only when they really only
make sense to be used in a developer context...

Thanks,

Stephen