Encrypting sessions

In the digital age, privacy and data integrity have become two of the most talked about areas of computing. It seems that almost every day someone new has been cracked, or a new security hole has been found in an application you once trusted.

At the same time, the encrypting of data sessions has become veritably common place amongst computer users. Every reputable e-commerce site uses SSL (the Secure Sockets Layer) to protect user data while transmitting personal information such as credit cards and home addresses across the Internet.

The most common type of crack executed on a machine is not really a "crack" at all. It is usually an unsuspecting user trusting a protocol such as POP or FTP to transfer information over the Internet. By using these protocols, the user can unknowingly transmit their login and password in clear text (in an unencrypted form) over the Internet.

The transmission of data such as login names and passwords in clear text over the Internet means that anybody using a sniffer program (an application which listens to network traffic between two parties) could potentially gain access to your most personal information. In the world of databases, this scenario is no different.

If you connect remotely to PostgreSQL without the use of an encryption technology, there is a potential for misuse by crackers on the Internet. If a cracker uses a sniffer on your network, or on a network between your client and the database server that you are connecting to, they can gain complete access to the information that is stored within PostgreSQL.

We will cover three general methods of encrypting your data between PostgreSQL and client connections:

Built-in SSL

The built-in PostgreSQL SSL support, enabled with the --with-ssl flag at compilation, allows psql (or any client written specifically to connect to PostgreSQL through SSL) to connect securely to PostgreSQL.


An SSH (Secure SHell) session may be used to create a tunnel to a remote server, provided that an SSH daemon (e.g., sshd) is installed and accessible by the connecting user. This requires shell access to the system running PostgreSQL for each user who wishes to connect.


Stunnel is an application which creates an encrypted tunnel between a client and the PostgreSQL server. The Stunnel method requires shell access to set up, but may be configured to run on a client system for a user who does not have direct shell access to the remote server.

Built-in SSL

PostgreSQL provides the option to compile with support for SSL with the --with-ssl configuration parameter. This option is a good choice if you are going to be doing the majority of your work with PostgreSQL in psql, as it natively supports this method of connection.

Most people will choose to use PostgreSQL as a backend to a variety of client applications. If this is the case, you will either need to develop your own client to understand SSL connections to PostgreSQL, or choose an external method of encrypting sessions between your client or application and the PostgreSQL server (such as with SSH, or Stunnel).


OpenSSH provides an excellent method for using external encryption between a client and server. OpenSSH is a commonly implemented standard among security professionals and system administrators. It is most commonly used for terminal or file transfer applications. The SSH protocol is a general method of encryption, and it can be applied in a general fashion for just about any application.

Provided that you have access to a system account on the remote server, you may authenticate to that system and open a tunnel between the remote and local hosts with the -L flag. Such a tunnel will listen to a specified port on the local machine, encrypt incoming packet data, and forward it to the remote server in an encrypted form. The data will then be decrypted and forwarded to another specified port on the remote server.

In this fashion, you can easily create a generalized encrypted tunnel of data between the client and server. Further, the entire process is invisible to PostgreSQL, which believes it is accepting packet input from the same machine it is running on, from the user which authenticated the creation of the tunnel. Make careful note of this, as your pg_hba.conf will need to reflect the appropriate host.

The SSH executable is usually called ssh, and can be used to create a tunnel with the following syntax:

  ssh -L localport:remotehost:remoteport username@remotehost

The localport is any arbitrary port that you wish to locally listen on. This port number must be above 1024, unless you are logged in as the root user, which is not advisable. This number will be the local port that your client believes it is connecting to PostgreSQL on. In actuality, the data received on this port will be forwarded to remotehost on its listening SSH port (usually 22), decrypted, and then forwarded again from the remote server to itself, on the specified remoteport number.

The phrase username@remotehost must be provided in order to authenticate a valid system user. Without a valid system account an SSH tunnel cannot be created. This entire process is demonstrated in Example 8-14, in which the ellipses separate a pair of terminal sessions. The first terminal connection creates the SSH tunnel, and must remain active in order for the tunnel to exist. The second terminal connection actually takes advantage of the tunnel to make a connection to the local tunnel port, which is then forwarded to the remote host, decrypted, and passed through to the PostgreSQL server.

Example 8-14. Making an SSH Tunnel to PostgreSQL

[user@local ~]$ ssh -L 4001:remotehost:5432 user@remotehost
user@remotehost's password:
[user@remote ~]$ 


[user@local ~]$ psql -h localhost -p 4001 template1
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit



When issuing the ssh command, you may specify the -T flag if you don't need to be provided with a command line after creating the SSH tunnel, which is the default behavior. This will cause the terminal to appear to hang after authentication. Such a session may be terminated with CTRL+C when finished.

The only drawback to the use of an SSH tunnel is that it requires a system account from the user who is connecting to PostgreSQL. SSH does not provide completely transparent access to encrypted data streams until you initiate a connection and authenticate against the ssh daemon service, which is typically called sshd service. Depending on your needs this could be a positive or negative restriction.

If you wish to set up an even more generalized encryption tunnel, read through the next section for information on Stunnel.

Configuring and Using Stunnel

While both the built-in SSL and OpenSSH encryption methods provide robust, secure connections to PostgreSQL, they each have their own idiosyncrasies and usage restrictions. Many users of PostgreSQL will therefore be interested to know that there is another dependable encryption method available for use with totally transparent remote access to the server. If you wish to encrypt database sessions transparently for any client, without needing to bind to SSH, this is possible using two easily available tools: OpenSSL and Stunnel.

If you are a UNIX or Linux system administrator, you are most likely familiar with one or both of these, as they are quite useful beyond the scope of this context (in so much as encryption in general is useful beyond the scope of this context). If you are a system administrator who is not familiar with encryption, it is advisable that you become familiar with the subject.


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 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 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 you just saved the file to. 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.


If you are running the GNU version of tar, you can simply type tar -xzf openssl-[version].tar.gz instead of issuing separate gzip and tar commands.

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 makeprogram

  • Perl 5, or higher

  • An ANSI C compiler

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

  • A supported UNIX-compatible 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 (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).

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.

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.

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.

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


You must install OpenSSL before you install Stunnel.

Stunnel can be found at http://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:

Run the configuration script:

$ ./configure

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

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.

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 different computer than 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.


If you use Stunnel with inetd, you will not need to call it from a startup script.

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 rc.local) or by calling it directly from the installed directory. You then must run the Stunnel process remotely on the machine which PostgreSQL is running on. 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.

Example 8-15. Using Stunnel Remotely

[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

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 - d9000 parameter causes th4e 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 parameter instructs the process that the server computer is located at, 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.

Example 8-16. Using Stunnel Locally

[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 host localhost on 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.

Example 8-17. An Example inetd Entry

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.

Example 8-18. An Example xinetd Entry

# 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).


To preserve the integrity of your data encryption, be sure that your certificate file is configured to only be readable the user which initiates the stunnel server process.

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] (usually 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.


Notice 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.