Batten down the Hatches, Encrypting your PostgreSQL Traffic with OpenSSL

It's 3AM... do you know what your server is doing?

You probably don't know. If you do know, you need to do something other than worry about your server.

In most cases, it's probably not doing anything more than when you left it at the end of the day. But, during the day, you have some external users connecting, querying your PostBooks or xTuple databases — All seemingly innocent... or ARE THEY?! It could be some MITM parsing your datastream.

Did you know that network traffic can be read between the origination and destination points? Unless you've taken steps to ensure that those connecting to your network have secure and encrypted connections to your database, you may as well be streaming all of that plain text SQL queries and results across a billboard alongside the freeway. Now, I have to make this clear. We (xTuple/PostBooks/OpenMFG) are only showing you HOW to set this up. WE DO NOT HAVE ANYTHING TO DO WITH OPENSSL or SSL or SSH, or the technologies that make those things possible. If your server is configured to allow/require SSL connections, the GUI Client will abide by that. This is a function of the PostgreSQL libraries that the GUI Client is built against. For more information, read all about it on the PostgreSQL website.

Here's a sample of unencrypted data that I obtained with Wireshark while sniffing the traffic to my non-SSL enabled server:

Egads man! I thought my data was SAFE! Well, the only secure computer is one that is off. The next secure computer would be one that has no network connection. And the next secure would be one without any services to offer. So, unless you work in a cave without computer and network access your computer is as secure as the traffic you allow it to access and the access methods you allow to it. For all those people that keep credit card numbers in the improper fields — comments, notes, plaintext fields — Imagine if this query was against the notes field where you keep sensitive items. An attacker, already intimate with your server and network has been observing your traffic. Then, in one fell swoop, after they have looked into a couple of minor details, they come up with:

echo "\t \\\\ \o CCScan.txt \\\\ SELECT * FROM comment WHERE comment_text ~'5049' OR comment_text ~'cred' OR comment_text~'card' OR comment_text~'number' LIMIT 10;" | psql -U admin -p 6666 -h insecure.host.com productiondatabase

Then, all the attacker has to do is figure out your admin password — probably still 'admin', run the query, then open the CCScan.txt file that was generated, and there ya go! Might yield quite a payout...

So, what can we do? Well... for starters, lets get OpenSSL running on your PostgreSQL. It is pretty easy to do, and works on Windows, Linux and MacOS. While Linux and Mac make OpenSSL readily available Windows MCSEs will have to do some finagling, but they're used to that... Right?

Since I like Linux, I'm going to pick on the MCSE's and other M$ variants of consultants. No offense guys and gals, but this stuff is practically built into free OS's. < Yes, while my last remark wasn't 'strictly' true, I would like to get my comment count up on this blog. Trolling? :D

***ATTENTION***

Linux and MacOS users, you may leave the class early today if you promise to go read up: http://www.postgresql.org/docs/8.4/static/ssl-tcp.html

***EOT***

Let's start with downloading OpenSSL for Windows. We get this package from:

OpenSSL for Windows: http://www.slproweb.com/products/Win32OpenSSL.html

The file I used for this tutorial is: Win32OpenSSL-1_0_0d.exe

Once you've read through the site and downloaded the installer — Start the installer, click 'Next'.

Agree to the terms and conditions, accept the license agreement. Consider donating to the developer! And, of course, click 'Next'.

Set the location you want to install OpenSSL into. The default usually works. You could shorten the name to just OpenSSL if you wanted to. Make up your mind and click 'Next'.

Set your menu option shortcut. Click 'Next'.

Set additional tasks. Click 'Next'.

Review your selections and if everything looks OK — click 'Install'.

Once the install starts, don't click on cancel, unless you don't want to install OpenSSL. Also, there is a donate screen that pops up afterwards. You should consider sending a donation to support this project.

Now, once everything is installed, start up your command line. Windows users can try Start -> Run, type 'cmd' in the box, do a runas administrator thingy, or whatever / however it is they get to a command prompt on a Windows machine. Once at the dark and mysterious land of the console change directory (cd) to the OpenSSL-Win32 directory on the C: drive. i.e. type `cd C:\OpenSS~1` or `cd C:\OpenSSL-Win32`. Sit, and wait for further instruction.

Once in the C:\OPENSSL-WIN32 or C:\OPENSS~1 directory type `cd bin`

You should now be in C:\OPENSSL-WIN32\bin

To create a self-signed certificate for the server, use the following OpenSSL command from the bin directory. (linux and mac users can wake up now — if you didn't have openSSL installed for some reason, I suggest you yum,apt-get,synaptic,mac-port, whatnot it into your system.)

Type:

openssl req –new –text –out server.req

Fill out the information that openssl asks for. Make sure you enter the local host name as "Common Name"; the challenge password can be left blank. The program will generate a key that is passphrase protected; it will not accept a passphrase that is less than four characters long.

Enter PEM pass phrase: xtuple
Verifying – Enter PEM pass phrase: xtuple
Country Name: US
State or Province: Virginia
Locality Name: Norfolk
Organization Name: xTuple
Organisational Unit Name: blank
Common Name: Common Name <- Must enter “Common Name”
Email Address: blah@blah.com
A challenge password: leave blank
Optional company name: leave blank

Dear Windows users, this is what your cmd window will look like.

Once the key has been created, do the following from the commandline to remove the passphrase (if you want automatic start-up of the server), run the command:

openssl rsa -in privkey.pem -out server.key

Delete privkey.pem

del privkey.pem

Enter the old passphrase to unlock the existing key. To turn the certificate into a self-signed certificate and to copy the key and certificate to where the server will look for them.

openssl req -x509 -in server.req -text -key server.key -out server.crt

Copy your keys from the directory where you created them (C:\OpenSSL-Win32\bin\) to where your PostgreSQL data directory (PGDATA) is — possibly in C:\Program Files\PostgreSQL\8.4\data. You can also look in the postgresql service definition in the server/services management screen in Windows.

Copy (drag-n-drop) server.crt, server.req, server.key from C:\OpenSSL-Win32\bin to the PGDATA directory.

Now, edit the PGDATA\postgresql.conf file, and make the following changes under Security and Authentication. A hash mark (#) on a configuration parameter line means that the value is a default. The one we’re concerned with are setting `ssl = on`, you can also remove the hash mark from the `ssl_ciphers` line if you wish. Make the change and save the file.

# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
ssl = on # (change requires restart) << Change from OFF, remove hash mark (#) at beginning of line
ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#ssl_renegotiation_limit = 512MB # amount of data between renegotiations
#password_encryption = on
#db_user_namespace = off

Now, open the PGDATA\pg_hba.conf file. This file contains the PostgreSQL access rules. Create entries for the SSL users using the `hostssl` directive. Hostssl directives must come before the regular host directives. Make your changes and save the pg_hba.conf file.

# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
hostssl all all 192.168.0.0/24 md5
hostssl all admin,mfgadmin 76.125.236.20/32 md5
hostssl production bob 98.123.47.60/32 md5
host all all 192.168.1.0/24 md5
#host all all ::1/128 md5

Restart your PostgreSQL instance and try to login from another machine. In my example below, the hostssl rule picked up that I was logging in from a machine in the network range that is required to use SSL. — Note the `SSL connection` line in the output below.

[pclark@pclark ~]$ /opt/PostgreSQL/8.4/bin/psql -U postgres -h 192.168.0.112 -p5888
Password for user postgres:
psql (8.4.6)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
postgres=#

Another thing you could do is to monitor the network traffic to the server and see if it is encrypted. Once configured, this is what they'll see coming over the wire. Not very useful to the attacker, but your data is now encrypted between the two points that are communicating.

Resources:
postgresql.org
OpenSSL For Windows
PostgreSQL SSL Doc
Installing PostBooks the Long Way
Installing PostgreSQL from Source

And please, if any of the information presented here is way way way wrong, please leave me a comment below!