Sample Header Ad - 728x90

Postgres authentication fails: log refers to "line 90" in pg_hba.conf that shouldn't exist

0 votes
1 answer
472 views
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  LOG:  listening on IPv4 address "0.0.0.0", port 5433
2019-12-23 00:24:50.620 CET  LOG:  listening on IPv6 address "::", port 5433
2019-12-23 00:24:50.623 CET  LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2019-12-23 00:24:50.649 CET  LOG:  database system was shut down at 2019-12-23 00:24:50 CET
2019-12-23 00:24:50.654 CET  LOG:  database system is ready to accept connections
2019-12-23 00:25:22.452 CET  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  user_test@user_test FATAL:  Peer authentication failed for user "user_test"
2019-12-23 00:25:22.452 CET  user_test@user_test DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"
Here's a summary of my setup: 0. Fresh installation of postgesql 12 on ubuntu cloud-server 1. created a cluster "db_test" (alongside to the standard installation "main") 2. added a role "admin" and assigned user "user_test" to the cluster 3. told postgres to listen to all ports (ALTER SYSTEM SET listen_addresses='*';) 4. 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
5. restarted the process using either:
postgres@servername:~$ sudo service postgresql@12-db_test restart
or
pg_ctlcluster 12 db_test start
Asked by xof (35 rep)
Dec 23, 2019, 08:26 AM
Last activity: Dec 23, 2019, 02:20 PM