Establish SSH Tunnel to PostgreSQL Server

Here’s an example how I setup an SSH tunnel with a remote PostgreSQL Server.

The following command will setup the tunnel with the server where the PostgreSQL Server resides. The port number after the localhost is the port on the remote server where you’d like to make a connection. The port before the localhost is a local port that maps to the remote port. The last parameter is the username@servername.

ssh -f -N -L 2000:localhost:5432 remoteusername@remoteserver.name.com

Parameters:
-f Requests ssh to go to background just before command execution.
This is useful if ssh is going to ask for passwords or
passphrases, but the user wants it in the background. This
implies -n. The recommended way to start X11 programs at a
remote site is with something like ssh -f host xterm.

-N Do not execute a remote command. This is useful for just for-
warding ports (protocol version 2 only).

-L [bind_address:]port:host:hostport
Specifies that the given port on the local (client) host is to be
forwarded to the given host and port on the remote side. This
works by allocating a socket to listen to port on the local side,
optionally bound to the specified bind_address. Whenever a con-
nection is made to this port, the connection is forwarded over
the secure channel, and a connection is made to host port
hostport from the remote machine. Port forwardings can also be
specified in the configuration file. IPv6 addresses can be spec-
ified with an alternative syntax:
[bind_address/]port/host/hostport or by enclosing the address in
square brackets. Only the superuser can forward privileged
ports. By default, the local port is bound in accordance with
the GatewayPorts setting. However, an explicit bind_address may
be used to bind the connection to a specific address. The
bind_address of “localhost” indicates that the listening port be
bound for local use only, while an empty address or ‘*’ indicates
that the port should be available from all interfaces.

Now, when connecting to the server, you need to make sure you are not reopening connections. Here’s a test that you can do in a shell script before opening the connection to make sure you’re not reopening connection of facing issues if the connection is already open and the port is in use:

# Run the 'ls' command remotely.  If it returns non-zero, then create a new connection
netstat -lpnt | grep 2000 | grep ssh > /dev/null
if [[ $? -ne 0 ]]; then
    ssh -f -N -L 2000:localhost:5432 remoteusername@remoteserver.name.com
    echo Created new SSH tunnel connection to Remote Server DB
else
    echo SSH Tunnel already exists to Remote Server DB
fi
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s