Abhishek Singh

PostgreSQL on AWS RDS — Setup and Connectivity Guide

3 min read

Goal

Set up a PostgreSQL database on Amazon RDS, connect to it from a local development machine (via DBeaver and Spring Boot), and handle network-level access configuration securely.

✅ Steps Performed

1. Create a PostgreSQL RDS Instance

We created a new PostgreSQL instance using the AWS CLI:

aws rds create-db-instance \
  --db-instance-identifier my-postgres-db \
  --db-instance-class db.t3.micro \
  --engine postgres \
  --engine-version 15.13 \
  --master-username myuser \
  --master-user-password mypassword123 \
  --allocated-storage 20 \
  --publicly-accessible \
  --no-multi-az \
  --backup-retention-period 0 \
  --storage-type gp2 \
  --region ap-south-1

🔎 Why:

  • t3.micro is free tier eligible
  • publicly-accessible allows local dev tools to connect
  • postgres engine version 15.13 is stable and supported

2. Verified the RDS Endpoint

We retrieved the connection endpoint via:

aws rds describe-db-instances \
  --db-instance-identifier my-postgres-db \
  --region ap-south-1 \
  --query "DBInstances[0].Endpoint.Address" \
  --output text

🧠 This is the host used for DBeaver/Spring Boot JDBC connection

3. Checked RDS Status

aws rds describe-db-instances \
  --db-instance-identifier my-postgres-db \
  --region ap-south-1 \
  --query "DBInstances[0].DBInstanceStatus" \
  --output text

✅ Expected: available

4. Opened Port 5432 via Security Group

We found the security group:

aws rds describe-db-instances \
  --db-instance-identifier my-postgres-db \
  --region ap-south-1 \
  --query "DBInstances[0].VpcSecurityGroups[0].VpcSecurityGroupId" \
  --output text

Then allowed our IP:

aws ec2 authorize-security-group-ingress \
  --group-id sg-xxxxxxxxxxxxxxxxx \
  --protocol tcp \
  --port 5432 \
  --cidr <YOUR_IP>/32 \
  --region ap-south-1

❌ Problem: Connection Timeout

After setup, nc -vz and DBeaver showed timeouts:

Operation timed out

🕵️ Root Cause:

  • Public IP had changed

  • Or IP rule wasn’t matching due to NAT or ISP proxying

✅ Fix: Temporarily Opened Port to All

aws ec2 authorize-security-group-ingress \
  --group-id sg-xxxxxxxxxxxxxxxxx \
  --protocol tcp \
  --port 5432 \
  --cidr 0.0.0.0/0 \
  --region ap-south-1

Confirmed with:

nc -vz <rds-endpoint> 5432
# Output: Connection succeeded!

This verified that the DB is reachable from public internet.

✅ Follow-up: Secured Back Again

We revoked global access:

aws ec2 revoke-security-group-ingress \
  --group-id sg-xxxxxxxxxxxxxxxxx \
  --protocol tcp \
  --port 5432 \
  --cidr 0.0.0.0/0 \
  --region ap-south-1

And then allowed our current IP only:

aws ec2 authorize-security-group-ingress \
  --group-id sg-xxxxxxxxxxxxxxxxx \
  --protocol tcp \
  --port 5432 \
  --cidr $(curl -s https://checkip.amazonaws.com)/32 \
  --region ap-south-1

🛠️ Automation Script

We created a script to auto-add the current IP:

#!/bin/bash
REGION="ap-south-1"
SG_ID="sg-xxxxxxxxxxxxxxxxx"
PORT="5432"
IP=$(curl -s https://checkip.amazonaws.com)
 
aws ec2 authorize-security-group-ingress \
  --group-id "$SG_ID" \
  --protocol tcp \
  --port "$PORT" \
  --cidr "$IP/32" \
  --region "$REGION"

✅ Final Result

  • PostgreSQL is running on AWS RDS

  • DBeaver can connect

  • Spring Boot can connect via JDBC

  • Port 5432 is securely exposed to only our current dev IP

💡 Notes for Production

  • Avoid publicly-accessible = true for prod
  • Use EC2 in same VPC to tunnel to RDS
  • Use IAM authentication or a bastion host for secure access
  • Monitor IP drift if you’re behind NAT

Graph View

Backlinks