A Guide to Connect Private PostgreSQL Database through GUI over SSH Tunnel

Overview

Accessing a private PostgreSQL database through pgAdmin4 (GUI tool) using SSH provides an extra layer of security for your data.

SSH (Secure Shell) is a cryptographic network protocol that allows secure communication between two networked devices. SSH can establish a secure connection between your local machine and the remote server where the PostgreSQL database is hosted.

When you access the PostgreSQL database using pgAdmin4 through SSH, your connection is encrypted. Any sensitive information you transmit, such as your database login credentials or the data itself, is protected from unauthorized access.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Benefits of Accessing a Private PostgreSQL database with pgAdmin4

User-Friendly Interface: pgAdmin4 is a popular GUI tool that provides an intuitive interface for interacting with PostgreSQL databases. It offers many features, such as viewing database objects, running queries, and managing users and permissions.
Enhanced Security: By using a private PostgreSQL database, you can restrict access to your data and ensure that only authorized users can access it. Additionally, pgAdmin4 supports SSL encryption, which helps secure communication between the client and server.
Improved Database Management: With pgAdmin4, you can easily manage your PostgreSQL database, including creating new databases, modifying existing ones, and backing up your data. This can help streamline your workflow and improve your overall database management experience.
Multi-Platform Support: pgAdmin4 is a cross-platform tool that can be used on Windows, Mac, and Linux operating systems. This makes it a convenient option for developers who work across multiple platforms.

Steps to follow to access a private PostgreSQL database with pgAdmin4 through SSH

Create an Amazon EC2 instance and make sure you have an SSH port open in its security group
Create a private database and connect it with the ec2 server which you created in Step 1
Once you are connected to the remote server, launch pgAdmin4 on your local machine.
In pgAdmin4, click on the “Add New Server” button.
In the “New Server” dialog box, enter the connection details for the PostgreSQL database, including the hostname or IP address, port number, and database username and password.
In the “Connection” tab, select “SSH Tunnel” as the “Tunnel” method.
Enter the SSH connection details, including the SSH hostname, SSH username, and SSH password or SSH key, and click “Save”.
Click “Save” to test the connection and make sure it works.
Once the connection is established, you can access the private PostgreSQL database through pgAdmin4, and your connection will be encrypted and secure.

Link to download pgAdmin4: Download (pgadmin.org)

Steps to connect the Private RDS database using pgAdmin

Step 1: Create a private PostgreSQL database

Step1

Step1b

Step 2: Create an EC2 server with Public IP

Step2

Step 3: Launch pgAdmin and choose to add a new server option

Step3

Step 4: Give a name to the server in a general setting

Step4

Step 5: Give the RDS hostname/endpoint, database name, username, and password of the database to which you want to connect locally in the connection tab.

Step5

Step 6: Provide the EC2 hostname, username, and Pem file in the SSH tunnel tab. Click on save.

Step6

Step 7: Private PostgreSQL database has been connected locally via SSH through pgAdmin

Step7

Conclusion

Accessing a private PostgreSQL database with pgAdmin4 through SSH adds a layer of security to your data and ensures your connection is encrypted and protected from unauthorized access.

Making IT Networks Enterprise-ready – Cloud Management Services

Accelerated cloud migration
End-to-end view of the cloud environment

Get Started

About CloudThat

CloudThat is also the official AWS (Amazon Web Services) Advanced Consulting Partner and Training partner and Microsoft Gold Partner, helping people develop knowledge of the cloud and help their businesses aim for higher goals using best in industry cloud computing practices and expertise. We are on a mission to build a robust cloud computing ecosystem by disseminating knowledge on technological intricacies within the cloud space. Our blogs, webinars, case studies, and white papers enable all the stakeholders in the cloud computing sphere.

Drop a query if you have any questions regarding PostgreSQL Database and I will get back to you quickly.

To get started, go through our Consultancy page and Managed Services Package that is CloudThat’s offerings.

FAQs

1. What is SSH, and why do I need it to connect to my private PostgreSQL database?

ANS: – SSH, or Secure Shell, is a protocol to connect to remote servers or devices over the internet securely. Using SSH, you can create a secure tunnel between your local machine and your private PostgreSQL database server, ensuring that your database credentials and data are transmitted securely.

2. How do I set up an SSH connection to my private PostgreSQL database?

ANS: – To set up an SSH connection, you’ll need to have SSH client software installed on your local machine. You’ll also need login credentials for the remote server hosting your PostgreSQL database. Once you have those, you can use a command-line SSH client to create a secure tunnel between your local machine and the remote server. The exact commands will depend on your operating system and your SSH client.

3. Can I use a graphical user interface (GUI) to connect to my private PostgreSQL database?

ANS: – Yes, many GUI tools support connecting to PostgreSQL databases over an SSH tunnel. Some popular options include DBeaver, Navicat, and PgAdmin. To connect using a GUI tool, you’ll need to configure the SSH tunnel settings in the tool’s connection settings. Again, the exact steps will depend on the tool you’re using.

4. What are some common issues I might encounter when connecting to my private PostgreSQL database via SSH?

ANS: – Some common issues include incorrect SSH credentials, firewall or network issues, and misconfigured SSH tunnel settings. If you’re having trouble connecting, check your SSH credentials and ensure your firewall allows traffic on the correct ports. You may also need to adjust the settings on your database server to allow connections from your local machine.

5. Is connecting to my private PostgreSQL database via SSH from a public network safe?

ANS: – While SSH tunnels provide a secure connection, connecting to sensitive systems from public networks or untrusted devices is generally not recommended. If possible, it’s best to use a trusted network or device when connecting to your private database. If you must connect from a public network, consider using a VPN or other security measures to protect your connection.

WRITTEN BY Ayush Agarwal

Ayush Agarwal works as a Research Associate at CloudThat. He has excellent analytical thinking and carries an optimistic approach toward his life. He is having sound Knowledge of AWS Cloud Services, Infra setup, Security, WAR, and Migration. He is always keen to learn and adopt new technologies.