RDS: Design Decisions for Managed PostgreSQL and Completing the Security Group Chain
In the previous chapter, Tasuku gained a runtime foundation for launching containers on Fargate. However, the tasks it launches aren't handling any data yet. As a task management SaaS, we need somewhere to store task content and user information. In this chapter, we build the managed database that serves as that storage destination: RDS for PostgreSQL. The security group chain foreshadowed in section 4.7 of Chapter 4 is also completed in this chapter.
9.1. Division of responsibility for a managed database
RDS (Relational Database Service) is a mechanism that provides relational database engines, including PostgreSQL, as a managed service. As foreshadowed in Chapter 1, Tasuku runs PostgreSQL on this RDS.
The other option is to install and operate PostgreSQL yourself on an EC2 instance. With this approach, you have to build and maintain everything yourself: OS patching, PostgreSQL minor version upgrades, taking and storing backups, and switching over when a failure occurs. RDS handles this management work on your behalf — AWS takes on backups, software patching, and automatic failure detection and recovery.1 High availability through failover to a synchronous secondary instance is also one of the mechanisms RDS provides.1 There's no reason for Tasuku, at its scale, to build this operational work itself, so we choose RDS.
What RDS doesn't handle on your behalf is table design, how you write queries, access control, and instance size selection. This chapter mainly covers instance size selection (section 9.4) and access control (sections 9.5 and 9.7). Table design and queries fall within the scope of Tasuku's application code, so per the scope declaration in section 1.3 of Chapter 1, this series doesn't cover them.
9.2. Choosing a PostgreSQL version
PostgreSQL has several major versions, and as of this writing, RDS currently supports versions 18 through 14.2 Each major version has a standard support end date on RDS.2 Version 14, released in 2022, reaches the end of standard support in February 2027, while version 17, released in 2024, continues until February 2030.2
This chapter adopts PostgreSQL 17. As of this writing (July 2026), about a year and a half has passed since its release on RDS, leaving nearly four years before standard support ends. The latest version, 18, was released only recently and doesn't yet have much of a track record. There's no official AWS recommendation for version selection — this is an editorial decision for this series.
9.3. Subnet group: reusing the private-data subnets
To place an RDS instance inside a VPC, you need a DB subnet group. A DB subnet group is a container that specifies which subnets an RDS instance can be placed in, and it must include subnets spanning at least two availability zones.3 If it doesn't, creating the RDS instance itself fails with an error.3
The private-data subnets created in Chapter 4 (10.0.20.0/24 and 10.0.21.0/24, spanning two availability zones) were designed for RDS from the start, and they satisfy this 2-AZ requirement as they are. This chapter doesn't create new subnets — it uses this subnet group as is.
9.4. Instance class, storage, and Multi-AZ
RDS instance classes come in several families, including general purpose, memory optimized, and burstable performance.4 This chapter adopts db.t4g.micro.
db.t4g is a burstable performance instance class that runs on Arm-based AWS Graviton2 processors,4 and it supports all current versions including PostgreSQL 17.5 Following the same thinking as starting Fargate's cpu and memory at the minimum configuration (256 and 512 MiB) in Chapter 8, we start with db.t4g.micro.
You may come across a warning about T instances (db.t2, t3, t4g) saying they're "for development and test environments only, not recommended for production." This is what's stated in the Aurora guide.6 In the standard RDS guide (as opposed to Aurora), this warning appears only for db.t2, not for db.t3 or db.t4g.7 Since Tasuku uses standard RDS rather than Aurora, there's no issue using db.t4g.micro as the starting point for production.
db.t4g.micro has 1 GiB of memory.8 Whether this is enough for Tasuku's assumed scale (a few dozen tenants, on the order of a few hundred concurrent users, per section 8.2 of Chapter 8) is something we won't know until we actually apply and put it under load. Fargate dynamically adjusts the number of tasks in response to load via Auto Scaling in Chapter 10, but the cpu and memory values themselves stay fixed at Chapter 8's choices and aren't revisited throughout this series. The RDS instance class is the same way — no chapter in this series plans to revisit it. Treat this value as a starting point that's meant to be addressed by changing the instance class once a shortfall shows up in actual operation.
For storage, we use gp3 at the minimum size of 20 GiB.9 We enable encryption at creation time. RDS instance encryption cannot be added to an existing instance after creation.10 The only way is to make a copy of an encrypted snapshot and restore a new instance from it.10 Enabling it from the start avoids having to rebuild the instance this way.
Finally, we enable Multi-AZ. In Chapter 4, we decided on a configuration that keeps the service running even if one availability zone becomes unavailable. If RDS alone stayed single-AZ, this premise would break down at the database layer. Enabling Multi-AZ makes RDS automatically provision a standby instance as a synchronous replica in another availability zone and fail over to it when a failure occurs.11 This approach is called a "Multi-AZ DB instance deployment,"11 and unlike the separate "Multi-AZ DB cluster deployment" approach, which has two readable standbys,12 it has no instance class restrictions.12 That's why we can make it Multi-AZ while keeping db.t4g.micro.
Enabling Multi-AZ adds charges for the standby instance. This is the same decision as the NAT Gateway redundancy in Chapter 4. Check the AWS pricing page for specific rates.
9.5. Master password and Secrets Manager integration
An RDS instance requires a master user. There are two approaches to managing this password: writing it directly in Terraform code, or delegating management to AWS Secrets Manager. This chapter uses the latter.
Enabling manage_master_user_password makes RDS generate the password and manage its entire lifecycle as a secret in Secrets Manager.13 By default, the secret is automatically rotated every 7 days.14 From the examples in the official AWS documentation, we can confirm that the generated secret includes at least a username and a password key.15 We design things so that the connection hostname, port number, and database name are retrieved from Terraform outputs rather than relying on the contents of the secret.
The permissions required to enable this integration, when not using a custom KMS key, are these three: kms:DescribeKey, secretsmanager:CreateSecret, and secretsmanager:TagResource.16 The Terraform executor permissions set up in Chapter 3 include PowerUserAccess. This policy excludes only three namespaces — iam:*, organizations:*, and account:*17 — and none of the three permissions above fall under this exclusion. Unlike in section 8.5 of Chapter 8, where the ECR execution role permissions needed a custom policy, this integration requires no additional IAM policy.
9.6. Parameter groups
To change PostgreSQL's engine settings (parameters), you need to create a dedicated DB parameter group. A parameter group has one corresponding engine-and-major-version combination (family).18 This chapter creates a new parameter group with the family postgres17.
The first thing we considered was explicitly changing the rds.force_ssl parameter, which enforces SSL connections, to 1. However, as of PostgreSQL 15, this parameter's default value is already 1 (on).19 The same holds for PostgreSQL 17, which Tasuku adopts, so there's no need to change it. Rather than changing it in the parameter group, we confirm in section 9.9's verification that it's enabled by default as expected.
What we actually change in the parameter group is log_min_duration_statement. This parameter isn't set by default,20 and when you specify a value in milliseconds, it logs any SQL statement that takes at least that long. We set it to 1000 (1 second) so that we can detect slow queries. We couldn't find a primary source stating clearly whether this parameter takes effect immediately or requires a DB instance restart. We confirm whether it has taken effect in section 9.9's verification, and a restart may be needed if it hasn't.
9.7. Security groups: completing the chain
The security group chain diagram in section 4.7 of Chapter 4 foreshadowed the RDS security group as allowing ingress only from the ECS task security group. Here we build it exactly that way, creating the third explicit SG in this series, following the ALB in Chapter 6 and the ECS tasks in Chapter 8.
For ingress, we allow only port 5432 from the ECS task security group created in Chapter 8 — PostgreSQL's default port.
We don't define any egress at all. Unless the RDS instance itself acts as a connection source to the outside, egress rules are effectively meaningless.21 In addition, when AWS creates a new security group within a VPC, it automatically adds an allow-all egress rule by default.22 When egress isn't explicitly specified, Terraform's aws_security_group resource detects and removes this auto-generated rule.23 In other words, if you don't write a single egress rule, the final state is a full egress deny with zero egress rules. The "intentionally empty" ALB security group in Chapter 6 is built on this same mechanism.
Finally, we add port 5432 egress to the RDS security group on the ECS task security group created in Chapter 8. Since the only egress as of Chapter 8 was port 443, this chapter completes the security group chain.
9.8. Terraform implementation
We write everything covered so far into a new file called rds.tf. You can't use name_prefix as-is for the DB name or master username. PostgreSQL's db_name allows only 1-63 alphanumeric characters and underscores, and master_username allows only 1-16 alphanumeric characters and underscores — neither can contain a hyphen.24 For db_name, we use a value with name_prefix's hyphens converted to underscores, and for master_username, we use a value built directly from var.project (default value tasuku, which contains no hyphens) without going through this conversion. Note that even when you specify db_name, a separate default database named postgres is always created as well.24 The Tasuku database isn't the only one that exists.
locals {
db_identifier_safe = replace(local.name_prefix, "-", "_")
}
resource "aws_db_subnet_group" "main" {
name = "${local.name_prefix}-db-subnet-group"
description = "Subnet group for RDS. Uses the private-data subnets from Chapter 4 (2 AZs)"
subnet_ids = [for k in local.private_data_subnet_keys : aws_subnet.this[k].id]
tags = {
Name = "${local.name_prefix}-db-subnet-group"
}
}
This is the security group chain decided in section 9.7.
resource "aws_security_group" "rds" {
name = "${local.name_prefix}-rds-sg"
description = "SG for RDS. Allows ingress only from the ECS task SG"
vpc_id = aws_vpc.main.id
tags = {
Name = "${local.name_prefix}-rds-sg"
}
}
resource "aws_vpc_security_group_ingress_rule" "rds_from_ecs_tasks" {
security_group_id = aws_security_group.rds.id
referenced_security_group_id = aws_security_group.ecs_tasks.id
from_port = 5432
to_port = 5432
ip_protocol = "tcp"
description = "Allows only PostgreSQL connections from the ECS task SG (Chapter 8)"
}
resource "aws_vpc_security_group_egress_rule" "ecs_tasks_to_rds" {
security_group_id = aws_security_group.ecs_tasks.id
referenced_security_group_id = aws_security_group.rds.id
from_port = 5432
to_port = 5432
ip_protocol = "tcp"
description = "PostgreSQL connection to the RDS SG (used for the psql connectivity check via run-task, and by the app itself in Chapter 10)"
}
This is the parameter group from section 9.6.
resource "aws_db_parameter_group" "postgres17" {
name = "${local.name_prefix}-postgres17"
family = "postgres17"
description = "PostgreSQL 17 parameter group for Tasuku. Enables slow query logging (1 second or more)"
parameter {
name = "log_min_duration_statement"
value = "1000"
}
tags = {
Name = "${local.name_prefix}-postgres17"
}
}
This is the RDS instance itself. It brings together the design decisions from sections 9.4 and 9.5.
resource "aws_db_instance" "main" {
identifier = "${local.name_prefix}-db"
engine = "postgres"
engine_version = "17"
instance_class = "db.t4g.micro"
allocated_storage = 20
storage_type = "gp3"
storage_encrypted = true
db_name = local.db_identifier_safe
username = "${var.project}_admin"
manage_master_user_password = true
db_subnet_group_name = aws_db_subnet_group.main.name
vpc_security_group_ids = [aws_security_group.rds.id]
parameter_group_name = aws_db_parameter_group.postgres17.name
multi_az = true
backup_retention_period = 7
deletion_protection = true
# A fixed-identifier final_snapshot_identifier would collide on name when destroy is re-run,
# so we don't create a final snapshot (deletion_protection separately guards against accidental deletion)
skip_final_snapshot = true
tags = {
Name = "${local.name_prefix}-db"
}
}
We explicitly specify 7 for backup_retention_period. If you omit this argument in Terraform, the default becomes 0 (automated backups disabled),25 which differs both from the 1-day default via the AWS API/CLI and from the 7-day default when created through the console.25 We explicitly set 7 days, matching the console default. deletion_protection also defaults to false,26 so we set it to true with production operation in mind.
We set skip_final_snapshot to true, so no final snapshot is created on deletion.27 Setting it to false creates a snapshot with the name specified in final_snapshot_identifier,27 but using a fixed string for this value causes a collision: if you destroy, apply the same code again, and destroy once more, it clashes with the same-named snapshot already created previously. Protection against accidental operations is already handled by deletion_protection, so this chapter simply chooses true.
Finally, we add a dedicated task definition bundled with the psql client for use in verification. Since Tasuku's application image doesn't exist yet, we substitute the official Docker postgres image, just as we did for hello-world in Chapter 8.28 We reuse Chapter 8's execution role, task role, cluster, and log group, changing only the log stream prefix.
resource "aws_ecs_task_definition" "psql_check" {
family = "${local.name_prefix}-psql-check"
requires_compatibilities = ["FARGATE"]
network_mode = "awsvpc"
cpu = 256
memory = 512
execution_role_arn = aws_iam_role.ecs_task_execution.arn
task_role_arn = aws_iam_role.ecs_task.arn
runtime_platform {
operating_system_family = "LINUX"
cpu_architecture = "X86_64"
}
container_definitions = jsonencode([
{
name = "psql-check"
image = "${aws_ecr_repository.app.repository_url}:psql-check"
essential = true
logConfiguration = {
logDriver = "awslogs"
options = {
"awslogs-group" = aws_cloudwatch_log_group.app.name
"awslogs-region" = var.region
"awslogs-stream-prefix" = "psql-check"
}
}
}
])
tags = {
Name = "${local.name_prefix}-psql-check"
}
}
We add the values that later chapters will reference to outputs.tf.
output "rds_endpoint" {
description = "The RDS instance's connection endpoint (address:port format, referenced in the app connection string in Chapter 10)"
value = aws_db_instance.main.endpoint
}
output "rds_address" {
description = "The RDS instance's hostname only (excludes the port number, referenced in the run-task psql connection command)"
value = aws_db_instance.main.address
}
output "rds_db_name" {
description = "The RDS instance's initial database name (referenced in the app connection string in Chapter 10)"
value = aws_db_instance.main.db_name
}
output "rds_master_user_secret_arn" {
description = "The ARN of the Secrets Manager secret managing the master password (for the run-task connectivity check and reference by the app in Chapter 10)"
value = aws_db_instance.main.master_user_secret[0].secret_arn
}
output "rds_port" {
description = "The RDS instance's port number"
value = aws_db_instance.main.port
}
output "rds_security_group_id" {
description = "The RDS SG's ID (may be referenced in Chapter 10)"
value = aws_security_group.rds.id
}
output "psql_check_task_definition_arn" {
description = "The ARN of the task definition for the psql connectivity check (referenced in run-task)"
value = aws_ecs_task_definition.psql_check.arn
}
9.9. Verification
We run terraform fmt -check -recursive -diff and terraform validate to confirm the syntax and provider schema are consistent. This chapter's verification likewise stops at validate — plan and apply aren't included.
Here are the steps to actually apply this and verify it. First, pull the postgres:17-alpine image from Docker Hub and re-host it to ECR. This is the same procedure as hello-world in section 8.9 of Chapter 8.
aws ecr get-login-password --region ap-northeast-1 \
| docker login --username AWS --password-stdin <account-id>.dkr.ecr.ap-northeast-1.amazonaws.com
docker pull postgres:17-alpine
docker tag postgres:17-alpine <account-id>.dkr.ecr.ap-northeast-1.amazonaws.com/tasuku-dev-app:psql-check
docker push <account-id>.dkr.ecr.ap-northeast-1.amazonaws.com/tasuku-dev-app:psql-check
Next, retrieve the master password from Secrets Manager. We use the jq command to extract just the password from the JSON. Make sure to install it beforehand.
PGPASSWORD=$(aws secretsmanager get-secret-value \
--secret-id <value of rds_master_user_secret_arn> \
--query SecretString --output text | jq -r .password)
We inject the retrieved password into the container's environment variable and launch a one-off psql task with run-task. As in section 8.9 of Chapter 8, we explicitly specify subnets and securityGroups under --network-configuration. Omitting them would use the VPC's default security group, and since we closed that SG completely in Chapter 4, it couldn't connect. For the connection hostname, we use rds_address rather than rds_endpoint. Since rds_endpoint is in address:port format, including the port number, it can't be passed as-is to psql's -h option.
aws ecs run-task \
--cluster tasuku-dev-cluster \
--task-definition tasuku-dev-psql-check \
--launch-type FARGATE \
--network-configuration "awsvpcConfiguration={subnets=[<private-app subnet ID>],securityGroups=[<ecs_tasks SG ID>],assignPublicIp=DISABLED}" \
--overrides "{\"containerOverrides\":[{\"name\":\"psql-check\",\"environment\":[{\"name\":\"PGPASSWORD\",\"value\":\"$PGPASSWORD\"}],\"command\":[\"psql\",\"-h\",\"<value of rds_address>\",\"-U\",\"tasuku_admin\",\"-d\",\"tasuku_dev\",\"-c\",\"SELECT version(); SHOW rds.force_ssl; SHOW log_min_duration_statement;\"]}]}"
Verification happens in two stages. First, confirm with aws ecs describe-tasks that lastStatus becomes STOPPED and containers[].exitCode is 0. Just because the task finished normally doesn't tell you whether it actually connected to PostgreSQL. Next, run aws logs tail /ecs/tasuku-dev-app and confirm the actual output of SELECT version() recorded in CloudWatch Logs, that rds.force_ssl is on, and that log_min_duration_statement is 1000. Only after these two stages can we say the connectivity check is complete.
As with previous chapters, the RDS instance in this chapter is held to the same verification standard: we don't guarantee the outcome of actually applying this to AWS and carrying out these steps.
Summary
- RDS is a managed service where AWS handles backups, patching, and failure detection and recovery on your behalf, while Tasuku is responsible for table design, access control, and instance size selection
- We adopted PostgreSQL 17 — an editorial decision based on having nearly four years before standard support ends, and not being as untested as the very latest version
- We start with the db.t4g.micro instance class. We confirmed that the "T instances are for development/test only" warning is Aurora-specific guidance, and standard RDS carries no such restriction
- Storage is gp3, 20 GiB, with encryption enabled, and we also enabled Multi-AZ, extending the resilience against availability zone failure decided in Chapter 4 consistently down to the database layer
- We enabled
deletion_protectionto prevent accidental deletion, and setskip_final_snapshotto true to avoid name collisions from a fixed-string identifier - We left the master password to the Secrets Manager integration (
manage_master_user_password), which required no additional IAM policy unlike Chapter 8 - In the parameter group, we left
rds.force_sslunchanged, since it's on by default from PostgreSQL 15 onward, and explicitly changed onlylog_min_duration_statement - We created a new RDS security group, completing the ALB → ECS → RDS security group chain foreshadowed in Chapter 4
- We performed verification via run-task using a task bundled with the psql client, in a two-stage check confirming both the exitCode and the actual output in CloudWatch Logs
Next steps
- Chapter 10: ECS Part 2: Publishing the web/api Services and Auto Scaling: Publishing the web and api services, injecting secrets into the app from Secrets Manager
- Chapter 8: ECS Part 1: Building a Container Runtime Foundation with Fargate: ECS cluster and task definitions, execution role and task role, ECR
- Series table of contents: The structure of all 12 chapters and how to read through them