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.

Installing PostgreSQL

It turned out that PostgreSQL 7.5.22 (not 8.2) with database server 7.4 is the most recent stable version for a Debian 4.0 system, such as my new one.

# apt-get install postgresql postgresql-client

The following NEW packages will be installed:
  openssl postgresql postgresql-7.4 postgresql-client postgresql-client-7.4
  postgresql-client-common postgresql-common ssl-cert

This automatically created a new Unix user (and group) postgres, a new PostgreSQL domain main; it placed the data directory in /var/lib/postgresql/7.4/main and instructions specific to Debian in /usr/share/doc/postgresql-7.4/README.Debian.gz. I then manually added a database and PostgreSQL user guava (notice that this name would not have to match a Unix user name; PASSWORD is only a placeholder, of course :)

# su postgres
# createuser -DA guava
# createdb -O guava guava
# psql template1
Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
template1=# alter user guava with password 'PASSWORD';
template1=# \q

I have also restricted client authentication from its defaults: only the PostgreSQL admin user keeps local access to all databases. PostgreSQL user guava can access only one database; her PostgreSQL password won’t be inquired for local connections, but TCP/IP connections need to convey digested passwords and must either originate on localhost or use SSL tunneling from my machine at home. Overall, I had to modify three lines in pg_hba.conf and add a new one (CLIENT is a placeholder for the actual IPv4 NAT address of my machine at home; actual SSL connectivity will require some further steps, which I’ll document later).

local   all   postgres                                                   ident sameuser
local   guava guava                                                      ident sameuser
host    guava guava    127.0.0.1 255.255.255.255                         md5
host    guava guava    ::1       ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff md5
hostssl guava guava    CLIENT/32                                         md5

My ISP assigns a NAT address to my ADSL router with DHCP, so the NAT address will change (it was already different yesterday). So far I don’t know the range of addresses in use, and will have to trace them by updating the configuration file. The PostgreSQL database server must be informed of changes to pg_hba.conf e.g. by restart.

# /etc/init.d/postgresql-7.4 restart

$ psql
guava=> select current_database();
guava=> \q

Adding a user account

I’ve added a first user account (named after a project I’m currently working on), and gave it permission to execute su and thus become root.

# adduser guava
# addgroup wheel
# adduser guava wheel

For protecting su I also had to uncomment a line in /etc/pam.d/su.

auth        requisite   pam_wheel.so group=wheel debug

Logging in via the Ajax-based virtual console and ssh leads to the two following logged in user entries respectively (CLIENT is placeholder name for my machine at home).

guava    tty1                Sat Jul 21 15:19 - 15:19  (00:00)
guava    ttyp0        CLIENT Sat Jul 21 15:08   still logged in

Moving to a Xen-based provider

Well, from the inside it looks as if developments at my first VPS provider have mostly come to a standstill around 2005. I’m therefore looking for alternatives that should match my list of requirements in a better way. The offer from BudgetDedicated looks most promising so far (details in brackets below), and I’ll go with their free 7 day trial next.

• hosting in Europe (yes: based in the Netherlands)
• Xen virtualization (yes: Xen hypervisor)
• ready support for Debian and Gentoo distributions (yes: e.g. Debian 4.0/etch)
• flexibility to boot other kernels (yes: allow upload of users’ own kernels and distributions)
• competent documentation on website (very good: round trip time from my request to fully activated test server was only 10 minutes)

Custom CSS with WordPress

I’ve added a custom stylesheet for drawing borders around those sections of code (see e.g. previous post). With a free hosted blog account from WordPress this is one of the few features that cost extra. I’m very impressed with their service overall and think that income is well deserved, but I guess with my shiny new virtual server I could also install their software locally, host the blog there, and save even those bucks :) Yes, and then add AdSense support too, of course …