Creating a user for pg_start_backup

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

* 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