Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page


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 

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


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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire