Peer authentication failed for user postgres - what should I do now?

2.9K Asked by ai_7420 in SQL Server , Asked on Oct 4, 2022

I just installed PostgreSQL 9.4 on Ubuntu 15.10.

I created a user with createuser -P myuser

I created a database with createdb -O myuser mydatabase

I edited pg_hba.conf and added local mydatabase myuser md5

I restarted PostgreSQL with sudo service postgresql restart

User myuser is a PostgresSQL user only and has no user account on Ubuntu.

When I try to connect to the database with psql -W mydatabase myuser it fails with psql: FATAL: Peer authentication failed for user "myuser".

PostgreSQL is running …

● postgresql.service - PostgreSQL RDBMS

Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)

Active: active (excited) since Thu 2016-03-03 09:53:00 CET; 9min ago

Process: 22219 ExecStart=/bin/true (code=exited, status=0/SUCCESS)

Main PID: 22219 (code=exited, status=0/SUCCESS)

Mar 03 09:53:00 SERVER01 systemd[1]: Starting PostgreSQL RDBMS...

Mar 03 09:53:00 SERVER01 systemd[1]: Started PostgreSQL RDBMS.

... and listening.

Proto Recv-Q Send-Q Local Address Foreign Address State

tcp        0      0 localhost:postgresql    *:*                     LISTEN
tcp6       0      0 localhost:postgresql    [::]:*                  LISTEN

Active UNIX domain sockets (only servers)

Proto RefCnt Flags Type State I-Node Path

unix 2 [ ACC ] STREAM LISTENING 151534 /var/run/postgresql/.s.PGSQL.5432

What do I have to do to connect with user myuser to database mydatabase?

Answered by Amit raj

If peer authentication failed for user postgres - First... check that you have the lines permissioning to the myuser user in pg_hba.conf. For example:

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

Or any other lines of permission to IPV4 (and IPv6 if you use) with: TYPE DATABASE USER ADDRESS METHOD

After this check, run the psql as follows:
psql -h localhost -U myuser mydatabase

And then, at the requested prompt, enter the user's password myuser.

Your Answer

Answer (1)

Ranjana

Resolving "Peer Authentication Failed For User Postgres" Error

The "Peer authentication failed for user postgres" error usually happens when PostgreSQL tries to authenticate a connection using peer authentication, but the operating system user doesn't match the PostgreSQL user.

Here's how to resolve this issue:

1. Switch to the Postgres User

If you have sudo privileges, switch to the postgres user before running the psql command:

sudo -i -u postgres

psql

2. Modify the pg_hba.conf File

Change the authentication method in the pg_hba.conf file from peer to password-based authentication.

Locate the pg_hba.conf file:

Common locations include:
Debian-based systems: /etc/postgresql//main/pg_hba.conf
Red Hat-based systems: /var/lib/pgsql//data/pg_hba.conf
Alternatively, run the following query to find the file location:

SHOW hba_file;

Edit the pg_hba.conf file:

  local   all             postgres                                peer

Change peer to md5 or password:

local all postgres md5

Restart PostgreSQL:

Apply the changes by restarting PostgreSQL:

sudo systemctl restart postgresql

For older systems:

sudo service postgresql restart

6 Months