Encryption and database access
Encrypt an RDS database at rest with a customer managed KMS key and in transit with verified TLS, then choose between IAM database authentication and rotated Secrets Manager credentials, and prove from C# what the database says about your session.
Assumes: .NET 10 LTS · Npgsql 10.0.3 · AWS SDK for .NET v4 (AWSSDK.RDS 4.1.0.2, AWSSDK.SecretsManager 4.1.1.2) · Amazon.CDK.Lib 2.277.0 · 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 RDS encryption at rest covers and what it cannot do, and say why a customer managed key (CMK) is the right default for a bank's database.
- Encrypt an existing unencrypted instance (snapshot, encrypted copy, restore) and describe what happens when the key is disabled.
- Enforce and verify TLS in transit from a .NET client: CA bundles,
rds.force_ssl,SSL Mode=VerifyFull. - Choose between IAM database authentication and Secrets Manager credentials with rotation, using the engine limits, and set up each.
- Write a least-privilege database role and the matching
rds-db:connectIAM statement. - Run a C# access probe that reports TLS version, cipher and role membership as the database sees them.
Prerequisites
- Multi-AZ, replicas, backups and PITR: snapshots and restores inherit encryption settings
- Amazon RDS for SQL Server and PostgreSQL
- KMS and envelope encryption and Secrets Manager and Parameter Store from Module 5
- Tools: AWS CLI v2 (
learn-sandbox), .NET 10 SDK. The lab instance from the module project is optional.
Why this matters in banking
A Harbourline engineer needs to reproduce a customer complaint, so she takes a manual snapshot of the production ledger and shares it with the staging account. It fails: the snapshot is encrypted with the AWS managed aws/rds key, and snapshots under that key cannot be shared. The workaround someone suggests, "just copy it unencrypted", is exactly what an auditor would flag. The real fix, a customer managed key whose policy names the staging account, should have been chosen before the database was created, because the key of an existing instance cannot be changed in place.
In the same week a different team finds a connection string with the master password in a build log. The password has not changed in three years, and nobody knows who else has it. And a penetration tester connects to the database from a laptop inside the VPN without TLS verification, because the application's connection string says SSL Mode=Require and never checks who it is talking to.
Three controls fix these three stories: keys you own, credentials that rotate or do not exist, and TLS that actually verifies the server. This lesson builds all three and shows how to prove each one from code.
The idea in plain language
Think of four separate questions, each with its own control:
| Question | Control | AWS pieces |
|---|---|---|
| Can someone who steals the disk or a backup read the data? | Encryption at rest | RDS storage encryption, AWS KMS key |
| Can someone on the network read or alter traffic, or impersonate the server? | Encryption in transit with server verification | TLS, RDS CA certificates, rds.force_ssl |
| Who is connecting, and how do they prove it? | Authentication | IAM database authentication, Secrets Manager, RDS-managed master password |
| What may they do once connected? | Authorisation inside the database | Database roles and grants (GRANT, row-level security) |
A fifth question, "who did what, and can we prove it?", is audit: CloudTrail for the API calls, KMS and Secrets Manager logs for key and secret use, and database engine logs (pgaudit, SQL Server Audit) for statements.
Analogy: The branch building has a vault. Encryption at rest is the vault door: if burglars carry the whole safe away, it still will not open. TLS is the armoured van between branches, and verifying the server is checking the driver's ID instead of waving the van through. Authentication is the keycard you swipe, and IAM tokens are keycards that self-destruct after 15 minutes. Authorisation is which drawers your card opens once you are inside.
What problem the AWS service solves
Encryption at rest
RDS encrypts the storage, logs, automated backups, read replicas and snapshots with AES-256, using a key in AWS KMS. Applications do not change. Per the RDS documentation (checked 2026-11-18):
- Only at creation. You can only encrypt an instance when you create it. You cannot switch it on later, and you cannot turn it off.
- The key is fixed. Once created you cannot change the KMS key of the instance in place. To change it, snapshot, copy the snapshot while choosing a different key, and restore. A blue/green deployment can also create an encrypted green environment from an unencrypted blue one, or one with a different key.
- Replicas of an encrypted instance must be encrypted. In the same Region they use the same key. A cross-Region replica needs a key in the destination Region, because KMS keys are Regional.
- Snapshots of an encrypted instance use the same key. You can re-encrypt on copy with a different key. A snapshot encrypted with the AWS managed key of an account cannot be shared with another account. A snapshot under a CMK can, if the key policy grants the other account.
- Disabling the key stops the database. If RDS loses access to the key (it is disabled, or the grant is lost), the instance moves into
inaccessible-encryption-credentials-recoverableabout two hours after detection (for instances with backups enabled) and stays there for seven days. Re-enable the key and start the instance to recover. For instances that cannot be stopped (read replicas, instances that have replicas), the state is terminal and you must restore from a snapshot or backup. - Grants. When you create the instance, RDS checks that the caller can use the key and creates a grant it keeps for the instance's lifetime. Revoking the caller's key access afterwards does not break the running database, but disabling the key does.
What encryption at rest does not do. It does not hide data from anyone who can log in: the database decrypts transparently for every authorised session. It does not satisfy "right to erasure" by itself (deleting a key destroys backups too, which is a blunt tool and a legal question, not a technical shortcut ⚠ ask your data-protection team). And TDE on RDS for SQL Server is a separate feature with its own keys; it can be layered with storage encryption, at some performance cost.
Why a CMK, not aws/rds: you control the key policy (who may administer and who may use it), you can share encrypted snapshots with a backup or DR account, you can alarm on DisableKey or ScheduleKeyDeletion in CloudTrail, and you can rotate and audit it. The price is a monthly key charge and the responsibility never to lose it.
Encryption in transit
All RDS engines support TLS. Two separate settings matter:
- The server requires TLS. PostgreSQL: parameter
rds.force_ssl=1(already the default on recent major versions ⚠ VERIFY for yours; set it explicitly so the intent is documented). SQL Server:rds.force_sslin the parameter group. - The client verifies the server. TLS without verification encrypts the connection but does not prove it is your database. Verification needs the RDS CA bundle: the
global-bundle.pemfile fromhttps://truststore.pki.rds.amazonaws.com/global/global-bundle.pem(or the Regional file such asus-east-1-bundle.pem). The documentation says to register only the root CA certificates in your trust store, not the intermediates, because RDS rotates the server certificate automatically.
The CAs offered are rds-ca-rsa2048-g1 (the default), rds-ca-rsa4096-g1 and rds-ca-ecc384-g1. Server certificates are valid for 1 year where the engine version supports rotation without restart, 3 years otherwise, and are rotated automatically. Pick the CA when you create or modify the instance, and use describe-certificates for validity dates.
RDS Proxy is different: it presents certificates from AWS Certificate Manager, so clients of a proxy do not need the RDS bundle (see the pooling lesson).
Authentication
| Method | Engines | Password in your app? | Rotation | Main limits |
|---|---|---|---|---|
| Static password in config | all | Yes | Manual, which means never | Do not use this for anything beyond a throwaway local container |
RDS-managed master password (--manage-master-user-password) | all | No: you read it from Secrets Manager | Automatic, managed by RDS | For the admin user only. Cannot create read replicas with it on most engines (SQL Server and Db2 excepted), as noted in the backups lesson |
| Secrets Manager secret for an application user | all | No: fetched at connect time | Scheduled by a rotation function (single-user or alternating-users) | Needs a rotation Lambda in the VPC with access to the database and to Secrets Manager. $0.40 per secret per month plus API calls ⚠ VERIFY current price |
| IAM database authentication | PostgreSQL, MySQL, MariaDB (not SQL Server) | No password exists | Token valid 15 minutes, generated per connect | Needs 300 to 1,000 MiB extra database memory; not logged by CloudTrail; some IAM condition keys (such as aws:SourceIp) are unsupported |
| Kerberos / Active Directory | SQL Server, PostgreSQL, MySQL, Oracle ⚠ VERIFY per engine | No | Directory-managed | Needs AWS Managed Microsoft AD or a trust to your AD |
Notes on IAM authentication, all from the RDS documentation (checked 2026-11-18):
- A token is a SigV4-signed string, about 1 KB or more. It is the password in the connection string. If a driver truncates it, authentication fails.
- It is used only to establish the connection. An open session continues after the token expires. So pooled connections keep working, and only new physical connections need a fresh token. That is why the lab's data source refreshes on a timer.
- For PostgreSQL, once
rds_iamis granted to a user (even the master user), IAM authentication takes precedence, and the user can no longer log in with a password. - It needs the instance to have the feature enabled (
IAMDatabaseAuthenticationEnabled) and the connection to use TLS. - The IAM policy resource is
arn:aws:rds-db:REGION:ACCOUNT:dbuser:RESOURCE-ID/DB-USER, whereRESOURCE-IDis the instance's DbiResourceId (db-…), not its name, or the cluster resource id for Aurora. The database user name is case sensitive. - Calls to generate a token are not logged by CloudTrail, and CloudWatch does not log IAM authentication. Your audit of "who connected" therefore comes from the database's own connection logs (
log_connections) rather than IAM events.
Choosing between IAM tokens and a rotated secret:
| Use IAM authentication when | Use a Secrets Manager secret when |
|---|---|
| PostgreSQL/MySQL/MariaDB, workload runs with an IAM role (ECS task, Lambda, EC2) | SQL Server, or a third-party tool that can only send a password |
| You want no secret to rotate or leak | You need the RDS Proxy standard mode (proxy-to-database uses secrets unless you use end-to-end IAM ⚠ VERIFY engine support) |
| New connections are infrequent (long-lived pools) | You create very many new connections per second: token generation is cheap but the database's authentication rate matters ⚠ VERIFY |
The instance has memory to spare (a 1 GiB db.t4g.micro is tight with IAM auth enabled) | The database has little spare memory |
Many banks use both: IAM authentication for application logins, and an RDS-managed master secret for the admin identity used only by migrations and break-glass access.
Authorisation inside the database
Authentication gets you in. Grants decide what you can do. The module project's scripts/create-app-user.sql is the model: a ledger_app login with rds_iam, SELECT on three tables, INSERT on transactions, UPDATE on three balance-related columns, and no DELETE, no DDL. A ledger is append-only; corrections are new reversing entries. The migration identity is a different user with DDL rights. PostgreSQL's row-level security can add per-row rules (for example, a support role sees only accounts in its region) and is the closest equivalent to the SQL Server feature of the same name.
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Transparent Data Encryption with a key in Azure Key Vault | RDS storage encryption with a KMS CMK | Chosen at creation; cannot be switched on later without a snapshot copy. TDE also exists on RDS SQL Server, separately |
| Azure SQL "Encrypt=True; TrustServerCertificate=False" | rds.force_ssl + CA bundle + client verification | You download and trust the RDS CA yourself; Azure's certificate chains to a public root |
| Managed identity / Microsoft Entra authentication to Azure SQL | IAM database authentication (PostgreSQL, MySQL, MariaDB) | A 15-minute token generated per connect. Not available for RDS SQL Server |
| Key Vault secret with rotation policy | Secrets Manager secret with a rotation Lambda | Rotation is a Lambda you deploy in the VPC (or a hosted one via CDK/CloudFormation), not a vault setting |
| Azure Private Link to SQL | Private subnet + security group + (optional) interface endpoints for Secrets Manager and KMS | The database itself has no public endpoint by default if you set PubliclyAccessible=false |
| Azure SQL auditing | pgaudit / SQL Server Audit to CloudWatch Logs, plus Database Activity Streams where supported ⚠ VERIFY engine | CloudTrail audits the control plane, not statements |
| SQL logins, contained users, Entra users | PostgreSQL roles, GRANT rds_iam | Same least-privilege thinking, different syntax |
Architecture
Security-boundary view: the trust boundary is the security group between tiers. Inside the database boundary, the role ledger_app is the only identity the application uses. Outside it, IAM decides who may assume that identity (rds-db:connect scoped to one resource id and one user) and KMS decides who may decrypt the storage (the key policy). Neither CloudTrail nor the database log lets you reconstruct a SQL statement from an IAM event, so enable log_connections, log_disconnections and pgaudit in the parameter group for the audit trail.
Step-by-step implementation
Create the key first
Create the CMK with automatic rotation enabled. Keep administration (create, disable, schedule deletion) separate from use (encrypt, decrypt through RDS). The module project's main.tf uses a policy that lets the account administer the key and lets only RDS and Secrets Manager, in this account, use it via kms:ViaService.
Create the instance encrypted, private, TLS-forced
Set StorageEncrypted and KmsKeyId, PubliclyAccessible=false, a parameter group with rds.force_ssl=1, IAMDatabaseAuthenticationEnabled=true, and a managed master password. Choose the CA (rds-ca-rsa2048-g1 is the default). The module project does all this.
Create the application database user
Connect as the master user from inside the VPC and run scripts/create-app-user.sql: CREATE ROLE ledger_app WITH LOGIN; GRANT rds_iam TO ledger_app; and the narrow grants. Never use the master user from the application.
Allow the application role to connect
Add an IAM statement with rds-db:connect on dbuser:<DbiResourceId>/ledger_app to the task role. Without it the token is generated but rejected with PAM authentication failed.
Verify from the client
Download the CA bundle, then run the access probe below with SSL Mode=VerifyFull. It must report tls=True, a protocol of TLSv1.2 or TLSv1.3, no superuser role, and no DELETE on the ledger.
Add rotation for any password-based identity
For users that need a password (third-party tools, SQL Server logins), store it in Secrets Manager with a CMK, attach a rotation schedule using the alternating-users strategy, and make the application re-read the AWSCURRENT version when a new connection is needed.
AWS Console instructions
Encrypted instance: Console → RDS → Create database → Standard create → PostgreSQL. Under Credentials management choose Managed in AWS Secrets Manager and pick your KMS key. Under Additional configuration → Encryption keep Enable encryption ticked and choose your CMK (not aws/rds). Under Database authentication tick Password and IAM database authentication. Under Connectivity choose Public access: No. Under Additional configuration choose your parameter group.
Encrypting an existing instance: Console → RDS → Snapshots → Take snapshot, then select it, Actions → Copy snapshot, tick Enable encryption, pick your CMK, then Actions → Restore snapshot on the encrypted copy. Point the application at the new endpoint, then delete the old instance and the unencrypted snapshots.
IAM database authentication: RDS → Databases → your instance → Modify → Database authentication, tick IAM, and apply (this applies without a restart for most engines ⚠ VERIFY).
Certificate authority: RDS → Databases → your instance → Connectivity & security: the Security section shows the certificate authority and expiry.
Secrets: Console → Secrets Manager → your secret → Rotation → Edit rotation.
AWS CLI
PROFILE=learn-sandbox
REGION=us-east-1
DB=hl-m06-ledger
# 1) Which encryption, CA and authentication settings does the instance have?
aws rds describe-db-instances --db-instance-identifier $DB \
--query 'DBInstances[0].{Encrypted:StorageEncrypted,Key:KmsKeyId,IAMAuth:IAMDatabaseAuthenticationEnabled,CA:CACertificateIdentifier,Public:PubliclyAccessible,ResourceId:DbiResourceId,Secret:MasterUserSecret.SecretArn}' \
--profile $PROFILE --region $REGION
# 2) Download the CA bundle (the download itself uses TLS) and look at what is inside.
curl -sS -o global-bundle.pem https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
grep -c "BEGIN CERTIFICATE" global-bundle.pem
# 3) CA validity dates for your account.
aws rds describe-certificates \
--query 'Certificates[].[CertificateIdentifier,ValidTill]' --output table \
--profile $PROFILE --region $REGION
# 4) Encrypt an existing UNENCRYPTED instance: snapshot -> encrypted copy -> restore.
aws rds create-db-snapshot --db-instance-identifier legacy-ledger --db-snapshot-identifier legacy-ledger-pre-encrypt \
--profile $PROFILE --region $REGION
aws rds wait db-snapshot-available --db-snapshot-identifier legacy-ledger-pre-encrypt \
--profile $PROFILE --region $REGION
aws rds copy-db-snapshot \
--source-db-snapshot-identifier legacy-ledger-pre-encrypt \
--target-db-snapshot-identifier legacy-ledger-encrypted \
--kms-key-id alias/hl-m06-ledger \
--profile $PROFILE --region $REGION
aws rds wait db-snapshot-available --db-snapshot-identifier legacy-ledger-encrypted \
--profile $PROFILE --region $REGION
aws rds restore-db-instance-from-db-snapshot \
--db-instance-identifier legacy-ledger-enc \
--db-snapshot-identifier legacy-ledger-encrypted \
--db-subnet-group-name <subnet-group> --vpc-security-group-ids <db-sg> \
--no-publicly-accessible --enable-iam-database-authentication \
--profile $PROFILE --region $REGION
# Then repoint the app, verify with the fingerprint, and DELETE the old instance and unencrypted snapshots.
# 5) Generate an IAM authentication token (valid 15 minutes) and use it as the password.
export PGPASSWORD=$(aws rds generate-db-auth-token \
--hostname <db-endpoint> --port 5432 --username ledger_app \
--profile $PROFILE --region $REGION)
psql "host=<db-endpoint> port=5432 dbname=ledger user=ledger_app sslmode=verify-full sslrootcert=global-bundle.pem" \
-c "SELECT current_user, ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"
# 6) Rotation on a customer secret: attach a rotation function and schedule.
aws secretsmanager rotate-secret --secret-id hl-m06/ledger-app \
--rotation-lambda-arn <rotation-lambda-arn> \
--rotation-rules AutomaticallyAfterDays=30 \
--profile $PROFILE --region $REGION
# 7) Which key versions of the secret exist right now? (AWSCURRENT, AWSPREVIOUS, AWSPENDING)
aws secretsmanager describe-secret --secret-id hl-m06/ledger-app \
--query 'VersionIdsToStages' --profile $PROFILE --region $REGIONInfrastructure as Code
The module project's Terraform already creates the CMK, the encrypted private instance, the TLS parameter group and the rds-db:connect policy. The tabs below add the application secret with rotation. Both were compiled or validated (CDK builds with Amazon.CDK.Lib 2.277.0; Terraform with terraform validate on 1.14 and AWS provider v6; not deployed).
using Amazon.CDK;
using Amazon.CDK.AWS.EC2;
using Amazon.CDK.AWS.KMS;
using Amazon.CDK.AWS.SecretsManager;
using Constructs;
namespace Harbourline.Infra;
public sealed class AppSecretStackProps : StackProps
{
public required string VpcId { get; init; }
public required string[] SubnetIds { get; init; }
public required string[] AvailabilityZones { get; init; }
public required string DbSecurityGroupId { get; init; }
/// <summary>ARN of the RDS-managed master secret (rds!db-...), used by the rotation function to create and alter the app user.</summary>
public required string MasterSecretArn { get; init; }
public required string DbHost { get; init; }
}
/// <summary>
/// A password secret for the application's database user, encrypted with a customer managed key, rotated
/// every 30 days by an AWS-hosted rotation function that runs inside the VPC (alternating-users strategy).
/// </summary>
public sealed class AppSecretStack : Stack
{
public AppSecretStack(Construct scope, string id, AppSecretStackProps props) : base(scope, id, props)
{
var vpc = Vpc.FromVpcAttributes(this, "Vpc", new VpcAttributes
{
VpcId = props.VpcId,
AvailabilityZones = props.AvailabilityZones,
PrivateSubnetIds = props.SubnetIds,
});
var key = new Key(this, "SecretKey", new KeyProps
{
Description = "Harbourline m06 application database credentials",
EnableKeyRotation = true,
RemovalPolicy = RemovalPolicy.DESTROY, // learning shortcut: production keeps the key
PendingWindow = Duration.Days(7),
});
var master = Secret.FromSecretCompleteArn(this, "Master", props.MasterSecretArn);
var appSecret = new Secret(this, "AppSecret", new SecretProps
{
SecretName = "hl-m06/ledger-app",
EncryptionKey = key,
GenerateSecretString = new SecretStringGenerator
{
SecretStringTemplate = $"{{\"username\":\"ledger_app\",\"host\":\"{props.DbHost}\",\"port\":5432,\"dbname\":\"ledger\"}}",
GenerateStringKey = "password",
PasswordLength = 32,
ExcludeCharacters = "/@\"' \\",
},
RemovalPolicy = RemovalPolicy.DESTROY,
});
var rotationSg = new SecurityGroup(this, "RotationSg", new SecurityGroupProps
{
Vpc = vpc,
Description = "Secrets Manager rotation function",
AllowAllOutbound = true, // needs the database, the Secrets Manager endpoint and KMS
});
_ = new CfnSecurityGroupIngress(this, "DbFromRotation", new CfnSecurityGroupIngressProps
{
GroupId = props.DbSecurityGroupId,
SourceSecurityGroupId = rotationSg.SecurityGroupId,
IpProtocol = "tcp",
FromPort = 5432,
ToPort = 5432,
Description = "Secret rotation function",
});
appSecret.AddRotationSchedule("Rotation", new RotationScheduleOptions
{
AutomaticallyAfter = Duration.Days(30),
HostedRotation = HostedRotation.PostgreSqlMultiUser(new MultiUserHostedRotationOptions
{
MasterSecret = master,
Vpc = vpc,
SecurityGroups = [rotationSg],
}),
});
Amazon.CDK.Tags.Of(this).Add("Project", "cea-lab");
Amazon.CDK.Tags.Of(this).Add("Lab", "m06-project");
_ = new CfnOutput(this, "AppSecretArn", new CfnOutputProps { Value = appSecret.SecretArn });
}
}PostgreSqlMultiUser is the alternating-users strategy: the function keeps two database users (the secret's user and a clone with a _clone suffix ⚠ VERIFY naming), flips between them each rotation, and needs the master secret to create and alter them. That is why existing pooled connections keep working through a rotation. Amazon.CDK.Tags.Of(this) must be fully qualified inside a Stack (plain Tags.Of resolves to the stack's own property, CS0176).
Complete code
The access probe connects the way the application will and prints what the database says about the session. It supports both authentication modes. Run it from inside the VPC (CloudShell VPC environment, an SSM session, or an ECS task).
<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="Npgsql" Version="10.0.3" />
<PackageReference Include="AWSSDK.RDS" Version="4.1.0.2" />
<PackageReference Include="AWSSDK.SecretsManager" Version="4.1.1.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>namespace Harbourline.AccessProbe;
/// <summary>Builds the resource ARN used by the rds-db:connect IAM action.</summary>
public static class RdsDbArn
{
/// <summary>
/// The ARN uses the instance's <b>DbiResourceId</b> (db-ABCDEFGHIJKL...), NOT its name, and for Aurora
/// the <b>cluster</b> resource id (cluster-...). The database user name is case sensitive.
/// </summary>
public static string Build(string partition, string region, string accountId, string resourceId, string dbUser)
{
if (resourceId.StartsWith("db-", StringComparison.Ordinal) is false && resourceId.StartsWith("cluster-", StringComparison.Ordinal) is false)
{
throw new ArgumentException("Use the DbiResourceId (db-...) or Aurora cluster resource id (cluster-...), not the instance name.", nameof(resourceId));
}
if (string.IsNullOrWhiteSpace(dbUser) || dbUser.Contains('/', StringComparison.Ordinal))
{
throw new ArgumentException("Database user name is required and cannot contain '/'.", nameof(dbUser));
}
return $"arn:{partition}:rds-db:{region}:{accountId}:dbuser:{resourceId}/{dbUser}";
}
}using System.Text.Json;
using Amazon;
using Amazon.RDS.Util;
using Amazon.Runtime.Credentials;
using Amazon.SecretsManager;
using Amazon.SecretsManager.Model;
using Npgsql;
// Access probe: connects to PostgreSQL the way the application will (TLS fully verified, IAM token or a
// Secrets Manager password) and prints what the DATABASE says about the session: who you are, whether
// TLS is on, which protocol and cipher, and which roles you hold. Read-only; creates nothing.
//
// IAM token: $env:HL_AUTH="iam"; $env:HL_HOST="<endpoint>"; $env:HL_USER="ledger_app"; $env:HL_CA="global-bundle.pem"
// Secret: $env:HL_AUTH="secret"; $env:HL_SECRET_ID="<arn>"; (plus HL_HOST, HL_USER, HL_CA)
// Run from inside the VPC (CloudShell VPC environment, SSM session, ECS task).
string Env(string name) => Environment.GetEnvironmentVariable(name) ?? throw new InvalidOperationException($"Set {name}.");
var host = Env("HL_HOST");
var user = Env("HL_USER");
var ca = Env("HL_CA");
var auth = (Environment.GetEnvironmentVariable("HL_AUTH") ?? "iam").ToLowerInvariant();
var region = RegionEndpoint.GetBySystemName(Environment.GetEnvironmentVariable("AWS_REGION") ?? "us-east-1");
var csb = new NpgsqlConnectionStringBuilder
{
Host = host,
Port = 5432,
Database = Environment.GetEnvironmentVariable("HL_DB") ?? "ledger",
Username = user,
SslMode = SslMode.VerifyFull, // verify the certificate chain AND that it names this host
RootCertificate = ca, // the RDS CA bundle, so verification can succeed
MaxPoolSize = 2,
ApplicationName = "access-probe",
};
var builder = new NpgsqlDataSourceBuilder(csb.ConnectionString);
// Called only when Npgsql opens a NEW physical connection, never for pooled ones.
builder.UsePeriodicPasswordProvider(async (_, ct) =>
{
switch (auth)
{
case "iam":
// Signed locally with the caller's credentials; valid for 15 minutes; no network call.
var credentials = DefaultAWSCredentialsIdentityResolver.GetCredentials();
return await RDSAuthTokenGenerator.GenerateAuthTokenAsync(credentials, region, host, 5432, user);
case "secret":
using (var secrets = new AmazonSecretsManagerClient(region))
{
var response = await secrets.GetSecretValueAsync(new GetSecretValueRequest { SecretId = Env("HL_SECRET_ID"), VersionStage = "AWSCURRENT" }, ct);
using var json = JsonDocument.Parse(response.SecretString);
return json.RootElement.GetProperty("password").GetString() ?? throw new InvalidOperationException("Secret has no password.");
}
default:
throw new InvalidOperationException("HL_AUTH must be iam or secret.");
}
}, TimeSpan.FromMinutes(10), TimeSpan.FromSeconds(5));
await using var source = builder.Build();
await using var conn = await source.OpenConnectionAsync();
await using (var cmd = new NpgsqlCommand(
"SELECT current_user, current_database(), s.ssl, s.version, s.cipher, s.bits FROM pg_stat_ssl s WHERE s.pid = pg_backend_pid()", conn))
await using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine($"user={reader.GetString(0)} database={reader.GetString(1)} tls={reader.GetBoolean(2)} protocol={reader.GetString(3)} cipher={reader.GetString(4)} bits={reader.GetInt32(5)}");
}
}
await using (var cmd = new NpgsqlCommand(
"SELECT r.rolname FROM pg_roles r WHERE pg_has_role(current_user, r.oid, 'member') AND r.rolname NOT LIKE 'pg\\_%' ORDER BY 1", conn))
await using (var reader = await cmd.ExecuteReaderAsync())
{
var roles = new List<string>();
while (await reader.ReadAsync())
{
roles.Add(reader.GetString(0));
}
Console.WriteLine($"roles: {string.Join(", ", roles)}");
Console.WriteLine(roles.Contains("rds_superuser") ? "WARNING: this login is a superuser-class role. Applications must not use it." : "OK: no rds_superuser membership.");
}
// Least privilege check: this must FAIL for an application login. A ledger is append-only.
await using (var cmd = new NpgsqlCommand("SELECT has_table_privilege(current_user, 'public.transactions', 'DELETE')", conn))
{
var canDelete = (bool)(await cmd.ExecuteScalarAsync() ?? false);
Console.WriteLine(canDelete ? "WARNING: can DELETE from transactions." : "OK: cannot DELETE from transactions.");
}
return 0;Run it with an IAM token:
aws sso login --profile learn-sandbox
$env:AWS_PROFILE = "learn-sandbox"
$env:HL_AUTH = "iam"
$env:HL_HOST = "<terraform output db_endpoint>"
$env:HL_USER = "ledger_app"
$env:HL_CA = "global-bundle.pem"
dotnet run --project AccessProbeExpected output (the protocol and cipher depend on the server version):
user=ledger_app database=ledger tls=True protocol=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256
roles: ledger_app, rds_iam
OK: no rds_superuser membership.
OK: cannot DELETE from transactions.Code explained line by line
Env(...)reads required settings and fails fast with a clear message. Nothing secret is hard-coded; the only secret-like value is the token generated at run time.SslMode.VerifyFullmakes Npgsql check the certificate chain and that the certificate names the host you dialled.RootCertificate = casupplies the RDS bundle. WithRequire, TLS would be on but the server unverified.UsePeriodicPasswordProviderregisters a callback that runs when Npgsql needs a password for a new physical connection. The10 minuterefresh interval is below the token's 15-minute life, and the5 secondfailure refresh interval retries quickly after an error. A pooled connection is never re-authenticated, which is correct because the database checks the token only at login.- In
iammode,DefaultAWSCredentialsIdentityResolver.GetCredentials()resolves credentials through the standard chain (SSO profile locally, task role in ECS).RDSAuthTokenGenerator.GenerateAuthTokenAsyncsigns a request locally. There is no network call, which is why the lesson can say tokens are cheap to create. - In
secretmode the callback readsAWSCURRENT. During rotation Secrets Manager moves that label only after the new password works, so reading it is always safe. - The first query reads
pg_stat_sslfor the current backend:ssl, TLSversion,cipherand keybitsas the server sees them. This is evidence, not a belief. - The second query lists the roles you hold via
pg_has_role, excluding built-inpg_roles (the\\_escapes the underscore inLIKE). A superuser-class role in an application login is a finding. has_table_privilege(..., 'DELETE')turns "least privilege" into a testable assertion. It must be false forledger_app.RdsDbArn.Buildshows the part people get wrong: the IAM resource uses the DbiResourceId (or the cluster resource id), not the instance name, and the name of the database user is case sensitive.
Security considerations
⚠ Learning shortcut — not for production
The lab uses skip_final_snapshot, a 1-day backup retention, a 7-day key deletion window and Terraform state on your laptop (which will contain the master secret ARN and, with the random provider, any generated password). Production keeps deletion protection on, retains backups for the period your resilience plan needs, stores state in an encrypted remote backend and restricts who can run terraform apply on the key.
- Separate key administrators from key users. The key policy in the module project lets the account administer the key and lets RDS and Secrets Manager use it. Add an SCP or IAM boundary that denies
kms:ScheduleKeyDeletionandkms:DisableKeyto everyone except a break-glass role, and alarm on those events in CloudTrail. Disabling the key stops the database. - Never log tokens or secrets. An IAM token is a short-lived credential. The module project's
RdsSecret.ToString()redacts the password. Do the same for any type that carries credentials, and never put them in connection strings that reach logs or exception messages. - Verify the server.
Requireencrypts but allows a man-in-the-middle with any certificate. UseVerifyFulland ship the root CA only (not intermediates), because RDS rotates server certificates. - One identity per purpose. Application, migration, reporting and break-glass identities are separate. The app role has no DDL and no
DELETE. - IAM authentication changes your audit story. Token generation is not logged by CloudTrail. Turn on
log_connections,log_disconnectionsandpgauditand ship them to CloudWatch Logs with a retention your records policy requires. - Memory headroom. IAM authentication needs extra memory on the database (the documentation says 300 to 1,000 MiB). On a 1 GiB
db.t4g.microthat is a large fraction. For the lab it works; for anything real, size accordingly. - Compliance. Encryption keys and TLS are technical controls. Whether they satisfy a PCI DSS or UK GDPR requirement, what retention applies, and how erasure requests interact with backups are decisions for your compliance and data-protection teams. This lesson claims nothing.
Banking use case
Harbourline's ledger runs in a production account. A separate backup account holds cross-account snapshot copies for ransomware resilience. The design:
- One CMK per environment, with a key policy that names the backup account's role so encrypted snapshots can be shared and copied. The key alias is
alias/hl-prod-ledger. - The API logs in as
ledger_appwith IAM tokens; a nightly reconciliation job logs in asledger_reader(also IAM) withSELECTonly; migrations run from a pipeline asledger_migratorusing a rotated secret; the RDS-managed master secret is sealed behind a break-glass role requiring two approvers. - A nightly check runs the access probe and the posture check from the RDS lesson and records the result as evidence for the quarterly access review.
- CloudTrail alarms fire on
DisableKey,ScheduleKeyDeletion,ModifyDBInstancechanges toPubliclyAccessible, andRevokeGranton the ledger key.
Common mistakes
- Choosing
aws/rds"because it is the default". You discover later that snapshots cannot be shared and the key policy cannot be tightened. Create a CMK first. - Assuming you can encrypt later. You cannot switch encryption on in place. Encrypt at creation, or snapshot, copy encrypted and restore.
- Granting
rds-db:connecton*or using the instance name in the ARN. Use the resource id and the specific user. - Forgetting the database-side grant. The IAM policy lets you obtain a token. The database user still needs
GRANT rds_iam TO ...and its table grants. - Using
SSL Mode=Requireand calling it secure. It does not verify the server. - Reading the secret on every query. Secrets Manager charges per API call and rate-limits. Fetch on new connection or cache with a short time-to-live.
- Rotating a password the pool never re-reads. If the application caches the password forever, rotation locks it out. Use alternating-users rotation and re-read
AWSCURRENTon new connections. - Disabling or deleting the key to "revoke access". It takes the database down. Remove the principal's permission instead.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
28000: PAM authentication failed for user "ledger_app" | Token older than 15 minutes, rds_iam not granted, IAM policy resource wrong (name instead of resource id), instance missing IAM authentication, or the token was truncated by the driver | Generate the token per new connection, check GRANT rds_iam, check the ARN with RdsDbArn.Build, confirm IAMDatabaseAuthenticationEnabled |
28000: no pg_hba.conf entry ... no encryption | Client connected without TLS while rds.force_ssl=1 | Use SSL Mode=VerifyFull with the CA bundle |
The remote certificate is invalid / UntrustedRoot | Missing, outdated or wrong CA bundle, or hostname does not match (connected by IP or a CNAME) | Download the current bundle, connect to the real endpoint name |
| IAM auth fails only on a tiny instance, with out-of-memory messages | IAM authentication needs extra database memory | Use a larger class, or reduce memory-hungry parameters |
AccessDeniedException on GetSecretValue | Role lacks the secret ARN, or kms:Decrypt via Secrets Manager on the key | Add both, with kms:ViaService |
Instance in inaccessible-encryption-credentials-recoverable | KMS key disabled or access lost | Enable the key, then aws rds start-db-instance |
| Cannot share a snapshot with another account | Snapshot is encrypted with the AWS managed key | Copy it with a CMK whose key policy grants the other account, then share |
| Copying an encrypted snapshot to another Region fails | No key specified in the destination Region | Pass --kms-key-id for a key in the destination Region |
| Rotation fails with a timeout | Rotation function cannot reach the database or the Secrets Manager endpoint | Put it in the VPC, open the database security group to it, add a Secrets Manager interface endpoint or NAT |
After rotation the app gets password authentication failed | App cached the old password; single-user rotation changed it in place | Re-read AWSCURRENT on new connections; prefer alternating users |
Cost and cleanup
| Item | Cost (verify current prices ⚠ VERIFY) | Notes |
|---|---|---|
| KMS CMK | about USD 1 per month plus per-request charges, pro-rated | A key scheduled for deletion is not billed after it is pending deletion ⚠ VERIFY |
| Secrets Manager secret | about USD 0.40 per secret per month plus API calls | The RDS-managed master secret is also billed |
RDS db.t4g.micro | cents per hour | Same as the module project |
| Rotation Lambda | effectively free at these volumes | Runs only on rotation |
Cost label: credits if you deploy the secret and rotation; free if you only run the local probe design and read the code. Free alternative: run the probe against the module project's local PostgreSQL container with SslMode=Disable and HL_AUTH=secret replaced by a local password; the TLS and IAM parts need RDS and are design-only.
Teardown:
PROFILE=learn-sandbox
REGION=us-east-1
# Terraform-managed lab: one command removes the instance, key (7-day pending deletion), secret and role.
# cd infra/terraform && terraform destroy
# CDK-managed secret stack:
# cdk destroy hl-m06-app-secret
# If you created a secret by hand:
aws secretsmanager delete-secret --secret-id hl-m06/ledger-app --force-delete-without-recovery \
--profile $PROFILE --region $REGION
# Manual snapshots from the "encrypt an existing instance" exercise:
aws rds delete-db-snapshot --db-snapshot-identifier legacy-ledger-pre-encrypt --profile $PROFILE --region $REGION
aws rds delete-db-snapshot --db-snapshot-identifier legacy-ledger-encrypted --profile $PROFILE --region $REGION
# Confirm nothing tagged for the lab remains (KMS keys show as PendingDeletion for 7 days, which is expected).
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab Key=Lab,Values=m06-project \
--profile $PROFILE --region $REGIONExercises
- 🟢 Guided: run the access probe against the lab database with IAM authentication. Then change
SslModetoRequireand remove the CA path: note that it still connects. Explain in two sentences what that proves and whyVerifyFullis the right setting. - 🟡 Challenge: write the IAM policy and the SQL for a second identity,
ledger_reader, that can connect with IAM authentication and onlySELECTfromtransactionsandaccounts. Extend the probe with an assertion thatINSERTis denied for it. UseRdsDbArn.Buildto produce the policy resource. - 🔴 Live problem: the unencrypted staging database
stg-ledger(PostgreSQL, 200 GB, takes writes all day) must be encrypted by Friday. The business accepts at most 15 minutes of write downtime. Compare the snapshot-copy-restore approach, a blue/green deployment and logical replication (DMS) on downtime, data-loss risk, cost and rollback. Recommend one and write the runbook, including how you verify the new instance and what you delete afterwards.
Assignment
Add docs/adr-0003-encryption-and-access.md to the module project. It must record: the key choice (CMK, key policy principals, rotation, deletion window), the TLS posture (CA, rds.force_ssl, VerifyFull), the authentication choice per identity (application, migration, reporting, break-glass) with the IAM-versus-secret reasoning, the grants for each database role, the audit sources you will rely on (and the gap: IAM token generation is not logged), and the alarms. Attach the probe output.
Solutions
With Require and no CA path the connection succeeds, and pg_stat_ssl still reports ssl=true, because the server accepted TLS. What it proves is that the channel is encrypted. It does not prove the server is yours: Npgsql accepts any certificate in Require mode. With VerifyFull and the RDS bundle, a wrong host name, a self-signed impostor or a missing root fails the handshake. In a bank, encryption without server authentication leaves a man-in-the-middle gap, so the setting is VerifyFull.
SQL (run as the master user):
CREATE ROLE ledger_reader WITH LOGIN;
GRANT rds_iam TO ledger_reader;
GRANT CONNECT ON DATABASE ledger TO ledger_reader;
GRANT USAGE ON SCHEMA public TO ledger_reader;
GRANT SELECT ON accounts, transactions TO ledger_reader;IAM statement: action rds-db:connect, resource RdsDbArn.Build("aws", "us-east-1", "<account-id>", "<DbiResourceId>", "ledger_reader"). Probe addition: SELECT has_table_privilege(current_user, 'public.transactions', 'INSERT') must return false. The reason for a separate identity is blast radius: a leaked reporting credential cannot post transactions, and the audit trail attributes reads to the reporting job.
Snapshot-copy-restore: simple and cheap, but writes after the snapshot are lost unless you freeze writes, so downtime equals snapshot plus copy plus restore time (tens of minutes for 200 GB), which breaks the 15-minute rule unless you accept a longer window. Blue/green: RDS creates an encrypted green environment (the documentation says blue/green can encrypt an unencrypted instance or change the key) and replicates from blue, so the switchover is brief (typically around a minute ⚠ VERIFY), data loss risk is low, rollback is switching back, and cost is a temporary second instance. DMS/logical replication: works across engines and versions but adds the most moving parts and needs replication prerequisites. Recommend blue/green if the engine and version are eligible ⚠ VERIFY limitations. Runbook: check eligibility, create the CMK, create the blue/green deployment with the new key, verify with the fingerprint command, switch over, run the access probe on the new endpoint, delete the old blue environment after the rollback window, and delete any manual snapshots under the old key.
Knowledge check
Answer: Not in place. Encryption is chosen at creation. You can snapshot, copy the snapshot with encryption enabled and restore, or use a blue/green deployment to create an encrypted green environment.
Answer: The AWS managed key's policy is controlled by AWS and cannot grant another account access, so the other account cannot decrypt the snapshot. A customer managed key's policy can name the other account.
Answer: The DB instance's DbiResourceId (db-...) or, for Aurora, the cluster resource id (cluster-...), plus the database user name. It does not use the instance name.
Answer: RDS for SQL Server. IAM database authentication supports MariaDB, MySQL and PostgreSQL. Use Secrets Manager or Active Directory for SQL Server.
Answer: The token is used only to establish the connection. After authentication the session does not depend on it. New physical connections need a fresh token.
Answer: Alternating users. Two database users with identical grants take turns, so the previous credentials stay valid for one rotation period. Single-user rotation changes the password in place and can lock out clients holding the old one.
Interview questions
- Walk me through how you would secure a new RDS database. Create a CMK, create the instance encrypted with it in private subnets, force TLS, enable IAM authentication where supported, create least-privilege database roles, store only the admin credential in Secrets Manager (RDS-managed), scope
rds-db:connectper user, enable engine audit logging, alarm on key and configuration changes, and verify with a probe. - IAM database authentication or Secrets Manager? IAM for PostgreSQL, MySQL and MariaDB workloads with an IAM role: no secret to rotate or leak. Secrets Manager for SQL Server, third-party tools, or when the proxy design needs stored credentials. Mention the memory overhead and the audit gap.
- What happens if someone disables the KMS key? The database becomes inaccessible. Instances that can stop go to a recoverable state for up to seven days. Read replicas and instances with replicas cannot recover that way. Re-enable the key, or restore from a snapshot.
- Why not just use
SSL Mode=Require? It encrypts but trusts any certificate.VerifyFullwith the RDS root CA authenticates the server.
Certification practice
A company must copy RDS snapshots of an encrypted production database to a separate backup account. The snapshots are encrypted with the default aws/rds key and the copy fails. What should a solutions architect do?
A. Share the snapshot and give the backup account access to the AWS managed key B. Create a customer managed KMS key, copy the snapshot encrypting it with that key, grant the backup account use of the key, then share it C. Decrypt the snapshot and share the unencrypted copy D. Export the snapshot to S3 with SSE-S3
Answer: B. Snapshots under the AWS managed key cannot be shared. A customer managed key's policy can grant the other account. A is not possible. C weakens security. D changes the format and does not preserve restorability.
An application on ECS needs to connect to RDS for PostgreSQL without storing any password. Which solution meets this with the LEAST operational overhead?
A. Store the password in an environment variable
B. Enable IAM database authentication, grant rds_iam to the database user and allow rds-db:connect for the task role
C. Store the password in Secrets Manager and rotate it manually each quarter
D. Use the master user and restrict the security group
Answer: B. The task role generates short-lived tokens, so no password exists. A and C keep a password. D uses the master identity, which violates least privilege.
A developer's application connects to RDS for PostgreSQL with IAM database authentication. After 20 minutes, existing queries still work but new connections from the pool fail with authentication errors. What is the cause?
A. The IAM role was deleted B. The authentication token was cached and is older than 15 minutes C. The database ran out of storage D. TLS was disabled on the server
Answer: B. Tokens last 15 minutes and are needed for every new connection. Existing sessions survive expiry. The code must generate a fresh token for each new physical connection. A would not explain why existing sessions still work, and C and D would affect existing sessions too.
Completion checklist
- I can state what RDS encryption covers and the four limits that matter (creation only, fixed key, replica rules, snapshot sharing)
- I know what happens when the KMS key is disabled
- I can explain
RequireversusVerifyFulland where the CA bundle comes from - I can choose IAM authentication or a secret for a given engine, and justify it
- I ran the access probe (or read its expected output) and understand every line
- I deleted any secrets, keys, snapshots and instances I created and confirmed nothing is billing
Key takeaways
- Encrypt at creation with a customer managed key. You cannot switch it on later or change the key in place, and
aws/rdssnapshots cannot be shared. - Disabling the key stops the database. Protect key administration separately from use.
- TLS needs verification:
rds.force_sslon the server, the RDS root CA andSSL Mode=VerifyFullon the client. - IAM tokens beat passwords where available (PostgreSQL, MySQL, MariaDB), but they need database memory, are not logged by CloudTrail, and do not exist for SQL Server. Otherwise use Secrets Manager with alternating-users rotation.
- Authorisation is separate: one narrow role per identity, no master user in the app, and a probe that proves it.
Further reading
- Encrypting Amazon RDS resources
- Using SSL/TLS to encrypt a connection to a DB instance or cluster
- IAM database authentication for MariaDB, MySQL, and PostgreSQL
- Secrets Manager rotation strategies
- Npgsql security and encryption
- Previous: Multi-AZ, replicas, backups and PITR · Next: Connection pooling and RDS Proxy
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.
Multi-AZ, read replicas, backups and point-in-time recovery
Know exactly what Multi-AZ instances, Multi-AZ DB clusters, read replicas, automated backups, snapshots and point-in-time restore protect against, map them to RPO and RTO, and run a repeatable, evidenced restore drill from C#.
Connection pooling and RDS Proxy
Work out how many database connections a .NET fleet can open, size ADO.NET and Npgsql pools to stay inside the limit, and decide when to put Amazon RDS Proxy in front of RDS or Aurora, including what makes it stop helping.