4.7. repmgr database user permissions

4.7.1. repmgr user as a non-superuser

If the repmgr database user (the PostgreSQL user defined in the conninfo setting is a superuser, no further user permissions need to be granted.

4.7.1. repmgr user as a non-superuser

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. Replication role

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:

  • a superuser
  • a non-superuser with the REPLICATION role
  • another user defined in the repmgr.conf parameter replication_user with the REPLICATION role Database roles

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. Extension creation

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. Function permissions

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. repmgr actions requiring a superuser

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.

  • The 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. repmgr commands with --superuser option

The following repmgr commands provide the -S/--superuser option: