Re: Additional role attributes && superuser review

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Jan 21, 2015 at 11:27 AM, Adam Brightwell
> <adam(dot)brightwell(at)crunchydatasolutions(dot)com> wrote:
> > After re-reading through this thread is seems like EXCLUSIVEBACKUP (proposed
> > by Magnus) seemed to be a potentially acceptable alternative.
>
> So this would let you do pg_start_backup() and pg_stop_backup(), but
> it wouldn't let you run pg_basebackup against the server?

Right. We already have a role attribute which allows pg_basebackup
(replication). Also, with pg_basebackup / rolreplication, your role
is able to read the entire data directory from the server, that's not
the case with only rights to run pg_start/stop_backup.

In conjunction with enterprise backup solutions and SANs, which offer
similar controls where a generally unprivileged user can have a snapshot
of the system taken through the SAN interface, you can give users the
ability to run ad-hoc backups of the cluster without giving them
superuser-level access or replication-level access.

Even with simpler solutions, it means that the backup user doesn't
have to be able to run some superuser-level script against the database
to run the backup.

As for pg_basebackup itself, I agree that it's not exactly intuitive
that 'replication' is what grants you the right to run pg_basebackup..
Perhaps we could rename it or make an alias for it, or something along
those lines? I wasn't looking to do anything with it at this time, but
it would probably be good to improve it somehow, if you (or anyone) have
suggestions on how best to do so.

Thanks!

Stephen