I got this kind of error when trying to access postgres more than once:
psql: FATAL: Peer authentication failed for user "postgres"
Also I wanted to be able to use postgres from a script without having to type the password in. Here’s what’s happening with posgres authentication.
pg_hba.conf
Postgres is doing something it calls ‘host based authentication’. There is a config file for this called pg_hba.conf. pg_hba.conf allows you to configure an authentication method for a login attempt depending on whether the attempt is local or over the network and which user it is attempting to log in as. Authentication methods are many and varied but of particular interest are ‘peer’, ‘trust’, and ‘md5’
It looks like this:
## TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local awesome some_user trust
local postgres fran peer
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
Postgres uses the first line that matches the connection type/database/user to select the authentication method it will use. So if you’re trying to connect locally, to database awesome as user some_user:
$ psql awesome --user some_user
postgres will match the first line and use the ‘trust’ authentication. And if you’re trying to connect locally to db ‘postgres’ as fran it’ll use ‘peer’. Locally any other db/user combination gets caught in the 3rd line and then ‘md5’ is used.
- ‘trust’ means that postgres will let you in without a password.
- ‘peer’ means that postgres will let you in without a password as long as your operating system user has the same name as the postgres user.
- ‘md5’ means it will ask for a password.
You can edit this file (it has helpful comments) and reload postgres:
$ sudo service postgresql reload
and try your new settings to see if they work any better. I couldn’t figure out how to get postgres to tell me which line in the pg_hba.conf it was using and why, so I found it helpful to temporarily reduce the config to the one line that I thought should be matching.
So if postgres tells you that ‘peer authentication failed for user "postgres"’ it means that it’s matching a line in pg_hba.conf that says it’s going to use peer authentication, and you are not logged in to the operating system as user ‘postgres’. Solutions are to log in as postgres for whatever it is you’re trying to do, or change the user you’re trying to access postgres as.
Using -h makes a host line match, not local.
I ran into a problem when I was trying a command like this:$ dropdb test --user fran -h localhost postgres kept asking me for a password even though I had a config line like this: local postgres fran peer that I thought should be being used. I removed all lines from the config except that one and instead of asking for a password postgres said:
FATAL: no pg_hba.conf entry for host "::1", user "fran", database "postgres", SSL off
and the clue is there where it says host "::1" instead of host "[local]". Using the -h switch made postgres see the login attempt as coming from over the network and it matched one of the host lines in pg_hba.conf instead of the local line I thought it would.
Can I just pass the password on the command line?
No. I’m pretty sure you can’t. However you can use a .pgpass file. Saved in your home directory and with lines like: localhost:5432:some_db:some_user:some_pass postgres will look to .pgpass for passwords before it asks them of you. It needs to have it’s perms set correctly though (as in the linked docs).
So if you need to pass a password you can use .pgpass, if you’re getting confusing authentication error messages trying to access postgres I hope this helps!
