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 

• Milen A. Radev: To pass peer authentication you have to be logged-in locally with the account you're trying to login in Postgres
• bruceg: make sure to check the logs. Occasionally, they have something useful to say. I had the same error, and I had a message that said: "rovided user name (blah) and authenticated user name (postgres) do not match"

Answer 1

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.

• s.k: If the -h localhost is given, pg_restore will prompt for a password for the user postgres. But if this user relies on a peer connection, it actually doesn't have a password. How would you bypass this?

Answer 2

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.

Answer 3

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.