pgAdmin is a popular database client to connect to PostgreSQL database server. It allows you to connect to your database in two ways – directly or using SSH tunnel. If you connect to your PostgreSQL database directly, you need to open port 5432 on its firewall. This is very risky since it exposes your system to brute force and other malicious attacks. Alternatively, you can configure pgAdmin to communicate to your PostgreSQL server via SSH tunnel. In this case, all information such as connection parameters, SQL queries and results are sent via SSH in an encrypted, secure manner. In fact, many organizations require you to connect to your PostgreSQL database this way only. In this article, we will learn how to connect to PostgreSQL database via SSH tunnel, in pgAdmin.
pgAdmin Connect Via SSH Tunnel
We have assumed that you have downloaded & installed pgAdmin on your local system, for this purpose.
Once you have installed pgAdmin, open it and create a new server connection. You can do this by clicking Objects tab, then Create and then Server. You will see a popup window. Click General tab in it, and enter server name as shown below.
Next, click Connection tab, and enter database connection details such as hostname or IP, username, password, port. You can check to Save Password, so that all these connection details are saved locally on your system, and does not require you to enter them every time.
Next, click SSH Tunnel tab. Check ‘Use SSH Tunneling’ and enter host ip address or hostname, tunnel port as 22, username for SSH login. Then select Authentication type. There are two types of authentication available for SSH connections – password string and private key file. If you login to SSH system via password, select that option, else select the IDENTITY FILE option. When you select this option, you will need to browse your private key file (.pem/.ppk) from your local system to be uploaded to your client. Click Save to save the connection.
Please note, the hostname, username & password for SSH tunnel tab is meant for connecting to your SSH server, not the database. In most cases, the database server and SSH server are located on the same machine but in some case they may be different. If they are located on different machines, pgAdmin will connect to your SSH remote server via tunneling, then make a direct connection to your remote database to execute queries.
If all details have been entered correctly, pgAdmin will be able to connect to your PostgreSQL database via SSH tunneling.
This tunnel will exist as long as your session is active but will disconnect on session termination.
In this article, we have learnt how to connect to remote PostgreSQL database via SSH tunneling. You can customize it as per your requirement. This is especially useful if you want to connect to a cloud database such as Amazon RDS or Redshift via SSH to an EC2 instance.
Also read:
How to Connect to PostgreSQL Server via SSH Tunnel
How to Connect to MySQL via SSH Tunnel in Windows
How to Connect to MySQL via SSH Tunnel in Linux
Git Diff Between Local & Remote Branch
Bash Loop Through Content of File