Cannot login to PostgreSQL when I specify "-h localhost"

Question

I use Ubuntu 14.10 and installed PostgreSQL 9.2 from PostgreSQL official apt repository. (apt.postgresql.org)

When I switched user postgres and try following command, I can successfully login.

$ psql -U postgres dbname -W
Password for user postgres: (Enter Password)
psql (9.2.9)
Type "help" for help.

dbname=# 

However, when I specify host value, I cannot login with following error.

$ psql -h localhost -U postgres notel -W
Password for user postgres: 
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

I'm trying to connect from Sequelize.js, an ORM for node.js, but I experienced almost the same error message:

Possibly unhandled Error: error: password authentication failed for user "postgres"

Does anyone know how I can solve this problem?

Edit

My pg_hba.conf is as follows:

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

I refered document about pg_hba.conf, but I don't know what's wrong...

Answer 1

Most likely this has to do with the client authentication file: pg_hba.conf. It holds entries for each host/socket/user/password/database combination.

When you change your host to localhost, you have a different access route than when you connect directly over a Unix socket. You will patch yourself through TCP/IP instead of going "directly". If you open your pg_hba.conf file, you will find a bunch of rules at the end. These rules define which combinations are allowed to access the database.

In your case, look for lines that start with host, which means access through TCP/IP (and thus localhost) as opposed to local which means a Unix socket.

Probably there is a line tucked in there which prevents host connection access, or not via the credentials you think are correct (peer/md5 pitfall, read below).

As you show in your pg_hba.conf file you have local entries with peer authentication and host entries with md5 authentication. If you don't know the difference between the two authentication mechanisms, then that is your culprit at the moment and can cause some serious head-banging (not the Metal kind; the Against-a-wall kind).

Common pitfall

To avoid possible confusion, the difference between peer and md5 is ground for a common pitfall. They both use a user called postgres (when using -U postgres, that is), but the former is actual a Unix user created during installment of your PostgreSQL system, the latter is a database user created inside your PostgreSQL bookkeeping tables.

Always remember, if your setting is peer, use the credentials of the Unix user, if it is md5 use the credentials of the database user.

If no password has been set for the database user postgres, make sure you set one first. Empty passwords are not allowed either.

Extra notes

Always try to make your rules specific, avoid too many all entries for databases and users as this could put your installation wide open.

The first line that fits your access combination will be picked and any subsequent lines will be ignored. Make sure that there is no higher line that overwrites your rule.

Remember to restart your PostgreSQL daemon after changing this file, otherwise the changes won't be picked up.