psql: FATAL: Peer authentication failed for user "dev"
Try:
psql -U user_name -h 127.0.0.1 -d db_name
where
-U
is the database user name-h
is the hostname/IP of the local server, thus avoiding Unix domain sockets-d
is the database name to connect to
This is then evaluated as a "network" connection by Postgresql rather than a Unix domain socket connection, thus not evaluated as a "local" connect as you might see in pg_hba.conf
:
local all all peer
Your connection failed because by default psql
connects over UNIX sockets using peer
authentication, that requires the current UNIX user to have the same user name as psql
. So you will have to create the UNIX user dev
and then login as dev
or use sudo -u dev psql test_development
for accessing the database (and psql
should not ask for a password).
If you cannot or do not want to create the UNIX user, like if you just want to connect to your database for ad hoc queries, forcing a socket connection using psql --host=localhost --dbname=test_development --username=dev
(as pointed out by @meyerson answer) will solve your immediate problem.
But if you intend to force password authentication over Unix sockets instead of the peer method, try changing the following pg_hba.conf
* line:
from
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
to
# TYPE DATABASE USER ADDRESS METHOD
local all all md5
peer
means it will trust the identity (authenticity) of UNIX user. So not asking for a password.md5
means it will always ask for a password, and validate it after hashing withMD5
.
You can, of course, also create more specific rules for a specific database or user, with some users having peer
and others requiring passwords.
After changing pg_hba.conf
if PostgreSQL is running you'll need to make it re-read the configuration by reloading (pg_ctl reload
) or restarting (sudo service postgresql restart
).
* The file pg_hba.conf
will most likely be at /etc/postgresql/9.x/main/pg_hba.conf
Edited: Remarks from @Chloe, @JavierEH, @Jonas Eicher, @fccoelho, @Joanis, @Uphill_What comments incorporated into answer.