[PATCH] New default role allowing to change per-role/database settings - Mailing list pgsql-hackers

From Michael Banck
Subject [PATCH] New default role allowing to change per-role/database settings
Date
Msg-id c2ee39152957af339ae6f3e851aef09930dd2faf.camel@credativ.de
Whole thread Raw
List pgsql-hackers
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.banck@credativ.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

Attachment

pgsql-hackers by date:

Previous
From: Masahiko Sawada
Date:
Subject: Re: Dependency isn't created between extension and schema
Next
From: Alvaro Herrera
Date:
Subject: support for MERGE