Re: How do I create a Backup Operator account ?

On Wed, Jul 31, 2019 at 2:48 AM Marcos Aurelio Nobre
<marconobre(at)gmail(dot)com> wrote:
> But I don't know how to implement this on Linux, nor how to write this entry in the pg_hba.conf file.

I would start with an entry in pg_hba.conf like the following:

host all pg_backup_username localhost md5

or

host all pg_backup_username localhost md5

The problem then comes on how to prevent the operating system user to
run psql. If you are doing backup from a backup machine, one solution
would be to remove the psql executable and leave the backup ones.
Again, this is a poor practice to me. Even something like the
following (untested) in /etc/sudoers will NOT prevent the user to
access the database:

User_Alias PGBACKUPUSERS = pg_backup_username
Cmd_Alias PGBACKUP = /usr/local/bin/pg_dump,
/usr/local/bin/pg_restore, ! /usr/local/bin/psql
PGBACKUPUSERS backup_host = PGBACKUP

because the user could use another client to inspect the database.
And again, I don't see the point in not allowing an user to access the
database but to be able to take a full backup. Therefore, I would go
to revoke all write grants to such user and see if he can still do a
backup.

Luca