This is a small story where I was in trouble to configure PostgreSQL TLS connection.
I added to /var/postgresql/data/pg_hba.conf in the database server a line like this:
hostssl all all 0.0.0.0/0 md5 clientcert=0
Here, hostssl
means only connection made with TLS/SSL encryption are acceptable.
Then I tried:
$ psql -U $USER -h $REMOTE_HOST ...
It resutled in the error:
SSL: certificate verify failed FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "...", database "...", SSL off
I was confused because the host I tried to connect was $REMOTE_HOST instead of 127.0.0.1.
I struggled to understand what really happened.
Finally, it made sense a little when hostssl
was replaced with host
for testing:
- hostssl all all 0.0.0.0/0 md5 clientcert=0
+ host all all 0.0.0.0/0 md5
Here, host
means connection made with TLS/SSL encryption is used if possible, otherwise plain one is.
psql
was successful this time. Hence I found 127.0.0.1 didn't matter. Without the necessity of TLS/SSL encryption, psql
could connect to $REMOTE_HOST.
I continued trials and solve the problem around certification files.
I don't know why 'rejects connection for host "127.0.0.1"' was shown in the error messages.
It might be fallback. Or maybe any effect in pg_hba.conf or postgresql.conf.
It was a tough but not-a-bad time because I was trained... 🙂
Top comments (0)