How to configure non super user for PgBackRest

By default PgBackRest requires a PostgreSQL super user, but in some environments with strict compliance policies, this requirement might not be possible.

It's still possible to use PgBackRest with a PostgreSQL user that doesn't have super user privileges, but such user needs to be configured with other privileges for PgBackRest to work. This article covers step by step what's needed.

Create the user

CREATE USER pgbackrest LOGIN REPLICATION PASSWORD 'backup-password';

Grant predefined role access to the user

GRANT pg_read_all_settings TO pgbackrest;

This permission allows reading all configuration variables, even those normally visible only to superusers.

Grant function permissions to the database you plan on using to connect to

This should be the same database specified in the pg1-database option in pgbackrest.conf.

Example: If you plan on connecting to the postgres database i.e pg1-database=edb:

Until PostgreSQL 14 / EPAS 14

GRANT EXECUTE ON FUNCTION pg_switch_wal TO pgbackrest;
GRANT EXECUTE ON FUNCTION pg_start_backup(text, boolean, boolean) TO pgbackrest;
GRANT EXECUTE ON FUNCTION pg_stop_backup(boolean, boolean) TO pgbackrest;
GRANT EXECUTE ON FUNCTION pg_create_restore_point(text) TO pgbackrest;

From PostgreSQL 15 / EPAS 15 onward

Note: pg_start_backup and pg_stop_backup functions were renamed to pg_backup_start and pg_backup_stop.

GRANT EXECUTE ON FUNCTION pg_switch_wal TO pgbackrest ;
GRANT EXECUTE ON FUNCTION pg_backup_start(text, boolean) TO pgbackrest;
GRANT EXECUTE ON FUNCTION pg_backup_stop(boolean) TO pgbackrest;
GRANT EXECUTE ON FUNCTION pg_create_restore_point(text) TO pgbackrest;

Verify the user's permissions

Database Privileges

SELECT 
    datname, 
    pg_catalog.pg_get_userbyid(datdba) AS owner, 
    has_database_privilege('pgbackrest', datname, 'CONNECT') AS can_connect,
    has_database_privilege('pgbackrest', datname, 'CREATE') AS can_create
FROM pg_database;

Role Attributes

SELECT
    rolname,
    rolsuper,
    rolinherit,
    rolcreaterole,
    rolcreatedb,
    rolcanlogin,
    rolreplication
FROM pg_roles
WHERE rolname = 'pgbackrest';

Function Privileges

SELECT
    routine_schema,
    routine_name,
    privilege_type
FROM information_schema.routine_privileges
WHERE grantee = 'pgbackrest';

Modify pgbackrest.conf

In your pgbackrest.conf file change the pg1-user attribute from superuser (enterprisedb or postgres) to non-superuser (pgbackrest).

Verify the stanza check

pgbackrest --stanza=<your-stanza-name> --log-level-console=info check

Note: Ensure that you are not receiving any error messages during this process.

Search