Postgresql psql: error: FATAL: Peer authentication failed for user "userrole"

Question

I have installed PostgreSQL and created a user 'userrole' with superuser privileges. Also able to connect through python code.

import psycopg2
conn = psycopg2.connect(
   database="postgres", user="userrole", password="userroot", host='127.0.0.1', port= '5432'
)
cursor = conn.cursor()
cursor.execute("select version()")
data = cursor.fetchone()
print("Connection established to: ",data)
conn.close()

This is my output:

Connection established to:  ('PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit',)

Process finished with exit code 0

The issue I'm facing is through CLI is cannot connect to this user:

(venv) resh@project:~/PycharmProjects/utilities$ sudo su -l userrole
[sudo] password for resh: 
su: user userrole does not exist

Sorry, I am new to ubuntu, now I have changed the command and still getting an issue :

(venv) resh@project:~/PycharmProjects/utilities$ sudo psql -U userrole
[sudo] password for resh: 
psql: error: FATAL:  Peer authentication failed for user "userrole"

A database user is different from an operating system user. su is about operating system users. — Laurenz Albe
– Laurenz Albe, Commented Apr 2, 2021 at 6:12
Now i am trying to access psql user and still facing the issue @LaurenzAlbe — Reshma Shyleshkumar
– Reshma Shyleshkumar, Commented Apr 2, 2021 at 6:20
Since I don't know what you are doing, I cannot say what you are doing wrong. Perhaps edit the question and add the new command. — Laurenz Albe
– Laurenz Albe, Commented Apr 2, 2021 at 6:30

jjanes · Accepted Answer · 2021-04-02 14:28:48Z

13

Your latest psql attempt is trying to connect over the Unix socket, which is (apparently, based on error message) configured to user peer authentication.

To use the password, you need to connect over TCP, like your python is doing:

psql -U userrole -h 127.0.0.1

Also, the sudo is useless for password-based authentication, so I removed it

answered Apr 2, 2021 at 14:28

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RiveN · Accepted Answer · 2022-05-19 12:03:47Z

2

I had the same problem and fixed it by using following command in Ubuntu:

psql -d database -U username -h 127.0.0.1

edited May 19, 2022 at 12:03

RiveN

2,65911 gold badges17 silver badges29 bronze badges

answered May 18, 2022 at 21:21

xhe

411 bronze badge

Comments

Tim Roberts · Accepted Answer · 2021-04-02 06:12:14Z

1

userrole is an SQL user. It is not a Linux user. The two user lists are completely separate.

answered Apr 2, 2021 at 6:12

Tim Roberts

55.3k4 gold badges28 silver badges41 bronze badges

Comments

Study It · Accepted Answer · 2024-03-18 19:43:59Z

0

Searched and scoured the web for a answer who will fix the problem. In my case it was solved simply by adding this line

TYPE DATABASE USER ADDRESS METHOD

local all youruser md5

edited Mar 18, 2024 at 19:43

answered Mar 18, 2024 at 19:39

Study It

11 bronze badge

Comments

Codemaker2015 · Accepted Answer · 2023-02-14 18:07:20Z

-1

Open the pg_hba.conf file using any text editor. It is located at /etc/postgresql/[major.minor]/main/pg_hba.conf eg /etc/postgresql/12/main/pg_hba.conf location.

Change the following line

local   all    postgres        peer

to

local   all    postgres        md5

answered Feb 14, 2023 at 18:07

Codemaker2015

16k9 gold badges118 silver badges93 bronze badges

Collectives™ on Stack Overflow

Postgresql psql: error: FATAL: Peer authentication failed for user "userrole"

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Related