When I try to open psql with this command:
psql -U postgres
I get this error:
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"
But it connects successfully when I use:
sudo -u postgres psql
Can someone please explain what is happening and how to diagnose/fix this problem? My pg_hba.conf contains the following:
# 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 md5
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
Peer authentication means that the connection is only allowed if the name of the database user is the same as the name of the operating system user.
So if you run psql -U postgres as operating system user root or jimmy, it won't work.
You can specify a mapping between operating system users and database users in pg_ident.conf.
You can edit your .conf files with privileges using an editor, for my case it is nano.
$sudo nano /etc/postgresql/14/main/pg_ident.conf
Map your user by adding this line
# MAPNAME SYSTEM-USERNAME PG-USERNAME
user1 <computer-username> postgres
Replace the <computer-username> with the System-Username, which can be found using the whoami command. Type in your terminal:
$whoami
Then go ahead to the pg_hba.conf
$sudo nano /etc/postgresql/14/main/pg_hba.conf
Add your postgre user, with method=peer, as shown below:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
This worked for me.
This error occurs when you do not have authorized access to connect to the PostgreSQL server on the specified socket. One possible solution is to change the configuration of the pg_hba.conf file to allow peer authentication for the user "postgres" . It is also suggested to review the configuration of the postgresql.conf file, it is recommended to change the port to 5432 and reboot the machine.
Another solution is to connect locally using local permissions, it is suggested to run the following command:
psql -h /var/run/postgresqlthis would allow you to connect locally.