PostgreSQL allows you to connect to remote databases via direct connection, or using SSH tunneling. When you connect via direct connection, you need to open port 5432 of remote PostgreSQL server as well as its firewall. This is risky because it exposes your remote system to brute force attacks. Alternatively, you can connect to remote PostgreSQL serve via SSH port 22, which is already open for remote connections and command executions. Also, all data and connections performed over SSH are encrypted and secure. In this article, we will learn how to connect to PostgreSQL server via SSH tunnel.
How to Connect to PostgreSQL Server via SSH Tunnel
Here are the steps to connect to PostgreSQL server via SSH tunnel. For this, you need to have a SSH connection from client to server. There is an already SSH client installed on most Linux systems, by default. We will use this for SSH tunneling. Here is the command to create SSH connection from local machine to remote PostgreSQL server.
$ ssh -N -L 1111:127.0.0.1:5432 [USER]@[SERVER_IP]
In the above command,
- -N – don’t execute remote command
- -L 1111:127.0.0.1:5432 – Local port forwarding. So all connections sent to local port 1111 are forwarded to remote port 5432 via SSH connection. 5432 is the default PostgreSQL port on remote server. Please note, you need to use a different local port number (e.g. 1111) in your port forwarding string, since your local PostgreSQL server may already be running on port 5432 and it will not be available.
- [USER]@[SERVER_IP] – Remote user and server IP address
By default, the above command will run the SSH tunnel in foreground. If you want to run the tunnel in background, use -f option,
By default, SSH runs on port 22. If your SSH server runs on a different port use -p [PORT NUMBER] to specify the actual SSH port number.
Similarly, in your remote PostgreSQL server runs on a port other than the default port 5432, you need to specify it in the local port forwarding string. For example, if your remote PostgreSQL server runs on port 4000, then you need to update the local port forwarding as shown below.
-L 1111:127.0.0.1:4000
Once the SSH tunnel is established, you can send PostgreSQL commands through it, as if you are issuing them to your local PostgreSQL database. Here is an example.
$ psql -h localhost -p 1111 -U your-db-username database-name
Please note, you need to specify the local port number (e.g. 1111) that has been forwarded to remote PostgreSQL server, using -p option. Otherwise, PostgreSQL will try to connect to local PostgreSQL database running on port 1111.
You can terminate SSH tunnel by entering Ctrl+C keys.
Alternatively, you can add the following line in a file .pgpass in your home directory on client where you’re running psql. This way PostgreSQL client will automatically forward your local port to remote PostgreSQL server when it starts.
localhost:1111:database-name:your-db-user:your-db-password
In this article, we have learnt how to connect to remote PostgreSQL Server. You can run them as standalone commands in terminal, or from within shell scripts. It provides a very secure way to connect to remote database servers, run queries and transfer data, since everything is encrypted by the SSH connection and not exposed to any network.
Also read:
How to Connect to MySQL Server via SSH Tunnel in Windows
How to Connect to MySQL Server via SSH Tunnel in Linux
Git Diff Between Local & Remote Branch
Bash Loop Through Content of File
How to Update Row Based on Previous Row in MySQL