Re: Granting control of SUSET gucs to non-superusers

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> 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.

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

Hmm, is there really any point in that? We already have roles
like "pg_write_server_files" and "pg_execute_server_program",
which allow trivial escalation to superuser if one wishes,
but are still useful as being roles you're a bit less likely
to break your database with accidentally than running as full
superuser.

So ISTM that "pg_set_superuser_parameters" could be treated as
being one of those same sorts of roles that you don't give out
to untrusted people, and then we don't have to worry about
exactly which GUCs might be exactly how dangerous to whom.

If we try to define it as being some lesser level of
privilege than that, I'm afraid we will spend lots of
not-very-productive time trying to classify the security
threats from different GUCs ... and they all have *some*
security issue involved, or they wouldn't be restricted in
the first place. Plus, I'm not looking forward to having
to issue CVEs when we realize we misclassified something.

regards, tom lane