psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres" (Ubuntu)

Question

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

score 38 · Accepted Answer · edited May 19 '23 at 21:56

38

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 with privileges

$sudo nano /etc/postgresql/14/main/pg_hba.conf

Add your postgres user, with method=peer, as shown below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer

This worked for me.

edited May 19 '23 at 21:56

aakaasaraamanna

61
10

answered Jan 18 '22 at 00:26

Daniel Ado

514
4
7

24

Changing `$sudo nano /etc/postgresql/14/main/pg_hba.conf` the line `local all postgres peer` from `peer` to `trust` then `systemctl restart postgresql@14-main.service` it works for me. – Jason Liu Feb 28 '22 at 07:56
2

I don't know what is the implication of the above solution but it worked for me thanks. – Raghavendra Acharya May 22 '22 at 04:56
1

@RaghavendraAcharya editing the pg_hba.conf allows the user (postgres to authenticate or connect* with the database backend), the computer username is mapped with the allowed database user which is why you added it as a peer in the pg_ident.conf file. The connection parameter is localhost, which is handled locally in your machine. – Daniel Ado May 22 '22 at 11:30
4

`local all postgres md5` will make the password really work. `trust` mode will never ask for password. – Ivan Veselovsky Oct 05 '22 at 15:47
It worked for me. However, don't forget to should reload the postgresql service to take effect. – Saran Raj Jul 19 '23 at 05:22

score 32 · Answer 2 · answered Oct 22 '21 at 14:36

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.

score 4 · Answer 3 · answered May 09 '23 at 11:53

I was trying to make the answer about using /etc/postgresql/14/main/pg_ident.conf work by adding a map like this:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
user1             <computer-username>               postgres

Then adding the next code to /etc/postgresql/14/main/pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer

But that didn't work for me. Then I read this documentation and found the solution! It's just a matter of adding a reference to the map to pg_hba.conf with map=user1:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer map=user1

Thanks! After a lot of tries found your ans working, but there is a problem, After implementing your solution `sudo -u postgres` is not working any more it ask for ubuntu user password after giving password showing an error — sifat, Jun 19 '23 at 06:25

score 3 · Answer 4 · answered Dec 12 '22 at 05:11

step-1 
vi /etc/postgresql/14/main# vi postgresql.conf

listen_addresses = '*'

step-2  

vi /etc/postgresql/14/main# vi pg_hba.conf

# 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             0.0.0.0/0            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

step-3
sudo /etc/init.d/postgresql restart

step-4
After restart your changes
create your required database and database username

score 3 · Answer 5 · answered Dec 15 '22 at 10:34

I don't think in production you wana bother yourself changing any files.
So, What you can do is temporarily make the username same to the username of your postgres which is usually postgres by passing command sudo -i -u postgres and then psql, now you successfully connected to postgres.

Kingsley Ike · Answer 6 · 2023-08-29T01:16:50.653

Depending on your use case and environment. For me, most of my environment is local. Just in case the above example fails to work. you can try this:

Edit the pg_indent.conf file

***$sudo nano /etc/postgresql/14/main/pg_ident.conf***

Add

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
user1           <computer-username>     <computer-username>

To find your computer user, you can use this command whoami, who, w ,who -q. any of those should give your a hint of the current user

> So you can replace your username against <computer-username>. So your
SYSTEM_USERNAME & PG-USERNAME should be = your username

Next

*sudo nano pg_hba.conf*

$sudo nano /etc/postgresql/14/main/pg_hba.conf

Add the changes to satisfy your usecase

*# TYPE  DATABASE        USER            ADDRESS                 METHOD
local    all             <Computer-username>                     peer map=user1

So this allows you to login without specifying our password

Make sure to restart postgresql

sudo /etc/init.d/postgresql restart

After restart your changes create your required database and database username

score 0 · Answer 7 · answered Aug 08 '23 at 01:52

If the above solutions do not work, try this one! It seems that it is a connection problem; probably, your issue would resolve by specifying all connection parameters. The general form of the command could be like this:

pg_dump --dbname=<DB-NAME> --username=<USER-NAME-OF-DB-OWNER> --port=<USUALLY: 5432> --host=<USUALLY: 127.0.0.1> --password > <DUMP-NAME>.sql

In addition, you can use different parameters for the dumping process.

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres" (Ubuntu)

7 Answers7

Linked