Superuser for checkpoint VS predefined role pg_checkpoint for PostgresSQL 15+

[F-S-T]

Hi,

According to documentation (https://repmgr.org/docs/repmgr.html#CONFIGURATION-PERMISSIONS-SUPERUSER-REQUIRED), repmgr needs superuser for checkpoint.

Since version 15 of PostgreSQL, predefined role pg_checkpoint is available (https://www.postgresql.org/docs/15/predefined-roles.html), so I created my repmgr user with pg_checkpoint role to avoid need of superuser.

repmgr=# \du repmgr
                                  Liste des rôles
 Nom du rôle |  Attributs  |                       Membre de
-------------+-------------+--------------------------------------------------------
 repmgr      | Réplication | {pg_read_all_settings,pg_read_all_stats,pg_checkpoint}

repmgr=# CHECKPOINT;
CHECKPOINT

But when I try to do a switchover, I have some warnings anyway.

$ repmgr standby switchover --dry-run
NOTICE: checking switchover on node "server2" (ID: 2) in --dry-run mode
WARNING: no superuser connection available
DETAIL: it is recommended to perform switchover operations with a database superuser
HINT: provide the name of a superuser with -S/--superuser
INFO: SSH connection to host "server1" succeeded
INFO: able to execute "repmgr" on remote host "server1"
INFO: 1 walsenders required, 10 available
INFO: demotion candidate is able to make replication connection to promotion candidate
INFO: 0 pending archive files
INFO: replication lag on this standby is 0 seconds
NOTICE: attempting to pause repmgrd on 2 nodes
NOTICE: local node "server2" (ID: 2) would be promoted to primary; current primary "server1" (ID: 1) would be demoted to standby
INFO: following shutdown command would be run on node "server1":
  "sudo systemctl stop postgresql-15.service"
INFO: parameter "shutdown_check_timeout" is set to 60 seconds
INFO: prerequisites for executing STANDBY SWITCHOVER are met

$ repmgr standby switchover
NOTICE: executing switchover on node "server2" (ID: 2)
WARNING: no superuser connection available
DETAIL: it is recommended to perform switchover operations with a database superuser
HINT: provide the name of a superuser with -S/--superuser
NOTICE: attempting to pause repmgrd on 2 nodes
NOTICE: local node "server2" (ID: 2) will be promoted to primary; current primary "server1" (ID: 1) will be demoted to standby
NOTICE: stopping current primary node "server1" (ID: 1)
WARNING: a superuser connection is required to issue a CHECKPOINT
HINT: provide a superuser with -S/--superuser
DETAIL: executing server command "sudo systemctl stop postgresql-15.service"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/28000028
NOTICE: promoting standby to primary
DETAIL: promoting server "server2" (ID: 2) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "server2" (ID: 2) was successfully promoted to primary
NOTICE: node "server2" (ID: 2) promoted to primary, node "server1" (ID: 1) demoted to standby
NOTICE: switchover was successful
DETAIL: node "server2" is now primary and node "server1" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully

Is checkpoint executed or this warning imply that no checkpoint is done?
WARNING: a superuser connection is required to issue a CHECKPOINT

Is there a way to avoid these warnings in case of version 15+ and pg_checkpoint role granted to repmgr user?

Regards,
FST

[F-S-T]

Hi,

After some investigation, I guess the code implied in this starts at ligne 2368 of repmgr-action-node.c:

		if (is_superuser_connection(conn, NULL) == false)
		{
			if (runtime_options.dry_run == true)
			{
				log_warning(_("a CHECKPOINT would be issued here but no superuser connection is available"));
			}
			else
			{
				log_warning(_("a superuser connection is required to issue a CHECKPOINT"));
			}

			log_hint(_("provide a superuser with -S/--superuser"));
		}
		else
		{
			if (runtime_options.dry_run == true)
			{
				log_info(_("a CHECKPOINT would be issued here"));
			}
			else
			{

				log_notice(_("issuing CHECKPOINT on node \"%s\" (ID: %i) "),
						   config_file_options.node_name,
						   config_file_options.node_id);

				checkpoint(conn);
			}
		}

Called in repmgr-action-standby.c with remote command defined in line 5023:

		appendPQExpBufferStr(&remote_command_str,
							 "node service --action=stop --checkpoint");

So I answer my own question: the checkpoint is not performed.

It would be great to avoid this in case of version 15+ and pg_checkpoint role granted to repmgr user.

Regards,
FST