How do I create a Backup Operator account ?

On Jul 29, 2019, at 7:49 PM, Marcos Aurelio Nobre <marconobre@gmail.com> wrote:

Hi all.

I need to create a PostgreSQL account , that only do a Backup and Restore operations over a PGSQL Database .

My main difficulty is to only assign Backup and Restore capability to one user account, unlike the ability to perform DQL and DML operations for ordinary user accounts.

I’m not sure I understand what you are really trying to do. If this Backup/Restore users is going to backup and restore the complete database then they will have to have essentially superuser permissions. Otherwise they will not have access to all of the data. They will have to have permission for DDL and DML as the restore has the potential for dropping and creating a bunch of objects, and at least DELETE, TRUNCATE, COPY and/or INSERT data.

What are you trying to protect against?

In POSTGRESQL there is no backup / restore privilege because these operations are performed by server specific utilities.

One possibility that I thought would be:

I was wondering if it was possible to configure the pgAdmin4 menus to be available for a particular login, only Backup & Restore items. But I'm not sure how to associate a bank login account with a specific menu setting of a client tool.

Could someone give me any ideas or directions?

Neil
https://www.fairwindsoft.com

On Tue, Jul 30, 2019 at 2:50 AM Marcos Aurelio Nobre
<marconobre@gmail.com> wrote:

I was wondering if it was possible to configure the pgAdmin4 menus to be available for a particular login, only Backup & Restore items. But I'm not sure how to associate a bank login account with a specific menu setting of a client tool.

I think that hiding features from a GUI is a poor idea to protect your
data (from what?).
If you are talking of a single database (or a restricted set of), you
can provide a role with the less privileges, like only SELECT and use
such account to do the backup. But you need all the grants on another
account to restore the backup.

Anyway, I think you should rethink about your aim: what's the point of
having a restricted user who can backup ALL the data?

Luca

On 7/30/19 12:58 AM, Luca Ferrari wrote:

On Tue, Jul 30, 2019 at 2:50 AM Marcos Aurelio Nobre
<marconobre@gmail.com> wrote:

I was wondering if it was possible to configure the pgAdmin4 menus to be available for a particular login, only Backup & Restore items. But I'm not sure how to associate a bank login account with a specific menu setting of a client tool.

I think that hiding features from a GUI is a poor idea to protect your
data (from what?).
If you are talking of a single database (or a restricted set of), you
can provide a role with the less privileges, like only SELECT and use
such account to do the backup. But you need all the grants on another
account to restore the backup.

Anyway, I think you should rethink about your aim: what's the point of
having a restricted user who can backup ALL the data?

Luca, it is common some large Enterprise environments to have Operations
staff that can run backups without being able to do anything else.  For
example, SQL Server has a per-database user mapping named db_backupoperator.

--
Angular momentum makes the world go 'round.

On Tue, Jul 30, 2019 at 2:22 PM Ron <ronljohnsonjr@gmail.com> wrote:

Luca, it is common some large Enterprise environments to have Operations
staff that can run backups without being able to do anything else. For
example, SQL Server has a per-database user mapping named db_backupoperator.

Yes, but I think here we have to solve it with external tools, e.g.,
sudo. I see, however, an hard time configuring an account to execute
only pg_dump without being able to "escape" into the database itself.

Luca

I've also been thinking about this possibility. Create a Linux (S.O.) user
account that has the ability to run pg_dump and pg_restore and enable this
user account to be able to connect to a database - I believe this is
possible through PG_HBA.CONF

But I don't know how to implement this on Linux, nor how to write this
entry in the pg_hba.conf file.

Em ter, 30 de jul de 2019 às 10:22, Luca Ferrari <fluca1978@gmail.com>
escreveu:

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

On 2019-07-31 07:48:36 +0200, Luca Ferrari wrote:

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.

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@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;