Postgres Pro
Downloads
Home   >   mailing lists

Re: [Fwd: [HACKERS] JDBC with SSL for postgresql] - Mailing list pgsql-jdbc

From Rich Elwyn
Subject Re: [Fwd: [HACKERS] JDBC with SSL for postgresql]
Date
Msg-id NEBBIILJKLNCLIJKABAGCEBPCIAA.relwyn@charter.net
Whole thread Raw
In response to [Fwd: [HACKERS] JDBC with SSL for postgresql]  (Dave Cramer <Dave@micro-automation.net>)
Responses Re: [Fwd: [HACKERS] JDBC with SSL for postgresql]
List pgsql-jdbc
Tree view
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 SSLUtils.java 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 http://java.sun.com/products/jdk/1.2/docs/tooldocs/solaris/keytool.html) 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
   -noprompt
 
Then import to the trustStore.
keytool -import -alias CA -keystore /usr/share/ssl/.truststore -file cacert.pem -storepass mypassword
   -noprompt
 
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
 
 
---------------------------------------------------------------------------------------------------------------------------------------
package org.apache.soap.util.net;
 
import java.net.*;
import java.io.*;
import java.util.*;
import javax.net.ssl.*;
import java.security.*;
import com.sun.net.ssl.*;
 

/**
 * A bunch of utility stuff for doing SSL things.
 *
 * @author Chris Nelson (cnelson@synchrony.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)
      System.setProperty("javax.net.ssl.trustStore","/usr/share/ssl/.truststore");
      System.setProperty("javax.net.ssl.keyStore","/usr/share/ssl/.keystore");
      // set the passwords for your keystore, truststore. Hard coding here not cool. You need a better method.
      System.setProperty("javax.net.ssl.trustStorePassword","password");
      System.setProperty("javax.net.ssl.keyStorePassword","password");
 
      // use Sun's reference implementation of a URL handler for the "https" URL protocol type.
      System.setProperty("java.protocol.handler.pkgs","com.sun.net.ssl.internal.www.protocol");      
 
      // 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.
      java.security.Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider());     
        /*
         * 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("javax.net.ssl.keyStorePassword").toCharArray();
     char[] trustStorePassword= System.getProperty("javax.net.ssl.trustStorePassword").toCharArray();
 
     ctx = SSLContext.getInstance("SSLv3","SunJSSE");
 
     kmf = KeyManagerFactory.getInstance("SunX509","SunJSSE");
     ks = KeyStore.getInstance("JKS");
     ks.load(new FileInputStream(System.getProperty("javax.net.ssl.keyStore")), keyStorePassword);
     kmf.init(ks, keyStorePassword);
 
     tmf = TrustManagerFactory.getInstance("SunX509","SunJSSE");
     ts = KeyStore.getInstance("JKS");
     ts.load(new FileInputStream(System.getProperty("javax.net.ssl.trustStore")), trustStorePassword);
     tmf.init(ts);
 
     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";
        sslSocket.setEnabledCipherSuites(lessEnabled);
 
        sslSocket.startHandshake();
 
    } catch (Exception e) {
      e.printStackTrace();
    }
 
    return  sslSocket;
 
    }
}
-----Original Message-----
From: Kovács Péter <peter.kovacs@sysdata.siemens.hu>
Subject: Re: [Fwd: [HACKERS] JDBC with SSL for postgresql]
Date: Tue, 21 May 2002 13:38:57 +0200
 
Rich,
> code. We at
> nuBridges http://www.nubridges.com/ 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.
 
Peter

pgsql-jdbc by date:

Previous
From: Kovács Péter
Date:
Subject: Re: [Fwd: [HACKERS] JDBC with SSL for postgresql]
Next
From: Richard Meester
Date:
Subject: CLDC implementation of the pgsql driver.