Troubleshooting a connection to an Amazon RDS Postgres database over the internet
I'm building a data engineering project, consisting of an ETL pipeline that serves data to a visualisation application (written in Python). One of the first steps to set up the project is to create the database that will hold the data returned from the API call. For this project, I decided to use an Amazon RDS database running a Postgres implementation as it's a widely implemented open-source database technology.
BLUF
Understanding security groups: Configure inbound rules to enable public access to an RDS Postgres database
Setting up database connections: Use
psqlCLI for rapid database testing and proof-of-concept
Challenges
During the initial setup and testing, I couldn't access my Amazon RDS instance over the public internet. This had to be fixed as my application was accessing a public API to obtain the current location of the International Space Station (ISS). I followed these steps to logically eliminate the potential cause of the problem.
- Look into the security group attached to my RDS database, and inspect the inbound rules. Since the initial connection to the RDS instance was failing, this was the first step to identify the problem. I ensured the inbound rules on the security group were set so that TCP traffic on port 5432 (default port for Postgres) was allowed.
Ensure the RDS is publicly accessible. This was another setting I had to review, as the point is to access the RDS instance over the public internet. So, the RDS database must be publicly accessible.
Finally, I needed to make sure the VPC had an internet gateway attached.
Success
Once those three troubleshooting steps were done, I was able to establish a connection to my Amazon RDS Postgres database using psql.
