psql: FATAL: Peer authentication failed for user

Question

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?

Answer 1

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.

• Daniel: Moving the line worked.
• s.k: How could we store the password by using a 'md5' auth so that we don't have to type it in again and again... ?

Answer 2

First of all, check if you have the lines giving permission 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, the requested prompt, enter the user's password myuser.

• Alvaro Neto: And of course, remove the peer authentication.
• Mark Carpenter Jr: Have an instance I updated to allow remote connections, the listen address is set to '*' and added the host all all 0.0.0.0/0 md5 rule. Didn't have to remove the peer auth. Just worked with the -h localhost param. Best answer since you most likely don't have to touch the config on a stock install.