0

I think I have exhausted everything I can think of. Using postgresql 16

If I try to connect with

psql -d dbname -h localhost

it prompts for a password and connects just fine. However, if I try to connect with

psql -d dbname -h 192.168.86.27

it does not prompt for password and I get this message

psql: error: connection to server at "192.168.86.27", port 5432 failed: FATAL: no pg_hba.conf entry for host "192.168.86.27", user "postgres", database "dbname", no encryption

My pg_hba.conf contains this

local all all trust local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust host all all 0.0.0.0/0 trust host all all ::0/0 trust host all all 192.168.0.0/16 trust

My postgresql.auto.conf contains

listen_addresses = '*'

netstat -nlt shows that the ports should be accepting all connections

Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0 192.168.122.1:53 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN tcp6 0 0 :::5432 :::* LISTEN tcp6 0 0 :::111 :::* LISTEN tcp6 0 0 :::22 :::* LISTEN tcp6 0 0 ::1:631 :::* LISTEN

I realize the security implications of setting it up this way. I'm just trying to get it to work at this point.

Improve this question
5
  • The netstat output is unnecessary; you already know that the postgres server is accepting the connection (and responding with an error message regarding access control). Commented Sep 11, 2024 at 18:30
  • I can't reproduce this behavior. I set up this test environment, and once it's running I can psql -h 192.168.86.27 -U postgres and connect without errors. Commented Sep 12, 2024 at 1:49
  • Are you /sure/ that [that specific] configuration is being used? There is no entry in there that would prompt for a password! "trust" just lets you in. The likes of "md5" would start asking for a password. Commented Sep 12, 2024 at 6:54
  • That's what I mean. It all makes no sense. It shouldn't prompt for a password, but does. It should allow through every connection, but it definitely does not. The netstat output was included to show that the listen_address was working correctly. Commented Sep 12, 2024 at 12:25
  • I have pg_hba down to 2 lines now //host all all 0.0.0.0/0 trust// //host all all ::0/0 trust// Without specifying host to psql, works without password. Specifying localhost prompts for password. Specifying IP gets error (no prompt for passwod). I'm looking at pg_hba.conf in /usr/local/pgsql/data ($PGDATA). I am looking at the wrong file? Commented Sep 12, 2024 at 12:36

1 Answer 1

Reset to default
0

I think I found the issue. I was working with the wrong pg_hba.conf file. The one in the PGDATA directory (/usr/local/pgsql/data) is the incorrect one. The real file is in /var/lib/pgsql/16/data.

I modified that file and it appears to be working correctly now.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.