Batten down the Hatches!!! Encrypting your PostgreSQL Traffic with OpenSSL.

 

pclark's picture

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:
www.xtuple.com
www.xtuple.org
www.postgresql.org
OpenSSL For Windows: www.slproweb.com/products/Win32OpenSSL.html
PostgreSQL SSL Doc: www.postgresql.org/docs/8.4/static/ssl-tcp.html
Installing PostBooks the Long Way: http://www.xtuple.org/node/3465
Installing PostgreSQL from Source: http://www.xtuple.org/InstallingPostgresFromSource

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

 
xikar's picture
Offline
Joined: 12/06/2007
Been there, done that...

Although SSL may be "practically built into free OS's" that doesn't mean it always works.

If your using Debian or a variant like Ubuntu, make sure and check out these bugs before turning on SSL. Your server may just start dropping connections and large/long queries will be slow as molasses.

http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=560205
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=555829
http://www.postgresql.org/docs/8.4/static/release-8-4-3.html

I spent days trying to figure out why my new server was slower than the old one when running pgbench and why I could never get a full restore of my xTuple database.

The work around solution for now is to add this to postgresql.conf.
ssl_renegotiation_limit = 0

You should definitely be running SSL, but make sure and do some testing and benchmarking before and after you turn it on to make sure it's working as expected.

 
pclark's picture
Offline
Joined: 12/09/2008
Thanks

Thanks for that valuable suggestion!
While we say there are best practices to adhere to, sometimes it is impossible to get the real world experience that is critical to understanding all of the ramifications of these suggestions.

Thanks,

Perry Clark

 
petergutman's picture
Offline
Joined: 04/26/2011
Perry, Can you add some

Perry,

Can you add some insight as to why xTuple may suggest the use of ODBC over SSL instead of the use of a VPN or even a ssh tunnel to support remote users? ODBC has some issues which make it a pretty poor cloud protocol and simply encrypting the data only fixes one.

Also, at what point, in terms of database size, does it make sense to use a remote desktop strategy such as Citrix, VNC or X Window to support remote users over a direct connection to the database?

Peter

 
pclark's picture
Offline
Joined: 12/09/2008
ODBC?

Hi Peter,

Unless I'm mistaken, I don't recall ever publishing anything about ODBC over SSL. Now, there are cases where PostgreSQL has been deployed with a port forwarded to the server without SSL. Hopefully, in most of these cases they've at least taken pains to secure the pg_hba.conf and make it very strict as to who/what and from where has access. The recommendation is whatever meets your security concerns. The best practice is to have the database as isolated from the internet as possible, but for those cases where remote access over the net is necessary, then SSL, VPN's, SSH tunnels all come into play.

Regarding remote desktops - I can't speak to that strategy as practically implemented. If I were to hypothesize, as I am wont to do, I would suggest that if you are in the habit of returning large datasets in the display queries that you will experience some redraw time lag. This is true of being directly on the server, in the LAN, in the cloud, or over any connection. Some rules of thumb should be ascertained here - Don't expect to return large data sets, limit your queries, make searches more specific (in the case of a contact/customer lookup). Connecting directly via the client to the postgresql port - and the data in most cases is plain ascii text, may possibly be the most efficient way to transport the data (over SSL of course).

Now I'm curious if compression over the connection would help a bit.
Question - what are the mechanisms to do this?

- Perry

 
petergutman's picture
Offline
Joined: 04/26/2011
Perry, My mistake re. ODBC.

Perry,

My mistake re. ODBC. My point is that database protocols tend to be connection-ful and chatty while cloud protocols are connection-less and less chatty, the most common of which to so at, perhaps, expense of verbosity. VPNs and ssh tunnels can help out with compression (probably a checkbox in a commercial VPN and a -C command-line modifier with ssh), but the back and forth nature of over-the-wire protocols have, in part, led to the development of cloud protocols which do their best to avoid latency overhead.

My question re. a remote connection vs. a remote desktop had to do with my experience when the return time of a large query is defined, in part, by the return of the complete query to the client in the case of a remote connection vs. the redraw of the portion of the client window which display the first lines of the query response. Users are users and don't like being told to carefully plan their queries in order to avoid excessive response times.

Peter

 
pclark's picture
Offline
Joined: 12/09/2008
Good point.

Well, since the remote desktop is doing the processing and the query results returning are happening (I'm assuming) on the LAN side, then the only consideration for the remote connection is the latency to redraw that remote desktop. That may be a good practice for larger databases that need to churn through a lot of data.

But - the question of acceptable latency comes down to server hardware, postgresql tuning, query performance, network infrastructure and performance, bandwidth, etc etc.

Thanks for the suggestion.

Perry Clark

 
dustin's picture
Offline
Joined: 07/22/2008
My experience

My experience with regard to speed of the client is pretty good. Some basic data points:

-DB server hosted on a virtual linux server, physically in NYC
-Connect over ssl
-As many as 7 users at any one time.
-Users in Puerto Rico (not the best connectivity around) and South Florida
-pgadmin tells me the db is 1356 MB in size
-I personally travel extensively and access the system from a variety of places, usually over a mobile connection (1MB download speeds). Connected from all the following this year: Argentina, Texas, Virginia, North Carolina, Puerto Rico, maybe more?

I rarely see a long lag in the client. More than I'd like in a perfect world, but always very usable. The longest lags are when running a G/L report or one of the financial statements, which is logical. I also have a sql I run to get my information to calculate sales commissions, and that can take a while running it from pgadmin. Most of the lag there isn't in running the sql, but in exporting it to csv, and it can take minutes over the mobile connection. I've never heard complaints about speed from other users (except when their connectivity was out).

From a practical perspective, if I'm working on reports or need to write some sql, I just connect to a copy of the database on my laptop and work out the sql there, then switch and run the query against the live data.

All that said, its always been very usable. Initially we ran it on a VPS that had too little RAM, and that caused some problems, but we dealt with that and nothing in the two years since. So count me as a successful use of xTuple over SSL.

 
thomasjs's picture
Offline
Joined: 06/16/2009
Is OpenSSL needed on client?

Thanks a lot, Perry, for this detailed instruction.

I'm a little confused though.

I use Postgres on Linux (server) and PostBooks on Windows (client). When I installed Postgres, SSL was already turned on in postgresql.conf and the server.crt and server.key were already installed (probably along with OpenSSL, which was also installed on the server). Since it was the snakeoil key and certificate, I created my own and replaced the snakeoil.

What confuses me is that I didn't have to do anything to have PostBooks running with encryption. It was doing this out of the box. I checked it with Wireshark. Just for testing I turned off SSL in postgresql.conf and then could see the traffic in plain text.

The only thing what was visible as plain text in the encrypted TCP stream was some information about the certificate, such as City, e-mail and the words "Common Name".

So encryption works for me without having done anything on the client. I looked into "C:\Program Files\xTuple\Client" on the Windows client and saw there is the OpenSSL shared library "ssleay32.dll". Is that the reason why everything works like this?

I would be happy if someone could confirm that everything is ok and secure like this.

Thanks

 
pclark's picture
Offline
Joined: 12/09/2008
Some clarification

In getting myself more familiar with the workings of SSL and our application, some clarification is needed about the client side requirements to ensure that SSL is being used. Here it is in a nutshell:

If the server is configured and implemented to use SSL, then ALL xTuple client connections will be using SSL. The user doesn't have to do anything. The connection will be transferring the data via SSL.

So, what does the checkbox for Require SSL Connection on the login screen's option button do? That checkbox tells the client 'Don't connect to the server if SSL isn't setup or working or if an SSL connection to the server cannot be established'. In other words, the users/clients don't have to do anything if SSL is working on the server. When the box is checked it is an explicit statement to the server saying 'Give me SSL, or I'm not connecting'.

Standalone OpenSSL is not needed on the client machines, these are functions of the postgresql libs that we've included with the client builds.

sjsthomas - "visible as plain text in the encrypted TCP stream was some information about the certificate, such as City, e-mail and the words "Common Name"." - this is normal. It is working as expected.

I hope that all of this information has cleared up a lot of the mystery and concerns about the SSL implementation, it certainly has for me!

Best regards,

Perry Clark

 
thomasjs's picture
Offline
Joined: 06/16/2009
This clarification...

... was very helpful!

Thanks a lot, Perry.

Also thanks to the developers of xTuple. It's great that we don't have to install extra software on the client. That makes rolling out much easier. Excellent!

Best regards,

Thomas

 
tomdatkins's picture
Offline
Joined: 10/09/2011
SSL

I got an error on creating the file:

openssl req –new –text –out server.req

WARNING: can't open config file: /usr/local/ssl/openssl.cnf

If you get that error then run this command first:

set OPENSSL_CONF=c:\[PATH TO YOUR OPENSSL DIRECTORY]\bin\openssl.cfg

Then everything worked fine.

Tom

 
gerhardpet's picture
Offline
Joined: 08/19/2012
I'm wondering if there will

I'm wondering if there will be an official write up on how to setup OpenSSL now that it is required for Postbooks 4.0. From what I can tell this blog post is a bit dated already....maybe not. Perhaps I'm missing something.

Thanks.

 
lcartee's picture
Offline
Joined: 05/24/2007
Thank You

Hi Perry:

I just set up my first Server following these very accurate specific instructions. Actually, I was doing it on a 64-bit Windows Server and a 64-bit Postgresql 9.1, so I downloaded the appropriate files for 64 bit.

Followed you instructions step by step. Worked perfectly the first time.

 

Thanks again

 

Larry Cartee

 

 
isogeo's picture
Offline
Joined: 02/19/2008
SSL Client Certificates

I've got Linux Server & Windows clients. SSL is working fine. I now need to further secure with SSL Client Certificate authentication. I have generated Client Certifcates, and am ready to distribute to remote users (2) for installation on their PCs.

 

Where are client certificates placed for remote xtuple pc client instllations?

 

Thanks,

George