Re: [Fwd: [HACKERS] JDBC with SSL for postgresql]

From: "Joe Shevland" <jshevland(at)j-elite(dot)com>
To: "Rich Elwyn" <relwyn(at)charter(dot)net>, <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: [Fwd: [HACKERS] JDBC with SSL for postgresql]
Date: 2002-05-22 22:29:56
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

To be honest, the change would be a matter of 1/2-1 hour coding... just a matter of pulling in the JSSE binaries, a config option for the public key, and thats pretty much it (imho I think ;). I'm happy to do it, I just wasn't sure the backend handled it.

All of the key stuff is left to the end user to fuddle through.

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Rich Elwyn
Sent: Wednesday, 22 May 2002 5:28
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] [Fwd: [HACKERS] JDBC with SSL for postgresql]

We simply don't have the developer time right now to do the coding and testing. In addition, it seems that postgresql server doesn't yet have SSL support working without bugs. There are a number of postings from Bear Giles in the pgsql-hackers forum regarding patches he submitted to make SSL work properly in postgresql. It's not clear when a version of postgresql will be available with these patches.

If you want to give it a try, I'm including at the end of this email a simplified version of code from Apache SOAP that we modified to add SSL support. We have tested it from a linux client system to linux server with Apache mod_ssl as the server.

You need to create a Java keystore and truststore with X509 certificates and your private key file, either self-signed or signed by a certificate authority (CA). You use Java keytool (see to do this. Also setup Apache with an X509 certificate and private key to support SSL. This is all pretty complex if you haven't done SSL from Java before. Here is a step by step of what to do (more or less), followed by the java source code.

Install the Java Secure Socket Extensions (JSSE) 1.0.2 package (available from Sun) on the client system.

Step one - create a client certificate, private key, and keystore:

This is done on the client. This creates the keystore file .keystore. The “-dname” option is to specify the Distinguished Name of the Appliance.

keytool -genkey -alias mykey -keyalg RSA -validity 365 -storetype JKS -keystore /usr/share/ssl/.keystore -dname " CN=xenon, OU=Client, O=Customer, L=Atlanta, ST=GA, C=US " -keypass mypassword -storepass mypassword

Step two –create a certificate signing request:

This creates the file client.csr.

keytool -keystore .keystore -certreq -file client.csr –keypass mypassword –storepass mypassword

Step three - convert the certificate signing request into signed cert:
1 Copy the certificate request (client.csr) from the Client to the certificate authority (CA) machine, in directory /usr/share/ssl/CA.

2. The CA signs the certificate, as follows:

openssl x509 -in client.csr -out client.cert -req -CA cacert.pem -CAkey private/cakey.pem -CAserial serial -days 365

Note that the CA’s PEM password is required to be entered. There is no command line option for the password.

3. Copy the signed Client certificate (e.g. client.cert) from the CA server to the Client directory /usr/share/ssl.

4 Copy the CA certificate cacert.pem from the CA server to the Client directory /usr/share/ssl. This is necessary to use the signed Client certificate with the Java keystore.

5 Import the CA’s certificate to the Applicance Java keyStore and trustStore. This must be the first import done, since the CA signed the other certificates. The trustStore is a replacement for the standard Java “cacerts” file which contains certificates from trusted certificate authorities. We are creating our own file (.trustStore) of trusted CA certificates, which contains only the CA certificate.

Do this in /usr/share/ssl on the Client.

First, import to the keyStore.
keytool -import -alias CA -keystore /usr/share/ssl/.keystore -file cacert.pem -storepass mypassword

Then import to the trustStore.
keytool -import -alias CA -keystore /usr/share/ssl/.truststore -file cacert.pem -storepass mypassword

6. Import the Client signed certificate to the keystore. This replaces the original self signed certificate with the certificate signed by the CA.

keytool -import -alias mykey -keystore /usr/share/ssl/.keystore -file xenon_ssl.cert
–storepass mypassword –keypass mypassword


import java.util.*;

* A bunch of utility stuff for doing SSL things.
* @author Chris Nelson (cnelson(at)synchrony(dot)net)
* Revision 2001/12/28 relwyn
* Add keystore support for SSL client authentication to SSL server */
* Revision 2002/04/30 relwyn
* Add ability to prune enabled cipher suites for export encryption

public class SSLUtils {

/** This method builds an SSL socket, after auto-starting SSL */
public static Socket buildSSLSocket(String host, int port)
throws IOException, UnknownHostException

System.err.println("--------------> Nu SSLUtils Class");

SSLSocket sslSocket = null;

try {
// specify where to find key material for the default TrustManager (overrides jssecacerts and cacerts)
// set the passwords for your keystore, truststore. Hard coding here not cool. You need a better method.

// use Sun's reference implementation of a URL handler for the "https" URL protocol type

// dynamically register sun's ssl provider
// This adds the JSSE security provider to the JDK security runtime.
// If you don't do this then you'll see a 'No SSL Provider' exception when you run the client.;

* Set up a key manager for client authentication if asked by the server
* Use the implementation's default secureRandom routine.
SSLSocketFactory factory = null;
SSLContext ctx = null;
KeyManagerFactory kmf = null;
KeyStore ks;
TrustManagerFactory tmf = null;
KeyStore ts;

/* get the keystore properties */
char[] keyStorePassword= System.getProperty("").toCharArray();
char[] trustStorePassword= System.getProperty("").toCharArray();

ctx = SSLContext.getInstance("SSLv3","SunJSSE");

kmf = KeyManagerFactory.getInstance("SunX509","SunJSSE");
ks = KeyStore.getInstance("JKS");
ks.load(new FileInputStream(System.getProperty("")), keyStorePassword);
kmf.init(ks, keyStorePassword);

tmf = TrustManagerFactory.getInstance("SunX509","SunJSSE");
ts = KeyStore.getInstance("JKS");
ts.load(new FileInputStream(System.getProperty("")), trustStorePassword);

ctx.init(kmf.getKeyManagers(), tmf.getTrustManagers(), null);

factory = ctx.getSocketFactory();

sslSocket = (SSLSocket)factory.createSocket(host, port);

* Handshaking is started manually in this example because
* PrintWriter catches all IOExceptions (including
* SSLExceptions), sets an internal error flag, and then
* returns without rethrowing the exception.
* Unfortunately, this means any error messages are lost,
* which caused lots of confusion for others using this
* code. The only way to tell there was an error is to call
* PrintWriter.checkError().

// Force only one cipher suite to be enabled
// 64 bit DES encryption is legal for export to most countries
// Every time you create a new socket in the line above, you get
// the full set of ciphers. You can insert here some logic to
// determine if you want to limit the socket to 64 bit DES, in
// which case execute the following three lines of code.
String[] lessEnabled = new String[1];
lessEnabled[0] = "SSL_RSA_WITH_DES_CBC_SHA";


} catch (Exception e) {

return sslSocket;


-----Original Message-----
From: Kovács Péter <peter(dot)kovacs(at)sysdata(dot)siemens(dot)hu>
To: relwyn(at)charter(dot)net
Subject: Re: [Fwd: [HACKERS] JDBC with SSL for postgresql]
Date: Tue, 21 May 2002 13:38:57 +0200

> code. We at
> nuBridges did something similar for
> Apache SOAP> and would rather not do it again for postgresql JDBC.
Why would you rather not do it again for postgresql JDBC? I am asking thisjust out of curiosity.


In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Joe Shevland 2002-05-22 22:39:43 Re: CLDC implementation of the pgsql driver.
Previous Message Joe Shevland 2002-05-22 22:27:54 Re: CLDC implementation of the pgsql driver.