Skip to main content

Query Aurora postgres data inside VPC

Depot Environments will provision Aurora into the Depot Environment VPC by default.

This means it is more difficult to connect to and query your Aurora cluster as it runs inside of the private network boundary of the Depot VPC. You may want to do this in development environments or scenarios where you wish to directly query Aurora without querying via the Depot API.

To query Aurora directly you will need to create a small EC2 instance and connect it to the same VPC as your Aurora cluster. SSH (or if you prefer a Windows instance, RDP) to obtain a remote connection, then query Postgres direct from inside the VPC network with any Postgres client.

Access Aurora Postgres data locally​

This method is preferred over the older method.

The Depot Aurora location type allows you to turn on a 'bastion host' which is a small instance that runs inside the VPC network where the Aurora cluster lives.

This host only allows connections from AWS Systems Manager (SSM), and is accessible from your local machine using your personal AWS credentials. There is no SSH or public facing setup required, and this solution is much more secure than a traditional style bastion or jump box.

Enable the bastion host by setting the property useBastionHost to true in your Depot environment Aurora Location configuration.

See the Depot CLI docs for the connect-bastion command for further information on connecting.

Old method​

caution

Important: When creating an EC2 instance, be sure to restrict the Security Group you configure on the instance to only allow inbound connections for your chosen protocol (e.g. 22 for SSH or 3389 for RDP) to a designated, static IP address belonging to you. It is always safest to choose My IP in the security group configuration wizard if you are unsure of your own address. Be sure that the resulting IP address ends with a /32 suffix. This means the rule is scoped to that address and that address only.

SSH tunnel to Aurora​

Steps to establish connection to Aurora cluster:

  • Create a small EC2 instance:
    • Choose an Amazon AMI (Linux 2) based image,
    • Choose a size of t2.nano or t3.nano, and leave the default EBS disk size.
    • Remember to keep in mind the warning above when creating a security group (don’t accept the default “launch-wizard” security group). i.e. allow port TCP 22 inbound for your home/office IP address only.
    • For the networking configuration, choose the same VPC as your Depot environment and ensure you choose one of the “Ingress” subnets (facing the internet side). This enabled a public IP address which you’ll connect to via SSH.
    • Take note of the EC2 keypair you choose and be sure you have a local copy somewhere safe.
  • Once the EC2 instance has started, you can connect to it by:
    • Creating an entry in your ~/.ssh/config file as follows
Host aurora-tunnel
HostName {public IP address of your EC2 instance}
User ec2-user
IdentitiesOnly yes
IdentityFile ~/.ssh/Keys/{your-keypair-file}.pem
LocalForward 5432 {reader-instace-endpoint}.eu-west-1.rds.amazonaws.com:5432
  • Start the SSH tunnel: ssh aurora-tunnel -N
  • Or use connection line
ssh -i ~/.ssh/{your-keypair-file}.pem -L 5432:{reader-instace-endpoint}.eu-west-1.rds.amazonaws.com:5432 ec2-user@{public IP address of your EC2 instance}
  • With the tunnel started, you can now use your preferred postgres client with connection details:
    • host: localhost
    • port: 5432 (or a port that ssh tunnel forward aurora connection)
    • username: can be found in Secrets Manager. The secret itself is named: {cluster-name}-connectionString
    • password: can be found in Secrets Manager. The secret itself is named: {cluster-name}-connectionString
info

The default SSL certificate won’t be valid on localhost, so you’ll need to accept the incorrect certificate or use --insecure on cURL when connecting.