Re: [PATCH] New default role allowing to change per-role/database settings

On Thu, Dec 31, 2020 at 6:16 PM Michael Banck <michael(dot)banck(at)credativ(dot)de>
wrote:

> Hi,
>
> in today's world, some DBAs have no superuser rights, but we can
> delegate them additional powers like CREATEDB or the pg_monitor default
> role etc. Usually, the DBA can also view the database logs, either via
> shell access or some web interface.
>
> One thing that I personally find lacking is that it is not possible to
> change role-specific log settings (like log_statement = 'mod' for a
> security sensitive role) without being SUPERUSER as their GUC context is
> "superuser". This makes setup auditing much harder if there is no
> SUPERUSER access, also pgaudit then only allows to configure object-
> based auditing. Amazon RDS e.g. has patched Postgres to allow the
> cluster owner/pseudo-superuser `rds_superuser' to change those log
> settings that define what/when we log something, while keeping the
> "where to log" entries locked down.
>
> The attached patch introduces a new guc context "administrator" (better
> names/bikeshedding for this welcome) that is meant as a middle ground
> between "superuser" and "user". It also adds a new default role
> "pg_change_role_settings" (better names also welcome) that can be
> granted to DBAs so that they can change the "administrator"-context GUCs
> on a per-role (or per-database) basis. Whether the latter should be
> included is maybe debatable, but I added both on the basis that they are
> the same "source".
>
> The initial set of "administrator" GUCs are all current GUCs with
> "superuser" context from these categories:
>
> * Reporting and Logging / What to Log
> * Reporting and Logging / When to
> Log
> * Statistics / Query and Index Statistics Collector
> * Statistics /
> Monitoring
>
> Of course, further categories (or particular GUCs) could be added now or
> in the future, e.g. RDS also patches the following GUCs in their v12
> offering:
>
> * temp_file_limit
> * session_replication_role
>
> RDS does *not* patch log_transaction_sample_rate from "Reporting and
> Logging / When to Log", but that might be more of an oversight than a
> security consideration, or does anybody see a big problem with that
> (compared to the others in that set)?
>
> I initially pondered not introducing a new context but just filtering on
> category, but as categories seem to be only descriptive in guc.c and not
> used for any policy decisions so far, I have abandoned this pretty
> quickly.
>
>
> Thoughts?
>
> 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, Jörg Folz, Sascha Heuer
>
> Unser Umgang mit personenbezogenen Daten unterliegt
> folgenden Bestimmungen: https://www.credativ.de/datenschutz
>

The patch (0001-Add-new-PGC_ADMINSET-guc-context-and-pg_change_role_.patch)
does
not apply successfully and has some hunks failed.

http://cfbot.cputube.org/patch_32_2918.log

1 out of 23 hunks FAILED -- saving rejects to file
src/backend/utils/misc/guc.c.rej
patching file src/include/catalog/catversion.h
Hunk #1 FAILED at 53.
1 out of 1 hunk FAILED -- saving rejects to file
src/include/catalog/catversion.h.rej
patching file src/include/catalog/pg_authid.dat

Can we get a rebase?

I am marking the patch "Waiting on Author".

--
Ibrar Ahmed