Postgresql – Postgres authentication fails: log refers to “line 90” in pg_hba.conf that shouldn’t exist

pg-hba.confpostgresql

Whatever settings I try, I keep getting connection errors, from external clients as well as trying to connect from a running console on the server itself.

My last attempt for a local connection with "user_test":

psql -h /var/run/postgresql -p5433 -U user_test

When I checked the log on /var/log/postgresql/postgresql-12-db_test.log he last line on the log puzzled me: why is the log referring to "line 90" in the pg_hba.conf? That line should not exist???

2019-12-23 00:24:50.620 CET [11398] LOG:  listening on IPv4 address "0.0.0.0", port 5433
2019-12-23 00:24:50.620 CET [11398] LOG:  listening on IPv6 address "::", port 5433
2019-12-23 00:24:50.623 CET [11398] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2019-12-23 00:24:50.649 CET [11399] LOG:  database system was shut down at 2019-12-23 00:24:50 CET
2019-12-23 00:24:50.654 CET [11398] LOG:  database system is ready to accept connections
2019-12-23 00:25:22.452 CET [11426] user_test@user_test LOG:  provided user name (user_test) and authenticated user name (postgres) do not match
2019-12-23 00:25:22.452 CET [11426] user_test@user_test FATAL:  Peer authentication failed for user "user_test"
2019-12-23 00:25:22.452 CET [11426] user_test@user_test DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"

Here's a summary of my setup:

  1. Fresh installation of postgesql 12 on ubuntu cloud-server
  2. created a cluster "db_test" (alongside to the standard installation "main")
  3. added a role "admin" and assigned user "user_test" to the cluster
  4. told postgres to listen to all ports (ALTER SYSTEM SET listen_addresses='*';)
  5. modified pg_hba.conf (sudo nano /var/lib/postgresql/12/db_test/pg_hba.conf)

That file currently reads (had many other settings none worked):

# TYPE  DATABASE USER ADDRESS METHOD
local all user_test trust
hostssl all all 31.164.122.223 md5
  1. restarted the process using either:
postgres@servername:~$ sudo service postgresql@12-db_test restart

or

pg_ctlcluster 12 db_test start

Best Answer

User jjanes' comment helped me find the real hba-file. It's actually here:

/etc/postgresql/12/db_test/pg_hba.conf

(and not in the /var/lib/postgresql/12/... Don't remember why I had expected it there. As I'm new to all this, the branches of a linux directory tree are misterious to me.)

Related Question