pg_dump: [archiver (db)] connection to database failed: FATAL: Peer authentication failed for user "postgres"
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
Solution 1:
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 :
- Create an hidden file named
.pgpass
in$HOME
- Perform a
chmod 600 .pgpass
- 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