v4-0001-Add-enable_alter_system-GUC.patch

From 39e2d2893a37a43b6f4226d01d2fbbc1cea0b8e8 Mon Sep 17 00:00:00 2001
From: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
Date: Fri, 8 Sep 2023 20:25:36 +0200
Subject: [PATCH v4] Add `enable_alter_system` GUC

Introduce the `enable_alter_system` GUC (by default set to `on`). This
GUC can be used as a guard rail to prevent accidental usages of ALTER
SYSTEM in environments where that is not the intended way to make global
configuration changes.

Author: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
Author: Jelte Fennema-Nio <postgres@jeltef.nl>
---
 doc/src/sgml/config.sgml                      | 57 +++++++++++++++++++
 src/backend/utils/errcodes.txt                |  1 +
 src/backend/utils/misc/guc.c                  |  9 +++
 src/backend/utils/misc/guc_tables.c           | 12 ++++
 src/backend/utils/misc/postgresql.conf.sample |  5 ++
 src/include/utils/guc_tables.h                |  4 ++
 src/test/regress/expected/sysviews.out        |  3 +-
 7 files changed, 90 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 65a6e6c4086..b75c3c024ff 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -631,6 +631,63 @@ include_dir 'conf.d'
      </para>
    </sect1>
 
+   <sect1 id="runtime-config-guard-rails">
+    <title>Guard Rails</title>
+    <para>
+     Configuration parameters in this section are intended to act as guard
+     rails to prevent accidental usage of <productname>PostgreSQL</productname>
+     features that can have unexpected consequences in certain environments.
+     These parameters are <emphasis>not</emphasis> security features. A
+     superuser has many ways to work around these guard rails if they really
+     want to do so.
+    </para>
+
+    <variablelist>
+     <varlistentry id="guc-enable-alter-system" xreflabel="enable_alter_system">
+      <term><varname>enable_alter_system</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>enable_alter_system</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        When <literal>enable_alter_system</literal> is set to
+        <literal>off</literal> and error is returned if the <command>ALTER
+        SYSTEM</command> command is used. This parameter can only be set in the
+        <filename>postgresql.conf</filename> file or on the server command
+        line. The default value is <literal>on</literal>.
+       </para>
+
+       <para>
+        This paramater is <emphasis>not</emphasis> a security feature. Instead
+        setting this parameter to <literal>off</literal> should be considered a
+        guard rail against accidental use of <command>ALTER SYSTEM</command>.
+        Such a guard rail can be useful in environments where global
+        configuration changes are made through some outside mechanism.
+        In such environments using <command>ALTER SYSTEM</command> to make
+        configuration changes might appear to work, but then may be discarded
+        at some point in the future when that outside mechanism updates the
+        configuration. The configurations changed by
+        <command>ALTER SYSTEM</command> may also be represented incorrectly in
+        a management dashboard if that dashboard is based on the values in the
+        outside configuration mechanism, rather than values directly from
+        Postgres.
+       </para>
+
+       <para>
+        This parameter only controls the use of <command>ALTER SYSTEM</command>.
+        The settings stored in <filename>postgresql.auto.conf</filename> always
+        take effect, even if <literal>enable_alter_system</literal> is set to
+        <literal>off</literal>. So, changes made to
+        <filename>postgresql.auto.conf</filename> are not rolled back by
+        setting <literal>enable_alter_system</literal> to
+        <literal>off</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </sect1>
+
    <sect1 id="runtime-config-connection">
     <title>Connections and Authentication</title>
 
diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt
index 3250d539e1c..4b1f08a1818 100644
--- a/src/backend/utils/errcodes.txt
+++ b/src/backend/utils/errcodes.txt
@@ -431,6 +431,7 @@ Section: Class 57 - Operator Intervention
 57P03    E    ERRCODE_CANNOT_CONNECT_NOW                                     cannot_connect_now
 57P04    E    ERRCODE_DATABASE_DROPPED                                       database_dropped
 57P05    E    ERRCODE_IDLE_SESSION_TIMEOUT                                   idle_session_timeout
+57P06    E    ERRCODE_GUARD_RAIL                                             guard_rail
 
 Section: Class 58 - System Error (errors external to PostgreSQL itself)
 
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 391866145ee..f6d46936807 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -4563,6 +4563,15 @@ AlterSystemSetConfigFile(AlterSystemStmt *altersysstmt)
 	 */
 	name = altersysstmt->setstmt->name;
 
+	if (!EnableAlterSystem)
+	{
+
+		ereport(ERROR,
+				(errcode(ERRCODE_GUARD_RAIL),
+				 errmsg("ALTER SYSTEM is disabled using enable_alter_system=off"),
+				 errhint("You should probably make global configuration changes through a configuration system outside of PostgreSQL")));
+	}
+
 	switch (altersysstmt->setstmt->kind)
 	{
 		case VAR_SET_VALUE:
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 57d9de4dd92..8ed6cd5b020 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -494,6 +494,7 @@ extern const struct config_enum_entry dynamic_shared_memory_options[];
 /*
  * GUC option variables that are exported from this module
  */
+bool		EnableAlterSystem = true;
 bool		log_duration = false;
 bool		Debug_print_plan = false;
 bool		Debug_print_parse = false;
@@ -677,6 +678,7 @@ const char *const config_group_names[] =
 	[CONN_AUTH_TCP] = gettext_noop("Connections and Authentication / TCP Settings"),
 	[CONN_AUTH_AUTH] = gettext_noop("Connections and Authentication / Authentication"),
 	[CONN_AUTH_SSL] = gettext_noop("Connections and Authentication / SSL"),
+	[GUARD_RAILS] = gettext_noop("Guard Rails"),
 	[RESOURCES_MEM] = gettext_noop("Resource Usage / Memory"),
 	[RESOURCES_DISK] = gettext_noop("Resource Usage / Disk"),
 	[RESOURCES_KERNEL] = gettext_noop("Resource Usage / Kernel Resources"),
@@ -1040,6 +1042,16 @@ struct config_bool ConfigureNamesBool[] =
 		false,
 		NULL, NULL, NULL
 	},
+	{
+		{"enable_alter_system", PGC_SIGHUP, GUARD_RAILS,
+			gettext_noop("Enable ALTER SYSTEM command"),
+			NULL,
+			GUC_DISALLOW_IN_AUTO_FILE
+		},
+		&EnableAlterSystem,
+		true,
+		NULL, NULL, NULL
+	},
 	{
 		{"bonjour", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
 			gettext_noop("Enables advertising the server via Bonjour."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 2244ee52f79..1d2aec2aa54 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -128,6 +128,11 @@
 #ssl_passphrase_command = ''
 #ssl_passphrase_command_supports_reload = off
 
+#------------------------------------------------------------------------------
+# GUARD RAILS
+#------------------------------------------------------------------------------
+
+#enable_alter_system = on
 
 #------------------------------------------------------------------------------
 # RESOURCE USAGE (except WAL)
diff --git a/src/include/utils/guc_tables.h b/src/include/utils/guc_tables.h
index 0a2e274ebb2..8f1b03b2e9b 100644
--- a/src/include/utils/guc_tables.h
+++ b/src/include/utils/guc_tables.h
@@ -60,6 +60,7 @@ enum config_group
 	CONN_AUTH_TCP,
 	CONN_AUTH_AUTH,
 	CONN_AUTH_SSL,
+	GUARD_RAILS,
 	RESOURCES_MEM,
 	RESOURCES_DISK,
 	RESOURCES_KERNEL,
@@ -320,4 +321,7 @@ extern char *config_enum_get_options(struct config_enum *record,
 									 const char *suffix,
 									 const char *separator);
 
+/* GUC reference to enable/disable alter system */
+extern PGDLLIMPORT bool EnableAlterSystem;
+
 #endif							/* GUC_TABLES_H */
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 9be7aca2b8a..a4aa012b36f 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -111,6 +111,7 @@ select count(*) = 0 as ok from pg_stat_wal_receiver;
 select name, setting from pg_settings where name like 'enable%';
               name              | setting 
 --------------------------------+---------
+ enable_alter_system            | on
  enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
@@ -134,7 +135,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(23 rows)
+(24 rows)
 
 -- There are always wait event descriptions for various types.
 select type, count(*) > 0 as ok FROM pg_wait_events

base-commit: 071e3ad59d6fd2d6d1277b2bd9579397d10ded28
-- 
2.34.1