WIP: using local_conn var. "conn" doesn't exist

* Added check for pg_checkpoint role presence

This commit provides the needed infrastructure in `repmgr` so if the `repmgr` database
user is a member of the `pg_checkpoint` role, and inherits its privileges, there is no 
need for such a user to be a superuser.

Co-authored-by: Martín Marqués <martin.marques@enterprisedb.com>

.github/CODEOWNERS

@@ -0,0 +1,7 @@
+# Each line is a file pattern followed by one or more owners.
+
+# These owners will be the default owners for everything in
+# the repo. Unless a later match takes precedence,
+# @global-owner1 and @global-owner2 will be requested for
+# review when someone opens a pull request.
+*       @EnterpriseDB/repmgr-dev

dbutils.c

@@ -1852,6 +1852,51 @@ get_wal_receiver_pid(PGconn *conn)
 /* =============================== */
 
 
+/*
+ * Determine if the user associated with the current connection can execute CHECKPOINT command.
+ * User must be a supersuer or a member of the pg_checkpoint default role (available from PostgreSQL 15).
+ */
+bool
+can_execute_checkpoint(PGconn *conn)
+{
+	PQExpBufferData query;
+	PGresult   *res;
+	bool		has_pg_checkpoint_role = false;
+
+	/* superusers can do anything, no role check needed */
+	if (is_superuser_connection(conn, NULL) == true)
+		return true;
+
+	/* pg_checkpoint available from PostgreSQL 15 */
+	if (PQserverVersion(conn) < 150000)
+		return false;
+
+	initPQExpBuffer(&query);
+	appendPQExpBufferStr(&query,
+						 " SELECT pg_catalog.pg_has_role('pg_checkpoint','USAGE') ");
+
+	res = PQexec(conn, query.data);
+
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		log_db_error(conn, query.data,
+					 _("can_execute_checkpoint(): unable to query user roles"));
+	}
+	else
+	{
+		has_pg_checkpoint_role = atobool(PQgetvalue(res, 0, 0));
+	}
+	termPQExpBuffer(&query);
+	PQclear(res);
+
+	return has_pg_checkpoint_role;
+}
+
+
+/*
+ * Determine if the user associated with the current connection
+ * has sufficient permissions to use pg_promote function
+ */
 bool
 can_execute_pg_promote(PGconn *conn)
 {
@@ -1979,13 +2024,13 @@ connection_has_pg_monitor_role(PGconn *conn, const char *subrole)
 	initPQExpBuffer(&query);
 	appendPQExpBufferStr(&query,
 						 "  SELECT CASE "
-						 "           WHEN pg_catalog.pg_has_role('pg_monitor','MEMBER') "
+						 "           WHEN pg_catalog.pg_has_role('pg_monitor','USAGE') "
 						 "             THEN TRUE ");
 
 	if (subrole != NULL)
 	{
 		appendPQExpBuffer(&query,
-						  "           WHEN pg_catalog.pg_has_role('%s','MEMBER') "
+						  "           WHEN pg_catalog.pg_has_role('%s','USAGE') "
 						  "             THEN TRUE ",
 						  subrole);
 	}
@@ -2492,7 +2537,10 @@ get_repmgr_extension_status(PGconn *conn, t_extension_versions *extversions)
 /* node management functions */
 /* ========================= */
 
-/* assumes superuser connection */
+/*
+ * Assumes the connection can execute CHECKPOINT command.
+ * A check can be executed via 'can_execute_checkpoint' function.
+ */
 void
 checkpoint(PGconn *conn)
 {

dbutils.h

@@ -453,6 +453,7 @@ TimeLineHistoryEntry *get_timeline_history(PGconn *repl_conn, TimeLineID tli);
 pid_t		get_wal_receiver_pid(PGconn *conn);
 
 /* user/role information functions */
+bool		can_execute_checkpoint(PGconn *conn);
 bool		can_execute_pg_promote(PGconn *conn);
 bool		can_disable_walsender(PGconn *conn);
 bool		connection_has_pg_monitor_role(PGconn *conn, const char *subrole);

doc/configuration-permissions.xml

@@ -79,6 +79,10 @@
         Alternatively the meta-role <varname>pg_monitor</varname> can be granted, which includes membership
         of the above predefined roles.
       </para>
+      <para>
+        PostgreSQL 15 introduced the <varname>pg_checkpoint</varname> predefined role which allows a 
+        non-superuser &repmgr; database user to perform a CHECKPOINT command.
+      </para>
       <para>
         Membership of these roles can be granted with e.g. <command>GRANT pg_read_all_stats TO repmgr</command>.
       </para>
@@ -148,6 +152,8 @@
               <link linkend="repmgr-standby-switchover">repmgr standby switchover</link>. This can only
               be executed by a superuser; if the &repmgr; user is not a superuser,
               the <option>-S</option>/<option>--superuser</option> should be used.
+              From PostgreSQL 15 the <varname>pg_checkpoint</varname> predefined role removes the need of
+              superuser permissions to perform <command>CHECKPOINT</command> command.
             </simpara>
             <simpara>
               If &repmgr; is not able to execute <command>CHECKPOINT</command>,

doc/repmgr-node-service.xml

@@ -77,7 +77,8 @@
           </para>
           <para>
             Note that a superuser connection is required to be able to execute the
-             <command>CHECKPOINT</command> command.
+            <command>CHECKPOINT</command> command. From PostgreSQL 15 the <varname>pg_checkpoint</varname>
+            predefined role removes the need for superuser permissions to perform <command>CHECKPOINT</command> command.
           </para>
         </listitem>
      </varlistentry>

doc/repmgr-standby-switchover.xml

@@ -79,7 +79,8 @@
     <para>
       Note that <command>CHECKPOINT</command> requires database superuser permissions to execute.
       If the <literal>repmgr</literal> user is not a superuser, the name of a superuser should be
-      provided with the <option>-S</option>/<option>--superuser</option> option.
+      provided with the <option>-S</option>/<option>--superuser</option> option. From PostgreSQL 15 the <varname>pg_checkpoint</varname>
+      predefined role removes the need for superuser permissions to perform <command>CHECKPOINT</command> command.
     </para>
     <para>
       If &repmgr; is unable to execute the <command>CHECKPOINT</command> command, the switchover

repmgr-action-node.c

@@ -2365,18 +2365,25 @@ do_node_service(void)
 			conn = establish_db_connection_by_params(&source_conninfo, true);
 		}
 
-		if (is_superuser_connection(conn, NULL) == false)
+		if (can_execute_checkpoint(conn) == false)
 		{
 			if (runtime_options.dry_run == true)
 			{
-				log_warning(_("a CHECKPOINT would be issued here but no superuser connection is available"));
+				log_warning(_("a CHECKPOINT would be issued here but no authorized connection is available"));
 			}
 			else
 			{
-				log_warning(_("a superuser connection is required to issue a CHECKPOINT"));
+				log_warning(_("an authorized connection is required to issue a CHECKPOINT"));
 			}
 
-			log_hint(_("provide a superuser with -S/--superuser"));
+			if (PQserverVersion(conn) >= 150000)
+			{
+				log_hint(_("provide a superuser with -S/--superuser or grant pg_checkpoint role to repmgr user"));
+			}
+			else
+			{
+				log_hint(_("provide a superuser with -S/--superuser"));
+			}
 		}
 		else
 		{

repmgr-action-standby.c

@@ -5288,7 +5288,7 @@ do_standby_switchover(void)
 			checkpoint_conn = superuser_conn;
 		}
 
-		if (is_superuser_connection(checkpoint_conn, NULL) == true)
+		if (can_execute_checkpoint(checkpoint_conn) == true)
 		{
 			log_notice(_("issuing CHECKPOINT on node \"%s\" (ID: %i) "),
 					   config_file_options.node_name,
@@ -5297,7 +5297,16 @@ do_standby_switchover(void)
 		}
 		else
 		{
-			log_warning(_("no superuser connection available, unable to issue CHECKPOINT"));
+			log_warning(_("no authorized connection available, unable to issue CHECKPOINT"));
+
+			if (PQserverVersion(local_conn) >= 150000)
+			{
+				log_hint(_("provide a superuser with -S/--superuser or grant pg_checkpoint role to repmgr user"));
+			}
+			else
+			{
+				log_hint(_("provide a superuser with -S/--superuser"));
+			}
 		}
 	}
 

repmgr.conf.sample

@@ -12,7 +12,7 @@
 #
 # For details on the configuration file format see the documentation at:
 #
-#   https://repmgr.org/docs/current/configuration-file.html#CONFIGURATION-FILE-FORMAT
+#  https://repmgr.org/docs/current/configuration-file.html#CONFIGURATION-FILE-FORMAT
 #
 # =============================================================================
 # Required configuration items
@@ -76,7 +76,7 @@
 #location='default'		 # An arbitrary string defining the location of the node; this
 				 # is used during failover to check visibility of the
 				 # current primary node. For further details see:
-				 #   https://repmgr.org/docs/current/repmgrd-network-split.html
+				 #  https://repmgr.org/docs/current/repmgrd-network-split.html
 
 #use_replication_slots=no	 # whether to use physical replication slots
 				 # NOTE: when using replication slots,
@@ -181,8 +181,8 @@
 
 #pg_ctl_options=''			# Options to append to "pg_ctl"
 #pg_basebackup_options=''		# Options to append to "pg_basebackup"
-                                        # (Note: when cloning from Barman, repmgr will honour any
+					# (Note: when cloning from Barman, repmgr will honour any
-                                        # --waldir/--xlogdir setting present in "pg_basebackup_options"
+					# --waldir/--xlogdir setting present in "pg_basebackup_options"
 #rsync_options=''			# Options to append to "rsync"
 ssh_options='-q -o ConnectTimeout=10'	# Options to append to "ssh"
 
@@ -212,8 +212,8 @@ ssh_options='-q -o ConnectTimeout=10'	# Options to append to "ssh"
 
 #recovery_min_apply_delay=		# If provided, "recovery_min_apply_delay" will be set to
 					# this value (PostgreSQL 9.4 and later). Value can be
-                                        # an integer representing milliseconds, or a string
+					# an integer representing milliseconds, or a string
-                                        # representing a period of time (e.g. '5 min').
+					# representing a period of time (e.g. '5 min').
 
 
 #------------------------------------------------------------------------------
@@ -299,7 +299,7 @@ ssh_options='-q -o ConnectTimeout=10'	# Options to append to "ssh"
 					# a value of zero prevents the node being promoted to primary
 					# (default: 100)
 
-#connection_check_type=ping		# How to check availability of the upstream node; valid options:
+#connection_check_type='ping'		# How to check availability of the upstream node; valid options:
 					#  'ping': use PQping() to check if the node is accepting connections
 					#  'connection': attempt to make a new connection to the node
 					#  'query': execute an SQL statement on the node via the existing connection
@@ -344,20 +344,27 @@ ssh_options='-q -o ConnectTimeout=10'	# Options to append to "ssh"
 					# From PostgreSQL 15 repmgr must be a superuser or have 'ALTER SYSTEM wal_retrieve_retry_interval' privilege.
 					# (see: https://repmgr.org/docs/current/repmgrd-standby-disconnection-on-failover.html )
 #sibling_nodes_disconnect_timeout=30	# If "standby_disconnect_on_failover" is true, the maximum length of time
-					#  (in seconds) to wait for other standbys to confirm they have disconnected their
+					# (in seconds) to wait for other standbys to confirm they have disconnected their
 					# WAL receivers
 #primary_visibility_consensus=false	# If "true", only continue with failover if no standbys have seen
 					# the primary node recently. *Must* be the same on all nodes.
 #always_promote=false			# Always promote a node, even if repmgr metadata is outdated
-#failover_validation_command=''		# Script to execute for an external mechanism to validate the failover
+#failover_validation_command=''	# Script to execute for an external mechanism to validate the failover
-					# decision made by repmgrd. One or both of the following parameter placeholders
+					# decision made by repmgrd. Each of the following parameter placeholders
-					# should be provided, which will be replaced by repmgrd with the appropriate
+					# should be provided, which will be replaced by repmgrd with the appropriate value:
-					# value: %n (node_id), %a (node_name). *Must* be the same on all nodes.
+					#   %n (node_id)
+					#   %a (node_name)
+					#   %v (number of visible nodes)
+					#   %u (number of shared upstream nodes)
+					#   %t (total number of nodes)
+					# *Must* be the same on all nodes.
 #election_rerun_interval=15		# if "failover_validation_command" is set, and the command returns
 					# an error, pause the specified amount of seconds before rerunning the election.
-					#
+
-					# The following items are relevant for repmgrd running on the primary,
+# The following items are relevant for repmgrd running on the primary,
-					# and will be ignored on non-primary nodes
+# and will be ignored on non-primary nodes.
+# (see: https://repmgr.org/docs/current/repmgrd-primary-child-disconnection.html )
+
 #child_nodes_check_interval=5		# Interval (in seconds) to check for attached child nodes (standbys)
 #child_nodes_connected_min_count=-1	# Minimum number of child nodes which must remain connected, otherwise
 					# disconnection command will be triggered
@@ -365,6 +372,7 @@ ssh_options='-q -o ConnectTimeout=10'	# Options to append to "ssh"
 					# (ignored if "child_nodes_connected_min_count" set)
 #child_nodes_disconnect_timeout=30	# Interval between child node disconnection and disconnection command execution
 #child_nodes_disconnect_command=''	# Command to execute if child node disconnection detected
+#child_nodes_connected_include_witness=false	# Whether to count the witness node (if in use) as a child node when determining whether to execute child_nodes_disconnect_command.
 
 #------------------------------------------------------------------------------
 # service control commands
@@ -387,20 +395,20 @@ ssh_options='-q -o ConnectTimeout=10'	# Options to append to "ssh"
 #
 # For example, to use systemd, you can set
 #
-#    service_start_command = 'sudo systemctl start postgresql-9.6'
+#    service_start_command = 'sudo systemctl start postgresql-16'
 #    (...)
 #
 # and then use the following sudoers configuration:
 #
 #    # this is required when running sudo over ssh without -t:
 #    Defaults:postgres !requiretty
-#    postgres ALL = NOPASSWD: /usr/bin/systemctl stop postgresql-9.6, \
+#    postgres ALL = NOPASSWD: /usr/bin/systemctl stop postgresql-16, \
-#	/usr/bin/systemctl start postgresql-9.6, \
+#        /usr/bin/systemctl start postgresql-16, \
-#	/usr/bin/systemctl restart postgresql-9.6
+#        /usr/bin/systemctl restart postgresql-16
 #
 # Debian/Ubuntu users: use "sudo pg_ctlcluster" to execute service control commands.
 #
-# For more details, see: https://repmgr.org/docs/current/configuration-file-service-commands.html
+# For further details, see: https://repmgr.org/docs/current/configuration-file-service-commands.html
 
 #service_start_command = ''
 #service_stop_command = ''
@@ -443,4 +451,3 @@ ssh_options='-q -o ConnectTimeout=10'	# Options to append to "ssh"
 					# "repmgr standby switchover" to warn about potential
 					# issues with shutting down the demotion candidate.
 
-