Connect by using an SSH tunnel in pgAdmin - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resources

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.

pgAdmin with SSH tunnel connects through internet gateway to EC2 instance that connects to database.

Tools

AWS services

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

TaskDescriptionSkills 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:

  • Host name/address

  • Port

  • Maintenance database

  • Username

  • Password

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.

  • Set Use SSH tunneling to Yes to specify that pgAdmin should use an SSH tunnel when connecting to the specified server.

  • In the Tunnel host field, specify the name or IP address of the SSH host (for example, 10.x.x.x).

  • In the Tunnel port field, specify the port of the SSH host (for example, 22).

  • In the Username field, specify the name of a user with login privileges for the SSH host (for example, ec2-user).

  • Specify the type of authentication as Identity file so that pgAdmin will use a private key file when connecting.

    • Include the location of the Privacy Enhanced Mail (PEM) file in the Identity file field. The .pem file is the Amazon EC2 key pair.

DBA

Save and connect.

Choose Save to complete the setup and connect to the Aurora PostgreSQL-Compatible database by using the SSH tunnel.

DBA

Related resources