Hi,
Am Mittwoch, den 14.07.2021, 21:29 +0530 schrieb vignesh C:
> On Wed, Apr 7, 2021 at 5:23 PM Michael Banck <michael.ba...@credativ.de>
> wrote:
> > 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.
>
> The patch does not apply on Head anymore, could you rebase and post a
> patch. I'm changing the status to "Waiting for Author".
Thanks for letting me know, I've attached a rebased v4 of this patch, no
other changes.
Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
E-Mail: 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, Geoff Richardson, Peter
Lilley
Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz
From 144306ed2835d33b2c1b2f5c7b5c247c41d80df0 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 v4] 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 | 61 +++++++++++++++++++------------
src/include/catalog/pg_authid.dat | 5 +++
src/include/utils/guc.h | 1 +
6 files changed, 62 insertions(+), 30 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 aa69821be4..652f8aca21 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -876,7 +876,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)
{
@@ -887,7 +888,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 a2e0f8de7e..b0c5057cfb 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -693,6 +693,7 @@ const char *const GucContext_Names[] =
/* PGC_SU_BACKEND */ "superuser-backend",
/* PGC_BACKEND */ "backend",
/* PGC_SUSET */ "superuser",
+ /* PGC_ADMINSET */ "administrator",
/* PGC_USERSET */ "user"
};
@@ -1368,7 +1369,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
},
@@ -1421,7 +1422,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
},
@@ -1466,7 +1467,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
},
@@ -1475,7 +1476,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
},
@@ -1484,7 +1485,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
},
@@ -1493,7 +1494,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
},
@@ -1515,7 +1516,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 "
@@ -1526,7 +1527,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
},
@@ -1535,7 +1536,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
},
@@ -1632,7 +1633,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
},
@@ -2942,7 +2943,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."),
@@ -2955,7 +2956,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."),
@@ -2979,7 +2980,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
@@ -3458,7 +3459,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
@@ -3795,7 +3796,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).")
},
@@ -3805,9 +3806,10 @@ static struct config_real ConfigureNamesReal[] =
},
{
- {"log_transaction_sample_rate", PGC_SUSET, LOGGING_WHEN,
- gettext_noop("Sets the fraction of transactions from which to log all statements."),
- gettext_noop("Use a value between 0.0 (never log) and 1.0 (log all "
+ {"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 "
"statements for all transactions).")
},
&log_xact_sample_rate,
@@ -4700,7 +4702,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
},
@@ -4710,7 +4712,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.")
@@ -4721,7 +4723,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.")
@@ -4732,7 +4734,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
},
@@ -4813,7 +4815,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
},
@@ -7454,6 +7456,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 3da68016b6..1702b57f48 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 a7c3a4958e..0b97d72bbc 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
