How to connect with PgAdminIII to PostgreSQL with SSH tunnel

Published on Sep 28 2012 in Control Panels Databases Non-Java

On a cPanel server remote connections to PostgreSQL server are disabled by default. See how to leverage SSH tunnel and connect with PgAdminIII and Putty.

1. SSH tunnel with Windows and Putty

If your key-based connection works fine (refer 'Steps to connect with Putty using key based authentication' article) lets start Putty again and create SSH tunnel needed to remotely connect to PostgreSQL database.

Load my_conection session but do not open it.
Goto Connection > SSH > Tunnels in left side Putty area.
Find 'Add new forwarded port' secton and:

in 'Source port' enter an arbitrary local port for example 5433 (it can be also 5432 if no PostgreSQL server is using it locally).
in 'Destination' enter destination hostname and port for example: 127.0.0.1:5432
Click Add.

SSH putty tunnel 4. Go to Session and save my_connection
5. Download PgAdminIII (in this tutorial version 1.16.0 was used - you can use any other client the same way). PostgreSQL server will see the connection as coming from 127.0.0.1 and will accept it when correct database credentials are given.

Start pgAdminIII > File > Add Server and fill in connection details

SSH pgadminIII Click Ok and you should be connected. When in Windows you can see your tunnel listening and established connection by listing open ports and connections with 'netstat -an' in command line.

2. SSH tunnel to access PostgreSQL server - Linux and MacOS

2.1. Make sure your key based SSH login works


    ssh -p 1033 -i id_dsa user@server
logout

2.2. Create tunnel that will connect a local port, for example 5433 with port 5432 on the server


    ssh -i id_dsa -p 1033 -f user@server -L 5433:127.0.0.1:5432 -N

This will start SSH process in background. You can verify that the tunnel is running and listening for connections on port 5433


    netstat -4tlnp | grep 5433
tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN 25400/ssh

2.3. Now the usual part - you login to PostgreSQL


    psql -h 127.0.0.1 -p 5433 -U user_dbuser -d user_dbname
Password for user user_dbuser: 
user_dbname=>

Alternatively use PgAdminIII with above address/port.

If you get


    FATAL: no pg_hba.conf entry for host "::1" ...

make sure you are using 127.0.0.1 instead of localhost as localhost can resolve to IPv6 address as first on some hosts and IPv6 related entries may be missing in pg_hba.conf. By specifying IPv4 address 127.0.0.1 you are explicitly requesting IPv4 connection.

Articles