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