Skip to main content
This guide explains how to connect to the PostgreSQL database for debugging and maintenance.

​
Overview

The Address API uses RDS PostgreSQL 15 in private subnets. Direct connections are not possible - you must use a bastion host.

​
Connection architecture

Your Computer
     │
     ▼
AWS Session Manager (SSM)
     │
     ▼
Bastion Host (EC2)
     │
     ▼
RDS PostgreSQL (Private Subnet)

​
Prerequisites

  • AWS CLI installed and configured
  • Session Manager plugin installed
  • Appropriate IAM permissions

​
Step 1: Configure security group

Add your IP to the RDS security group to allow connections:
  1. Go to EC2 → Security Groups
  2. Find {env}-address-api-postgres-sg
  3. Click Edit inbound rules
  4. Click Add rule:
    • Type: All traffic
    • Source type: Anywhere-IPv4
    • Description: Temporary access for debugging
  5. Click Save rules
⚠️ Important: Remove this rule after you’re done for security.

​
Step 2: Connect to bastion host

  1. Go to EC2 → Instances
  2. Find {env}-bastion-host
  3. Click Connect
  4. Select Session Manager tab
  5. Click Connect
You’ll be connected to the bastion host terminal.

​
Step 3: Get database credentials

Retrieve credentials from Secrets Manager: 
# In the bastion host terminal
aws secretsmanager get-secret-value \\
  --secret-id {env}-address-api-secrets \\
  --region ap-south-1 \\
  --query SecretString \\
  --output text | jq
This returns: 
{
  "RDS_USERNAME": "app",
  "RDS_PASSWORD": "your-password-here",
  "RDS_HOSTNAME": "staging-address-api-postgres.xxxxx.ap-south-1.rds.amazonaws.com",
  "RDS_PORT": "5432",
  "RDS_DBNAME": "app"
}

​
Step 4: Connect to PostgreSQL

Construct the connection URL: 
postgres://{RDS_USERNAME}:{RDS_PASSWORD}@{RDS_HOSTNAME}:{RDS_PORT}/{RDS_DBNAME}
Connect using psql: 
psql "postgres://app:your-password@staging-address-api-postgres.xxxxx.ap-south-1.rds.amazonaws.com:5432/app"

​
Common database operations

​
List tables

\dt
Expected tables:
  • iso_3166 - ISO 3166 subdivision data
  • address_cache - Geocoding cache
  • api_key - API keys
  • atlas_schema_revisions - Migration history

​
Check table sizes

SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

​
View recent cache entries

SELECT 
  original_address,
  city,
  state_code,
  country_code,
  cached_at
FROM address_cache
ORDER BY cached_at DESC
LIMIT 10;

​
Count API keys by role

SELECT 
  unnest(roles) as role,
  COUNT(*) as count
FROM api_key
GROUP BY role;

​
Check migration status

SELECT * FROM atlas_schema_revisions ORDER BY executed_at DESC;

​
Troubleshooting

​
Error: “could not connect to server”

Cause: Security group not configured or bastion host can’t reach RDS. Fix:
  1. Verify security group rules (Step 1)
  2. Check RDS is running: Go to RDS → Databases
  3. Verify bastion host is in the same VPC

​
Error: “password authentication failed”

Cause: Incorrect credentials. Fix:
  1. Re-fetch credentials from Secrets Manager
  2. Ensure you’re using the correct environment’s secrets
  3. Check for special characters in password (may need URL encoding)

​
Error: “database does not exist”

Cause: Wrong database name. Fix: Use app as the database name (default for Address API).

​
Security best practices

  • Remove security group rules after debugging
  • Use read-only queries when possible
  • Never modify data in production without approval
  • Log all database access for audit purposes
  • Rotate credentials regularly

​
Next steps