PostgreSQL Peer Authentication Failed – How to Resolve psql FATAL Error?

authenticationpg-hba.confpostgresqlpostgresql-9.4

I just installed PostgreSQL 9.4 on Ubuntu 15.10.

  1. I created a user with createuser -P myuser
  2. I created a database with createdb -O myuser mydatabase
  3. I edited pg_hba.conf and added local mydatabase myuser md5
  4. 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 (exited) 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?

Best Answer

In a fresh install from a few days ago, the second line of my pg_hba.conf is

local   all             all              peer

I believe this is the one that makes your connection attempt fail.

The order of rules matter here: the first one that matches the access method, username, database name and source IP range will be considered. If it fails, then there is no second try, so the connection attempt will likely fail. Or, as the documentation states:

There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

The solution is easy: either remove the above line if you don't plan to use peer authentication, or move your specific rule above this one.

Related Question