psql: FATAL: Peer authentication failed for user “postgres” (or any user)
The 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 postgres
and then login as postgres
or use sudo -u postgres psql database-name
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=database-name --username=postgres
(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
. -
trust
means it will never ask for a password, and always trust any connection.
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
you'll need to restart PostgreSQL if it's running. E.g.sudo service postgresql restart
Steps to change/create default postgres
user's password:
trust
connection by adding inpg_hba.conf
file
local all postgres trust
- Restart postgresql service
sudo service postgresql restart
-
psql -U postgres
-
At the
postgres=#
prompt, change the user namepostgres
password:
ALTER USER postgres with password ‘new-password’;
- Revert the changes in
pg_hba.conf
file fromtrust
tomd5
and restartpostgresql
.
pg_hba.conf file location
The file pg_hba.conf
will most likely be at /etc/postgresql/9.x/main/pg_hba.conf
To check location of pg_hba.conf connect to postgres db using psql then type SHOW hba_file;
command.
After change pg_hba.conf file, you can execute SELECT pg_reload_conf();
or pg_ctl reload with superuser instead of restart postgresql service.
* Source
Thanks a lot! 🥰🥰
if this didn't work with you,
you can use this way