PostgreSQL connectivity with JDBC over SSL

First, I configured the PostgreSQL server for SSL by changing two parameters in the file /var/lib/postgresql/7.4/main/postgresql.conf.

tcpip_socket = true
ssl = true

I generated a private key and self-signed certificate for the PostgreSQL server. ANY is a placeholder for a pass phrase (it won’t be needed any further, because automatic database server start-up requires that we store an unencrypted private key); HOST is a placeholder for my VPS host name.

# openssl req -new -text -out pg_server.req
Enter PEM pass phrase: ANY
Common Name (eg, YOUR name) []: HOST.vps.budgetdedicated.com
# openssl rsa -in privkey.pem -out pg_server.key
# openssl req -x509 -in pg_server.req -text -key pg_server.key -out pg_server.crt
# chmod og-rwx pg_server.key
# rm privkey.pem pg_server.req

Next, I switched to my client machine (an iMac G5, which runs Mac OS X, OpenSSL 0.9.7l, Java 1.5, and Eclipse 3.3 among other things) and generated a private key and self-signed certificate for its use (and for client certificate verification on the server).

$ openssl req -new -text -out pg_client.req
Enter PEM pass phrase: ANY
Common Name (eg, YOUR name) []: mymac
$ openssl rsa -in privkey.pem -out pg_client.key
$ openssl req -x509 -in pg_client.req -text -key pg_client.key -out pg_client.crt
$ chmod og-rwx pg_client.key
$ rm privkey.pem pg_client.req

I used scp for copying the certificate files pg_server.crt and pg_client.crt to both machines. I then installed the various cryptographic instruments in the PostgreSQL data directory and restarted the server.

# su postgres
# mv /var/lib/postgresql/7.4/main/root.crt root.crt.save
# cp pg_server.key /var/lib/postgresql/7.4/main/server.key
# cp pg_server.crt /var/lib/postgresql/7.4/main/server.crt
# cp pg_client.crt /var/lib/postgresql/7.4/main/root.crt
# /etc/init.d/postgresql-7.4 restart

On the client, the equivalent step consists of adding the server certificate to the Java key store (default password applies).

$ sudo su -
# export JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home
# openssl x509 -in pg_server.crt -out pg_server.crt.der -outform der
# keytool -keystore $JAVA_HOME/lib/security/cacerts -alias pg_server \\
          -import -file pg_server.crt.der

I wrote a Java test case in Eclipse, and then installed and added the PostgreSQL JDBC 3 driver to the project build path. As before, HOST and PASSWORD are placeholders for my VPS host name and guava’s PostgreSQL password respectively.

package guava.junit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import org.junit.Test;
import static org.junit.Assert.*;

public class JdbcRemotePostgreSqlTest {

  @Test public void connection() throws Exception {
    Class.forName("org.postgresql.Driver").newInstance();

    final String     url        = "jdbc:postgresql://HOST.vps.budgetdedicated.com/guava";
    final Properties properties = new Properties();
    properties.put("user",     "guava");
    properties.put("password", "PASSWORD");
    properties.put("ssl",      "true");

    Connection connection = DriverManager.getConnection(url, properties);
    assertFalse(connection.isClosed());

    connection.close();
  }
}

The test case succeeded, and the following remote authentication steps are in effect:

• JDBC client validates PostgreSQL server certificate
• PostgreSQL server checks IP address of (JDBC) client
• PostgreSQL server requires password from (JDBC) client (digest over SSL)

As for client certificate validation by the PostgeSQL server, I’m still a bit puzzled. Since there is a root.crt (with a copy of pg_client.crt) on the server, I would expect that the server enforces this check, but so far the Java program does not yet know nor convey the right client credential. Further inspection of debugging information suggests that the server sends a Certificate request (as it should), and the client replies with zero Client certificates (as it also should, but why does the server let this pass?). Anyway, I’ll call it a day for now, and the setup is probably already quite secure as is :)

I’ve also deleted all of the aforementioned files pg_* on both machines (those copies are no longer needed).