Connect by using an SSH tunnel in pgAdmin
Created by Jeevan Shetty (AWS) and Bhanu Ganesh Gudivada (AWS)
Environment: Production | Technologies: Databases; Security, identity, compliance | Workload: Open-source |
AWS services: Amazon RDS; Amazon Aurora |
Summary
For security reasons, it’s always good to place databases in a private subnet. Queries against the database can be run by connecting through an Amazon Elastic Compute Cloud (Amazon EC2) bastion host in a public subnet on the Amazon Web Services (AWS) Cloud. This requires installing software, such as pgAdmin or DBeaver, which are commonly used by developers or database administrators, on the Amazon EC2 host.
Running pgAdmin on a Linux server and accessing it through a web browser requires the installation of additional dependencies, permissions setup, and configuration.
As an alternate solution, developers or database administrators can connect to a PostgreSQL database by using pgAdmin to enable an SSH tunnel from their local system. In this approach, pgAdmin uses the Amazon EC2 host in the public subnet as an intermediary host before connecting to the database. The diagram in the Architecture section shows the setup.
Note: Ensure that the security group attached to the PostgreSQL database allows connection on port 5432 from the Amazon EC2 host.
Prerequisites and limitations
Prerequisites
An existing AWS account
A virtual private cloud (VPC) with a public subnet and a private subnet
An EC2 instance with a security group attached
An Amazon Aurora PostgreSQL-Compatible Edition database with a security group attached
A Secure Shell (SSH) key pair for setting up the tunnel
Product versions
pgAdmin version 6.2+
Amazon Aurora PostgreSQL-Compatible Edition version 12.7+
Architecture
Target technology stack
Amazon EC2
Amazon Aurora PostgreSQL-Compatible
Target architecture
The following diagram shows using pgAdmin with an SSH tunnel to connect through an internet gateway to the EC2 instance, which connects to the database.
Tools
AWS services
Amazon Aurora PostgreSQL-Compatible Edition is a fully managed, ACID-compliant relational database engine that helps you set up, operate, and scale PostgreSQL deployments.
Amazon Elastic Compute Cloud (Amazon EC2) provides scalable computing capacity in the AWS Cloud. You can launch as many virtual servers as you need and quickly scale them up or down.
Other services
pgAdmin
is an open-source management tool for PostgreSQL. It provides a graphical interface that helps you create, maintain, and use database objects.
Epics
Task | Description | Skills required |
---|---|---|
Create a server. | In pgAdmin, choose Create, and then choose Server. For additional help with setting up pgAdmin to register a server, configure a connection, and connect through SSH tunneling by using the Server Dialog, see the links in the Related resources section. | DBA |
Provide a name for the server. | On the General tab, enter a name. | DBA |
Enter the database details. | On the Connection tab, enter values for the following:
| DBA |
Enter the Amazon EC2 server details. | On the SSH Tunnel tab, provide the details of the Amazon EC2 instance that is in the public subnet.
| DBA |
Save and connect. | Choose Save to complete the setup and connect to the Aurora PostgreSQL-Compatible database by using the SSH tunnel. | DBA |