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
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
. 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
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
represents the version number of the software, e.g.,
gzip -d openssl-
tar xf openssl-
These commands extract the OpenSSL files into a directory named
is whatever version number you downloaded.
Note: If you are running the GNU version of
, you can simply type
tar -xzf openssl-
instead of issuing separate
To complete installation of OpenSSL,
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:
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
file (from which these steps were taken).
Execute the configuration script:
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:
This is the primary
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:
This command tests the validity of the compilation; if there are any errors, refer to the
If the test is successful, you can install the OpenSSL binaries:
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
Stunnel is an SSL
, 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
. 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
into the directory where you saved it. Then unzip and untar the file with the
gzip -d stunnel-
tar xf stunnel-
You should now have the stunnel files extracted into a directory named
is the version number that you downloaded).
Fortunately, the installation process of Stunnel is normally quicker than that of OpenSSL. Once everything is
into the directory. Remember that you must have already installed OpenSSL
before this point or the installation of Stunnel will
work. Use the following process to make
and install Stunnel:
Run the configuration script:
This command will gather information about your system and configure Stunnel's installation scripts.
Next, compile the Stunnel sources:
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
). This file is the certificate with which your data is encrypted.
After successfully compiling Stunnel, go ahead and install it:
This step will install the compiled files.
You have two options available when deciding how to run Stunnel on your system: using
running the Stunnel binary as a daemon. Running it as a daemon is preferred over the former, as using
can place limitations on the software due to various issues related to SSL. These limitations include:
Stunnel must be initialized for every connection with
No session cache is possible
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
client is located on, it is possible to provide a secure connection from
to that database. If your database is hosted
on the same computer as the
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
There should be a file named
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
, 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.
Note: If you use Stunnel with
, you will not need to call it from a startup script.
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
to connect to the port
number that the stunnel client is running on.
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
) 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
will have to be run from the directory where it is located.
Example 8-15. Using Stunnel remotely
[[email protected] ~]$
# This command starts the server on the remote machine.
[[email protected] ~]$
stunnel -P/tmp/ -p ~/stunnel.pem -d 9000 -r
[[email protected] ~]$
# This command starts the client on the local machine.
[[email protected] ~]$
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
as the certificate for encryption, and to open a Stunnel process as a daemon. The
parameter causes the daemon to listen for encrypted data on port 9000. The
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
flag), listening on port 5432. The
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
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
), though you
may specify the complete filename if you wish.
Once each of these Stunnel processes are running on their respective machines, the
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
backend process is concerned, the data is coming through to it in plain text, because it
is decrypted before being forwarded to the
. 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
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
[[email protected] ~]$
stunnel -P/tmp/ -p ~/stunnel-3.15/stunnel.pem -d 9000 -r 5432
[[email protected] ~]$
stunnel -P/tmp/ -c -d 5433 -r localhost:9000
The first use of
in Example 8-16 opens the server process,
and tells it to use
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
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.
If you wish to configure your system to invoke only the server-side Stunnel instance when requested, you may
configure it for use with
, 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
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
will either need to add a new service file called
path, or add the service into
. 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
In this format,
is the location of the certificate file (this is originally placed in
the directory you compiled Stunnel in), and
is the port which PostgreSQL is
listening on (usually 5432). Note that the primary difference between invoking
-style service versus as a daemon is that the
flag is not passed.
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
file. Note that
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
, 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
), may be used for non-reserved
configuration entry is displayed in Example 8-18.
On a machine using
, this data would reside in
Again, be sure that the certificate pointed to by the
parameter is where your certificate file is
located. Additionally, as with
, you may not want to run
Example 8-18. An example xinetd entry
# xinetd configuration for 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
entry to your configuration, you
must re-start the relevant service (on Red Hat systems, this is usually done with a call to
[[email protected] ~]#
service xinetd restart
Stopping xinetd: [ OK ]
Starting xinetd: [ OK ]
[[email protected] ~]#
command is unavailable, you may usually achieve the same net effect by
command with the parameters
, 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 by the user which initiates the
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
, you may use the following syntax:
Enter the port number that the Stunnel client is listening on for
, then the host that
the client is listening on for
, in this case), followed
, and the
to connect to. This should connect
you to the database just as if you had opened it normally with
Note: Notice that you will need to start
flag to be
able to connect to it with Stunnel. The
TCP/IP connections, which are required for Stunnel to work.