LAB29_AWS_Production RDS: Multi-AZ, Read Replica, Enhanced Monitoring, and Secrets Manager

variable "aws_region"    { type = string; default = "us-east-1" }
variable "vpc_id"        { type = string }
variable "db_subnet_ids" { type = list(string); description = "Private subnets in 2+ AZs." }
variable "app_cidr"      { type = string; default = "10.0.11.0/24" }
variable "db_name"       { type = string; default = "appdb" }
variable "db_username"   { type = string; default = "dbadmin" }
variable "environment"   { type = string; default = "prod" }

provider "aws" { region = var.aws_region }
locals { tags = { Environment = var.environment, ManagedBy = "terraform" } }

resource "random_password" "db" { length = 24; special = true; override_special = "!#$%-_=+" }

resource "aws_kms_key" "rds" {
  description = "CMK for RDS and Secrets — ${var.environment}"
  enable_key_rotation = true; deletion_window_in_days = 7
  tags = local.tags
}
resource "aws_kms_alias" "rds" { name = "alias/rds-${var.environment}"; target_key_id = aws_kms_key.rds.key_id }

resource "aws_db_subnet_group" "main" { name = "rds-subnet-${var.environment}"; subnet_ids = var.db_subnet_ids; tags = local.tags }

resource "aws_security_group" "rds" {
  name = "rds-sg-${var.environment}"; vpc_id = var.vpc_id
  ingress { from_port = 3306; to_port = 3306; protocol = "tcp"; cidr_blocks = [var.app_cidr] }
  egress  { from_port = 0;    to_port = 0;    protocol = "-1";  cidr_blocks = ["0.0.0.0/0"] }
  tags = local.tags
}

# Enhanced Monitoring IAM role — assumed by the RDS monitoring service (not your code).
data "aws_iam_policy_document" "mon_assume" {
  statement { effect = "Allow"; actions = ["sts:AssumeRole"]
    principals { type = "Service"; identifiers = ["monitoring.rds.amazonaws.com"] } }
}
resource "aws_iam_role" "rds_monitoring" {
  name = "rds-monitoring-${var.environment}"; assume_role_policy = data.aws_iam_policy_document.mon_assume.json
}
resource "aws_iam_role_policy_attachment" "rds_monitoring" {
  role = aws_iam_role.rds_monitoring.name
  policy_arn = "arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole"
}

# Multi-AZ primary
resource "aws_db_instance" "primary" {
  identifier     = "app-db-primary-${var.environment}"
  engine         = "mysql"; engine_version = "8.0"; instance_class = "db.t3.small"
  multi_az       = true
  db_name        = var.db_name; username = var.db_username; password = random_password.db.result
  allocated_storage = 50; max_allocated_storage = 200; storage_type = "gp3"
  storage_encrypted = true; kms_key_id = aws_kms_key.rds.arn
  db_subnet_group_name = aws_db_subnet_group.main.name
  vpc_security_group_ids = [aws_security_group.rds.id]; publicly_accessible = false
  backup_retention_period = 14; backup_window = "03:00-04:00"
  monitoring_interval = 60; monitoring_role_arn = aws_iam_role.rds_monitoring.arn
  deletion_protection = true; skip_final_snapshot = false
  final_snapshot_identifier = "app-db-primary-final"
  tags = local.tags
}

# Read replica — asynchronous, for read scaling.
resource "aws_db_instance" "replica" {
  identifier          = "app-db-replica-${var.environment}"
  replicate_source_db = aws_db_instance.primary.identifier
  instance_class      = "db.t3.small"
  vpc_security_group_ids = [aws_security_group.rds.id]
  publicly_accessible = false; storage_encrypted = true; skip_final_snapshot = true
  tags = merge(local.tags, { Role = "read-replica" })
}

# Store credentials in Secrets Manager.
resource "aws_secretsmanager_secret" "db" {
  name = "${var.environment}/rds/master"; kms_key_id = aws_kms_key.rds.arn
  recovery_window_in_days = 7; tags = local.tags
}
resource "aws_secretsmanager_secret_version" "db" {
  secret_id = aws_secretsmanager_secret.db.id
  secret_string = jsonencode({
    username = var.db_username; password = random_password.db.result
    host = aws_db_instance.primary.address; port = 3306; dbname = var.db_name
  })
  depends_on = [aws_db_instance.primary]
}

# CloudWatch alarm on DatabaseConnections.
resource "aws_cloudwatch_metric_alarm" "db_connections" {
  alarm_name = "rds-high-connections-${var.environment}"
  comparison_operator = "GreaterThanThreshold"; evaluation_periods = 2
  metric_name = "DatabaseConnections"; namespace = "AWS/RDS"; period = 300
  statistic = "Average"; threshold = 100
  dimensions = { DBInstanceIdentifier = aws_db_instance.primary.id }
  tags = local.tags
}

output "primary_endpoint" { value = aws_db_instance.primary.endpoint }
output "replica_endpoint" { value = aws_db_instance.replica.endpoint }
output "secret_arn"       { value = aws_secretsmanager_secret.db.arn }
output "db_password"      { value = random_password.db.result; sensitive = true }

terraform init
terraform apply -var="vpc_id=vpc-xxxx" -var='db_subnet_ids=["subnet-aaa","subnet-bbb"]'

# Check Multi-AZ status
aws rds describe-db-instances --db-instance-identifier app-db-primary-prod \
  --query 'DBInstances[0].{MultiAZ:MultiAZ,AZ:AvailabilityZone}'

# Retrieve credentials from Secrets Manager
aws secretsmanager get-secret-value --secret-id prod/rds/master \
  --query SecretString | python3 -m json.tool

# Trigger Multi-AZ failover test
aws rds reboot-db-instance --db-instance-identifier app-db-primary-prod --force-failover

terraform destroy -var="vpc_id=..." -var='db_subnet_ids=["..."]'