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