An Engineer’s Guide to:

Connect Athena to RDS PostgreSQL.

Step-by-step: connect the data source through the AWS console.

7 min readSep 26

Intro.

This document assumes that you already have a PostgreSQL RDS instance (SQL and other engines are very similar) and have set up all the required networking capabilities, and have gathered the following connection credentials. If not, please follow this guide to set up your RDS using CDK or this or this guide to set up through the console.
Please also make sure there are some tables, schemas, views, and sample data in your database. I’m planning to publish an easy-to-follow guide to do this; please make sure to subscribe to be notified!

Needed information:

Database host
Database Port
DB username and password
RDS security group ( accessible from Lambda )
RDS subnet ( Accessible from Lambda )

Step 1.

Create an S3 bucket for spillage, and take note of the name.

Step 2.*

* You don’t need to do this step if you used the linked CDK to create your DB as it will generate the secrets for you.

Create a record in AWS SecretsManager with the following information ( the key names should be exactly as shown if you elect to create them manually: read the note below ).. Please note the secret name for later ( or its prefix )

password: YourSuperSecretDBPassword#123456789$
username: postgres

⚠️ ️I would recommend using RDS automatic secret rotation mechanism which will create and update SecretsManager automatically, like that you wouldn’t need to worry about keeping SecretsManager and RDS in sync as and when you change the DB credentials.