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:
- 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 31.164.122.223 md5
- restarted the process using either:
postgres@servername:~$ sudo service postgresql@12-db_test restart
or
pg_ctlcluster 12 db_test start
show hba_file ;