PreviewLab10 hours

Postgres

Provision RDS PostgreSQL, configure networking and security, enable Multi-AZ and read replicas, and practice backups, restore, and monitoring.

RDS mindset shift: you never SSH into the instance. AWS manages the OS, storage, and engine. Everything is via API, parameter groups, or the database connection. This is the fundamental difference from self-managed Postgres.

Prerequisites

0 of 6 checked

Provisioning & Connectivity

Goal

Provision an RDS PostgreSQL instance via CLI. Configure networking (subnet group, security group). Connect via psql. Create a custom parameter group and observe static vs dynamic parameter behavior.

Estimated time: 1–2 hours

Gather VPC and subnet information

What's happening here

RDS requires a DB subnet group: a collection of subnets across at least 2 AZs. RDS uses this to place the primary instance and (when Multi-AZ is enabled) the standby in separate AZs. Without subnets in 2+ AZs, Multi-AZ creation fails. RDS instances are VPC-private by default: there is no public endpoint unless you explicitly enable PubliclyAccessible. Best practice: never enable public access. Connectivity always goes through a bastion, SSM port forwarding, or an application in the same VPC.

bash

mkdir rds-lab && cd rds-lab

# Get default VPC
export VPC_ID=$(aws ec2 describe-vpcs \
  --filters Name=isDefault,Values=true \
  --region $AWS_REGION \
  --query 'Vpcs[0].VpcId' --output text)

echo "VPC: $VPC_ID"

# Get subnets in at least 2 AZs
aws ec2 describe-subnets \
  --filters Name=vpc-id,Values=$VPC_ID \
  --region $AWS_REGION \
  --query 'Subnets[*].{SubnetId:SubnetId,AZ:AvailabilityZone,CIDR:CidrBlock}'

# Export first two subnet IDs
export SUBNET_1=$(aws ec2 describe-subnets \
  --filters Name=vpc-id,Values=$VPC_ID \
  --region $AWS_REGION \
  --query 'Subnets[0].SubnetId' --output text)

export SUBNET_2=$(aws ec2 describe-subnets \
  --filters Name=vpc-id,Values=$VPC_ID \
  --region $AWS_REGION \
  --query 'Subnets[1].SubnetId' --output text)

echo "Subnet 1: $SUBNET_1"
echo "Subnet 2: $SUBNET_2"

Create DB subnet group and security group

What's happening here

The DB subnet group tells RDS which subnets it can use. It's a logical resource, not a running resource. You create it once and reference it on every RDS instance in that VPC. The security group controls network access to port 5432. The inbound rule here allows access from the same VPC CIDR, in production you'd restrict this to your application's security group ID, not a CIDR range. The common mistake: forgetting this rule entirely, then getting a connection timeout (not a refused connection, timeout, which is much harder to diagnose).

bash

# Create DB subnet group
aws rds create-db-subnet-group \
  --db-subnet-group-name rds-lab-subnet-group \
  --db-subnet-group-description "RDS Lab subnet group" \
  --subnet-ids $SUBNET_1 $SUBNET_2 \
  --region $AWS_REGION

echo "DB subnet group created"

# Get VPC CIDR
export VPC_CIDR=$(aws ec2 describe-vpcs \
  --vpc-ids $VPC_ID \
  --region $AWS_REGION \
  --query 'Vpcs[0].CidrBlock' --output text)

echo "VPC CIDR: $VPC_CIDR"

# Create security group for RDS
export RDS_SG_ID=$(aws ec2 create-security-group \
  --group-name rds-lab-sg \
  --description "RDS Lab security group" \
  --vpc-id $VPC_ID \
  --region $AWS_REGION \
  --query 'GroupId' --output text)

echo "RDS Security Group: $RDS_SG_ID"

# Allow PostgreSQL (5432) from within VPC
aws ec2 authorize-security-group-ingress \
  --group-id $RDS_SG_ID \
  --protocol tcp \
  --port 5432 \
  --cidr $VPC_CIDR \
  --region $AWS_REGION

echo "Inbound rule added: TCP 5432 from $VPC_CIDR"

Create a custom parameter group

What's happening here

A parameter group is a named collection of engine configuration values, the RDS equivalent of postgresql.conf. RDS ships with default parameter groups that cannot be modified. You must create a custom one to change any parameter. Parameters are either static (require instance reboot to apply) or dynamic (apply immediately without downtime). log_min_duration_statement is dynamic (flip it on in production to catch slow queries without a reboot. max_connections is static) changing it requires a reboot and a maintenance window. The family postgres16 must match the engine version you'll provision. Check available families with aws rds describe-db-engine-versions.

bash

# Check available PostgreSQL families
aws rds describe-db-engine-versions \
  --engine postgres \
  --region $AWS_REGION \
  --query 'DBEngineVersions[*].{Version:EngineVersion,Family:DBParameterGroupFamily}' \
  | jq 'unique_by(.Family) | .[-5:]'

# Create custom parameter group
aws rds create-db-parameter-group \
  --db-parameter-group-name rds-lab-pg16 \
  --db-parameter-group-family postgres16 \
  --description "RDS Lab custom parameter group" \
  --region $AWS_REGION

echo "Parameter group created"

# Set dynamic parameters (apply immediately, no reboot)
aws rds modify-db-parameter-group \
  --db-parameter-group-name rds-lab-pg16 \
  --parameters \
    'ParameterName=log_min_duration_statement,ParameterValue=1000,ApplyMethod=immediate' \
    'ParameterName=log_connections,ParameterValue=1,ApplyMethod=immediate' \
    'ParameterName=log_disconnections,ParameterValue=1,ApplyMethod=immediate' \
  --region $AWS_REGION

# Set static parameters (require reboot)
aws rds modify-db-parameter-group \
  --db-parameter-group-name rds-lab-pg16 \
  --parameters \
    'ParameterName=shared_preload_libraries,ParameterValue=pg_stat_statements,ApplyMethod=pending-reboot' \
  --region $AWS_REGION

# Verify
aws rds describe-db-parameters \
  --db-parameter-group-name rds-lab-pg16 \
  --region $AWS_REGION \
  --query 'Parameters[?ParameterValue!=null] | [?IsModifiable==`true`].{Name:ParameterName,Value:ParameterValue,ApplyType:ApplyType}'

Checkpoint: parameter group exists with custom values.

Provision the RDS instance

What's happening here

Key provisioning decisions:

db.t3.medium: smallest instance with burst-able CPU. Fine for a lab, not for production workloads with sustained load.
--no-multi-az: single AZ for now. We add Multi-AZ in Session 4.
--backup-retention-period 7 (enables automated backups (required for PITR in Session 3). Setting to 0 disables backups entirely) no PITR, no automated snapshots.
--storage-encrypted: enables encryption at rest using the default KMS key. Cannot be added to an existing unencrypted instance: you must snapshot and restore to an encrypted instance.
--enable-performance-insights: turns on PI from day one. Retroactive enabling is possible but you lose historical baseline data.

Creation takes 5–10 minutes. The CLI returns immediately; use wait to block until available.

bash

export DB_PASSWORD=$(openssl rand -base64 24 | tr -dc 'a-zA-Z0-9' | head -c 24)
export DB_IDENTIFIER=rds-lab-postgres

echo "DB Password: $DB_PASSWORD"
echo "Save this, you'll need it in subsequent sessions."
echo $DB_PASSWORD > .db_password

aws rds create-db-instance \
  --db-instance-identifier $DB_IDENTIFIER \
  --db-instance-class db.t3.medium \
  --engine postgres \
  --engine-version 16 \
  --master-username labadmin \
  --master-user-password $DB_PASSWORD \
  --db-name labdb \
  --db-subnet-group-name rds-lab-subnet-group \
  --vpc-security-group-ids $RDS_SG_ID \
  --db-parameter-group-name rds-lab-pg16 \
  --allocated-storage 20 \
  --storage-type gp3 \
  --no-multi-az \
  --backup-retention-period 7 \
  --storage-encrypted \
  --enable-performance-insights \
  --performance-insights-retention-period 7 \
  --region $AWS_REGION \
  --query 'DBInstance.{Status:DBInstanceStatus,Class:DBInstanceClass,Engine:Engine,AZ:AvailabilityZone}'

echo "Waiting for instance to become available (5–10 minutes)..."
aws rds wait db-instance-available \
  --db-instance-identifier $DB_IDENTIFIER \
  --region $AWS_REGION

# Get endpoint
export DB_ENDPOINT=$(aws rds describe-db-instances \
  --db-instance-identifier $DB_IDENTIFIER \
  --region $AWS_REGION \
  --query 'DBInstances[0].Endpoint.Address' --output text)

echo "DB Endpoint: $DB_ENDPOINT"

Checkpoint: instance available: endpoint printed.

Connect via psql

What's happening here

RDS is VPC-private. To connect from your laptop you need one of:

SSM port forwarding through an EC2 instance (no open SSH port needed), used here
A bastion host with SSH tunnel
Enabling PubliclyAccessible (not recommended)

SSM port forwarding works by opening a secure WebSocket tunnel through the SSM Agent on the EC2 instance to the RDS endpoint. Your local psql connects to localhost:5433 and traffic is forwarded transparently. No inbound SSH port (22) needs to be open on the EC2 instance.

bash

# Launch a small EC2 instance as SSM jump host (Amazon Linux 2023 has SSM agent built in)
export JUMP_AMI=$(aws ec2 describe-images \
  --owners amazon \
  --filters 'Name=name,Values=al2023-ami-*-x86_64' 'Name=state,Values=available' \
  --region $AWS_REGION \
  --query 'sort_by(Images, &CreationDate)[-1].ImageId' --output text)

export SSM_ROLE_ARN=$(aws iam create-role \
  --role-name rds-lab-ssm-role \
  --assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Principal":{"Service":"ec2.amazonaws.com"},"Action":"sts:AssumeRole"}]}' \
  --query 'Role.Arn' --output text)

aws iam attach-role-policy \
  --role-name rds-lab-ssm-role \
  --policy-arn arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore

aws iam create-instance-profile --instance-profile-name rds-lab-ssm-profile
aws iam add-role-to-instance-profile \
  --instance-profile-name rds-lab-ssm-profile \
  --role-name rds-lab-ssm-role

sleep 10

export JUMP_INSTANCE_ID=$(aws ec2 run-instances \
  --image-id $JUMP_AMI \
  --instance-type t3.micro \
  --iam-instance-profile Name=rds-lab-ssm-profile \
  --subnet-id $SUBNET_1 \
  --no-associate-public-ip-address \
  --region $AWS_REGION \
  --query 'Instances[0].InstanceId' --output text)

echo "Jump host: $JUMP_INSTANCE_ID"
aws ec2 wait instance-running --instance-ids $JUMP_INSTANCE_ID --region $AWS_REGION
sleep 30  # SSM agent registration

# Start SSM port forwarding (run in a separate terminal or background)
echo "Run this in a separate terminal to open the tunnel:"
echo "aws ssm start-session \\"
echo "  --target $JUMP_INSTANCE_ID \\"
echo "  --document-name AWS-StartPortForwardingSessionToRemoteHost \\"
echo "  --parameters host=$DB_ENDPOINT,portNumber=5432,localPortNumber=5433 \\"
echo "  --region $AWS_REGION"

# Then connect with psql
echo "\nConnect with:"
echo "PGPASSWORD=$DB_PASSWORD psql -h 127.0.0.1 -p 5433 -U labadmin -d labdb"

Once connected, create test tables:

sql

-- Run inside psql
CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  customer_id VARCHAR(50) NOT NULL,
  amount INTEGER NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO invoices (customer_id, amount, status)
SELECT
  'customer-' || (random() * 100)::int,
  (random() * 10000)::int,
  (ARRAY['pending','paid','voided'])[floor(random()*3+1)]
FROM generate_series(1, 1000);

SELECT COUNT(*), AVG(amount), MIN(created_at) FROM invoices;
\q

Checkpoint: connected via psql, 1000 rows inserted.

Break It: Wrong Security Group

bash

# Create a second SG with NO inbound rules
export BLOCKED_SG=$(aws ec2 create-security-group \
  --group-name rds-lab-blocked-sg \
  --description "No inbound rules" \
  --vpc-id $VPC_ID \
  --region $AWS_REGION \
  --query 'GroupId' --output text)

# Swap the instance to the blocked SG
aws rds modify-db-instance \
  --db-instance-identifier $DB_IDENTIFIER \
  --vpc-security-group-ids $BLOCKED_SG \
  --apply-immediately \
  --region $AWS_REGION

aws rds wait db-instance-available \
  --db-instance-identifier $DB_IDENTIFIER --region $AWS_REGION

# Try to connect, this will hang, not refuse
echo "Attempting connection (will timeout after ~30s)..."
timeout 30 bash -c "PGPASSWORD=$DB_PASSWORD psql -h 127.0.0.1 -p 5433 -U labadmin -d labdb -c 'SELECT 1'" || echo "Connection timed out"

the connection times out: not refuses. A refused connection (Connection refused) means the port is actively rejected, the host is reachable but nothing is listening. A timeout means the SYN packet is silently dropped by the security group, the host is unreachable at the network level. This is the most common RDS connectivity failure mode and is harder to diagnose than a refusal.

bash

# Restore correct SG
aws rds modify-db-instance \
  --db-instance-identifier $DB_IDENTIFIER \
  --vpc-security-group-ids $RDS_SG_ID \
  --apply-immediately \
  --region $AWS_REGION

aws rds wait db-instance-available \
  --db-instance-identifier $DB_IDENTIFIER --region $AWS_REGION

aws ec2 delete-security-group --group-id $BLOCKED_SG --region $AWS_REGION

Security: Encryption, IAM Auth, Secrets Manager

Goal

Understand encryption at rest. Enable and use IAM database authentication, connect without a password using a short-lived IAM token. Integrate Secrets Manager for credential storage and automatic rotation.