loading...

PostgreSQL: Confused by error messages around TLS connection

nabbisen profile image Heddi Nabbisen ・2 min read

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
Enter fullscreen mode Exit fullscreen mode

Here, hostssl means only connection made with TLS/SSL encryption are acceptable.

Then I tried:

$ psql -U $USER -h $REMOTE_HOST ...
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Discussion

pic
Editor guide