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 else 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 that 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.
SSH/OpenSSH
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
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.
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 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:
The localport is any arbitrary port that you wish to locally listen on. This port 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
template1=#
Note: 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.
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
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.
Note: 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 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).
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.
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:
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:
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.
Note: If you use Stunnel with inetd, 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 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.
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 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.
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.
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.
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).
Warning
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 stunnel server process.
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 usernamedatabase_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.
Note: 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.
