Can't backup a Postgres database: FATAL: Peer authentication failed for user "postgres"

Question

I can connect to my database no problem remotely, either from PHP scripts on our webserver or using PGAdmin3.

Unfortunately, when I try to run pg_dump backups locally on the server itself, I get:

pg_dump --username=postgres -W omega | gzip > omega.10.10.13.gz

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

Previously I'd had no password at all for my database, but to try to get around this I actually gave the postgres user a password. Still no dice, peer authentication fails every time.

Here's the settings in my pg_hba.conf file... please tell me what I can do to fix this. Really want to run some backups on my database.

# 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

host    all             all             70.89.205.250/24        md5 
host    all             all             23.21.112.163/24        md5 
host    all             all             24.188.1.163/24         md5

score 7 · Answer 1 · edited May 23 '17 at 12:40

Its a weird quirk of the Postgres clients that the ones that are remote or in scripts typically connect to the server using its address and are then authenticated by password but when you connect from the same machine via the command line client then it tries to use peer authentication and you get that error. I found from this answer that you can fix it by simply specifying a host:

pg_dump -h 127.0.0.1 --username=postgres -W omega | gzip > omega.10.10.13.gz

This is what your PHP script is doing internally. I prefer this fix as it doesn't mess with any global configuration and thought you might want to know about it too.

score 3 · Answer 2 · answered Aug 01 '14 at 22:26

3

In pg_hba.conf, change this line:

local   all             all                                     peer

to:

local   all             all                                     password

Then you can log in locally using a password.

answered Aug 01 '14 at 22:26

Jason Champion

181
8

score 1 · Answer 3 · edited May 23 '17 at 12:40

Either run the script as system user postgres - who has a corresponding db user with peer authentication set up, according to your pg_hba.conf file. Like @Milen already hinted in his comment. Or set up password-less access for your system user, preferably with a .pgpass file.

More details in this closely related answer:
PostgreSQL Scheduled Database Backup.

Or this comprehensive answer about password-less access on SO.

Can't backup a Postgres database: FATAL: Peer authentication failed for user "postgres"

3 Answers3