18

Postgres not accepting connection if I say -h localhost but it works if I say -h 127.0.0.1

[root@5d9ca0effd7f opensips]# psql -U postgres -h localhost -W
Password for user postgres:
psql: FATAL:  Ident authentication failed for user "postgres"
[root@5d9ca0effd7f opensips]# psql -U postgres -h 127.0.0.1 -W
Password for user postgres:
psql (8.4.20)
Type "help" for help.

postgres=#

My /var/lib/pgsql/data/pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                              trust
local   all         all                              ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host    all         all         127.0.0.1/32          ident
# IPv6 local connections:
host    all         all         ::1/128               ident

If I add following line then Postgres service failed to start:

host    all         all        localhost             ident
host    all         all        localhost             trust

Wwhat is wrong there?

Update

My /etc/hosts file:

[root@5d9ca0effd7f opensips]# cat /etc/hosts
172.17.0.2      5d9ca0effd7f
127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
Community
  • 1
  • 1
Satish
  • 16,544
  • 29
  • 93
  • 149

2 Answers2

16

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 and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

Note the reversed order:

host    all         all         127.0.0.1/32          trust
host    all         all         127.0.0.1/32          ident

But:

host    all         all        localhost             ident
host    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 server process receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload, calling the SQL function pg_reload_conf(), or using kill -HUP) to make 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 is authorized to access the database with whatever database user name they 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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • it was `8.4` now i have upgraded to `9.4` and getting error now `psql: FATAL: Ident authentication failed for user "postgres"` I have put `ident` first in `pg_hba.conf` file. also i have changed `postgres` password but not allowing me to get in. – Satish Apr 22 '15 at 17:25
  • 2
    Holy cow!! its works when i change to `host all all 127.0.0.1/32 md5` How???? – Satish Apr 22 '15 at 17:43
  • @Satish: Because `md5` is a completely different authentication method. Read a bit in the manual, I provided links. In Postgres 9.1 or later you would also rather use `peer` than `ident`. – Erwin Brandstetter Apr 22 '15 at 17:53
13

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.

Cas
  • 6,123
  • 3
  • 36
  • 35