Re: Granting control of SUSET gucs to non-superusers

Hi,

On Fri, Apr 30, 2021 at 04:19:22PM -0700, Mark Dilger 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.
>
> 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.
>
> I have a WIP patch that I'm not attaching, but if I get any feedback,
> I might be able to adjust the patch before the first version posted.
> The basic idea is that it allows things like:
>
> GRANT pg_set_stats_monitoring TO tenant_role;
>
> And then tenant_role could, for example
>
> SET log_parser_stats TO off;

Just saying, I've proposed something very similar, albeit for a narrower
scope (mostly the Reporting and Logging category) here:
https://www.postgresql.org/message-id/flat/c2ee39152957af339ae6f3e851aef09930dd2faf(dot)camel(at)credativ(dot)de

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael(dot)banck(at)credativ(dot)de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Sascha Heuer

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz