RDS with Terraform in Terraform, IaC & Cloud Automation

Introduction

By day 31 of your infrastructure-as-code journey, you've likely deployed compute, networking, and storage. Now comes the hardest part: stateful data. RDS with Terraform in Terraform, IaC & Cloud Automation means treating your database like your load balancer or security group—repeatable, versioned, auditable, and safe. Managed relational databases sit at the critical path of almost every production system we've run, from e-commerce checkouts to SaaS billing engines to ride-hailing dispatch platforms.

RDS takes the operational burden off our shoulders—AWS handles patches, backups, and failover. Terraform takes the toil out of provisioning and maintaining that infrastructure. Together, they eliminate manual database clicks and the inevitable configuration drift that follows.

Subnet Group — Private Subnets Only

Before you create an RDS instance, you need a DB subnet group. This is your first line of defense: it restricts your database to private subnets, keeping it off the public internet entirely.

A subnet group is a collection of subnets in different availability zones. RDS uses it to determine where your instances can live. In a multi-AZ setup (which we recommend for anything production-facing), AWS spreads your primary and standby replicas across zones within this group.

resource "aws_db_subnet_group" "main" {
  name            = "orders-db-subnet-group"
  subnet_ids      = [
    aws_subnet.private_a.id,
    aws_subnet.private_b.id,
    aws_subnet.private_c.id,
  ]
  description     = "Subnet group for production orders database"

  tags = {
    Environment = "production"
    Name        = "orders-db-subnet"
  }
}

Always use private subnets. Never, ever put your database on a public subnet with a public IP. We've seen teams "temporarily" do this during development and then forget to change it before production. Eighteen months later, when an attacker finds it through a simple port scan, chaos ensues.

The subnet group itself is cheap and harmless. It's your insurance policy. Make it a habit to define one early in your root module or database module, and reference it consistently.

Instance or Cluster — Postgres Instance or Aurora Cluster

Now you choose: a single RDS instance (like a Postgres database server) or an Aurora cluster (managed, distributed, with automatic failover). This decision shapes everything downstream.

Single RDS Instance: Postgres

For many teams—especially early stage—a single Postgres instance on RDS is the right call. It's straightforward, costs less than Aurora, and handles enormous throughput if sized well. We've run multi-million-transaction systems on a single db.r5.xlarge without breaking a sweat.

resource "aws_db_instance" "postgres_main" {
  identifier              = "orders-db-prod"
  engine                  = "postgres"
  engine_version          = "15.3"
  instance_class          = "db.r5.large"
  allocated_storage       = 100
  storage_type            = "gp3"
  storage_encrypted       = true
  
  db_subnet_group_name    = aws_db_subnet_group.main.name
  
  multi_az                = true
  backup_retention_period = 30
  backup_window           = "02:00-03:00"
  maintenance_window      = "sun:03:00-sun:04:00"
  
  skip_final_snapshot     = false
  final_snapshot_identifier = "orders-db-prod-final-snapshot-${formatdate("YYYY-MM-DD-hhmm", timestamp())}"
  
  vpc_security_group_ids  = [aws_security_group.db.id]
  publicly_accessible     = false
  
  tags = {
    Environment = "production"
    CriticalPath = "true"
  }
}

Key settings here: multi_az = true gives you automatic failover. storage_encrypted = true encrypts data at rest. backup_retention_period = 30 keeps 30 days of automated backups. For production, we never skip the final snapshot.

Aurora Cluster: High Availability and Read Scaling

Aurora is AWS's managed relational engine. It's Postgres-compatible or MySQL-compatible, but the architecture is different—storage is shared across cluster members, failover is near-instant, and read replicas are cheap and fast.

resource "aws_rds_cluster" "aurora_main" {
  cluster_identifier              = "orders-cluster-prod"
  engine                          = "aurora-postgresql"
  engine_version                  = "15.2"
  database_name                   = "ordersdb"
  master_username                 = "postgres"
  manage_master_user_password     = true
  master_password                 = random_password.db_password.result
  
  db_subnet_group_name            = aws_db_subnet_group.main.name
  vpc_security_group_ids          = [aws_security_group.db.id]
  
  backup_retention_period         = 35
  preferred_backup_window         = "02:00-03:00"
  preferred_maintenance_window    = "sun:03:00-sun:04:00"
  
  skip_final_snapshot             = false
  final_snapshot_identifier       = "orders-cluster-prod-final-${formatdate("YYYY-MM-DD-hhmm", timestamp())}"
  
  deletion_protection             = true
  storage_encrypted               = true
  
  tags = {
    Environment = "production"
    CriticalPath = "true"
  }
}

resource "aws_rds_cluster_instance" "aurora_instances" {
  count              = 2
  cluster_identifier = aws_rds_cluster.aurora_main.id
  instance_class     = "db.r5.large"
  engine              = aws_rds_cluster.aurora_main.engine
  engine_version      = aws_rds_cluster.aurora_main.engine_version
  
  publicly_accessible = false
  
  tags = {
    Environment = "production"
    Name        = "aurora-instance-${count.index + 1}"
  }
}

An Aurora cluster includes a primary instance and optionally one or more read replicas. The primary handles writes; replicas handle reads. If the primary fails, Aurora promotes a replica in seconds. This architecture is common in trading systems and real-time SaaS platforms where downtime is measured in real dollars.

The trade-off: Aurora costs more than a comparable single Postgres instance. Use it when you need automatic read scaling, high availability is non-negotiable, or you're running analytics queries that you want isolated from transactional load.

Credentials — manage_master_user_password Stores Them in Secrets Manager

Never hardcode database passwords. Never store them in your Terraform code or state. The industry standard is manage_master_user_password = true, which tells AWS to generate a strong password and store it in AWS Secrets Manager.

resource "aws_db_instance" "postgres_main" {
  # ... other config ...
  
  manage_master_user_password = true
  master_username             = "postgres"
  
  # ... rest of config ...
}

# To retrieve the password from your application:
data "aws_secretsmanager_secret_version" "db_password" {
  secret_id = aws_db_instance.postgres_main.master_user_secret[0].secret_arn
}

AWS Secrets Manager charges a small fee per secret per month, but it's worth every penny. Your password rotates automatically every 30 days if you enable automatic rotation. Your application can retrieve it via the AWS SDK at runtime, and audit logs show who accessed it and when.

For local development and CI/CD pipelines, you might still need temporary credentials or a separate non-production database user. Consider using a separate database role for your app and keeping the master user locked down for emergencies only.

If you're using this pattern across multiple databases, create a reusable module that centralizes the credential retrieval logic:

module "rds_postgres" {
  source = "./modules/rds"
  
  instance_identifier = "orders-db"
  engine              = "postgres"
  instance_class      = "db.r5.large"
  manage_master_user_password = true
  
  # other variables...
}

output "database_endpoint" {
  value = module.rds_postgres.endpoint
}

output "database_secret_arn" {
  value = module.rds_postgres.secret_arn
}

Protection — deletion_protection and final_snapshots in Prod

Production databases must be protected from accidental deletion. Two settings matter:

deletion_protection = true: This prevents the resource from being destroyed via AWS Console or API calls without explicitly disabling the flag first. It won't stop a determined attacker, but it stops you from accidentally deleting your database on a Friday at 5 PM.

skip_final_snapshot = false with a final_snapshot_identifier: When you destroy the RDS resource, Terraform creates a snapshot before tearing it down. You can restore from that snapshot if disaster strikes. Always name it clearly with a timestamp so you can find it later.

resource "aws_db_instance" "postgres_prod" {
  identifier                = "orders-db-prod"
  engine                    = "postgres"
  instance_class            = "db.r5.large"
  
  deletion_protection       = true
  skip_final_snapshot       = false
  final_snapshot_identifier = "orders-db-prod-final-${formatdate("YYYY-MM-DD-hhmm", timestamp())}"
  
  # ... other config ...
}

For development and staging, you might set deletion_protection = false and skip_final_snapshot = true to avoid snapshot clutter. Use environment-specific variables:

variable "environment" {
  type = string
}

variable "enable_deletion_protection" {
  type    = bool
  default = false
}

variable "skip_final_snapshot" {
  type    = bool
  default = true
}

resource "aws_db_instance" "main" {
  deletion_protection = var.enable_deletion_protection
  skip_final_snapshot = var.skip_final_snapshot
  
  # ... rest of config ...
}

In production, set those variables to true. In dev, set them to false. Your infrastructure code now reflects your operational reality.

Common Mistakes

• Hardcoding passwords in Terraform code or tfvars files. Always use manage_master_user_password = true and retrieve credentials from Secrets Manager at runtime.
• Using public subnets for databases. Even if you don't assign a public IP, never create a database in a public subnet. Use private subnets with a NAT gateway if your application needs outbound internet.
• Forgetting backup retention and multi-AZ in production. These are not luxury features; they're insurance. A single-AZ database with no backups will fail, and you will lose data.
• Creating databases without deletion protection. Terraform destroy is powerful and unforgiving. Protect your data first, ask questions later.
• Mixing managed passwords with manual password arguments. If you enable manage_master_user_password, don't pass a password argument. Let AWS own the credential lifecycle.
• Not versioning your RDS module. Use Terraform modules to encapsulate RDS best practices. Version them. Reuse them. Don't write raw RDS resources in your root modules.

Key Takeaways

• Subnet groups restrict your database to private subnets across multiple availability zones. Define them early and never deviate.
• Choose between single-instance RDS (simpler, cheaper) and Aurora clusters (higher availability, automatic failover) based on your operational needs and risk tolerance.
• Use manage_master_user_password = true to offload credential management to AWS Secrets Manager. Never hardcode passwords.
• Enable deletion_protection and skip final snapshots only in dev environments. Production databases need both flags set to safe values.
• Wrap RDS configuration in a reusable Terraform module. This ensures consistency across environments and makes policy changes painless.
• Always enable automated backups, multi-AZ failover, and encryption at rest for production workloads. These features are cheap compared to data loss.