SSL reactivated for PostgreSQL 8.1

I’ve used the following procedure for allowing SSL connections between PostgreSQL interactive terminals and the server. The organization of keys and certificates is roughly as was documented earlier.

1) Created CA root key and self-signed certificate on iMac

<MACUSER>$ mkdir tmp.ca
<MACUSER>$ cd tmp.ca

<MACUSER>$ openssl req -new -x509 -out ca.crt -keyout ca.pem -days 365
Generating a 1024 bit RSA private key
Enter PEM pass phrase: <PASSPHRASE1>
Verifying - Enter PEM pass phrase: <PASSPHRASE1>
Country Name (2 letter code) [AU]: <BLANKS>
State or Province Name (full name) [Some-State]: <BLANK>
Locality Name (eg, city) []: <BLANK>
Organization Name (eg, company) [Internet Widgits Pty Ltd]: <BLANK>
Organizational Unit Name (eg, section) []: <BLANK>
Common Name (eg, YOUR name) []: ca
Email Address []: <BLANK>

<MACUSER>$ chmod go-rwx ca.pem

2) Created database server and client keys and certificates on iMac

<MACUSER>$ openssl req -new -text -out pg_server.req -keyout pg_server.pem -days 365
Enter PEM pass phrase: <PASSPHRASE2>
Verifying - Enter PEM pass phrase: <PASSPHRASE2>
Country Name (2 letter code) [AU]:   <BLANKS>
State or Province Name (full name) [Some-State]:  <BLANK>
Locality Name (eg, city) []:  <BLANK>
Organization Name (eg, company) [Internet Widgits Pty Ltd]:  < BLANK>
Organizational Unit Name (eg, section) []:  <BLANK>
Common Name (eg, YOUR name) []:  pg_server
Email Address []:  <BLANK>
A challenge password []: <EMPTY>
An optional company name []: <EMPTY>

<MACUSER>$ openssl req -new -text -out pg_client.req -keyout pg_client.pem -days 365
Enter PEM pass phrase: <PASSPHRASE3>
Verifying - Enter PEM pass phrase: <PASSPHRASE3>
Country Name (2 letter code) [AU]:  <EMPTY>
State or Province Name (full name) [Some-State]: <EMPTY>
Locality Name (eg, city) []: <EMPTY>
Organization Name (eg, company) [Internet Widgits Pty Ltd]: <EMPTY>
Organizational Unit Name (eg, section) []: <EMPTY>
Common Name (eg, YOUR name) []: pg_client
Email Address []: <EMPTY>
A challenge password []: <EMPTY>
An optional company name []: <EMPTY>

<MACUSER>$ openssl x509 -req -in pg_server.req -out pg_server.crt \\
  -CA ca.crt -CAkey ca.pem -set_serial 0
Enter pass phrase for ca.pem: <PASSPHRASE1>

<MACUSER>$ openssl x509 -req -in pg_client.req -out pg_client.crt \\
  -CA ca.crt -CAkey ca.pem -set_serial 0
Enter pass phrase for ca.pem: <PASSPHRASE1>

<MACUSER>$ rm pg_server.req pg_client.req
<MACUSER>$ chmod go-rwx pg_server.pem pg_client.pem

3) Copied all keys and certificates to permanent location

<MACUSER>$ sudo su -
root# mkdir /etc/ssl
root# mkdir /etc/ssl/certs
root# mkdir /etc/ssl/certs
root# mv ~<MACUSER>/tmp.ca/*.pem /etc/ssl/private/
root# mv ~<MACUSER>/tmp.ca/*.crt /etc/ssl/certs/
root# exit

<MACUSER>$ rm -fr ~/tmp.ca

4) Copied select keys and certificates to Debian VPS

guava$ mkdir tmp.ca

<MACUSER>$ cd  /etc/ssl/private/
<MACUSER>$ scp pg_server.pem pg_client.pem \\
  guava@<VPS>.vps.budgetdedicated.com:tmp.ca
<MACUSER>$ cd  /etc/ssl/certs
<MACUSER>$ scp ca.crt pg_server.crt pg_client.crt \\
  guava@<VPS>.vps.budgetdedicated.com:tmp.ca

5) Added two lines to /etc/postgresql/8.1/main/pg_hba.conf

hostssl guava guava 127.0.0.1/32      md5
hostssl guava guava ::1/128           md5

6) Installed (unencrypted) key and certificate into PostgreSQL 8.1 server

guava$ su
root# cd /var/lib/postgresql/8.1/main/
root# mv root.crt root.crt.save
root# mv server.key server.key.save
root# mv server.crt server.crt.save
root# cp ~guava/tmp.ca/ca.crt root.crt
root# cp ~guava/tmp.ca/pg_server.pem server.pem
root# cp ~guava/tmp.ca/pg_server.crt server.crt

root# openssl rsa -in server.pem -out server.key
Enter pass phrase for server.pem: <PASSPHRASE2>
root# chmod og-rwx server.key
root# rm server.pem
root# chown postgres root.crt server.key server.crt
root# chgrp postgres root.crt server.key server.crt

root# /etc/init.d/postgresql-8.1 restart
root# exit
guava$
postgres# psql guava
postgres=# \\q

7) Installed (unencrypted) key and certificate into PostgreSQL interactive console

guava$ mkdir ~/.postgresql
guava$ cd ~/.postgresql
guava$ cp ~/tmp.ca/ca.crt root.crt
guava$ cp ~/tmp.ca/pg_client.pem postgresql.pem
guava$ cp ~/tmp.ca/pg_client.crt postgresql.crt

guava$ openssl rsa -in postgresql.pem -out postgresql.key
Enter pass phrase for postgresql.pem: <PASSPHRASE3>
guava$ chmod og-rwx postgresql.key
guava$ rm postgresql.pem

8) Performed santity check as ahead simulation of SSL client and server authentication

(sanity check before SSL server authentication)
guava$ su
root# openssl verify -CAfile ~guava/.postgresql/root.crt \\
  /var/lib/postgresql/8.1/main/server.crt
/var/lib/postgresql/8.1/main/server.crt: OK

(sanity check before SSL client authentication)
root# openssl verify -CAfile /var/lib/postgresql/8.1/main/root.crt \\
  ~guava/.postgresql/postgresql.crt
~guava/.postgresql/postgresql.crt: OK

9) And that was it …

root# exit
guava$ psql -h localhost guava
Password: <GUAVA_DATABASE_PASSWORD>
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
guava=> \\q

Informal benchmark

I’ve run an informal benchmark for comparing the relative speeds of the virtual private server and my iMac G5 at home. As can be seen from the plot, the iMac (2 GHz PowerPC) wins throughout and by up to a factor of 2. The horizontal axis corresponds to the range of algorithms participating in openssl speed measurements.

A couple of (security) updates

So far, I have been trusting Debian (security) updates rather blindly, but in the future I’d prefer a simple tool for inspecting changelogs or similar on what’s being specifically addressed …

# aptitude update
# aptitude --show-versions upgrade
The following packages will be upgraded:
  bind9-host [1:9.3.4-2 -> 1:9.3.4-2etch1]
  dnsutils [1:9.3.4-2 -> 1:9.3.4-2etch1] file [4.17-5etch1 -> 4.17-5etch2]
  libbind9-0 [1:9.3.4-2 -> 1:9.3.4-2etch1]
  libdns22 [1:9.3.4-2 -> 1:9.3.4-2etch1]
  libisc11 [1:9.3.4-2 -> 1:9.3.4-2etch1]
  libisccc0 [1:9.3.4-2 -> 1:9.3.4-2etch1]
  libisccfg1 [1:9.3.4-2 -> 1:9.3.4-2etch1]
  liblwres9 [1:9.3.4-2 -> 1:9.3.4-2etch1]
  libmagic1 [4.17-5etch1 -> 4.17-5etch2]

Upgrade from PostgreSQL 7.4 to 8.1

I had mistakenly thought that Debian package postgresql was an “alias” for the most recent version of the database server, but evidently it contains support functions specifically for 7.x versions, and hence my earlier install only led to PostgreSQL 7.4. I should therefore be able to upgrade and use PostgresSQL 8.1 under Debian 4.0 after all.

Before installing the 8.1 packages, I backed up by dumping the databases as well as saving copies of the files server.key, server.crt, and root.crt.

# su postgres
postgres# pg_dumpall > outputfile
postgres# exit

This time I referred to the correct package names for the PostgeSQL 8.1 server and client respectively.

# aptitude --show-versions --prompt install postgresql-8.1 postgresql-client-8.1
The following NEW packages will be installed:
  libpq4 [8.1.9-0etch1] postgresql-8.1 [8.1.9-0etch1]
  postgresql-client-8.1 [8.1.9-0etch1]
# aptitude update
# aptitude upgrade

The configuration in /etc/postgresql/8.1/main/postgresql.conf has SSL enabled by default (ssl = true, ssl_socket = true is now obsolete), and I only had to adapt pg_hba.conf by commenting out three lines (they may actually have been commented already) and adding one with the current IPv4 NAT address of my computer at home (actual address masked by placeholder CLIENT).

local   all   postgres              ident sameuser
# local all   all                   ident sameuser
# host  all   all      127.0.0.1/32 md5
# host  all   all      ::1/128      md5
hostssl guava guava    CLIENT/32    md5

Next, I copied the saved versions of server.key, server.crt, and root.crt into /var/lib/var/lib/postgresql/8.1/main/ (after renaming three existing symbolic links by the same names into *.save), and set their owner and group to postgres. At this point, both versions are installed in parallel, and I can start either server, however the new one listens on port 5433 for the time being.

# /etc/init.d/postgresql-7.4 stop
# /etc/init.d/postgresql-8.1 start
# su postgres
postgres# psql --port 5433
postgres=# \\q

I tried the new client for restoring the data, and encountered a few error messages. From inspection of the SQL file I judged to ignore those.

postgres# /usr/lib/postgresql/8.1/bin/psql -p 5433 -d postgres -f outfile
psql:/home/guava/outfile:11: ERROR:  cannot delete from a view
HINT:  You need an unconditional ON DELETE DO INSTEAD rule.
psql:/home/guava/outfile:13: NOTICE:  SYSID can no longer be specified
psql:/home/guava/outfile:20: ERROR:  cannot delete from a view
HINT:  You need an unconditional ON DELETE DO INSTEAD rule.
psql:/home/guava/outfile:122: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create \\ 
implicit index "blog_pkey" for table "blog"
psql:/home/guava/outfile:131: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create \\
implicit index "tag_pkey" for table "tag"

I stopped the 8.1 server and switched it to the default port by setting port = 5432 in postgresql.conf, and then removed the PostgreSQL 7.4 and its redundant dependent packages.

# aptitude -purge postgresql-7.4 postgresql-client-7.4
The following packages will be REMOVED:
  postgresql-7.4{p} postgresql-client-7.4{p}
The following packages have unmet dependencies:
  postgresql: Depends: postgresql-7.4 but it is not installable
  postgresql-client: Depends: postgresql-client-7.4 but it is not installable
The following actions will resolve these dependencies:
Remove the following packages:
postgresql
postgresql-client

The 7.4 installation and its configuration files are now gone (find / -name 7.4), and I can restart the 8.1 server again. The JDBC client on my computer at home does not yet work, presumably because it does not do SSL client authentication against root.crt (was not supported in PostgreSQL 7.4). Local SSL loopback connectivity with psql -h localhost also does not work yet, presumably for related reasons. I’ll try to finish the SSL-related security update next, but at this time can already use the interactive terminal on a local Unix-domain socket connection.

# /etc/init.d/postgresql-8.1restart
# su postgres
postgres# psql
postgres=# \\q

Signing the new deal

My experience with the new VPS provider during the one-week free trial period was a pleasant one, and I’ve now signed up for their monthly plan (see invoice below). There was only one incident (hardware hickup), which was quickly resolved on a Sunday. I could now get a dedicated domain name separately, but the prevalence of search engine use has made this step somewhat irrelevant and therefore optional.

Certificate zoo

So far I am only maintaining two applications with SSL authentication on the Debian server (ssh and PostgreSQL), but keeping track of all distinct certificates and private keys is already becoming quite difficult. I therefore plan on adopting a common simplifying convention under the following guiding principles.

• store only one local copy of certificates and keys on the host that generated each pair; preferably use directory /etc/ssl/cert for certificates, and /etc/cert/private for keys; create additional references as symbolic links
• use only one self-signed certificate from a local certificate authority; sign all other new certificates with its key; don’t generate the root certificate and key on the server, but on my machine at home (Mac OS X)

The drawing depicts a draft plan with the following notation (assumes PostgreSQL 8.1 with two types of client: JDBC on Mac OS X and psql on Debian).

• new certificates and keys on Debian server inside darker gray boxes
• other unmodified certificate and keys on Debian server inside lighter gray boxes
• new certificates and keys on Mac OS X client inside red boxes
• master copy of certificates and keys (only one pair per host) in bold font
• symbolic links in italic font
• (*) in cases where another certificate and key is replaced (archived as *.save file)
• connecting lines indicate two certificates signed with root key
• application needs dictate certificate and key file names in directories other than /etc/ssl/cert and /etc/cert/private

Update: locally created certificates should go into directory /etc/ssl/certs instead of /etc/ssl/cert (corrected typo).

Debian security updates

The default /etc/apt/sources.list file did not yet account for security updates (RSS feed here). I added a new first line and ran aptitude update; aptitude upgrade. (I noticed that by now aptitude is the preferred program for Debian package management from console.)

deb http://security.debian.org/ etch/updates main contrib non-free
deb http://ftp.nl.debian.org/debian etch main

As it turns out, my PostgreSQL installation was in need of security updates.

The following packages will be upgraded:
  libkrb53 postgresql-7.4 postgresql-client-7.4

One more thing

Probably it is the case that PostgreSQL 7.4 does not support SSL client authentication. In my previous post, I was unintentionally linking to the most recent 8.2 manual, and the relevant section is indeed missing from the 7.4 manual.

The older PostgreSQL version (in conjunction with the JDBC 3 driver) however does support SSL server authentication, as I just double-checked by temporarily removing the server certificate from the client’s Java keystore.

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).

Setting the local timezone

The system was still on UTC, and I used tzconfig to set the timezone to CET.