On Wed, Jul 31, 2019 at 2:48 AM Marcos Aurelio Nobre
<marconobre@gmail.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