Thread: 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
DQLand 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
restorethe complete database then they will have to have essentially superuser permissions.  Otherwise they will not
haveaccess to all of the data.  They will have to have permission for DDL and DML as the restore has the potential for
droppingand 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

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/>