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:
- Fresh installation of postgesql 12 on ubuntu cloud-server
- created a cluster "db_test" (alongside to the standard installation "main")
- added a role "admin" and assigned user "user_test" to the cluster
- told postgres to listen to all ports (ALTER SYSTEM SET listen_addresses='*';)
- 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 126.96.36.199 md5
- restarted the process using either:
postgres@servername:~$ sudo service postgresql@12-db_test restart
pg_ctlcluster 12 db_test start