Hi Jeroen/Everyone,
Hope you guys can help (sorry – rather lengthy email and lots of questions this time round)
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.
I tested the setup initially with PGAdmin3 by changing the SSL option to “require”. And this seemed to work just fine.
I was also able to use psql to connect to the server.
The problem came in when I tried to enable our application programs (that use libpqxx) 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. When running this the server complained that “sslmode” is not a valid connection option.
Subsequently I changed it to “requiressl=1” rather and the server no longer complained – but things still did not work.
The following messages appear in the Postgresql log when the application tries to establish a connection to the server:
------------------------------------------------------------------------------------------------------------------
Unexpected EOF on client connection
SSL SYSCALL error: EOF detected”
could not receive data from client: Connection reset by peer.
-------------------------------------------------------------------------------------------------------------------
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.
Could it be that I am building our apps against a non ssl enabled libpq? Hence the “sslmode is not a valid connection option” message?
The development machine that I compile our apps on had postgres 7.3 installed from rpm during the initial Redhat 9 installation process. Since then we have moved to PostgreSQL version 8.
I uninstalled version 7.3, renamed the libpq libraries in /usr/lib and created a soft link in /usr/lib to the libpq libs in /usr/local/pgsql/lib to try and ensure that they are from the version 8 build. But this still did not resolve the problem.
Even if this is not the problem - How do I ensure that I build our app against the correct libraries from the version 8 build? (Our app is statically linked to libpq and libpqxx). I’m concerned that I have 2 possible versions of the libraries on the machine. I saw several mails in lists that complained about SSL problems in version 7.
I also suspected that my new iptables configuration on the database server might be causing the SSL connection problem.
I flushed all the rules and changed the default policy to ACCEPT for all chains. But I still got the same result.
Is it something I have to do during the libpqxx build? (Checked the documentation – could not find anything related)
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?
Hope I did not bore you guys too much with all the questions that are not even libpqxx related.
Thanks in advance.
This e-mail, its attachments and any rights attaching hereto are, unless the context clearly indicates otherwise, the property of Standard Bank Group Limited and/or its subsidiaries ("the Group"). It is confidential, private and intended for the addressee only.
Should you not be the addressee and receive this e-mail by mistake, kindly notify the sender, and delete this e-mail, immediately and do not disclose or use same in any manner whatsoever. Views and opinions expressed in this e-mail are those of the sender unless clearly stated as those of the Group. The Group accepts no liability whatsoever for any loss or damages whatsoever and howsoever incurred, or suffered, resulting, or arising, from the use of this email or its attachments.
The
Group does not warrant the integrity of this e-mail
nor that it is free of errors, viruses, interception or interference.
Licensed
divisions of the Standard Bank Group are authorised financial services providers
in terms of the Financial Advisory and Intermediary Services Act, No 37 of 2002
(FAIS).
For information about the Standard Bank Group Limited visit our website http://www.standardbank.co.za
_________________________________________________________________________________________________________________________________________________________________________________ Libpqxx-general mailing list Libpqxx-general@gborg.postgresql.org http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
