3

We have an application that connects to PostgreSQL 8.0, 9.0 or 9.5 (different versions for different clients). Recently we decided to add SSL connection for more security. Everything seems good on 9.0 and 9.5 but not in 8.0.

Here is what I do to test the connection (I will compare 8.0 and 9.0 settings as they are quite similar). I am testing the connection on a local machine, it is done locally just for testing so don't tell me to turn ssl off for localhost, this is not the answer I am looking for.

I have prepared all the needed certificates. Server side:

root.crt
server.crt
server.key

And I have put those files in \data folders on both 9.0 and 8.0 PostgreSQL installations.

I have client certificates:

postgresql.crt
postgresql.key

They are in \appdata\Roaming\postgresql folder on the same machine.

I edited postgresql.conf in both 8.0 and 9.0 and set this option:

ssl = on (I have tried ssl = true too)

In pg_hba.conf I have only one connect option:

TYPE     DATABASE USER CIDR-ADDRESS   METHOD

9.0:

hostssl   all    all   ::1/128   cert

8.0

hostssl   all    all   127.0.0.1/32 md5 clientcert=1

In 8.0 I use "md5 clientcert=1" because there is no "cert" option (I tried "trust" and "md5" too) and I tried different addresses as well - ::1/128, even "all". The result is always the same - I cannot connect to 8.0 server if hostssl option is the only available. I get this error:

SSL error: tlsv1 alet decrypt error FATAL: no pg_hba.conf entry for host "127.0.0.1", user "SU", database "template1", SSL off

I have no problem connecting to 9.0 (and to 9.5). I use PgAdmin III to try to connect because if I connect using it, I will be able to connect to the server with the application too.

Does anyone has an idea why I cannot connect through SSL to PostgresSQL 8.0?

Georgi Bonchev
  • 269
  • 4
  • 12

2 Answers2

1

Whoever is using PostgreSQL 8.0 is not sufficiently concerned with security to care about SSL connections, right?

For example, since the database is subject to CVE-2013-1899, anybody with network access to the server can write over arbitrary files in the database.

That said, I'd assume that the problem is that the 8.0 server uses an old version of OpenSSL, e.g. one without a fix for CVE-2009-3555, and later versions of OpenSSL that contain the fix refuse the handshake.

You could consider upgrading OpenSSL on the 8.0 server.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Yes, I know PostgreSQL 8.0 has a lot of security and other problems, still clients used it. Anyway, thank you, probably old OpenSSL is the problem. – Georgi Bonchev Jun 20 '17 at 12:34
0

Finally I managed to make SSL connection to PostgreSQL 8.0. When making certificate files (*.crt) with OpenSSL this option should be added to the command prompt commands:

-sha1

Otherwise it uses as dafault SHA-256 but this algorithm is not supported in the old openssl version that is included in PostgreSQL 8.0.

Georgi Bonchev
  • 269
  • 4
  • 12