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