Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to backup my Postgres DB to another server but I keep getting denied access.

my pg_hba.conf file looks like this:

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

If I change the postgres setting from PEER to TRUST, i can backup the code but the web site can no longer access the database (something about SSL error, which I know isn't the issue).

When I leave it as is, PEER, the server works - php can access the db - but I get error while trying to run the backup:

root> pg_dump -U postgres tablename > test.sql

.pg_dump: [archiver (db)] connection to database "tablename" failed: FATAL: Peer authentication failed for user "postgres".

PHP Apache Web site uses appropriate username and password.

Backup script looks like this: pg_dump -U postgres tablename > test.sql

how can I satisfy both the web site and the backup script so I can run both?

Entire script attempted is this: ssh SERVER "pg_dump -U postgres tablename| gzip -c" > /backup/sqlbackup.sql.gz

taken from Server Fault: ssh remote command-fu

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

If your PHP Apache web site works with peer, keep this, and let's deal with it.

1. First Method :

As authentication is peer based, you have to specify the hostname (usually localhost) using -h option :

ssh server "pg_dump -h localhost -U postgres | gzip -c" >/backup/sqlbackup.sql.gz

The main issue here is that you will be prompted for the postgres user password. It is a problem for backups automation .

You can refer to this to avoid to be prompted for the password. But i will describe :

  1. Create an hidden file named .pgpass in $HOME
  2. Perform a chmod 600 .pgpass
  3. Edit file .pgpass and add this line : localhost:5432:postgres:postgres:password

2. Second Method :

You could add a specific role for backups and trust it.

Create the backup role in Postgres :

CREATE ROLE backup WITH LOGIN SUPERUSER PASSWORD 'password'

Edit file pg_hba.conf, add role backup and trust it :

# Database administrative login by Unix domain socket
local   all             postgres                                peer
local   all             backup                                  trust

Restart postgresql

Then you should be able to run backup with the following :

ssh server "pg_dump -U backup postgres | gzip -c" >/backup/sqlbackup.sql.gz
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.