RDS Postgres in the Database Subnets

1. 1

   Database variables

   Only size and HA are variables; the safety settings are derived from the environment in the resource itself.

   variable "db_instance_class" {
     type    = string
     default = "db.t4g.micro"
   }
   
   variable "db_multi_az" {
     description = "Synchronous standby in a second AZ. Required in prod."
     type        = bool
     default     = false
   }

2. 2

   Subnet group and parameter group

   The subnet group tells RDS which subnets it may use, here the database tier with no internet route. The parameter group forces TLS for every connection (rds.force_ssl) and logs queries slower than 500 ms. Parameter groups can't be renamed or have their family changed in place, hence name_prefix with create_before_destroy, the same pattern as security groups.

   resource "aws_db_subnet_group" "main" {
     name       = "${local.name_prefix}-db"
     subnet_ids = [for s in aws_subnet.database : s.id]
   }
   
   resource "aws_db_parameter_group" "postgres16" {
     name_prefix = "${local.name_prefix}-pg16-"
     family      = "postgres16"
   
     parameter {
       name  = "rds.force_ssl"
       value = "1"
     }
   
     parameter {
       name  = "log_min_duration_statement"
       value = "500"
     }
   
     lifecycle {
       create_before_destroy = true
     }
   }

3. 3

   The instance

   Read it top to bottom: encrypted gp3 storage that autoscales up to 100 GB; manage_master_user_password instead of a password; placed in the database subnets with the DB security group from Mission 2.3; not publicly accessible; seven days of point-in-time backups; and the environment-dependent safety settings.

   locals {
     is_prod = var.environment == "prod"
   }
   
   resource "aws_db_instance" "main" {
     identifier     = "${local.name_prefix}-db"
     engine         = "postgres"
     engine_version = "16"
     instance_class = var.db_instance_class
   
     allocated_storage     = 20
     max_allocated_storage = 100
     storage_type          = "gp3"
     storage_encrypted     = true
   
     db_name                     = "shoplite"
     username                    = "shoplite_admin"
     manage_master_user_password = true
   
     db_subnet_group_name   = aws_db_subnet_group.main.name
     vpc_security_group_ids = [aws_security_group.db.id]
     parameter_group_name   = aws_db_parameter_group.postgres16.name
     publicly_accessible    = false
     multi_az               = var.db_multi_az
   
     backup_retention_period = 7
     copy_tags_to_snapshot   = true
   
     deletion_protection       = local.is_prod
     skip_final_snapshot       = !local.is_prod
     final_snapshot_identifier = "${local.name_prefix}-db-final"
     apply_immediately         = !local.is_prod
   }

4. 4

   Outputs — an ARN, not a password

   master_user_secret is a list with one element holding the secret's ARN. That's all Terraform knows.

   output "db_endpoint" {
     value = aws_db_instance.main.address
   }
   
   output "db_master_secret_arn" {
     value = aws_db_instance.main.master_user_secret[0].secret_arn
   }

5. 5

   Apply — and be patient

   RDS creation takes 5–10 minutes. Terraform polls and prints progress. Everything else in the plan finishes in seconds.

   terminalcopy cmd

   $ terraform apply

   ── expected output ──

   Plan: 3 to add, 0 to change, 0 to destroy.

   ...

   aws_db_instance.main: Still creating... [5m0s elapsed]

   aws_db_instance.main: Still creating... [7m30s elapsed]

   aws_db_instance.main: Creation complete after 7m52s [id=db-ABCDEFGHIJKLMNOPQRSTUVWXYZ]

    

   Apply complete! Resources: 3 added, 0 changed, 0 destroyed.

    

   Outputs:

   ...

   db_endpoint = "shoplite-dev-db.c1x2y3z4a5b6.ap-south-1.rds.amazonaws.com"

   db_master_secret_arn = "arn:aws:secretsmanager:ap-south-1:123456789012:secret:rds!db-7f3e...-AbCdEf"

6. 6

   Connect through a Session Manager tunnel

   The database has no public endpoint and only accepts the app security group. The probe instance from Mission 2.4 uses that group, so turn it on and forward a local port through it to RDS. No SSH, no bastion, no open ports. Fetch the password with YOUR credentials (not Terraform's state) and connect with psql from your laptop.

   terminalcopy cmd

   $ terraform apply -var enable_probe=true -auto-approve >/dev/null

   aws ssm start-session --target $(terraform output -raw probe_instance_id) \

   --document-name AWS-StartPortForwardingSessionToRemoteHost \

   --parameters "host=$(terraform output -raw db_endpoint),portNumber=5432,localPortNumber=15432" &

   export PGPASSWORD=$(aws secretsmanager get-secret-value --secret-id "$(terraform output -raw db_master_secret_arn)" --query SecretString --output text | jq -r .password)

   psql "host=localhost port=15432 dbname=shoplite user=shoplite_admin sslmode=require" -c 'select version();'

   ── expected output ──

   Starting session with SessionId: priya-0f1e2d...

   Port 15432 opened for sessionId priya-0f1e2d....

   version

   -------------------------------------------------------------------------------

   PostgreSQL 16.6 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 12.4.0, 64-bit

   (1 row)

Make each change, run the command, and read the error before revealing the diagnosis. Recognising these messages on sight is what makes you fast on a real team. Undo the change afterwards.