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

Hi,

Am Montag, den 08.03.2021, 20:54 +0500 schrieb Ibrar Ahmed:
> On Thu, Dec 31, 2020 at 6:16 PM Michael Banck <michael(dot)banck(at)credativ(dot)de> wrote:
> > 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
>
> The patch (0001-Add-new-PGC_ADMINSET-guc-context-and-pg_change_role_.patch) does
> not apply successfully and has some hunks failed.

Thanks for letting me know.

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

Please find attached the rebase; I've removed the catversion hunk as I
believe it is customary to leave that to committers.

> I am marking the patch "Waiting on Author".

I've put that back to "Needs Review".

Cheers,

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