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

From: Michael Banck <michael(dot)banck(at)credativ(dot)de>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: [PATCH] New default role allowing to change per-role/database settings
Date: 2020-12-31 13:15:37
Message-ID: c2ee39152957af339ae6f3e851aef09930dd2faf.camel@credativ.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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
* Statistics / Query and Index Statistics Collector
* Statistics /

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

* 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



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

Attachment Content-Type Size
0001-Add-new-PGC_ADMINSET-guc-context-and-pg_change_role_.patch text/x-patch 12.0 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2020-12-31 13:47:36 support for MERGE
Previous Message Masahiko Sawada 2020-12-31 11:05:02 Re: Dependency isn't created between extension and schema