Can connect to postgres through localhost without password but not through 127.0.0.1. Why?

Question

I have Kubuntu 14.10 desktop with PostgreSQL 9.4 database installed. I have changed the password of the postgres user in the database by executing the SQL:

ALTER USER postgres PASSWORD 'password';

And I can connect to DB server by psql -h localhost -U postgres -W and giving that password but I can also connect without a password requirement by simply psql -h localhost -U postgres.

On the other hand, if I run psql -h 127.0.0.1 -U postgres it prompts me for the password set before.

What is the difference between the localhost and 127.0.0.1 hosts and their login method? Where is it set? I see no localhost-related entries in the pg_hba.conf file.

Answer 1

The behavior you see might be caused by a password file. The password file is conventionally named ~/.pgpass on Unix systems, but a different filename can be given through the PGPASSFILE environmental variable.

I think a password file that contains a line for "localhost", but does not contain a line for "127.0.0.1" will show the behavior you're seeing. My own ~/.pgpass file contains this line.

localhost:*:*:postgres:password

Here's what happens when I try to connect just like you did.

$ psql -h localhost -U postgres
psql (9.3.5)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

sandbox=# \q
$ psql -h 127.0.0.1 -U postgres
Password for user postgres: 

Adding the line 127.0.0.1:*:*:postgres:password to ~/.pgpass lets me log in using 127.0.0.1 and no password.

$ psql -h 127.0.0.1 -U postgres
psql (9.3.5)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

sandbox=#