I got an AWS question and implemented it to make sure that the option is correct.
A critical financial application runs on RDS for PostgreSQL. The requirements are tight: 1-second RPO, 60-second RTO, and database credentials rotated every 30 days without taking the application offline.
Two independent problems. Two independent solutions.
Prerequisites
Check these before running terraform apply:
RDS Proxy availability
RDS Proxy is not available on all instance types. It requires instances with at least 2 vCPUs. db.t3.micro is not supported. db.t3.medium and above work.
Terraform executor permissions
The IAM principal running Terraform needs, at minimum:
rds:CreateDBInstance
rds:CreateDBProxy
rds:CreateDBProxyTargetGroup
rds:RegisterDBProxyTargets
rds:ModifyDBInstance
iam:CreateRole
iam:AttachRolePolicy
iam:PutRolePolicy
iam:PassRole
secretsmanager:CreateSecret
secretsmanager:PutSecretValue
secretsmanager:RotateSecret
lambda:CreateFunction
lambda:AddPermission
ec2:CreateSecurityGroup
ec2:AuthorizeSecurityGroupIngress
ec2:CreateDBSubnetGroup
AdministratorAccess on the account covers all of these. Lock it down after the initial setup.
VPC requirements
RDS Proxy runs inside your VPC. You need at least two private subnets in different Availability Zones. The rotation Lambda also runs inside the VPC so it can reach the RDS instance directly during the credential update step.
The problem
Database failure recovery
RPO of 1 second means almost no data loss is acceptable. RTO of 60 seconds means the application must resume within a minute of a failure. A standard single-instance RDS setup fails both requirements: there is no automatic failover, and restoring from a backup takes far longer than 60 seconds.
Credential rotation
Rotating credentials on a schedule sounds simple until you factor in application downtime. If you update a password and the application still holds connections authenticated with the old one, those connections fail. The rotation mechanism needs to handle the transition without the application noticing.
The solutions
Multi-AZ + RDS Proxy
RDS Multi-AZ keeps a synchronous standby replica in a second Availability Zone. Every write to the primary is committed to the standby before the transaction is acknowledged. This gives you RPO close to zero, typically under 1 second for PostgreSQL.
When the primary fails, RDS automatically promotes the standby. The DNS record for the cluster endpoint is updated to point to the new primary. Failover typically completes in 60 seconds or less, which meets the RTO requirement.
The problem is what happens to application connections during that DNS update. Existing database connections are dropped. If your application has a connection pool, it will spend time re-establishing connections and waiting for DNS propagation. Under load, this causes errors.
RDS Proxy sits between the application and the database. The application connects to the Proxy endpoint, not the RDS endpoint directly. The Proxy maintains a warm pool of connections to the database. During a Multi-AZ failover, the Proxy detects the new primary and reroutes connections internally. The application connection to the Proxy stays alive. From the application's perspective, the failover is nearly invisible.
Without the Proxy, Multi-AZ alone may technically meet 60-second RTO on paper but cause application-level errors during the DNS transition. With the Proxy, the application keeps working through the failover.
Secrets Manager with automatic rotation
Secrets Manager stores the database credentials and handles rotation through a managed Lambda function. AWS provides a built-in rotation Lambda for RDS PostgreSQL that handles the full rotation cycle: generate a new password, update it in RDS, update the secret, verify the new credentials work.
The rotation Lambda follows a four-step process using staging labels (AWSPENDING, AWSCURRENT, AWSPREVIOUS). The new password is set in the database before it becomes AWSCURRENT. This means both the old and new passwords are valid during the transition window. Applications retrieving credentials from Secrets Manager at runtime always get the current valid credentials without any downtime.
RDS Proxy reads credentials from Secrets Manager directly. The application connects to the Proxy endpoint and never touches credentials directly. When Secrets Manager rotates the password, the Proxy picks up the new credentials on its next refresh cycle. The application connection to the Proxy stays alive throughout.
Terraform
File structure
├── variables.tf
├── networking.tf
├── iam.tf
├── rds.tf
├── secrets.tf
└── outputs.tf
variables.tf
variable "aws_region" {
description = "AWS region to deploy resources"
type = string
default = "us-east-1"
}
variable "vpc_id" {
description = "ID of the VPC where RDS and RDS Proxy will run"
type = string
}
variable "private_subnet_ids" {
description = "List of private subnet IDs in at least two AZs"
type = list(string)
}
variable "app_security_group_id" {
description = "Security group ID of the application servers that connect to RDS Proxy"
type = string
}
variable "db_password" {
description = "Initial database password (Secrets Manager takes over after first rotation)"
type = string
sensitive = true
}
networking.tf: security groups
# Security group for the RDS instance
# Accepts connections from RDS Proxy and the rotation Lambda only
resource "aws_security_group" "rds" {
name = "financial-app-rds"
description = "Allow PostgreSQL access from RDS Proxy and rotation Lambda"
vpc_id = var.vpc_id
ingress {
description = "PostgreSQL from RDS Proxy"
from_port = 5432
to_port = 5432
protocol = "tcp"
security_groups = [aws_security_group.rds_proxy.id, aws_security_group.rotation_lambda.id]
}
egress {
from_port = 0
to_port = 0
protocol = "-1"
cidr_blocks = ["0.0.0.0/0"]
}
}
# Security group for RDS Proxy
# Accepts connections from the application tier
resource "aws_security_group" "rds_proxy" {
name = "financial-app-rds-proxy"
description = "Allow PostgreSQL access from application servers"
vpc_id = var.vpc_id
ingress {
description = "PostgreSQL from application"
from_port = 5432
to_port = 5432
protocol = "tcp"
security_groups = [var.app_security_group_id]
}
egress {
from_port = 0
to_port = 0
protocol = "-1"
cidr_blocks = ["0.0.0.0/0"]
}
}
# Security group for the rotation Lambda
# Needs outbound access to RDS and to the Secrets Manager VPC endpoint (or internet)
resource "aws_security_group" "rotation_lambda" {
name = "financial-app-rotation-lambda"
description = "Allow rotation Lambda to reach RDS and Secrets Manager"
vpc_id = var.vpc_id
egress {
from_port = 0
to_port = 0
protocol = "-1"
cidr_blocks = ["0.0.0.0/0"]
}
}
# DB subnet group: RDS requires subnets in at least two AZs
resource "aws_db_subnet_group" "main" {
name = "financial-app-db-subnet-group"
subnet_ids = var.private_subnet_ids
tags = {
Name = "financial-app-db-subnet-group"
}
}
iam.tf: roles for RDS Proxy and rotation Lambda
# Role for RDS Proxy to read credentials from Secrets Manager
resource "aws_iam_role" "rds_proxy_role" {
name = "RDSProxySecretsManagerRole"
assume_role_policy = jsonencode({
Version = "2012-10-17"
Statement = [{
Effect = "Allow"
Principal = { Service = "rds.amazonaws.com" }
Action = "sts:AssumeRole"
}]
})
}
resource "aws_iam_role_policy" "rds_proxy_secrets" {
name = "RDSProxySecretsManagerPolicy"
role = aws_iam_role.rds_proxy_role.id
policy = jsonencode({
Version = "2012-10-17"
Statement = [{
Effect = "Allow"
Action = [
"secretsmanager:GetSecretValue",
"secretsmanager:DescribeSecret"
]
Resource = aws_secretsmanager_secret.db_credentials.arn
}]
})
}
rds.tf: RDS instance and Proxy
resource "aws_db_instance" "primary" {
identifier = "financial-app-db"
engine = "postgres"
engine_version = "15.4"
instance_class = "db.t3.medium"
allocated_storage = 100
db_name = "financialdb"
username = "dbadmin"
password = var.db_password
multi_az = true
storage_encrypted = true
backup_retention_period = 7
skip_final_snapshot = false
vpc_security_group_ids = [aws_security_group.rds.id]
db_subnet_group_name = aws_db_subnet_group.main.name
tags = {
Name = "financial-app-db"
}
}
resource "aws_db_proxy" "main" {
name = "financial-app-proxy"
debug_logging = false
engine_family = "POSTGRESQL"
idle_client_timeout = 1800
require_tls = true
role_arn = aws_iam_role.rds_proxy_role.arn
vpc_security_group_ids = [aws_security_group.rds_proxy.id]
vpc_subnet_ids = var.private_subnet_ids
auth {
auth_scheme = "SECRETS"
iam_auth = "DISABLED"
secret_arn = aws_secretsmanager_secret.db_credentials.arn
}
tags = {
Name = "financial-app-proxy"
}
}
resource "aws_db_proxy_default_target_group" "main" {
db_proxy_name = aws_db_proxy.main.name
connection_pool_config {
connection_borrow_timeout = 120
max_connections_percent = 100
max_idle_connections_percent = 50
}
}
resource "aws_db_proxy_target" "main" {
db_instance_identifier = aws_db_instance.primary.identifier
db_proxy_name = aws_db_proxy.main.name
target_group_name = aws_db_proxy_default_target_group.main.name
}
The application connects to aws_db_proxy.main.endpoint, not to the RDS instance endpoint directly.
secrets.tf: Secrets Manager and rotation
The rotation Lambda comes from the AWS Serverless Application Repository. AWS maintains it. You do not write or host any rotation code.
resource "aws_secretsmanager_secret" "db_credentials" {
name = "financial-app/db-credentials"
recovery_window_in_days = 7
tags = {
Name = "financial-app-db-credentials"
}
}
resource "aws_secretsmanager_secret_version" "db_credentials" {
secret_id = aws_secretsmanager_secret.db_credentials.id
secret_string = jsonencode({
username = "dbadmin"
password = var.db_password
engine = "postgres"
host = aws_db_instance.primary.address
port = 5432
dbname = "financialdb"
dbInstanceIdentifier = aws_db_instance.primary.identifier
})
}
# Deploy the AWS-managed rotation Lambda from the Serverless Application Repository
resource "aws_serverlessapplicationrepository_cloudformation_stack" "rotation_lambda" {
name = "SecretsManagerRDSPostgreSQLRotationSingleUser"
application_id = "arn:aws:serverlessrepo:us-east-1:297356227824:applications/SecretsManagerRDSPostgreSQLRotationSingleUser"
semantic_version = "1.1.387"
capabilities = [
"CAPABILITY_IAM",
"CAPABILITY_RESOURCE_POLICY"
]
parameters = {
endpoint = "https://secretsmanager.${var.aws_region}.amazonaws.com"
functionName = "SecretsManagerRDSPostgreSQLRotation"
vpcSubnetIds = join(",", var.private_subnet_ids)
vpcSecurityGroupIds = aws_security_group.rotation_lambda.id
}
}
data "aws_lambda_function" "rotation" {
function_name = "SecretsManagerRDSPostgreSQLRotation"
depends_on = [aws_serverlessapplicationrepository_cloudformation_stack.rotation_lambda]
}
resource "aws_lambda_permission" "secrets_manager" {
statement_id = "AllowSecretsManagerInvocation"
action = "lambda:InvokeFunction"
function_name = data.aws_lambda_function.rotation.function_name
principal = "secretsmanager.amazonaws.com"
}
resource "aws_secretsmanager_secret_rotation" "db_credentials" {
secret_id = aws_secretsmanager_secret.db_credentials.id
rotation_lambda_arn = data.aws_lambda_function.rotation.arn
rotation_rules {
automatically_after_days = 30
}
depends_on = [aws_lambda_permission.secrets_manager]
}
outputs.tf
output "proxy_endpoint" {
description = "Connect the application to this endpoint, not to the RDS instance directly"
value = aws_db_proxy.main.endpoint
}
output "secret_arn" {
description = "ARN of the Secrets Manager secret storing database credentials"
value = aws_secretsmanager_secret.db_credentials.arn
}
output "rds_instance_id" {
description = "RDS instance identifier"
value = aws_db_instance.primary.identifier
}
Validating the deployment
Check 1: Multi-AZ is active
aws rds describe-db-instances \
--db-instance-identifier financial-app-db \
--query 'DBInstances[0].MultiAZ'
# Expected: true
Check 2: RDS Proxy is available and connected to the instance
aws rds describe-db-proxies \
--db-proxy-name financial-app-proxy \
--query 'DBProxies[0].Status'
# Expected: "available"
aws rds describe-db-proxy-targets \
--db-proxy-name financial-app-proxy \
--query 'Targets[*].{Endpoint:Endpoint,State:TargetHealth.State}'
# Expected: State "AVAILABLE"
Check 3: rotation is configured and working
aws secretsmanager describe-secret \
--secret-id financial-app/db-credentials \
--query '{RotationEnabled:RotationEnabled,LastRotatedDate:LastRotatedDate,NextRotationDate:NextRotationDate}'
To trigger a manual rotation immediately and confirm the Lambda executes without errors:
aws secretsmanager rotate-secret \
--secret-id financial-app/db-credentials
Then check the rotation status:
aws secretsmanager describe-secret \
--secret-id financial-app/db-credentials \
--query 'RotationRules'
Check 4: simulate a failover
RDS provides a reboot-with-failover option that forces the standby to become primary. This lets you observe the actual failover behavior before a real incident:
aws rds reboot-db-instance \
--db-instance-identifier financial-app-db \
--force-failover
Monitor the event log during the failover:
aws rds describe-events \
--source-identifier financial-app-db \
--source-type db-instance \
--duration 10 \
--query 'Events[*].{Time:Date,Message:Message}'
The application should continue serving requests through the Proxy during this window. Check your application logs for connection errors. If the Proxy is working correctly, you should see none.
Why the other options do not work?
Option: automated backups with PITR
PITR restores to a point in time by replaying transaction logs onto a snapshot. This creates a new RDS instance. Provisioning a new instance, restoring data, and updating connection strings takes 15 to 30 minutes at minimum. The 60-second RTO is not achievable with this approach.
Option: Parameter Store with EventBridge rotation
Parameter Store does not have native RDS credential rotation. You write the rotation logic yourself in Lambda, manage the staging window yourself, and handle the transition between old and new credentials yourself. This is exactly what Secrets Manager already does, with managed Lambda functions and staging label support built in. Option D reimplements a subset of Secrets Manager without the reliability guarantees.
Option: read replica with manual promotion
A read replica provides an asynchronous copy of the data. Promotion requires manual steps: stopping replication, promoting the replica, updating the application connection string. The process takes several minutes and requires human intervention. Neither the RTO nor the automation requirement is met.
What the architecture looks like end to end
Application
|
v
RDS Proxy endpoint
|
|-- Normal operation --> RDS Primary (AZ-a)
| |
| Synchronous replication
| |
| RDS Standby (AZ-b)
|
|-- During failover --> Proxy detects new primary
Proxy reroutes internally
Application connection stays alive
Secrets Manager (30-day rotation schedule)
|
v
Rotation Lambda (inside VPC)
|-- Updates password in RDS
|-- Updates AWSCURRENT in Secrets Manager
|
RDS Proxy reads new credentials on next refresh
Application never sees the credential change
Cost to keep in mind
Multi-AZ doubles the instance cost because the standby runs continuously. RDS Proxy charges per vCPU of the underlying RDS instance per hour. Secrets Manager charges $0.40 per secret per month plus $0.05 per 10,000 API calls.
For a financial application with a 1-second RPO and 60-second RTO requirement, the alternative to this setup is manual failover, potential data loss, and credential management code you have to maintain and test yourself.
