How to run PostgreSQL backups using sudo

How to run PostgreSQL backups using sudo

One of the prerequisites for running PostgreSQL from the Veritas NetBackup for PostgreSQL Administrator's Guide:

■ (Linux) The users of the agent is a superuser or has superuser privileges.

When running the backup command "nbpgsql -o backup" as a root user you do not encounter any issues.

If you run a backup as Postgres user (ie.postgres or enterprisedb) then the backup operations can fail with the following messages below:

Error creating the mount directory: Permission Denied

Failed to mount LVM snapshot

This is where the sudo command is required

Steps to implement sudo command:

Step 1) Identify user to run for PostgreSQL (cat /etc/passwd)

Step 2) Create .pgpass file for PostgreSQL user in /root/.pgpass

Format:

#<localhost>:<port>:<instance>:<user>:<password>

eg. # vi /root/.pgpass

localhost:*:*:enterprisedb:enterprisedb

Note: If PostgreSQL installation was deployed from https://www.postgresql.org then skip Step 2).

Step 3) Enable PostgreSQL user into sudo

Allow <postgres_user> to run any root commands

As root:

[client] # sudo /usr/sbin/visudo

## Allow root to run any commands anywhere

root                ALL=(ALL)      ALL
enterprisedb    ALL=(ALL)      ALL

Save File and Exit.

Step 4) Test backup by running:

[client]# su - enterprisedb

[client]$ id
uid=xxx(enterprisedb) gid=xxx(enterprisedb) groups=xxx(enterprisedb)

[client]$ sudo /usr/NBPostgreSQLAgent/nbpgsql -o backup
[sudo] password for enterprisedb:
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
Backup initiated from XBSA ...
PostgreSQL version: 9.6.2.7
PostgreSQL database backup is in progress...

Prompt for password will occur for the first attempt and any subsequent sudo commands will skip password prompt

and password cache expires by default after a period 5-15 minutes.

Optional to ensure sudo password does not expire so sudo command can be scripted.

Step 5) Set up sudo not to expire password

As root:

[client]# sudo /usr/sbin/visudo

## You can disable this default expiry of password cache by setting: (/etc/sudoers)
#timestamp_timeout=-1 (minus one) makes the sudo password not expire.
Defaults timestamp_timeout=-1

Save File and Exit.

Step 6) Confim backup command with no password prompting:

[client]# su - enterprise

[client]$ sudo /usr/NBPostgreSQLAgent/nbpgsql -o backup
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
Backup initiated from XBSA ...
PostgreSQL version: 9.6.2.7
PostgreSQL database backup is in progress...

NB: Use 'sudo -k' to manually force expiry of password cache.