Configuring User Accounts for PostgreSQL Backups

FSBased Backup Set

• The PostgreSQL agent requires a user account with following privileges:

  • The user must own the maintenance database set in the PostgreSQL instance properties.

• Upto Versions 9.6

  • The database user specified in the PostgreSQL instance properties must be a superuser.

• Versions above 9.6

  • The user must be a member of pg_read_all_settings and should have execute privilege on functions like pg_backup_start, pg_backup_stop and pg_switch_wal.

  • Connect to the maintenance database specified in the PostgreSQL instance properties and execute the following GRANT statements:

    • Version 10 to 14

      • GRANT pg_read_all_settings to user_name;

      • GRANT execute on function pg_start_backup , pg_stop_backup() , pg_stop_backup(boolean,boolean) , pg_switch_wal to user_name;

    • Version 15 or above

      • GRANT pg_read_all_settings to user_name;

      • GRANT execute on function pg_backup_start(text, boolean) , pg_backup_stop(boolean) , pg_switch_wal() to user_name;

Enabling Log Backups with Caching

To enable disk caching for frequent database log backups feature, you must meet the following privileges and user account requirements:

• Upto version 14

  • The database user specified in the PostgreSQL instance properties must be a superuser.

• Version 15 or above

  • GRANT pg_read_all_settings to user_name;

  • GRANT execute on function pg_backup_start(text, boolean) , pg_backup_stop(boolean) , pg_switch_wal() to user_name;

  • GRANT EXECUTE ON FUNCTION pg_reload_conf() TO user_name;

  • GRANT ALTER SYSTEM ON PARAMETER archive_command TO user_name;

DumpBased Backup Set

• For DumpBasedBackupSet, the PostgreSQL agent requires a user account that has sufficient privileges to perform the following:

  • Perform backup and restore operations

  • Access the PostgreSQL database objects as part of the subclient content

  The PostgreSQL user account should have the following privileges: CREATEDB, CONNECT, CREATEROLE, INHERIT and LOGIN.

  CONNECTION LIMIT, if set, should at least have a value of 2.

  Initially, the user account credentials is provided during the instance configuration after the installation of the agent. You can change the user account at the instance level.

• You require SELECT privileges on all tables and objects within the schema or databases being backed up.

  The following statements are valid for PostgreSQL:

  • Version 14 or above

    • GRANT pg_read_all_data to user_name;

  • Version 13 or below

    • GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO user_name;

At the Instance Level

This user account will be used for all instances and associated subclients. Use the following steps to change the user account for an Instance:

1. From the CommCell Browser, navigate to Client Computers | <Client> | PostGreSQL.

2. Right-click the <Instance> and click Properties.

3. Click the Accounts tab.

4. Type the user name in the PostGres User Name box.

5. Type the password in the PostGres Account Password box.

6. Retype the password in the PostGres Confirm Password box.

7. Click OK.