3.4. 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 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.
3.4.1. 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 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 (most do), or choose an external method of encrypting sessions between your client or application and the PostgreSQL server (such as with SSH). In this section we cover the basics on creating a self signed certificate for PostgreSQL. If you wish more detailed instrunctions on the use of SSL in general you should visit http://www.openssl.org/ and read the OpenSSL documentation.
In order to use PostgreSQL with the built-in SSL capability PostgreSQL must be compiled using the --with-openssl option. OpenSSL will be required for the compilation to complete correctly. If you are using a preconfigured PostgreSQL package from your Linux distribution or PostgreSQL distribution provider, most likely SSL has already been enabled.
3.4.1.1. Enabling SSL connectivity
Beyond the complilation steps mentioned above you must also modify the SSL in the postgresql.conf and configure and install a SSL certificate..
After turning on SSL within the postgresql.conf you must configure a new SSL certificate for use by PostgreSQL. The following examples will create a self signed certificate which will not require the purchase of a authorized certificate from a certificate authority. The following steps should be taken from within your PostgreSQL data directory. This would be the directory created in .
 | The file names server.key and server.crt used in these examples are required by postgresql. Do not change them. |
Example 3-35. Creating a self signed certificate
jd@localhost:openssl req -new -text -out server.req Generating a 1024 bit RSA private key ...++++++ ................++++++ writing new private key to 'privkey.pem' Enter PEM pass phrase: Verifying - Enter PEM pass phrase: ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]:US State or Province Name (full name) [Some-State]:Oregon Locality Name (eg, city) []:Gresham Organization Name (eg, company) [Internet Widgits Pty Ltd]:Command Prompt, Inc. Organizational Unit Name (eg, section) []:PostgreSQL Division Common Name (eg, YOUR name) []:localhost Email Address []:nospam@commandprompt.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
We used an arbitrary password for the PEM pass phrase and our challenge password was left blank. If you are going to be sending your information to a certifying authority you will not want to do leave these values blank. The next step will be to remove the PEM pass phrase.
Example 3-36. Removing the PEM pass phrase
jd@localhost:openssl rsa -in privkey.pem -out server.key jd@localhost:rm privkey.pem
We remove the PEM pass phrase to allow PostgreSQL to start without asking for a password. The last step to create the self signed certificate is to unlock the key you just creating.
3.4.2. SSH/OpenSSH
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 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 3-38, 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 3-38. 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=# | 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.