RDS: Security, Backup & Monitoring

Network Security

# RDS should be in private subnets — never publicly accessible
# DB subnet group spans multiple AZs for Multi-AZ support
aws rds create-db-subnet-group \
  --db-subnet-group-name my-db-subnets \
  --db-subnet-group-description "Private DB subnets" \
  --subnet-ids subnet-private-1a subnet-private-1b subnet-private-1c

# Security group: allow only app servers (or specific CIDR)
aws ec2 create-security-group \
  --group-name rds-sg \
  --description "RDS access"

aws ec2 authorize-security-group-ingress \
  --group-id sg-rds123 \
  --protocol tcp \
  --port 5432 \
  --source-group sg-app123   # App server SG only

Encryption at Rest & In Transit

• Encryption at rest: enabled at creation (KMS key), cannot be added to existing instance

• Workaround for adding encryption: snapshot → encrypted copy → restore from encrypted snapshot

• In transit: SSL/TLS required via rds.force_ssl=1 (MySQL) or ssl=true connection param

• KMS: use custom CMK for compliance (vs AWS-managed key)

# Enforce SSL in PostgreSQL via parameter group
aws rds modify-db-parameter-group \
  --db-parameter-group-name my-pg-params \
  --parameters "ParameterName=rds.force_ssl,ParameterValue=1,ApplyMethod=immediate"

# Connection with SSL (PostgreSQL)
psql "host=my-postgres.abc.eu-west-1.rds.amazonaws.com \
  dbname=mydb user=admin sslmode=require \
  sslrootcert=global-bundle.pem"

IAM Database Authentication

With IAM authentication, you authenticate using an IAM token instead of a password. Tokens expire after 15 minutes. Supported for MySQL and PostgreSQL.

# Enable IAM auth on instance
aws rds modify-db-instance \
  --db-instance-identifier my-postgres \
  --enable-iam-database-authentication \
  --apply-immediately

# Create IAM user in DB (PostgreSQL)
# GRANT rds_iam TO myapp_user;

# Generate auth token (valid 15 min)
TOKEN=$(aws rds generate-db-auth-token \
  --hostname my-postgres.abc.eu-west-1.rds.amazonaws.com \
  --port 5432 \
  --region eu-west-1 \
  --username myapp_user)

# Connect using token as password
PGPASSWORD=$TOKEN psql -h my-postgres.abc.eu-west-1.rds.amazonaws.com \
  -U myapp_user -d mydb --ssl

Secrets Manager Integration

# Create secret for RDS credentials
aws secretsmanager create-secret \
  --name rds/my-postgres/master \
  --secret-string '{"username":"admin","password":"SecureP@ss","host":"my-postgres.abc.eu-west-1.rds.amazonaws.com","port":5432,"dbname":"mydb"}'

# Enable automatic rotation (Lambda rotates password every 30 days)
aws secretsmanager rotate-secret \
  --secret-id rds/my-postgres/master \
  --rotation-rules AutomaticallyAfterDays=30

Automated Backups & Snapshots

# Automated backups: retention 1-35 days, daily backup window
aws rds modify-db-instance \
  --db-instance-identifier my-postgres \
  --backup-retention-period 14 \
  --preferred-backup-window "02:00-03:00"

# Point-in-time restore (within retention window)
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier my-postgres \
  --target-db-instance-identifier my-postgres-restored \
  --restore-time 2024-01-15T10:30:00Z

# Manual snapshot (persists until deleted)
aws rds create-db-snapshot \
  --db-instance-identifier my-postgres \
  --db-snapshot-identifier my-postgres-before-migration

# Restore from snapshot
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier my-postgres-restored \
  --db-snapshot-identifier my-postgres-before-migration

# Copy snapshot cross-region
aws rds copy-db-snapshot \
  --source-db-snapshot-identifier arn:aws:rds:eu-west-1:123:snapshot:my-snap \
  --target-db-snapshot-identifier my-snap-us-copy \
  --region us-east-1

CloudWatch Metrics & Alarms

# Key metrics to monitor:
# CPUUtilization      — alert >80% sustained
# FreeStorageSpace    — alert <20% of allocated
# DatabaseConnections — alert approaching max_connections
# ReadIOPS/WriteIOPS  — watch for I/O saturation
# ReadLatency/WriteLatency — p99 latency
# ReplicaLag         — for read replicas

# Create alarm for low free storage
aws cloudwatch put-metric-alarm \
  --alarm-name rds-low-storage \
  --metric-name FreeStorageSpace \
  --namespace AWS/RDS \
  --dimensions Name=DBInstanceIdentifier,Value=my-postgres \
  --statistic Average \
  --period 300 \
  --threshold 5368709120 \
  --comparison-operator LessThanThreshold \
  --evaluation-periods 2 \
  --alarm-actions arn:aws:sns:eu-west-1:123:my-alerts

Performance Insights

• Performance Insights: query-level visibility into DB load. Free tier available.

• Shows top SQL by load (db.load.avg), wait events, and active sessions

• Retention: 7 days free, up to 2 years with paid tier

• Enhanced Monitoring: OS-level metrics (memory/CPU/disk/processes) at 1-60 second granularity

# Enable Performance Insights
aws rds modify-db-instance \
  --db-instance-identifier my-postgres \
  --enable-performance-insights \
  --performance-insights-retention-period 7