Обсуждение: Creating a user for pg_start_backup
Dear all, I'm setting up hot backups on my database server. As such, I'd like to set up a Postgres user that has access to only pg_start_backup and pg_stop_backup. I'm unable to work out how to do this with the various GRANT options. Can someone point me in the right direction please? Or is there a better way to achieve this, rather than having a dedicated user? Thanks, Andy
On Tue, Jul 21, 2015 at 4:47 PM, Andrew Beverley <andy@andybev.com> wrote: > Dear all, > > I'm setting up hot backups on my database server. As such, I'd like to set up a > Postgres user that has access to only pg_start_backup and pg_stop_backup. > > I'm unable to work out how to do this with the various GRANT options. Can someone > point me in the right direction please? Or is there a better way to achieve this, > rather than having a dedicated user? Access to pg_start_backup and pg_stop_backup can be done with either a replication user or a superuser. You can define user with such rights with CREATE ROLE with the keyword REPLICATION: http://www.postgresql.org/docs/devel/static/sql-createrole.html -- Michael
On Tue, 2015-07-21 at 16:54 +0900, Michael Paquier wrote: > On Tue, Jul 21, 2015 at 4:47 PM, Andrew Beverley <andy@andybev.com> wrote: > > Dear all, > > > > I'm setting up hot backups on my database server. As such, I'd like to set up a > > Postgres user that has access to only pg_start_backup and pg_stop_backup. > > > > I'm unable to work out how to do this with the various GRANT options. Can someone > > point me in the right direction please? Or is there a better way to achieve this, > > rather than having a dedicated user? > > Access to pg_start_backup and pg_stop_backup can be done with either a > replication user or a superuser. You can define user with such rights > with CREATE ROLE with the keyword REPLICATION: > http://www.postgresql.org/docs/devel/static/sql-createrole.html Great, thanks Michael, spot on. For the purposes of the archives: create user backup with password 'xxxx' replication; I had to specify a database name when connecting: psql -U backup -c "select pg_start_backup('Daily backup')" -d postgres Thanks, Andy
On 7/21/2015 1:31 AM, Andrew Beverley wrote:
> I had to specify a database name when connecting:
>
> psql -U backup -c "select pg_start_backup('Daily backup')" -d postgres
psql defaults to the current user for both the database name and user
name. I probably would have run that psql command as the system
postgres user and not specified any -U or -d ...
--
john r pierce, recycling bits in santa cruz
On Tue, 2015-07-21 at 01:46 -0700, John R Pierce wrote:
> On 7/21/2015 1:31 AM, Andrew Beverley wrote:
> > I had to specify a database name when connecting:
> >
> > psql -U backup -c "select pg_start_backup('Daily backup')" -d postgres
>
> psql defaults to the current user for both the database name and user
> name. I probably would have run that psql command as the system
> postgres user and not specified any -U or -d ...
Thanks John. The backup script is running as root, so presumably I'd have to use
sudo? Or should I run a separate cron job as postgres to do the above, and run the
backup script separately?
Thanks,
Andy
On 7/21/2015 1:51 AM, Andrew Beverley wrote: > Thanks John. The backup script is running as root, so presumably I'd have to use > sudo? Or should I run a separate cron job as postgres to do the above, and run the > backup script separately? those are both possibilities. I'd either use su (not sudo) from root, or I'd cron it from the postgres DBA account, depending. -- john r pierce, recycling bits in santa cruz
Andrew Beverley wrote: > I'm setting up hot backups on my database server. As such, I'd like to set up a > Postgres user that has access to only pg_start_backup and pg_stop_backup. > > I'm unable to work out how to do this with the various GRANT options. Can someone > point me in the right direction please? Or is there a better way to achieve this, > rather than having a dedicated user? If you want to be as restrictive as possible, you could create functions owned by a superuser with SECURITY DEFINER that do only these things and give execution rights only to a user that has no other privileges. Youes, Laurenz Albe
On Tue, 2015-07-21 at 03:00 -0700, John R Pierce wrote: > On 7/21/2015 1:51 AM, Andrew Beverley wrote: > > Thanks John. The backup script is running as root, so presumably I'd have to > > use > > sudo? Or should I run a separate cron job as postgres to do the above, and run > > the > > backup script separately? > > those are both possibilities. I'd either use su (not sudo) from root, > or I'd cron it from the postgres DBA account, depending. Sorry to be dragging this off-topic, but what's the reason for using su instead of sudo? Thanks, Andy
On 7/21/2015 8:36 AM, Andrew Beverley wrote:
> Sorry to be dragging this off-topic, but what's the reason for using su instead of
> sudo?
sudo is for non root users, it ends up running the su command. normally
root doesn't use sudo at all, look at all the init.d scripts that run
daemons as other users, they typically use su, like...
$SU -l postgres -c "$PGENGINE/postmaster -p '$PGPORT' -D
'$PGDATA' ${PGOPTS} &" >> "$PGLOG" 2>&1 <
/dev/null
(where $SU is su or runuser)
--
john r pierce, recycling bits in santa cruz
* Michael Paquier (michael.paquier@gmail.com) wrote: > On Tue, Jul 21, 2015 at 4:47 PM, Andrew Beverley <andy@andybev.com> wrote: > > Dear all, > > > > I'm setting up hot backups on my database server. As such, I'd like to set up a > > Postgres user that has access to only pg_start_backup and pg_stop_backup. > > > > I'm unable to work out how to do this with the various GRANT options. Can someone > > point me in the right direction please? Or is there a better way to achieve this, > > rather than having a dedicated user? > > Access to pg_start_backup and pg_stop_backup can be done with either a > replication user or a superuser. You can define user with such rights > with CREATE ROLE with the keyword REPLICATION: > http://www.postgresql.org/docs/devel/static/sql-createrole.html Note that the REPLICATION role gets a great deal more access than simply being able to run pg_start/stop_backup, such as being able to connect to the magic replication database and be able to stream the contents of the database. Would be great to understand your use-case better, to see if the proposed default roles would be a better eventual solution for you. Thanks, Stephen