Hi,
Am Dienstag, den 06.04.2021, 15:37 +0200 schrieb Michael Banck:
> Am Montag, den 05.04.2021, 14:33 -0400 schrieb Stephen Frost:
> > Should drop the 'DEFAULT_' to match the others since the rename to
> > 'predefined' roles went in.
>
> Right, will send a rebased patch ASAP.
Here's a rebased version that also removes the ALTER DATABASE SET
capability from this new predefined role and adds some documentation.
Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email:
michael.ba...@credativ.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
From a581d62db1751ba94d800ea2c6c6dfb04e4bcbf7 Mon Sep 17 00:00:00 2001
From: Michael Banck <michael.ba...@credativ.de>
Date: Tue, 15 Dec 2020 20:53:59 +0100
Subject: [PATCH v3] Add new PGC_ADMINSET guc context and
pg_change_role_settings predefined role.
This commit introduces `administrator' as a middle ground between `superuser'
and `user' for guc contexts.
Those settings initially include all previously `superuser'-context settings
from the 'Reporting and Logging' ('What/When to Log' but not 'Where to Log')
and both 'Statistics' categories. Further settings could be added in follow-up
commits.
Also, a new predefined role pg_change_role_settings is introduced. This allows
administrators (that are not superusers) that have been granted this role to
change the above PGC_ADMINSET settings on a per-role basis like "ALTER ROLE
[...] SET log_statement".
The rationale is to allow certain settings that affect logging to be changed
in setups where the DBA has access to the database log, but is not a full
superuser.
---
doc/src/sgml/ref/alter_role.sgml | 11 +++---
doc/src/sgml/user-manag.sgml | 7 ++++
src/backend/commands/user.c | 7 ++--
src/backend/utils/misc/guc.c | 56 +++++++++++++++++++------------
src/include/catalog/pg_authid.dat | 5 +++
src/include/utils/guc.h | 1 +
6 files changed, 59 insertions(+), 28 deletions(-)
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 5aa5648ae7..d332c29c71 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -115,11 +115,12 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
<para>
Superusers can change anyone's session defaults. Roles having
- <literal>CREATEROLE</literal> privilege can change defaults for non-superuser
- roles. Ordinary roles can only set defaults for themselves.
- Certain configuration variables cannot be set this way, or can only be
- set if a superuser issues the command. Only superusers can change a setting
- for all roles in all databases.
+ <literal>CREATEROLE</literal> privilege or which are a member of the
+ <literal>pg_change_role_settings</literal> predefined role can change
+ defaults for non-superuser roles. Ordinary roles can only set defaults for
+ themselves. Certain configuration variables cannot be set this way, or can
+ only be set if a superuser issues the command. Only superusers can change a
+ setting for all roles in all databases.
</para>
</refsect1>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index fe0bdb7599..48a7aa8cc2 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -541,6 +541,13 @@ DROP ROLE doomed_role;
<entry>Read all configuration variables, even those normally visible only to
superusers.</entry>
</row>
+ <row>
+ <entry>pg_change_role_settings</entry>
+ <entry>Allow changing role-based settings for all non-superuser roles
+ with <literal>ALTER ROLE <replaceable>rolename</replaceable> SET
+ <replaceable>varname</replaceable> TO
+ <replaceable>value</replaceable></literal>.</entry>
+ </row>
<row>
<entry>pg_read_all_stats</entry>
<entry>Read all pg_stat_* views and use various statistics related extensions,
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index a8c5188ebc..3039c0d69c 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -936,7 +936,8 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
/*
* To mess with a superuser you gotta be superuser; else you need
- * createrole, or just want to change your own settings
+ * createrole, the pg_change_role_settings default role, or just want
+ * to change your own settings.
*/
if (roleform->rolsuper)
{
@@ -947,7 +948,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt)
}
else
{
- if (!have_createrole_privilege() && roleid != GetUserId())
+ if (!have_createrole_privilege() &&
+ roleid != GetUserId() &&
+ !is_member_of_role(GetUserId(), ROLE_PG_CHANGE_ROLE_SETTINGS))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied")));
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index c9c9da85f3..3a453457b7 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -659,6 +659,7 @@ const char *const GucContext_Names[] =
/* PGC_SU_BACKEND */ "superuser-backend",
/* PGC_BACKEND */ "backend",
/* PGC_SUSET */ "superuser",
+ /* PGC_ADMINSET */ "administrator",
/* PGC_USERSET */ "user"
};
@@ -1362,7 +1363,7 @@ static struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
{
- {"log_replication_commands", PGC_SUSET, LOGGING_WHAT,
+ {"log_replication_commands", PGC_ADMINSET, LOGGING_WHAT,
gettext_noop("Logs each replication command."),
NULL
},
@@ -1414,7 +1415,7 @@ static struct config_bool ConfigureNamesBool[] =
},
{
- {"log_duration", PGC_SUSET, LOGGING_WHAT,
+ {"log_duration", PGC_ADMINSET, LOGGING_WHAT,
gettext_noop("Logs the duration of each completed SQL statement."),
NULL
},
@@ -1459,7 +1460,7 @@ static struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
{
- {"log_parser_stats", PGC_SUSET, STATS_MONITORING,
+ {"log_parser_stats", PGC_ADMINSET, STATS_MONITORING,
gettext_noop("Writes parser performance statistics to the server log."),
NULL
},
@@ -1468,7 +1469,7 @@ static struct config_bool ConfigureNamesBool[] =
check_stage_log_stats, NULL, NULL
},
{
- {"log_planner_stats", PGC_SUSET, STATS_MONITORING,
+ {"log_planner_stats", PGC_ADMINSET, STATS_MONITORING,
gettext_noop("Writes planner performance statistics to the server log."),
NULL
},
@@ -1477,7 +1478,7 @@ static struct config_bool ConfigureNamesBool[] =
check_stage_log_stats, NULL, NULL
},
{
- {"log_executor_stats", PGC_SUSET, STATS_MONITORING,
+ {"log_executor_stats", PGC_ADMINSET, STATS_MONITORING,
gettext_noop("Writes executor performance statistics to the server log."),
NULL
},
@@ -1486,7 +1487,7 @@ static struct config_bool ConfigureNamesBool[] =
check_stage_log_stats, NULL, NULL
},
{
- {"log_statement_stats", PGC_SUSET, STATS_MONITORING,
+ {"log_statement_stats", PGC_ADMINSET, STATS_MONITORING,
gettext_noop("Writes cumulative performance statistics to the server log."),
NULL
},
@@ -1508,7 +1509,7 @@ static struct config_bool ConfigureNamesBool[] =
#endif
{
- {"track_activities", PGC_SUSET, STATS_COLLECTOR,
+ {"track_activities", PGC_ADMINSET, STATS_COLLECTOR,
gettext_noop("Collects information about executing commands."),
gettext_noop("Enables the collection of information on the currently "
"executing command of each session, along with "
@@ -1519,7 +1520,7 @@ static struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
{
- {"track_counts", PGC_SUSET, STATS_COLLECTOR,
+ {"track_counts", PGC_ADMINSET, STATS_COLLECTOR,
gettext_noop("Collects statistics on database activity."),
NULL
},
@@ -1528,7 +1529,7 @@ static struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
{
- {"track_io_timing", PGC_SUSET, STATS_COLLECTOR,
+ {"track_io_timing", PGC_ADMINSET, STATS_COLLECTOR,
gettext_noop("Collects timing statistics for database I/O activity."),
NULL
},
@@ -1625,7 +1626,7 @@ static struct config_bool ConfigureNamesBool[] =
#endif
{
- {"log_lock_waits", PGC_SUSET, LOGGING_WHAT,
+ {"log_lock_waits", PGC_ADMINSET, LOGGING_WHAT,
gettext_noop("Logs long lock waits."),
NULL
},
@@ -2917,7 +2918,7 @@ static struct config_int ConfigureNamesInt[] =
},
{
- {"log_min_duration_sample", PGC_SUSET, LOGGING_WHEN,
+ {"log_min_duration_sample", PGC_ADMINSET, LOGGING_WHEN,
gettext_noop("Sets the minimum execution time above which "
"a sample of statements will be logged."
" Sampling is determined by log_statement_sample_rate."),
@@ -2930,7 +2931,7 @@ static struct config_int ConfigureNamesInt[] =
},
{
- {"log_min_duration_statement", PGC_SUSET, LOGGING_WHEN,
+ {"log_min_duration_statement", PGC_ADMINSET, LOGGING_WHEN,
gettext_noop("Sets the minimum execution time above which "
"all statements will be logged."),
gettext_noop("Zero prints all queries. -1 turns this feature off."),
@@ -2954,7 +2955,7 @@ static struct config_int ConfigureNamesInt[] =
},
{
- {"log_parameter_max_length", PGC_SUSET, LOGGING_WHAT,
+ {"log_parameter_max_length", PGC_ADMINSET, LOGGING_WHAT,
gettext_noop("When logging statements, limit logged parameter values to first N bytes."),
gettext_noop("-1 to print values in full."),
GUC_UNIT_BYTE
@@ -3429,7 +3430,7 @@ static struct config_int ConfigureNamesInt[] =
},
{
- {"log_temp_files", PGC_SUSET, LOGGING_WHAT,
+ {"log_temp_files", PGC_ADMINSET, LOGGING_WHAT,
gettext_noop("Log the use of temporary files larger than this number of kilobytes."),
gettext_noop("Zero logs all files. The default is -1 (turning this feature off)."),
GUC_UNIT_KB
@@ -3766,7 +3767,7 @@ static struct config_real ConfigureNamesReal[] =
},
{
- {"log_statement_sample_rate", PGC_SUSET, LOGGING_WHEN,
+ {"log_statement_sample_rate", PGC_ADMINSET, LOGGING_WHEN,
gettext_noop("Fraction of statements exceeding log_min_duration_sample to be logged."),
gettext_noop("Use a value between 0.0 (never log) and 1.0 (always log).")
},
@@ -3776,7 +3777,7 @@ static struct config_real ConfigureNamesReal[] =
},
{
- {"log_transaction_sample_rate", PGC_SUSET, LOGGING_WHEN,
+ {"log_transaction_sample_rate", PGC_ADMINSET, LOGGING_WHEN,
gettext_noop("Sets the fraction of transactions to log for new transactions."),
gettext_noop("Logs all statements from a fraction of transactions. "
"Use a value between 0.0 (never log) and 1.0 (log all "
@@ -4662,7 +4663,7 @@ static struct config_enum ConfigureNamesEnum[] =
},
{
- {"log_error_verbosity", PGC_SUSET, LOGGING_WHAT,
+ {"log_error_verbosity", PGC_ADMINSET, LOGGING_WHAT,
gettext_noop("Sets the verbosity of logged messages."),
NULL
},
@@ -4672,7 +4673,7 @@ static struct config_enum ConfigureNamesEnum[] =
},
{
- {"log_min_messages", PGC_SUSET, LOGGING_WHEN,
+ {"log_min_messages", PGC_ADMINSET, LOGGING_WHEN,
gettext_noop("Sets the message levels that are logged."),
gettext_noop("Each level includes all the levels that follow it. The later"
" the level, the fewer messages are sent.")
@@ -4683,7 +4684,7 @@ static struct config_enum ConfigureNamesEnum[] =
},
{
- {"log_min_error_statement", PGC_SUSET, LOGGING_WHEN,
+ {"log_min_error_statement", PGC_ADMINSET, LOGGING_WHEN,
gettext_noop("Causes all statements generating error at or above this level to be logged."),
gettext_noop("Each level includes all the levels that follow it. The later"
" the level, the fewer messages are sent.")
@@ -4694,7 +4695,7 @@ static struct config_enum ConfigureNamesEnum[] =
},
{
- {"log_statement", PGC_SUSET, LOGGING_WHAT,
+ {"log_statement", PGC_ADMINSET, LOGGING_WHAT,
gettext_noop("Sets the type of statements logged."),
NULL
},
@@ -4775,7 +4776,7 @@ static struct config_enum ConfigureNamesEnum[] =
},
{
- {"track_functions", PGC_SUSET, STATS_COLLECTOR,
+ {"track_functions", PGC_ADMINSET, STATS_COLLECTOR,
gettext_noop("Collects function-level statistics on database activity."),
NULL
},
@@ -7341,6 +7342,19 @@ set_config_option(const char *name, const char *value,
return 0;
}
break;
+ case PGC_ADMINSET:
+ if ((context == PGC_USERSET &&
+ !((source == PGC_S_DATABASE_USER || source == PGC_S_TEST) &&
+ is_member_of_role(GetUserId(), ROLE_PG_CHANGE_ROLE_SETTINGS))) ||
+ context == PGC_BACKEND)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to set parameter \"%s\"",
+ name)));
+ return 0;
+ }
+ break;
case PGC_USERSET:
/* always okay */
break;
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index f78802e41f..94408756d1 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -79,5 +79,10 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '8801', oid_symbol => 'ROLE_PG_CHANGE_ROLE_SETTINGS',
+ rolname => 'pg_change_role_settings', rolsuper => 'f', rolinherit => 't',
+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+ rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+ rolpassword => '_null_', rolvaliduntil => '_null_' },
]
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 5004ee4177..6aa7319e8b 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -73,6 +73,7 @@ typedef enum
PGC_SU_BACKEND,
PGC_BACKEND,
PGC_SUSET,
+ PGC_ADMINSET,
PGC_USERSET
} GucContext;
--
2.20.1
