MD5 authentication for local users

Question

PostgreSQL 10.0, Ubuntu 16.04

I'd like users at localhost to login with password. For this purpose I moved rule with md5 to the top.

SHOW hba_file;

postgres=# # TYPE  DATABASE        USER            ADDRESS                 METHOD
postgres-# 
postgres-# # IPv4 local connections:
postgres-# host    all             all             127.0.0.1/32            md5
postgres-# # "local" is for Unix domain socket connections only
postgres-# local   all             all                                     peer
postgres-# # IPv6 local connections:
postgres-# host    all             all             ::1/128                 md5
postgres-# # Allow replication connections from localhost, by a user with the
postgres-# # replication privilege.
postgres-# local   replication     all                                     peer
postgres-# host    replication     all             127.0.0.1/32            md5
postgres-# host    replication     all             ::1/128                 md5

Then restarted the service:

sudo service postgresql restart

The problem:

michael@michael-HP:~$ psql -U admin -W
Password for user admin: 
psql: FATAL:  Peer authentication failed for user "admin"

Could you help me understand why my md5 settings doesn't work?

Answer 1

The manual page for psql states:

If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets.

So if your server is listening on a domain socket as well as over TCP/IP, the connection you are making will be of type local as explained on the pg_hba.conf manual page.

Consequently, it will match this line:

local   all             all                                     peer

This will attempt to use "peer authentication", as described here.

You should either:

• Change that line to require md5 authentication for local (domain-socket) connections.
• Specify the hostname in your psql command with -h 127.0.0.1/--host 127.0.0.1 to force use of TCP/IP.
• Disable Unix domain sockets completely, by setting unix_socket_directories to an empty string as described in the configuration section of the manual.