If the repmgr database user (the PostgreSQL user defined in the
conninfo setting is a superuser, no further user permissions need
to be granted.
In principle the repmgr database user does not need to be a superuser.
In this case the repmgr will need to be granted execution permissions on certain
functions, and membership of certain roles. However be aware that repmgr does
expect to be able to execute certain commands which are restricted to superusers;
in this case either a superuser must be specified with the -S/--superuser
(where available) option, or the corresponding action should be executed manually as a superuser.
The following sections describe the actions needed to use repmgr with a non-superuser, and relevant caveats.
repmgr requires a database user with the REPLICATION role
to be able to create a replication connection and (if configured) to administer
replication slots.
By default this is the database user defined in the conninfo
setting. This user can be:
REPLICATION role
repmgr.conf parameter replication_user with the REPLICATION role
A non-superuser repmgr database user should be a member of the following predefined roles (PostgreSQL 10 and later):
pg_read_all_stats
(to read the status column of pg_stat_replication
and execute pg_database_size() on all databases)
pg_read_all_settings (to access the data_directory setting)
Alternatively the meta-role pg_monitor can be granted, which includes membership
of the above predefined roles.
Membership of these roles can be granted with e.g. GRANT pg_read_all_stats TO repmgr.
Users of PostgreSQL 9.6 or earlier should upgrade to a supported PostgreSQL version, or provide
the -S/--superuser where available.
repmgr requires that the database defined in the conninfo
setting contains the repmgr extension. The database user defined in the
conninfo setting must be able to access this database and
the database objects contained within the extension.
The repmgr extension can only be installed by a superuser.
If the repmgr user is a superuser, repmgr will create the extension automatically.
Alternatively, the extension can be created manually by a superuser
(with "CREATE EXTENSION repmgr") before executing
repmgr primary register.
If the repmgr database user is not a superuser, EXECUTE permission should be
granted on the following function:
pg_wal_replay_resume() (required by repmgrd during failover operations;
if permission is not granted, the failoved process may not function reliably if a node
has WAL replay paused)
pg_promote() (PostgreSQL 12 and later; if permission is not granted,
repmgr will fall back to pg_ctl promote)
EXECUTE permission on functions can be granted with e.g.:
GRANT EXECUTE ON FUNCTION pg_catalog.pg_wal_replay_resume() TO repmgr.
In some circumstances, repmgr may need to perform an operation which cannot be delegated to a non-superuser.
The CHECKPOINT command is executed by
repmgr standby switchover. This can only
be executed by a superuser; if the repmgr user is not a superuser,
the -S/--superuser should be used.
If repmgr is not able to execute CHECKPOINT,
there is a risk that the demotion candidate may not be able to shut down as smoothly as might otherwise
have been the case.
ALTER SYSTEM is executed by repmgrd if
standby_disconnect_on_failover is set to true in
repmgr.conf. ALTER SYSTEM can only be executed by
a superuser; if the repmgr user is not a superuser, this functionality will not be available.
The following repmgr commands provide the -S/--superuser option:
--copy-external-config-files provided)CHECKPOINT)repmgr node check --data-directory-config; note this is also called by repmgr standby switchover)CHECKPOINT via the --checkpoint; note this is also called by repmgr standby switchover)