0

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
  • 1
    It is using a pg_hba other than the one you think it is. Since you can apparently log in as some user, try show hba_file ; – jjanes Dec 23 '19 at 13:30
  • THANK YOU! I had suspected that too, but I had a couple of knots in my head. Turns out, the hba-files I had been editing were not the original ones, by mistake I had created new files! (didn't realise nano is opening a new file if the specified file does not exist). Got a lot to learn... – xof Dec 23 '19 at 14:16
0

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.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.