Skip to Content
Cloud Engineering Academy
6. Databases & Data Architecture

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.

intermediate⏱ 1 h 45 min🟡 Low costSAA-C03SCS-C03DVA-C03

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:connect IAM statement.
  • Run a C# access probe that reports TLS version, cipher and role membership as the database sees them.

Prerequisites

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:

QuestionControlAWS pieces
Can someone who steals the disk or a backup read the data?Encryption at restRDS storage encryption, AWS KMS key
Can someone on the network read or alter traffic, or impersonate the server?Encryption in transit with server verificationTLS, RDS CA certificates, rds.force_ssl
Who is connecting, and how do they prove it?AuthenticationIAM database authentication, Secrets Manager, RDS-managed master password
What may they do once connected?Authorisation inside the databaseDatabase 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-recoverable about 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:

  1. 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_ssl in the parameter group.
  2. 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.pem file from https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem (or the Regional file such as us-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

MethodEnginesPassword in your app?RotationMain limits
Static password in configallYesManual, which means neverDo not use this for anything beyond a throwaway local container
RDS-managed master password (--manage-master-user-password)allNo: you read it from Secrets ManagerAutomatic, managed by RDSFor 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 userallNo: fetched at connect timeScheduled 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 authenticationPostgreSQL, MySQL, MariaDB (not SQL Server)No password existsToken valid 15 minutes, generated per connectNeeds 300 to 1,000 MiB extra database memory; not logged by CloudTrail; some IAM condition keys (such as aws:SourceIp) are unsupported
Kerberos / Active DirectorySQL Server, PostgreSQL, MySQL, Oracle ⚠ VERIFY per engineNoDirectory-managedNeeds 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_iam is 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, where RESOURCE-ID is 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 whenUse 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 leakYou 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 equivalentWhat's different
Transparent Data Encryption with a key in Azure Key VaultRDS storage encryption with a KMS CMKChosen 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 verificationYou download and trust the RDS CA yourself; Azure's certificate chains to a public root
Managed identity / Microsoft Entra authentication to Azure SQLIAM database authentication (PostgreSQL, MySQL, MariaDB)A 15-minute token generated per connect. Not available for RDS SQL Server
Key Vault secret with rotation policySecrets Manager secret with a rotation LambdaRotation is a Lambda you deploy in the VPC (or a hosted one via CDK/CloudFormation), not a vault setting
Azure Private Link to SQLPrivate subnet + security group + (optional) interface endpoints for Secrets Manager and KMSThe database itself has no public endpoint by default if you set PubliclyAccessible=false
Azure SQL auditingpgaudit / SQL Server Audit to CloudWatch Logs, plus Database Activity Streams where supported ⚠ VERIFY engineCloudTrail audits the control plane, not statements
SQL logins, contained users, Entra usersPostgreSQL roles, GRANT rds_iamSame 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 $REGION

Infrastructure 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).

infra/cdk/AppSecretStack.cs
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).

AccessProbe/AccessProbe.csproj
<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>
AccessProbe/RdsDbArn.cs
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}";
    }
}
AccessProbe/Program.cs
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 AccessProbe

Expected 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

  1. 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.
  2. SslMode.VerifyFull makes Npgsql check the certificate chain and that the certificate names the host you dialled. RootCertificate = ca supplies the RDS bundle. With Require, TLS would be on but the server unverified.
  3. UsePeriodicPasswordProvider registers a callback that runs when Npgsql needs a password for a new physical connection. The 10 minute refresh interval is below the token's 15-minute life, and the 5 second failure 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.
  4. In iam mode, DefaultAWSCredentialsIdentityResolver.GetCredentials() resolves credentials through the standard chain (SSO profile locally, task role in ECS). RDSAuthTokenGenerator.GenerateAuthTokenAsync signs a request locally. There is no network call, which is why the lesson can say tokens are cheap to create.
  5. In secret mode the callback reads AWSCURRENT. During rotation Secrets Manager moves that label only after the new password works, so reading it is always safe.
  6. The first query reads pg_stat_ssl for the current backend: ssl, TLS version, cipher and key bits as the server sees them. This is evidence, not a belief.
  7. The second query lists the roles you hold via pg_has_role, excluding built-in pg_ roles (the \\_ escapes the underscore in LIKE). A superuser-class role in an application login is a finding.
  8. has_table_privilege(..., 'DELETE') turns "least privilege" into a testable assertion. It must be false for ledger_app.
  9. RdsDbArn.Build shows 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:ScheduleKeyDeletion and kms:DisableKey to 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. Require encrypts but allows a man-in-the-middle with any certificate. Use VerifyFull and 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_disconnections and pgaudit and 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.micro that 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_app with IAM tokens; a nightly reconciliation job logs in as ledger_reader (also IAM) with SELECT only; migrations run from a pipeline as ledger_migrator using 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, ModifyDBInstance changes to PubliclyAccessible, and RevokeGrant on 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:connect on * 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=Require and 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 AWSCURRENT on new connections.
  • Disabling or deleting the key to "revoke access". It takes the database down. Remove the principal's permission instead.

Troubleshooting

SymptomLikely causeFix
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 driverGenerate the token per new connection, check GRANT rds_iam, check the ARN with RdsDbArn.Build, confirm IAMDatabaseAuthenticationEnabled
28000: no pg_hba.conf entry ... no encryptionClient connected without TLS while rds.force_ssl=1Use SSL Mode=VerifyFull with the CA bundle
The remote certificate is invalid / UntrustedRootMissing, 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 messagesIAM authentication needs extra database memoryUse a larger class, or reduce memory-hungry parameters
AccessDeniedException on GetSecretValueRole lacks the secret ARN, or kms:Decrypt via Secrets Manager on the keyAdd both, with kms:ViaService
Instance in inaccessible-encryption-credentials-recoverableKMS key disabled or access lostEnable the key, then aws rds start-db-instance
Cannot share a snapshot with another accountSnapshot is encrypted with the AWS managed keyCopy it with a CMK whose key policy grants the other account, then share
Copying an encrypted snapshot to another Region failsNo key specified in the destination RegionPass --kms-key-id for a key in the destination Region
Rotation fails with a timeoutRotation function cannot reach the database or the Secrets Manager endpointPut 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 failedApp cached the old password; single-user rotation changed it in placeRe-read AWSCURRENT on new connections; prefer alternating users

Cost and cleanup

ItemCost (verify current prices ⚠ VERIFY)Notes
KMS CMKabout USD 1 per month plus per-request charges, pro-ratedA key scheduled for deletion is not billed after it is pending deletion ⚠ VERIFY
Secrets Manager secretabout USD 0.40 per secret per month plus API callsThe RDS-managed master secret is also billed
RDS db.t4g.microcents per hourSame as the module project
Rotation Lambdaeffectively free at these volumesRuns 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 $REGION

Exercises

  • 🟢 Guided: run the access probe against the lab database with IAM authentication. Then change SslMode to Require and remove the CA path: note that it still connects. Explain in two sentences what that proves and why VerifyFull is the right setting.
  • 🟡 Challenge: write the IAM policy and the SQL for a second identity, ledger_reader, that can connect with IAM authentication and only SELECT from transactions and accounts. Extend the probe with an assertion that INSERT is denied for it. Use RdsDbArn.Build to 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

Knowledge check

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:connect per 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. VerifyFull with the RDS root CA authenticates the server.

Certification practice

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 Require versus VerifyFull and 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/rds snapshots cannot be shared.
  • Disabling the key stops the database. Protect key administration separately from use.
  • TLS needs verification: rds.force_ssl on the server, the RDS root CA and SSL Mode=VerifyFull on 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

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.

On this page