loading...

PostgreSQL: Confused by error messages around TLS connection

nabbisen profile image Heddi Nabbisen ・2 min read

PostgreSQL: TLS Connection (2 Part Series)

1) PostgreSQL: TLS Connection 2) PostgreSQL: Confused by error messages around TLS connection

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

PostgreSQL: TLS Connection (2 Part Series)

1) PostgreSQL: TLS Connection 2) PostgreSQL: Confused by error messages around TLS connection

Posted on by:

nabbisen profile

Heddi Nabbisen

@nabbisen

An ICT designer/developer and a security monk. "With a cool brain and a warm heart", I am challenging unsolved problems in our society. I use OpenBSD/Rust/etc.

Discussion

markdown guide