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

Issue

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"

Solution

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 By - jjanes

Answer Checked By - David Marino (WPSolving Volunteer)