Re: How do I create a Backup Operator account ?

On 2019-07-31 07:48:36 +0200, Luca Ferrari wrote:
> 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.

This problem can be solved by not granting anyone shell access as that
user. It is only used as a target for sudo, and sudo is configured to
run only pg_dump and pg_restore as that user (plus maybe other programs
to list available backups, review logs, remove old backups, ...)

A web interface might be used as an alternative to sudo.

> 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

This is the wrong way around. It should be something like

alice, bob = (pg_backup_username) /usr/local/bin/pg_dump

(Apologies if I didn't get the syntax right. Slogging through the sudoes
manual reminded me why I wrote xssd 15 years ago).

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>