OpenSSL

The OpenSSL software package is a software project developed by members of the Open Source community. It is a robust set of tools provided to help your system implement the Secure Sockets Layer (SSL), as well as other security-related protocols, 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). Please 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, point your web browser to the OpenSSL web page at https://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 a couple of older listings for bug fixes. Most likely you will be interested in the newest major release, or a subsequent bug-fix.

A major release should be listed in a fashion similar to this:

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 the file for the version you want, 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 in which you just saved the file. The file will be tarred and gzipped, so you will need to extract it with the following command (note that [version] represents the version number of the software, e.g., 0.9.6b ):

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

Then type:

tar xf openssl-
[version]
.tar

These commands extract 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. OpenSSL is a source distribution, so a bit of compiling is in order. Before we begin to delve into compilation, there are a few requirements you need to be aware of:

• The gmake (or make ) program

• Perl 5, or higher

• An ANSI C compiler

• A development environment (development libraries, and C header files)

• A supported UNIX-compatible operating system

If you have all of these things, you are ready to proceed. Otherwise you will need to acquire them (e.g., download and install them) 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).

1. Execute the configuration script:

   $ 
   ./config
   
   

   This step 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.

2. Next, compile the OpenSSL software:

   $ 
   make
   
   

   This is the primary 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.

3. After compiling, run the test:

   $ 
   make test
   
   

   This command tests the validity of the compilation; if there are any errors, refer to the INSTALL file.

4. If the test is successful, you can install the OpenSSL binaries:

   $ 
   make install
   
   

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

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 useful, because you can use it to create a secure connection with a PostgreSQL database, thus encrypting your database connections, thus tightening general system security, and protecting your data.

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

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

$ 
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 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. Use the following process to make and install Stunnel:

1. Run the configuration script:

   $ 
   ./configure
   
   

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

2. Next, compile the Stunnel sources:

   $ 
   make
   
   

   This command compiles the binary files from Stunnel's source code. The program will prompt you with some questions regarding your locality and domain name. It will use your input to help build the PEM file (which will be called stunnel.pem ). This file is the certificate with which your data is encrypted.

3. After successfully compiling Stunnel, go ahead and install it:

   $ 
   make install
   
   

   This step will install the compiled files.

Knowing how to start Stunnel

You have two options available when deciding how to run Stunnel on your system: using inetd , or running the Stunnel binary 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. These limitations include:

• Stunnel must be initialized for every connection with inetd

• No session cache is possible

• inetd requires forking (which causes extra processor overhead)

It is possible to use Stunnel to provide a secure connection for both remote and local databases. If you host a database on a computer other than the one the psql client is located on, it is possible to provide a secure connection from psql to that database. 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 (in case you are concerned about other users on the machine observing local connections over TCP/IP sockets).

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 , or another preferred path. Also, you may wish to put links to this file in your start-up scripts so that it is automatically started (as one process or two, depending on how you wish to run it) when the system boots.

Running Stunnel in daemon mode

Running 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 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 that the stunnel client is running on.

After psql has connected to the client, any data will be encrypted and then sent to the Stunnel server (located on another port, which is given to the client when you start it) where it is decrypted and sent to the actual PostgreSQL server. The client has to be told a specific port to run on, 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).

The most common use of Stunnel is to send data from a local client to a remote server. The way to do this is to start the client Stunnel process locally, either by calling it during a start-up script (such as /etc/rc.d/rc.local ) or by calling it directly from the installed directory. You then must run the Stunnel process remotely on the machine on which PostgreSQL is running. As with the client, you may want to start the server automatically during system startup.

Both the client and server executions of an example Stunnel scenario are demonstrated in Example 8-15. The ellipses separate the remote server from the local client. Remember that if you do not copy the stunnel executable into /usr/sbin , Stunnel will have to be run from the directory where it is located.

[user@remote ~]$ 
# This command starts the server on the remote machine.

[user@remote ~]$ 
stunnel -P/tmp/ -p ~/stunnel.pem -d 9000 -r

localhost:5432

...

[user@local ~]$ 
# This command starts the client on the local machine.

[user@local ~]$ 
stunnel -P/tmp/ -c -d 5432 -r 192.168.1.2:9000

The remote host command (the first command) in Example 8-15 tells the server to use ~/stunnel.pem as the certificate for encryption, and to open a Stunnel process as a daemon. The -d 9000 parameter causes the daemon to listen for encrypted data on port 9000. The -r localhost:5432 parameter tells the daemon process that when it receives encrypted data on its listening port (9000, in this case), it should decrypt it and send it to localhost on port 5432 (which is the PostgreSQL port number, meaning the decrypted data will be sent along to the database server on the local host).

The second command in Example 8-15 opens an instance of Stunnel on a client machine, in client mode (as dictated by the -c flag), listening on port 5432. The -r 192.168.1.2:9000 parameter instructs the process that the server computer is located at 192.168.1.2, and that it is listening on port 9000 for encrypted packets.

Both modes require the -P/tmp/ flag to provide a temporary path for the PID file, which is the file storing the system ID of the Stunnel process. You do not need to specify the PID filename, as a path is sufficient (the filename will default to something akin to stunnel.localhost.9000.pid  ), though you may specify the complete filename if you wish.

Once each of these Stunnel processes are running on their respective machines, the psql client may be pointed to port 5432 on the client machine. Packets sent to this port will be transparently encrypted, forwarded to port 9000 on the server machine, decrypted, and sent to PostgreSQL on port 5432. This is similar to the SSH tunnel discussed in the Section called SSH/OpenSSH ," with one notable distinction: the client Stunnel process may be created without any kind of authentication to the remote server. Thus, any user may create a secure "sender" to the database server, though it still requires that a secure "receiver" be configured to accept that incoming encrypted data.

This encryption occurs completely separately from PostgreSQL's normal authentication procedures; as far as the postmaster backend process is concerned, the data is coming through to it in plain text, because it is decrypted before being forwarded to the postmaster . Using Stunnel in conjunction with password authentication can be ideal, as it uses a password-based restriction policy, and also encrypts those passwords over the network connection.

Additionally, as mentioned, you have the option to run the two Stunnel processes locally to encrypt packets between two local TCP/IP ports. Starting both the client and server processes on the same machine is demonstrated in Example 8-16.

[user@local ~]$ 
stunnel -P/tmp/ -p ~/stunnel-3.15/stunnel.pem -d 9000 -r 5432

[user@local ~]$ 
stunnel -P/tmp/ -c -d 5433 -r localhost:9000

The first use of stunnel in Example 8-16 opens the server process, and tells it to use ~/stunnel-3.15/stunnel.pem as the certificate file. It also instructs the daemon to listen for connections on port 9000, and to send the unencrypted data from that port to port 5432. The example uses 5432 because the PostgreSQL server is running on that port.

The second use of stunnel in Example 8-16 opens the Stunnel client process on port 5433 (chosen arbitrarily to resemble the PostgreSQL port, in this case). That daemon is instructed to encrypt incoming data, and to forward it to the server process listening on the localhost to port 9000.

Running with inetd

If you wish to configure your system to invoke only the server-side Stunnel instance when requested, you may configure it for use with inetd (or xinetd , on newer systems), rather than in daemon mode. As has been stated previously, this can lead to negative performance effects. If you wish to use this feature in spite of this, it is fairly easy to accomplish. First, you must edit the /etc/services file, and add an entry for the server process. Something like the following will suffice:

pgssl		9000/tcp	# PostgreSQL stunnel wrapper

Depending on whether or not your system uses inetd or xinetd , you will either need to add a new service file called pgssl into the /etc/xinetd.d/ path, or add the service into /etc/inetd.conf . Both of these configurations require that you enter the complete command to be executed (including any arguments to the program). The command should follow this format:

stunnel -P/tmp/ -p 
path
/stunnel.pem -r 
port

In this format, path is the location of the certificate file (this is originally placed in the directory you compiled Stunnel in), and port is the port which PostgreSQL is listening on (usually 5432). Note that the primary difference between invoking stunnel through an inetd -style service versus as a daemon is that the -d flag is not passed.

An example inetd.conf entry (which must be placed entirely on a single line) might look as it does in Example 8-17. The location of the PEM file must of course be configured to point to your certificate file, and must be readable by the user specified in the inetd.conf file. Note that /usr/sbin/stunnel is the full path to the Stunnel binary.

pgssl stream tcp nowait root /usr/sbin/stunnel -P/tmp/ -p /root/my.pem -r 5432

The user specified in Example 8-17 is root , but you may wish to specify a more restricted user as a security concern. Any user with read access to the certificate file and execute access to the stunnel binary (e.g., nobody ), may be used for non-reserved ports.

An example xinetd configuration entry is displayed in Example 8-18. On a machine using xinetd , this data would reside in /etc/xinetd.d/pgssl . Again, be sure that the certificate pointed to by the -p parameter is where your certificate file is located. Additionally, as with inetd , you may not want to run stunnel as root .

# xinetd configuration for pgssl.

service pgssl
{
  disable      = no
  socket_type  = stream
  protocol     = tcp
  wait         = no
  user         = root
  server       = /usr/sbin/stunnel
  server_args  = -P/tmp/ -p /root/stunnel.pem -r 5432
}

After adding either an inetd or xinetd entry to your configuration, you must re-start the relevant service (on Red Hat systems, this is usually done with a call to service ):

[root@host ~]# 
service xinetd restart

Stopping xinetd:                                           [  OK  ]
Starting xinetd:                                           [  OK  ]
[root@host ~]#

If the service command is unavailable, you may usually achieve the same net effect by invoking the killall command with the parameters -HUP , and the name of the process (e.g., killall -HUP xinetd ).

Wrapping up

Once these steps are completed, you should be able to make a secure connection to your PostgreSQL database with any valid PostgreSQL client. To test this with psql , you may use the following syntax:

psql -p 
port
 -h 
host
 -U 
username
 
database_name

Enter the port number that the Stunnel client is listening on for port , then the host that the client is listening on for host ( localhost , in this case), followed by your username , and the database_name to connect to. This should connect you to the database just as if you had opened it normally with psql locally.