Amazon RDS for SQL Server and PostgreSQL
Create and operate an RDS instance like a bank would: pick the engine, edition and licence model, size instance and gp3 storage, manage parameter and option groups, plan maintenance windows and upgrades, and audit every instance's posture from C#.
Assumes: .NET 10 LTS · AWS SDK for .NET v4 (AWSSDK.RDS 4.1.0.2) · AWS CLI v2 · Terraform AWS provider 6.x · RDS for PostgreSQL 17 · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Explain what Amazon RDS manages for you and what stays your responsibility, using the shared responsibility model.
- Choose an engine, edition and licence model for RDS for SQL Server (License Included or BYOM) and RDS for PostgreSQL, and justify the choice on cost.
- Size an instance class and gp3 storage, including baseline IOPS and throughput and storage autoscaling.
- Use DB parameter groups and option groups in place of
sp_configure,postgresql.confand server features. - Plan maintenance windows, minor and major version upgrades, Extended Support and Blue/Green deployments.
- Create a small, encrypted, private RDS for PostgreSQL instance with the CLI, CDK or Terraform, then delete it.
- Run a C# posture check that flags unencrypted, public or unprotected instances across a Region.
Prerequisites
- SQL Server on AWS: RDS, EC2, Aurora PostgreSQL and Babelfish compared
- Module 2: Networking: VPC, private subnets, security groups
- Module 5: Security: KMS basics
- Tools: AWS CLI v2 with the
learn-sandboxSSO profile, .NET 10 SDK; optional Terraform 1.10+ or CDK v2
Why this matters in banking
An internal audit at Harbourline samples three databases created by project teams in the cloud sandbox. All three were created through the console with defaults:
- one is publicly accessible with a security group open to
0.0.0.0/0"just for testing"; - one has backup retention of 1 day, so point-in-time recovery for last week is impossible;
- one uses the AWS managed
aws/rdskey, so its snapshots can't be shared with the backup account the DR plan relies on.
None of these was malicious. The console made each choice easy, and nobody wrote down which defaults the bank accepts. In this lesson you learn the knobs, set them deliberately in code, and build a check that would have caught all three findings.
The idea in plain language
Amazon RDS (Relational Database Service) runs a database engine for you on a managed virtual machine with network storage. You don't get the machine; you get a DB instance with an endpoint (a DNS name) and a port.
AWS takes care of:
- the hardware, the operating system and the engine binaries;
- applying patches in a weekly window you choose;
- taking automated backups and keeping transaction logs for point-in-time restore;
- replacing failed hardware and, with Multi-AZ, failing over to a standby.
You still own:
- the engine and version, the instance size and the storage;
- the network placement and who can connect;
- database users and permissions;
- encryption keys, parameter settings and schema;
- your data and the queries that run against it.
Analogy: RDS is a serviced office. The landlord runs the building, power, cleaning, fire alarms and security desk. You choose the size of office, who gets a key card, what goes in the filing cabinets and whether the cabinets lock. If you leave the door open, the landlord's excellent fire alarms won't help.
What problem RDS solves
On-premises, a SQL Server DBA team spends a large share of its time on undifferentiated work:
- patching Windows and SQL Server cumulative updates;
- maintaining backup jobs and checking that they ran;
- building and testing Always On clusters;
- swapping disks and capacity planning.
RDS turns these into settings. You get a backup retention period instead of a backup job, a Multi-AZ checkbox instead of a cluster build, and a maintenance window instead of a patch weekend.
What you give up (sharp edges)
- No OS access and no superuser. On SQL Server you get
db_owner, notsysadmin. On PostgreSQL the master user getsrds_superuser, notsuperuser. Some features are unavailable (see the comparison lesson). - One writer per instance. Scale up (bigger class) or out for reads (read replicas). Writes don't scale horizontally.
- Storage can grow but never shrink. To reduce allocated storage you dump and restore into a new instance.
- Some changes need a reboot or cause an outage: changing static parameters, some instance-class changes, toggling backups between 0 and a non-zero value, and major version upgrades.
- Quotas: by default 40 DB instances per Region per account, and only 10 license-included instances per SQL Server edition. Both are adjustable.
- A stopped instance restarts after 7 days so it doesn't miss maintenance. You still pay for storage and backups while it is stopped.
Engines, editions and licence models
| RDS for SQL Server | RDS for PostgreSQL | |
|---|---|---|
| Major versions (Nov 2026) | 2016, 2017, 2019, 2022, 2025 | Current community majors; check describe-db-engine-versions |
| Editions | Express, Web, Standard, Enterprise (Developer via BYOM, non-production) | One edition |
| Licence models | License Included (Enterprise, Standard, Web, Express); Bring Your Own Media (Enterprise, Standard, Developer) with Software Assurance and License Mobility | Open source, no licence fee |
| Free plan eligible | Express edition only, on db.t3.micro / db.t4g.micro | db.t3.micro / db.t4g.micro |
| Max storage | gp3 up to 16 TiB; io2 up to 64 TiB per volume; up to 256 TiB with additional storage volumes | 64 TiB (smaller on some burstable classes: db.t4g.micro 6 TiB) |
| HA | Multi-AZ (Always On AGs; mirroring on older versions; block-level replication for Web) | Multi-AZ instance (one standby) or Multi-AZ DB cluster (two readable standbys) |
| Read replicas | Enterprise only, up to 15 | Up to 15 per primary (default quota) |
| IAM database authentication | No | Yes |
| Connection pooling service | RDS Proxy (with limits) | RDS Proxy |
Storage: gp3 is the default
| Engine | gp3 size | Baseline | Extra IOPS/throughput |
|---|---|---|---|
| PostgreSQL (and MySQL, MariaDB) | 20–399 GiB | 3,000 IOPS / 125 MiB/s | Not available below 400 GiB |
| PostgreSQL | 400 GiB–64 TiB | 12,000 IOPS / 500 MiB/s (striped over 4 volumes) | Up to 64,000 IOPS / 4,000 MiB/s |
| SQL Server | 20 GiB–16 TiB | 3,000 IOPS / 125 MiB/s (no striping) | Provisionable at any size; the AWS storage page lists both a 3,000–80,000 IOPS range and a 16,000 IOPS gp3 maximum for SQL Server ⚠ VERIFY for your instance class |
io2 Block Express gives sub-millisecond latency and up to 256,000 IOPS for latency-sensitive production OLTP. Magnetic storage is deprecated. From 1 July 2026 you can no longer restore a snapshot to magnetic storage.
Storage autoscaling: set a maximum allocated storage, and RDS grows the volume when free space runs low. It never shrinks it. Always set a ceiling, so a runaway log table can't grow your bill without limit.
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| SQL Server instance on a VM | RDS DB instance | No RDP; endpoint + port only |
sp_configure, server properties | DB parameter group | Static parameters need a reboot; changes are versioned in IaC |
| SQL Server features (TDE, SSRS, MSDTC, native backup) | Option group | Options are added to a group that instances use |
| Maintenance plans, patch Tuesday | Maintenance window + auto minor version upgrade | 30-minute weekly window you choose; some actions are mandatory |
| Azure SQL MI service tiers (GP / BC) | Instance class + storage type (gp3 / io2) | You size compute and storage separately |
| Azure SQL MI "stop" (not available) / dev-test pause | Stop instance (up to 7 days) | Storage and backups still billed |
| Azure Hybrid Benefit | BYOM (RDS for SQL Server) / BYOL on EC2 | Needs Software Assurance + License Mobility |
| Azure Database for PostgreSQL Flexible Server | RDS for PostgreSQL | Very similar operating model |
| Major version upgrade in place | In-place major upgrade or Blue/Green deployment | Blue/Green gives a tested switchover with short downtime |
Architecture
Security-boundary view.
- Network: the DB subnet group contains only private subnets, and the security group allows 5432 only from the application's security group (not from CIDR ranges).
- Identity: the master user is for administration only. Applications use their own least-privilege users.
- Encryption: a customer managed KMS key covers storage, logs, backups, snapshots and replicas. TLS is enforced in the parameter group.
- Audit: CloudTrail records RDS API calls (who changed the instance). Database-level activity needs engine logs (
pgaudit, SQL Server Audit) exported to CloudWatch Logs.
Step-by-step implementation
You will create the smallest sensible PostgreSQL instance in your sandbox, look at its settings, run the posture check, and delete it within the hour.
Choose the settings deliberately
Write these down before you click or type anything:
| Setting | Lab value | Production value | Why |
|---|---|---|---|
| Engine / version | PostgreSQL 17 | Latest supported major you've tested | Long support window |
| Class | db.t4g.micro | db.m7g / db.r7g family sized by load ⚠ VERIFY current generations | Burstable classes throttle under sustained load |
| Storage | gp3 20 GiB, max 50 GiB | gp3 or io2, sized + autoscaling ceiling | |
| Encryption | KMS CMK | KMS CMK | Can't be added later |
| Public access | No | No | Never for a bank database |
| Multi-AZ | No | Yes | Lab cost |
| Backup retention | 1 day | 7–35 days + AWS Backup | PITR window |
| Deletion protection | Off | On | Easy lab teardown |
| Master password | Managed in Secrets Manager | Managed in Secrets Manager | No human ever types it |
| IAM DB auth | On | On | Short-lived tokens for apps |
Create the network pieces
You need a DB subnet group with private subnets in at least two AZs (even for a single-AZ instance), and a security group for the database. Reuse the VPC from Module 2. If you don't have it, use the default VPC's subnets for this lab only, and keep public access off.
Create a parameter group
Create a custom parameter group for the postgres17 family. Set rds.force_ssl = 1, so clients without TLS are refused (already the default on recent PostgreSQL versions ⚠ VERIFY for your version). Set log_min_duration_statement = 500, so queries slower than 500 ms are logged. Never edit the default parameter group. You can't anyway, and a custom group documents your intent.
Create the instance
Use the CLI, CDK or Terraform below. Creation takes around 5–15 minutes.
Inspect it
Look at the endpoint, the Configuration tab (encryption, key, parameter group), Maintenance & backups (windows, retention) and the Master Credentials ARN. Run the posture check from the Complete code section.
Delete it
Delete the instance without a final snapshot, delete the parameter group and subnet group, and schedule the KMS key for deletion (if you created one only for this lab). Commands are in Cost and cleanup.
AWS Console instructions
- Console → RDS → Parameter groups → Create parameter group: family
postgres17, type DB Parameter Group, namehl-m06-l3-pg17. Open it, choose Edit, setrds.force_sslto1andlog_min_duration_statementto500, then Save changes. - Console → RDS → Subnet groups → Create DB subnet group: name
hl-m06-l3, choose your VPC, then add two private subnets in different AZs. - Console → RDS → Databases → Create database → Standard create:
- Engine: PostgreSQL, version 17.x. Templates: Free tier (if shown) or Dev/Test.
- Settings: identifier
hl-m06-l3-pg, master usernameledger_admin. Credentials management: Managed in AWS Secrets Manager. - Instance configuration:
db.t4g.micro. Storage: gp3, 20 GiB, enable autoscaling with a maximum of 50 GiB. - Connectivity: your VPC, subnet group
hl-m06-l3, Public access: No, a new security grouphl-m06-l3-db. - Database authentication: Password and IAM database authentication.
- Additional configuration: initial database name
ledger; parameter grouphl-m06-l3-pg17; backup retention 1 day; Encryption enabled with your KMS key; deletion protection off; tagsProject=cea-lab,Lab=m06-l3,Environment=learn,Owner=<you>.
- Create database. When the status is Available, open the instance and review Connectivity & security, Configuration and Maintenance & backups.
AWS CLI
# Variables - replace the subnet and VPC IDs with your private subnets (two AZs)
PROFILE=learn-sandbox; REGION=us-east-1
TAGS='Key=Project,Value=cea-lab Key=Lab,Value=m06-l3 Key=Environment,Value=learn Key=Owner,Value=your-name'
# 1. A customer managed KMS key for storage, backups and the master secret
KEY_ARN=$(aws kms create-key --description "hl-m06-l3 database key" \
--tags TagKey=Project,TagValue=cea-lab TagKey=Lab,TagValue=m06-l3 \
--query KeyMetadata.Arn --output text --profile $PROFILE --region $REGION)
aws kms enable-key-rotation --key-id "$KEY_ARN" --profile $PROFILE --region $REGION
# 2. Subnet group (private subnets in two AZs)
aws rds create-db-subnet-group --db-subnet-group-name hl-m06-l3 \
--db-subnet-group-description "m06 lesson 3 private subnets" \
--subnet-ids subnet-0aaaaaaaaaaaaaaa1 subnet-0bbbbbbbbbbbbbbb2 \
--tags $TAGS --profile $PROFILE --region $REGION
# 3. Security group with NO inbound rules yet (add an SG-to-SG rule from your app later)
SG_ID=$(aws ec2 create-security-group --group-name hl-m06-l3-db \
--description "m06 l3 database" --vpc-id vpc-0123456789abcdef0 \
--query GroupId --output text --profile $PROFILE --region $REGION)
# 4. Parameter group that forces TLS and logs slow statements
aws rds create-db-parameter-group --db-parameter-group-name hl-m06-l3-pg17 \
--db-parameter-group-family postgres17 --description "m06 l3" --tags $TAGS \
--profile $PROFILE --region $REGION
aws rds modify-db-parameter-group --db-parameter-group-name hl-m06-l3-pg17 \
--parameters "ParameterName=rds.force_ssl,ParameterValue=1,ApplyMethod=pending-reboot" \
"ParameterName=log_min_duration_statement,ParameterValue=500,ApplyMethod=immediate" \
--profile $PROFILE --region $REGION
# 5. The instance: private, encrypted with the CMK, RDS-managed master password, IAM auth on
aws rds create-db-instance \
--db-instance-identifier hl-m06-l3-pg \
--engine postgres --engine-version 17 \
--db-instance-class db.t4g.micro \
--allocated-storage 20 --max-allocated-storage 50 --storage-type gp3 \
--storage-encrypted --kms-key-id "$KEY_ARN" \
--master-username ledger_admin --manage-master-user-password \
--master-user-secret-kms-key-id "$KEY_ARN" \
--db-name ledger \
--db-subnet-group-name hl-m06-l3 --vpc-security-group-ids "$SG_ID" \
--no-publicly-accessible \
--db-parameter-group-name hl-m06-l3-pg17 \
--enable-iam-database-authentication \
--backup-retention-period 1 --copy-tags-to-snapshot \
--preferred-maintenance-window sun:04:00-sun:04:30 \
--preferred-backup-window 03:00-03:30 \
--auto-minor-version-upgrade \
--no-deletion-protection \
--tags $TAGS --profile $PROFILE --region $REGION
# 6. Wait, then look at what you built
aws rds wait db-instance-available --db-instance-identifier hl-m06-l3-pg --profile $PROFILE --region $REGION
aws rds describe-db-instances --db-instance-identifier hl-m06-l3-pg \
--query "DBInstances[0].{Endpoint:Endpoint.Address,Encrypted:StorageEncrypted,Public:PubliclyAccessible,Secret:MasterUserSecret.SecretArn,Params:DBParameterGroups[0].DBParameterGroupName}" \
--profile $PROFILE --region $REGION
# 7. Upcoming maintenance actions for this instance (OS or engine patches)
aws rds describe-pending-maintenance-actions --profile $PROFILE --region $REGIONIf --engine-version 17 is rejected, list the available versions first with aws rds describe-db-engine-versions --engine postgres, and pass a full minor version.
Infrastructure as Code
The module project in labs/m06-banking-data-store/infra/terraform/main.tf contains the full, validated version (KMS key policy, app IAM role). Below are minimal equivalents for this lesson.
using Amazon.CDK;
using Amazon.CDK.AWS.EC2;
using Amazon.CDK.AWS.KMS;
using Amazon.CDK.AWS.RDS;
using Constructs;
public sealed class LedgerDbStack : Stack
{
public LedgerDbStack(Construct scope, string id, IStackProps? props = null) : base(scope, id, props)
{
var vpc = Vpc.FromLookup(this, "Vpc", new VpcLookupOptions { Tags = new Dictionary<string, string> { ["Name"] = "hl-m02-vpc" } });
var key = new Key(this, "DbKey", new KeyProps { EnableKeyRotation = true, Alias = "alias/hl-m06-l3" });
var parameters = new ParameterGroup(this, "Params", new ParameterGroupProps
{
Engine = DatabaseInstanceEngine.Postgres(new PostgresInstanceEngineProps { Version = PostgresEngineVersion.VER_17 }),
Parameters = new Dictionary<string, string>
{
["rds.force_ssl"] = "1",
["log_min_duration_statement"] = "500",
},
});
var db = new DatabaseInstance(this, "Ledger", new DatabaseInstanceProps
{
InstanceIdentifier = "hl-m06-l3-pg",
Engine = DatabaseInstanceEngine.Postgres(new PostgresInstanceEngineProps { Version = PostgresEngineVersion.VER_17 }),
InstanceType = InstanceType.Of(InstanceClass.BURSTABLE4_GRAVITON, InstanceSize.MICRO),
Vpc = vpc,
VpcSubnets = new SubnetSelection { SubnetType = SubnetType.PRIVATE_WITH_EGRESS },
PubliclyAccessible = false,
StorageEncrypted = true,
StorageEncryptionKey = key,
StorageType = StorageType.GP3,
AllocatedStorage = 20,
MaxAllocatedStorage = 50,
DatabaseName = "ledger",
Credentials = Credentials.FromGeneratedSecret("ledger_admin", new CredentialsBaseOptions { EncryptionKey = key }),
IamAuthentication = true,
ParameterGroup = parameters,
BackupRetention = Duration.Days(1),
PreferredMaintenanceWindow = "sun:04:00-sun:04:30",
AutoMinorVersionUpgrade = true,
MultiAz = false, // production: true
DeletionProtection = false, // production: true
RemovalPolicy = RemovalPolicy.DESTROY, // ⚠ lab only: production uses SNAPSHOT or RETAIN
});
Amazon.CDK.Tags.Of(this).Add("Project", "cea-lab");
Amazon.CDK.Tags.Of(this).Add("Lab", "m06-l3");
Amazon.CDK.Tags.Of(this).Add("Environment", "learn");
_ = new CfnOutput(this, "Endpoint", new CfnOutputProps { Value = db.DbInstanceEndpointAddress });
}
}Credentials.FromGeneratedSecret makes CDK create the secret through a CloudFormation secret resource. That is different from RDS-managed master passwords (--manage-master-user-password), which RDS rotates itself. Both keep the password out of your code ⚠ VERIFY whether your CDK version exposes RDS-managed master passwords directly.
Complete code
The RDS posture check lists every DB instance in a Region, checks it against a bank-style baseline, and prints pending maintenance. It is read-only (rds:DescribeDBInstances, rds:DescribePendingMaintenanceActions) and free to run. It returns exit code 1 on any failure, so it can run in a nightly pipeline.
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="AWSSDK.RDS" Version="4.1.0.2" />
<!-- Needed to use an IAM Identity Center (SSO) profile locally -->
<PackageReference Include="AWSSDK.SSO" Version="4.1.0.2" />
<PackageReference Include="AWSSDK.SSOOIDC" Version="4.1.0.2" />
</ItemGroup>
</Project>using Amazon;
using Amazon.RDS;
using Amazon.RDS.Model;
// RDS posture check: lists every DB instance in the Region and flags settings a bank would reject.
// Read-only (rds:DescribeDBInstances, rds:DescribePendingMaintenanceActions). Free to run.
// Usage: $env:AWS_PROFILE="learn-sandbox"; dotnet run -- [region]
var region = RegionEndpoint.GetBySystemName(args.FirstOrDefault() ?? "us-east-1");
using var rds = new AmazonRDSClient(region); // default credential chain (SSO profile, role, ...)
var pending = new Dictionary<string, List<string>>(StringComparer.Ordinal);
await foreach (var action in rds.Paginators.DescribePendingMaintenanceActions(new DescribePendingMaintenanceActionsRequest()).PendingMaintenanceActions)
{
var list = pending.TryGetValue(action.ResourceIdentifier, out var l) ? l : pending[action.ResourceIdentifier] = [];
foreach (var detail in action.PendingMaintenanceActionDetails ?? [])
{
list.Add($"{detail.Action} (auto-applied after {detail.AutoAppliedAfterDate?.ToString("yyyy-MM-dd") ?? "n/a"}): {detail.Description}");
}
}
var failures = 0;
await foreach (var db in rds.Paginators.DescribeDBInstances(new DescribeDBInstancesRequest()).DBInstances)
{
Console.WriteLine($"{db.DBInstanceIdentifier} {db.Engine} {db.EngineVersion} {db.DBInstanceClass} {db.DBInstanceStatus}");
var checks = new (string Name, bool Ok, string Advice)[]
{
("Storage encrypted", db.StorageEncrypted == true, "Encryption can only be set at creation: snapshot, copy with a KMS key, restore."),
("Customer managed key", db.KmsKeyId is not null && !db.KmsKeyId.Contains("alias/aws/rds", StringComparison.Ordinal), "Prefer a CMK so you control the key policy and can share snapshots."),
("Not publicly accessible", db.PubliclyAccessible != true, "Set PubliclyAccessible=false and keep the instance in private subnets."),
("Backups retained >= 7 days", (db.BackupRetentionPeriod ?? 0) >= 7, "Raise BackupRetentionPeriod (0 disables PITR entirely)."),
("Deletion protection", db.DeletionProtection == true, "Turn on DeletionProtection for anything that is not a throwaway lab."),
("Multi-AZ", db.MultiAZ == true, "Production databases need a standby in a second AZ."),
("Auto minor version upgrade", db.AutoMinorVersionUpgrade == true, "Let RDS apply minor (security) versions in the maintenance window."),
("IAM DB auth (non-SQL Server)", db.Engine.StartsWith("sqlserver", StringComparison.Ordinal) || db.IAMDatabaseAuthenticationEnabled == true, "Enable IAM database authentication for PostgreSQL/MySQL apps."),
("Master password managed", db.MasterUserSecret is not null, "Use --manage-master-user-password so RDS stores and rotates it in Secrets Manager."),
};
foreach (var (name, ok, advice) in checks)
{
Console.WriteLine($" [{(ok ? "PASS" : "FAIL")}] {name}{(ok ? "" : " -> " + advice)}");
if (!ok)
{
failures++;
}
}
if (pending.TryGetValue(db.DBInstanceArn, out var actions))
{
foreach (var a in actions)
{
Console.WriteLine($" [INFO] Pending maintenance: {a}");
}
}
Console.WriteLine($" Maintenance window: {db.PreferredMaintenanceWindow} Backup window: {db.PreferredBackupWindow} CA: {db.CACertificateIdentifier}");
Console.WriteLine();
}
Console.WriteLine(failures == 0 ? "All checks passed." : $"{failures} check(s) failed.");
return failures == 0 ? 0 : 1;Run it against the lab instance:
aws sso login --profile learn-sandbox
$env:AWS_PROFILE = "learn-sandbox"
dotnet run --project RdsPostureCheck -- us-east-1Expected output for the lab instance (abridged). The FAILs are the deliberate lab shortcuts:
hl-m06-l3-pg postgres 17.x db.t4g.micro available
[PASS] Storage encrypted
[PASS] Customer managed key
[PASS] Not publicly accessible
[FAIL] Backups retained >= 7 days -> Raise BackupRetentionPeriod (0 disables PITR entirely).
[FAIL] Deletion protection -> Turn on DeletionProtection for anything that is not a throwaway lab.
[FAIL] Multi-AZ -> Production databases need a standby in a second AZ.
[PASS] Auto minor version upgrade
[PASS] IAM DB auth (non-SQL Server)
[PASS] Master password managed
Maintenance window: sun:04:00-sun:04:30 Backup window: 03:00-03:30 CA: rds-ca-rsa2048-g1
3 check(s) failed.Code explained line by line
RegionEndpoint.GetBySystemName(...): the Region comes from the command line, defaulting tous-east-1. Posture checks should run per Region, because RDS resources are Regional.new AmazonRDSClient(region): uses the SDK's default credential chain. Locally that is your SSO profile fromAWS_PROFILE; in a pipeline it is the job's IAM role. No keys appear in code.rds.Paginators.DescribePendingMaintenanceActions(...): SDK v4 paginators returnIAsyncEnumerableand followMarkertokens for you. You never miss instance 101.pending[...]: maps instance ARN to pending actions (for examplesystem-updateordb-upgrade) with the date after which RDS applies them automatically.detail.AutoAppliedAfterDate?.ToString(...): in SDK v4, value-type properties are nullable when the service didn't return them. Always handle null.await foreach (var db in ... DescribeDBInstances(...).DBInstances): streams every instance across pages.db.StorageEncrypted == true: comparing abool?totruetreats "unknown" as failure, which is the safe default for a security check.- The
alias/aws/rdscheck:KmsKeyIdis a key ARN, so in practice you would resolve it with KMSDescribeKeyand checkKeyManager == CUSTOMER. The string check is a simplification for brevity ⚠ see the challenge exercise. db.PubliclyAccessible != true: public accessibility gives the instance a public DNS resolution. Combined with a permissive security group, that exposes the database to the internet.(db.BackupRetentionPeriod ?? 0) >= 7: retention 0 means no automated backups and no PITR. The bank baseline here is 7 days, which is a policy choice, not an AWS rule.- The IAM auth check is skipped for SQL Server because RDS for SQL Server doesn't support IAM database authentication.
db.MasterUserSecret is not null: present only when RDS manages the master password in Secrets Manager.- The
returnexit code lets CI or a scheduled job alert on failure.
Security considerations
- Encryption is a creation-time decision. You can't encrypt an existing unencrypted instance in place, and you can't change the KMS key later. The route is snapshot, encrypted copy, restore (or a Blue/Green deployment). Decide on a customer managed key up front, so you control the key policy and can share snapshots with a backup account. Snapshots encrypted with the AWS managed key can't be shared.
- Private by default.
PubliclyAccessible=false, private subnets only, and security group rules that reference the application's security group, not IP ranges. - Force TLS with
rds.force_ssl=1(PostgreSQL) orrds.force_sslon SQL Server, and have clients verify the server certificate (see Encryption and access). - Never use the master user from applications. Create a least-privilege application user. Keep the master password RDS-managed in Secrets Manager, with access limited to a break-glass role.
- Parameter groups are security configuration. Logging (
log_connections,pgaudit) and TLS live there. Keep them in IaC and review changes. - CloudTrail logs control-plane calls (
ModifyDBInstance,DeleteDBInstance). Alert on deletions and onPubliclyAccessiblechanges (for example with an EventBridge rule).
⚠ Learning shortcut — not for production
The lab instance is single-AZ, keeps backups for 1 day, has deletion protection off and is deleted without a final snapshot. All four are wrong for anything holding real data. Production: Multi-AZ, 7–35 days of automated backups plus AWS Backup copies to a separate account, deletion protection on, a final snapshot on delete, and a change ticket for every modification.
Banking use case
Harbourline's platform team publishes an "approved RDS baseline" as a Terraform module. Every team that needs PostgreSQL calls the module and cannot opt out of:
- a CMK per environment;
- private subnets only;
rds.force_ssl=1;- RDS-managed master passwords;
- IAM DB auth enabled;
- backup retention 14 days (35 for ledger databases);
- deletion protection;
- a maintenance window on Sunday 04:00–04:30 UK time, outside the batch window.
The posture check from this lesson runs nightly in every account, through a read-only cross-account role. Its output goes to the risk dashboard. A failed check opens a ticket for the owning team, found through the instance's Owner tag.
For SQL Server, the module uses Standard edition License Included on db.m6i classes with Multi-AZ (Always On). It adds an option group with SQLSERVER_BACKUP_RESTORE for migrations and TDE only where the data classification requires it.
Common mistakes
- Accepting console defaults. Defaults vary by template and change over time. Encode your baseline in IaC.
- Editing the default parameter group. You can't; create a custom one and attach it. Attaching a new group can require a reboot.
- Burstable classes for steady production load.
db.t*classes run on CPU credits. When credits run out, performance drops sharply. MonitorCPUCreditBalance, or usem/rclasses. - No storage autoscaling ceiling. Without
MaxAllocatedStorage, the instance can run out of space. With an unbounded ceiling, a bug can cost a lot. - Choosing a maintenance window during the batch run. Patching at 02:00 while end-of-day processing runs causes failed jobs.
- Forgetting Extended Support. If you stay on a PostgreSQL major version past its end of standard support, RDS enrols the instance in paid RDS Extended Support automatically. Plan major upgrades ⚠ VERIFY current Extended Support pricing.
- Stopping an instance to save money and forgetting it restarts after 7 days.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
InvalidParameterCombination on create | Instance class not offered for that engine version, or storage too small for the type | aws rds describe-orderable-db-instance-options |
DBSubnetGroupDoesNotCoverEnoughAZs | Subnet group with one AZ | Add a subnet in a second AZ |
| Connection timeout from your laptop | Private instance (by design), or SG lacks a rule | Connect from inside the VPC; add an SG-to-SG rule |
no pg_hba.conf entry ... no encryption | rds.force_ssl=1 and a non-TLS client | Use sslmode=verify-full with the RDS CA bundle |
| Parameter change "pending-reboot" for days | Static parameter | Reboot in a window, or schedule --apply-immediately with care |
| Performance falls off a cliff at the same time daily | Burstable class out of CPU credits | Larger or non-burstable class |
StorageQuotaExceeded / InstanceQuotaExceeded | Account quotas | Service Quotas console |
Cost and cleanup
- Free plan accounts:
db.t4g.microordb.t3.microPostgreSQL is covered by the Free plan for up to 6 months. Check your plan in Billing → Free Tier. - Otherwise (paid plan): a
db.t4g.microPostgreSQL instance plus 20 GiB of gp3 costs a few US cents per hour ⚠ VERIFY on the RDS pricing page. The KMS key costs USD 1 per month (pro-rated), and the RDS-managed secret USD 0.40 per month (pro-rated). If you finish within an hour, expect well under USD 0.10. - Backup storage up to the size of your provisioned storage is free per Region.
Teardown (same day):
PROFILE=learn-sandbox; REGION=us-east-1
aws rds delete-db-instance --db-instance-identifier hl-m06-l3-pg \
--skip-final-snapshot --delete-automated-backups --profile $PROFILE --region $REGION
aws rds wait db-instance-deleted --db-instance-identifier hl-m06-l3-pg --profile $PROFILE --region $REGION
aws rds delete-db-parameter-group --db-parameter-group-name hl-m06-l3-pg17 --profile $PROFILE --region $REGION
aws rds delete-db-subnet-group --db-subnet-group-name hl-m06-l3 --profile $PROFILE --region $REGION
aws ec2 delete-security-group --group-id "$SG_ID" --profile $PROFILE --region $REGION
aws kms schedule-key-deletion --key-id "$KEY_ARN" --pending-window-in-days 7 --profile $PROFILE --region $REGION
# Nothing tagged for this lesson should remain (the KMS key shows PendingDeletion for 7 days)
aws resourcegroupstaggingapi get-resources --tag-filters Key=Lab,Values=m06-l3 \
--query "ResourceTagMappingList[].ResourceARN" --profile $PROFILE --region $REGION
aws rds describe-db-snapshots --snapshot-type manual --query "DBSnapshots[?starts_with(DBSnapshotIdentifier,'hl-m06')]" \
--profile $PROFILE --region $REGIONFree alternative: run PostgreSQL 17 locally (docker compose up -d in labs/m06-banking-data-store) and complete everything except the AWS-specific steps. The posture check still compiles and runs, and reports zero instances.
Exercises
- 🟢 Guided: Create the lab instance with the CLI, run the posture check, then fix Backups retained by running
aws rds modify-db-instance --backup-retention-period 7 --apply-immediatelyand re-run the check. Delete everything. - 🟡 Challenge: Improve the posture check so the "Customer managed key" rule calls KMS
DescribeKeyand checksKeyManager == KeyManagerType.CUSTOMER. Add a rule that the attached parameter group hasrds.force_ssl = 1, usingDescribeDBParameters. - 🔴 Live problem: Monday morning ticket: "Treasury DB (RDS PostgreSQL 13) shows 'Extended Support' charges on the bill. Finance wants them gone this month. The app team says they can't test a major upgrade until next quarter." Propose a plan with options, risks and a timeline.
Assignment
Publish a Terraform module (or CDK construct) rds-postgres-baseline in your portfolio repository. It should enforce encryption with a CMK, private subnets, a custom parameter group with TLS forced, RDS-managed master password, IAM auth, backup retention input (minimum 7), deletion protection input (default true), and the mandatory tags. Include a README with an example and terraform validate output.
Solutions
After modify-db-instance --backup-retention-period 7 --apply-immediately, the instance briefly shows modifying, then available. The posture check now reports [PASS] Backups retained >= 7 days. Changing retention between two non-zero values doesn't cause an outage. Changing from 0 to non-zero, or the reverse, does, which is one more reason never to create production instances with retention 0.
Why it matters: retention defines your point-in-time recovery window. With 1 day, an error discovered on Monday about Friday's data can't be undone from automated backups.
Add the AWSSDK.KeyManagementService package, then:
using Amazon.KeyManagementService;
using Amazon.KeyManagementService.Model;
using var kms = new AmazonKeyManagementServiceClient(region);
async Task<bool> IsCustomerManagedAsync(string? keyId)
{
if (keyId is null) return false;
var key = await kms.DescribeKeyAsync(new DescribeKeyRequest { KeyId = keyId });
return key.KeyMetadata.KeyManager == KeyManagerType.CUSTOMER;
}
async Task<bool> ForcesSslAsync(string parameterGroup)
{
await foreach (var p in rds.Paginators.DescribeDBParameters(new DescribeDBParametersRequest { DBParameterGroupName = parameterGroup }).Parameters)
{
if (p.ParameterName == "rds.force_ssl") return p.ParameterValue == "1";
}
return false;
}Call them inside the loop with db.KmsKeyId and db.DBParameterGroups[0].DBParameterGroupName. The tool's IAM policy now also needs kms:DescribeKey and rds:DescribeDBParameters. Why: string matching on ARNs is fragile. Asking KMS who manages the key is authoritative.
Facts first: confirm the engine version and the Extended Support charge per vCPU-hour on the bill, and check the instance class (charges scale with vCPUs).
Options:
- Major upgrade with a Blue/Green deployment (supported for RDS for PostgreSQL ⚠ VERIFY for your version path). Create the green environment on the new major version, run the app team's regression suite against it this sprint, then switch over in a maintenance window with typically about a minute of downtime. Old blue stays available for rollback.
- In-place major upgrade after taking a manual snapshot. Simpler, but longer downtime and harder rollback.
- Accept the charges until next quarter with a signed risk acceptance: cost known, security patches still provided under Extended Support.
Recommend option 1. Make the regression testing the critical path, schedule switchover outside batch windows, and keep option 3 as a documented fallback. Note that a two-major-version jump may need intermediate steps.
Knowledge check
Answer: RDS manages hardware, OS, engine installation and patching (in your maintenance window), automated backups and PITR, monitoring infrastructure, and failover with Multi-AZ. You own engine version choice, sizing, network placement, security groups, users and permissions, encryption key choice, parameters, schema, queries and data.
Answer: In a DB parameter group (custom, never the default). Static parameters apply after a reboot; dynamic ones can apply immediately. SQL Server features such as TDE or native backup/restore are enabled through option groups.
Answer: 3,000 IOPS and 125 MiB/s. At 400 GiB and above, RDS stripes the volume over four volumes, and the baseline rises to 12,000 IOPS and 500 MiB/s. You can then provision more IOPS and throughput.
Answer: Its key policy is controlled by AWS and can't grant other accounts access. With a customer managed key, you can add the other account to the key policy and share the snapshot, for example with a backup or DR account.
Answer: RDS starts it automatically, so it doesn't fall behind on maintenance, and instance-hour charges resume. Storage and backups are billed throughout.
Interview questions
- "Walk me through creating a production RDS for PostgreSQL instance." Custom parameter group (TLS forced, logging), private subnet group across AZs, SG-to-SG rule, CMK encryption, RDS-managed master secret, IAM DB auth, Multi-AZ, suitable class and gp3/io2 with an autoscaling ceiling, retention 7–35 days plus AWS Backup, deletion protection, maintenance window outside batch, tags, and all of it in IaC with a posture check afterwards.
- "How do you upgrade a major version with minimal downtime?" Blue/Green deployment: RDS creates a synchronised green copy on the new version. You test against it, then switch over, typically in about a minute, with automatic endpoint renaming. Snapshot first, and test the application against the new version beforehand.
- "What's the difference between a parameter group and an option group?" Parameter groups hold engine configuration values. Option groups add optional features (mainly SQL Server and Oracle) such as TDE, native backup/restore or SSRS. PostgreSQL uses extensions instead of option groups.
- "Your RDS instance runs out of storage at 3 a.m. How do you prevent that?" Enable storage autoscaling with a sensible maximum, alarm on
FreeStorageSpace, and investigate growth (log tables, bloat, missing retention jobs).
Certification practice
A company has an unencrypted Amazon RDS for PostgreSQL DB instance in production. A new policy requires all databases to be encrypted at rest with a customer managed AWS KMS key. What should a solutions architect do?
A. Modify the DB instance and enable encryption with the customer managed key B. Create a snapshot, copy the snapshot with encryption using the customer managed key, and restore a new DB instance from the encrypted copy C. Enable encryption on the DB subnet group D. Create an encrypted read replica of the unencrypted instance and promote it
Answer: B. RDS encryption can only be set at creation, so the route is snapshot, encrypted copy, restore (or a Blue/Green deployment). A isn't possible. C isn't a feature. D fails because you can't have an encrypted read replica of an unencrypted instance.
A development team uses an Amazon RDS for PostgreSQL instance only during UK business hours on weekdays. The team wants to minimise cost with minimal operational effort. Which solution is MOST cost-effective?
A. Stop the instance every evening and start it every morning with Amazon EventBridge Scheduler B. Delete the instance every evening and restore it from a snapshot each morning C. Purchase a 3-year reserved instance D. Move the database to a larger instance class to finish work faster
Answer: A. Stopping avoids instance-hour charges overnight and at weekends. Weekend stops are under 7 days, so the auto-start doesn't interfere. B adds restore time and risk. C commits to paying 24×7 for something used about 25% of the time. D increases cost.
A developer is deploying an application that connects to Amazon RDS for PostgreSQL. Security requires that no database password is stored in application configuration and that the master password is rotated automatically. Which combination should the developer use? (Choose two.)
A. Create the instance with RDS-managed master user password in AWS Secrets Manager
B. Store the master password in an encrypted environment variable
C. Enable IAM database authentication and connect as an application user with the rds_iam role
D. Use the master user from the application with SSL
E. Hard-code a password and rotate it manually each quarter
Answer: A and C. RDS manages and rotates the master secret, so humans and apps never type it. The application uses short-lived IAM tokens as its own least-privilege user. B and E keep static secrets in configuration. D uses the master user from an application, which violates least privilege.
Completion checklist
- I can explain the RDS shared-responsibility split and the main sharp edges
- I created (or designed, on the free path) an encrypted, private PostgreSQL instance with a custom parameter group
- I ran the posture check and understood each FAIL
- I deleted the instance, parameter group, subnet group and security group, and scheduled the key for deletion
- I confirmed with the tagging API that nothing tagged
Lab=m06-l3remains
Key takeaways
- RDS turns DBA chores into settings. You still own network, identity, keys, parameters, schema and data.
- Decide encryption (with a CMK), network placement and licence model at creation. They are hard or impossible to change later.
- Parameter groups replace
sp_configureandpostgresql.conf; option groups add SQL Server features. Keep both in IaC. - gp3 has a 3,000 IOPS / 125 MiB/s baseline (PostgreSQL rises to 12,000 / 500 at 400 GiB). Set an autoscaling ceiling.
- Encode the bank's baseline once (module) and check it continuously (posture check).
Further reading
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
SQL Server on AWS: RDS, EC2, Aurora PostgreSQL and Babelfish compared
Compare features, high availability, backups, tooling and licensing for SQL Server workloads across RDS for SQL Server, RDS Custom, SQL Server on EC2, Aurora PostgreSQL and Babelfish, and scan your T-SQL for blockers before you choose.
Amazon Aurora: storage, endpoints, Serverless v2 and Global Database
Understand how Aurora separates compute from a six-copy, three-AZ storage volume, use writer and reader endpoints from .NET, size Aurora Serverless v2, measure a real failover, and design cross-Region DR with Aurora Global Database.