Postgres not allowing localhost but works with 127.0.0.1
In pg_hba.conf, the first match counts. The manual:
The first record with a matching connection type, client address,requested database, and user name is used to perform authentication.There is no "fall-through" or "backup": if one record is chosen andthe authentication fails, subsequent records are not considered. If norecord matches, access is denied.
Note the reversed order:
host all all 127.0.0.1/32 trusthost all all 127.0.0.1/32 ident
But:
host all all localhost identhost all all localhost trust
Remember to reload after saving changes to pg_hba.conf
. (Restart is not necessary.) The manual:
The
pg_hba.conf
file is read on start-up and when the main serverprocess receives aSIGHUP
signal. If you edit the file on an activesystem, you will need to signal the postmaster (usingpg_ctl reload
,calling the SQL functionpg_reload_conf()
, or usingkill -HUP
) tomake it re-read the file.
If you really "add" the lines like you wrote, there should not be any effect at all. But if you replace the lines, there is.
In the first case, you get trust
authentication method, which is an open-door policy. The manual:
PostgreSQL assumes that anyone who can connect to the server isauthorized to access the database with whatever database user namethey specify (even superuser names)
But in the second case you get the ident
authentication method, which has to be set up properly to work.
Plus, as Cas pointed out later, localhost
covers both IPv4 and IPv6, while 127.0.0.1/32
only applies to IPv4.
If you are actually using the outdated version 8.4, go to the old manual for 8.4. You are aware that 8.4 has reached EOL in 2014 and is not supported any more? Consider upgrading to a current version.
In Postgres 9.1 or later you would rather use peer
than ident
.
More:
The Problem
Postgres will potentially use IPv6 when specifying -h localhost
which given the above pg_hba.conf
specifies ident
, a password prompt will be returned.
However when -h 127.0.0.1
is specified, it forces Postgres to use IPv4, which is set to trust
in above config and allows access without password.
The Answer
Thus the answer is to modify the IPv6 host line in pg_hba.conf
to use trust
:
# IPv6 local connections:host all all ::1/128 trust
Remembering to restart the Postgres service after making config changes.