psql: FATAL: Ident authentication failed for user "xxx" with method trust

Question

I've created a new role and Database in the following way (with postgres user):

CREATE ROLE xxx LOGIN
  PASSWORD 'CHOOSEAPASSWORD'
  NOSUPERUSER INHERIT NOCREATEDB CREATEROLE REPLICATION;

CREATE DATABASE xxx
  WITH OWNER = xxx
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       CONNECTION LIMIT = -1;

\connect xxx
GRANT ALL ON DATABASE xxx TO xxx;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xxx;

In the pg_hba.conf I've the following entries:

local   all             postgres                                trust
local   all             all                                     peer
# IPv4 local connections:
host    all             xxx             127.0.0.1/32            trust
host    all             all             127.0.0.1/32            ident

Nevertheless I can't connect with

psql -U xxx -h localhost -W

and get the error

psql: FATAL:  Ident authentication failed for user "xxx"

Any ideas where I could have a mistake? (I use PostgreSQL 9.4 and CentOS 6.7)

Are there other `pg_hba.conf` entries above? Did you `pg_ctl reload` or `select pg_reload_conf()` after making the change? — Craig Ringer, Sep 04 '15 at 01:52
There are no other entries in pg_hba.conf (behind is another entry for IPv6). I used the init script to restart after making the changes. (So I'm using neither pg_ctl reload nor select pg_reload_conf()) — Benedikt Bock, Sep 04 '15 at 07:50
... and if you use `-h 127.0.0.1` instead of `-h localhost`? — Craig Ringer, Sep 04 '15 at 08:49
127.0.0.1 works! But why? Technically 127.0.0.1 = localhost... — Benedikt Bock, Sep 04 '15 at 09:21

score 4 · Answer 1 · answered May 27 '21 at 13:55

4

Try to use 127.0.0.1 instead of localhost

answered May 27 '21 at 13:55

despotbg

740
6
12

Can you explailn why it work on 127.0.0.1 but not on localhost? – moanrisy Nov 12 '22 at 04:29

score 3 · Accepted Answer · answered Sep 04 '15 at 10:00

You haven't defined a rule for IPv6's localhost address ::1. Your host's resolver prefers the IPv6 address ::1 to the IPv4 address 127.0.0.1. So the IPv4 rule with trust never gets matched.

Add another rule for ::1

Another common issue, though already ruled out for you, is not having reloaded the config.

psql: FATAL: Ident authentication failed for user "xxx" with method trust

2 Answers2