Using SSL for secure connections to the DB

Hi Everyone,

Hope you guys can help.

I moved our Database to a separate server and I would like to use SSL for all connections (The server runs RH9, PostgreSQL 8).

I read the postgresql documentation and setup everything accordingly. I.e.

- built the server with ssl support.

- changed the postgresql.conf file to enable ssl

- changed pg_hba.conf file to only allow ssl connections from certain hosts. (All entries were changed to “hostssl” in order to force SSL connections).

- generated the server certificate and key.

- rebuilt libpqxx (that’s sits on top of libpq)

- rebuilt our application programs that use libpqxx

I tested the setup initially with PGAdmin3 by changing the SSL option to “require”.  And this seemed to work just fine.

The problem came in when I tried to change our application programs (that use the libpqxx library) to use SSL connections (They are Web based apps and we use apache).

I changed the connection string in all connections to include the “sslmode=require” option and started testing. 

When the applications try to connect to the database server the following message appears in the Postgresql log file:

------------------------------------------------------------------------------------------------------------------

Could not accept SSL connection:  EOF detected

-------------------------------------------------------------------------------------------------------------------

I googled this but I did not find much useful information on the subject.

I tried several things to resolve this but I kept getting the same messages.  I also tried this from Perl and Tcl and but still get the same result.

Funny thing is – it does not matter what I change “sslmode” to – I still get the same error message in the log – even when I change “hostssl” to just “host” in the pg_hba.conf file I still get the same messages in the log.

Could it have something to do with my ssl certificates?  I do not use the “root.crt” file, so the server should not request or check client certificates and should only use ssl for communication security (according to the documentation).  The way I understand this is that a user’s (apache) normal password will be used for authentication and that ssl will only be used to encrypt the communication between client and server.  Is this assumption correct?  (This did seem to apply when I tested the setup with PGAdmin3.) 

In the future I would like to implement client authentication via certificates but as far as I can tell (googled) this cannot be achieved at the application level yet.  Is this true?

If it’s not – how do I ensure that the client certificate is supplied when the program runs when started from apache?

Thanks in advance.

Hannes Wagener