The order of records is significant in pg_hba.conf

1 Issues

DB user "grouper" has been created with password set. password_encryption is "scram-sha-256".

The pg_hba.conf file is configured as below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                              peer
# IPv4 local connections:
host    all             all             127.0.0.1/32     ident
host    idp             idpaudit        samenet          scram-sha-256
host    all             grouper         samenet          scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128          ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                               peer
host    replication     all             127.0.0.1/32      ident
host    replication     all             ::1/128           ident

From the the host, log in as "grouper" and got errors.

$ psql -h 127.0.0.1 -U grouper

psql: error: FATAL:  Ident authentication failed for user "grouper"

2 Root cause 

The reason is the record in red was selected rather than in green. Based on the official doc, the first matching record is used and the following records will never be considered.

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.

That's why in the error message, we see "Ident".

3 Solution

3.1 Solutin 1

The issue can be fixed easily by change 127.0.0.1 to the non-local IP address. e.g.

$ psql -h 192.168.0.20  -U grouper -d postgres

Password for user grouper:

psql (13.11)

Type "help" for help.

3.2 Solution 2

We can change the pg_hba.conf to match 127.0.0.1 for scram-sha-256.

Before:

host    all             all             127.0.0.1/32     ident

After:

host    all             all             127.0.0.1/32     scram-sha-256

4 Other

Let have a look at the first record in pg_hba.conf.

local   all             all                              peer

This record is crucial to make sure the OS user "postgres" can connect as "postgres" role with a simple psql command.

[postgres@mk8 ~]$ psql

psql (13.11)

Type "help" for help.

"psql" command by default connects by Unix Socket (matching local type). So the first record matches and is used for authn.

The "peer" auth method, just compares the client's running account name with the DB user name. As "postgres" exists in both /etc/passwd and the DB, it is authenticated successfully.