Many users of PostgreSQL will be interested to know that there is a dependable encryption method available for use with the software. If you wish to encrypt database sessions, this is quite possible using two easily available tools: OpenSSL and Stunnel. If you are a Unix or Linux system administrator you will probably already be familiar with one or both of these, as they are useful beyond the scope of this context in so much as encryption in general is useful beyond the scope of this context, and if you are a system administrator who is not familiar with encryption it is advisable that you become familiar with the subject very soon .

OpenSSL

The OpenSSL software package is a software project co-developed by the open source community. It is a fairly robust set of tools to help your system implement the Secure Sockets Layer (SSL), as well as other security-related software such as Transport Layer Security (TLS). It also includes a cryptography library. This software package is important to anyone who is planning on using a fair amount of security on their Linux machine (not limited to PostgreSQL, though that will be our focus). Also note that because it is open source software, you are able to download it for free, unlike commercial SSL packages that require you to purchase the software and/or licensing.

To download the newest version of OpenSSL, open your web browser and load the OpenSSL web page at: http://www.openssl.org (http://www.openssl.org/) . There should be a list of available versions along with links to download them. There are two types of versions available: major releases and beta copies. There are also a couple of older listings that are bug fixes. You're interested in the newest major release.

A major release should be listed like this (though at the time of your reading the site may have been changed, in which case just click on the "Source" button anywhere on the screen):

09-Jul-2001: OpenSSL 0.9.6b is now available, a major release

Open the source page through the "available" link. Once there, you can download the most up-to-date version, which will logically be listed with the text "[LATEST]" printed next to it.

Download this file and save it into your home directory, or whichever directory you normally save files to. After it completes downloading, open a console window and cd into the directory you just saved it to. The file will be tarred and zipped, so you will need to extract it with the following command. Note that "[version]" will be the version number of the software.

gzip -d openssl-[version].tar.gz

Then type:

tar xf openssl-[version].tar

This command extracts the OpenSSL files into a directory named openssl-[version] where "[version]" is whatever version number you downloaded.

To complete installation of OpenSSL, cd into the installation directory. Because this is source code, a bit of compiling is in order. Before we begin to delve too deeply into compilation, there are a few requirements you need to be aware of.

  • the make program

  • Perl 5

  • an ANSI C compiler

  • a development environment in form of development libraries and C header files

  • a supported Unix operating system (you should definitely have this)

If you have all of these things, you are ready to proceed. Otherwise you will need to acquire them (also download and install them, if the terms apply) before you will be able to complete the installation of OpenSSL.

To finish installation, complete the following steps. If you have trouble, consult the INSTALL file (from which these steps were taken verbatim).

$ ./config

This will gather information about your system and configure the OpenSSL installation scripts. It shouldn't take incredibly long, though the time will depend on the speed of your system.

$ make

This is the big daddy make command. After configuration, this command begins compilation of the source code. Even on a fairly high-end machine, this process can take a bit of time.

$ make test

Run this command to test the make process; if there are any errors, refer to the INSTALL file.

$ make install

Typing this will install the compiled binaries. After this is finished you should be done with the installation of OpenSSL. If you experience any errors, refer to the documentation (specifically INSTALL and README ).

Note OpenSSL is required
 

You must install OpenSSL before you install Stunnel.

Stunnel

Stunnel is an SSL wrapper, which means it allows you to add SSL functionality to a daemon that is not normally designed to handle a secure layer. This is important because you can use it to create a secure connection with a PostgreSQL database, thus encrypting your database connections. This can be important for many reasons, in that it gives you the ability to restrict access and tighten security when dealing with sensitive data. Also, it tightens general system security.

Stunnel can be found at http://www.stunnel.org/ (http://www.stunnel.org/) . After opening the page in your web browser open the download page, then click "get the source code." From here you are able to download the newest version; save it into your home directory or wherever you wish to place it in your filesystem. Once you have it downloaded, open a console window and cd into the directory you save it at. Then unzip and untar it with the following commands. First, type:

gzip -d stunnel-[version].tar.gz

Then:

tar xf stunnel-[version].tar

You should now have the stunnel files extracted into a directory named stunnel-[version] (where "[version]" is the version number that you downloaded). Fortunately, the installation process of Stunnel is normally a bit quicker than that of OpenSSL. Once everything is extracted, cd into the directory. Remember that you must have already installed OpenSSL before this point or the installation of Stunnel will not work. Enter the following commands to make and install Stunnel:

./configure

This will gather information about your system and configure Stunnel's installation scripts.

make

This command (as you may know) compiles Stunnel's binary files.

make install

And, finally, this command will install the compiled files. After this is complete, Stunnel should be installed on your system. Now for the fun part.

Putting it Together

Once you have OpenSSL (and, subsequently, Stunnel) installed on your system, you are ready to delve into the interesting part of this process. There are few-to-none changes you will have to complete for OpenSSL to work properly after installation, as it is actually only a component that Stunnel needs to function. Stunnel is the heart of how the secure connection is built, hence it is what most post-installation configuration involves.

You have two options available when deciding how to run Stunnel on your system: using inetd or running the program as a daemon. Running it as a daemon is preferred over the former, as using inetd can place limitations on the software due to various issues related to SSL (including the requirement that it be initialized for every connection with inetd , no session cache is possible, and the fact that inetd requires forking). All of these things can drag on the performance of your machine, therefore it is advisable that you use Stunnel in daemon mode.

Along with the choice of how to run Stunnel, you should know that it is possible to use it to provide a secure connection for both local databases and remote databases. This means if you host a database on a different computer than the psql client is located on, it is possible to provide a secure connection from psql to that database. This also means that if your database is hosted on the same computer as the psql client you can provide an equally secure connection between the two local programs.

There should be a file named stunnel in your Stunnel directory; this is the executable for the program. The instructions included assume you are using the executable from this directory, but you may copy it out to /usr/local/sbin if you wish. Also, you may wish to put calls to this file in your startup scripts so that it is automatically started (as one process or two, depending on how you wish to run it) when the system boots. Note: if you use Stunnel with inetd , you will not need to call it from a startup script.

Running in daemon mode

The way to do this while using Stunnel as a daemon is fairly simple, whether you are connecting to a local or remote database. To use Stunnel to connect to a local database you must start it as a client and also as a server (two different processes of the same program, each running on a different port). You then instruct psql to connect to the port number the client is running on. After psql has connected to the client, any data will be decrypted and then sent to the server (located on another port, which is given to the client when you start it) and then decrypted and sent to the actual PostgreSQL database. The client has to be told to run on a specific port, along with the port number that the server process is running on (so it knows where to connect to once it is given something to do). To start the client process, examine the following example. Note that we ran this sample from the directory that stunnel executable was located, and you will have to do the same if you don't copy the executable into /usr/sbin .

stunnel -p /home/abrookins/stunnel-3.15/stunnel.pem -d 9000 -r 5432 stunnel -P/tmp/
      -c -d 5433 -r localhost:9000

The first example opens the server process and tells it to use /home/abrookins/stunnel-3.15/stunnel.pem as the certificate file and listen for connections on port 9000. Then it is told to take data from the client and send it to port 5432 (we chose 5432 because the PostgreSQL server is running on that port). The second example opens the client process on port 5433 (normally we could open this on port 5432, the default PostgreSQL port, but because we're running it locally, that port is taken by a running database process) and tells it that the server process is listening at the host "localhost" on port 9000.

As was mentioned earlier, it is also possible to have a client process running on the client machine and a server process listening to a specific port on the server machine. The way to do this is to start the client, either by calling it during a startup script or by calling it directly from the installed directory, like so:

stunnel -P/tmp/ -c -d 5432 -r 192.168.1.2:9000

This opens an instance of the Stunnel as a client listening on port 5432 and tells the process that the server computer is located at 192.168.1.2 (which is where the PostgreSQL server is) and that it is listening on port 9000. You must also have a process on the server started; do this by using the following command:

stunnel -P/tmp/ -p /home/root/stunnel.pem -d 9000 -r localhost:5432

This tells the server to use /home/root/stunnel.pem as the certificate, and to open an Stunnel process and act as a daemon, listening for data on port 9000. The -r tells it that when it receives data on its main port it should decrypt it and then send it to the 5432 port on localhost (which is the PostgreSQL port number, meaning the data will be sent to our database).

Running with inetd

In some situations it can be prudent to provide a higher level of security for the Stunnel connections; an easy way to do this is to configure the server process with inetd so that it can be started and stopped when need-be. As has been stated previously, this can lead to negative performance effects; however, if you wish to use this feature it is fairly easy to accomplish. Edit /etc/services and complete an entry for the server process. Something like the following:

pgssl 9000/tcp # PostgreSQL stunnel SSL wrapper

Then, depending on whether or not you have a newer system, you may need to start a new service in /etc/xinetd.d/ ; otherwise you would add the service into /etc/inetd.conf . Both of these ways will require you to enter in the program path; remember that you will need to include the arguments to the program. When you enter the path in, be sure to enter it in with the following format:

stunnel -P/tmp/ -p [path]/stunnel.pem -d [port1] -r [host]:[port2]

"Path" is the location of the certificate file (this is originally placed in whatever directory you compiled Stunnel in). "Port1" is port number that you wish the server to listen for connections on. "Host" is the host where PostgreSQL is located, and "port2" is the port on that host which PostgreSQL is listening on.

Wrapping Up

Once these steps are completed, you should be able to make a secure connection to your PostgreSQL database. Do so by typing:

psql -p [port] -h [server host] -U [username] [database name]

Enter the port number that the Stunnel client is listening on in the "port" field, then the host that the client is listening on (this is usually localhost) and then your username and the database name. This should connect you to the database just as if you had opened it normally with psql .

Note Postmaster and the -i flag
 

Remember that you will need to start postmaster with the -i flag to be able to connect to it with Stunnel. The -i flag tells postmaster to enable TCP/IP connections, which are required for Stunnel to work.