The AWS Console is really handy to get something up and running in the prototyping stage of a project. But often times the temptation is there to just take the 'ok, thats working now, phew!' approach and move on. Inevitably, 6 months or more down the track you've got to either recreate a similar resource or redeploy the same resource again, and despite being sure you'd remember all the little aspects, you've got to go on the voyage of rediscovery all over again to re-identify all the little settings you need.

Because I have the memory of a goldfish, I prefer to take a 'Infrastructure as Code' approach, even for personal side projects. So, here's a walk through how to write a Cloudformation template to provision a postgreSQL AWS RDS instance with optional multi-AZ deployment, a read-replica, an RDS Proxy fronting it, and auto rotating database credentials. Possibly some alerting as well if I get to it.

tldr; - if you've just after the template, you can grab it from here

So, first things first, we want to create a Security Group to associate to the RDS instance. We're going to start the template off with a Parameters block that we can adjust some settings when it comes time to deploy/redeploy. The first one is the DB Instance name that will be re-used for a variety of other resources to keep things together logically.

Then comes the Security Group. We'll be deploying the database into a VPC, so we want to limit incoming traffic to the postgreSQL port (5432) and allow traffic from within the VPC so we can connect to the database from a bastion host. You could lock this down further to a specific EC2 instance or other Security Group associated with specific EC2's, but for now, this will do. Outbound is possibly a bit relaxed, but again, will suffice for now.

Note some of these properties import values (!ImportValue) from Cloudformation Exports. These have been created previously by the VPC stack deployed as part of Building out a simple AWS VPC

AWSTemplateFormatVersion: '2010-09-09'
Description: PostgreSQL DB RDS Instance
Parameters:
  DBInstanceIdentifier:
    Description: Name of the RDS Instance.
    Type: String
    MinLength: '1'
    MaxLength: '50'
    Default: postgresdb

Resources:
  SecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupName: !Sub "${DBInstanceIdentifier}-rds-sg"
      GroupDescription: 'RDS security group'
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 5432
          ToPort: 5432
          CidrIp: !ImportValue vpc-cidr
          Description: Postgresql default port (Internal) for access via bastion host
      SecurityGroupEgress:
        - IpProtocol: tcp
          FromPort: 0
          ToPort: 65535
          CidrIp: 0.0.0.0/0
          Description: All outbound traffic
      VpcId: !ImportValue 'vpc-id'

Next the subnets that the RDS can be deployed into are grouped into a Subnet Group.

  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: !Sub "${DBInstanceIdentifier}-subnetgroup"
      DBSubnetGroupName: !Sub "${DBInstanceIdentifier}-subnetgroup"
      SubnetIds:
        - !ImportValue subnet-private-a
        - !ImportValue subnet-private-b
        - !ImportValue subnet-private-c

The next resources to create are a password for the master user. Ideally we store this password securely in Secrets Manager. Even more ideally we don't even need to look at this value for the deployment. And even better still is a scheduled rotation of that password so even if somehow it is inadvertently exposed, regular rotation as well as the ability to rotate it on demand add that extra level of security. Add the following to the template to achieve this.

First, add to the top of the template a transform for SecretsManager which will be required for the SecretsManager resources that will be added later. Add this as the second line of the template.

Transform: AWS::SecretsManager-2020-07-23

Next, add a new Parameter to the top of the template to allow for the specification of a database specific master user

  DBInstanceMasterUsername:
    Description: Master username
    Type: String
    MinLength: '0'
    MaxLength: '255'
    Default: dbo

Next add the following three resources. The first, DBInstancePassword creates the SecretsManager secret. The record will be a json object with the first key set to 'username' and the value of the value set or defaulted in the DBInstanceMasterUsername parameter. The second key will be set to 'password' and the value will be a value randomly generated by SecretsManager. Do not alter these json entity names from username and password - things expect these two keys and break if they're not found.

Note that specific characters can be excluded if the database engine that will be used has issues with specific characters. Also note that the GenerateSecretString properties can also exclude particular classes of characters (ExcludeNumbers, ExcludePunctuation etc.) if necessary (best to avoid this as it weakens the generated password).

The second resource adds the database connection information to the secret. This is necessary to allow the third resource, the secret rotation to connect to the database and rotate the users password on the database.

The third resource sets up an AWS Lambda using the specified template (PostgreSQLSingleUser) to rotate the Secrets Manager secret and database user password on a schedule (in this case at 1:00 UTC on the first sunday of the month). You can alter this rotation to be more frequent if desired, but no more frequently than every 4 hours. The '2h' Duration imply specifies that the rotation can occur in a 2 hour window from the rotation time.

As the database will be deployed into one of the private subnets, all possible VPC subnets that it could be deployed into need to be specified to ensure that the lambda is able to access the database.

  DBInstancePassword:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: !Sub "${DBInstanceIdentifier}-master-instance-password"
      Description: !Sub "The master instance password for the ${DBInstanceIdentifier} RDS database"
      GenerateSecretString:
        SecretStringTemplate: !Sub '{"username": "${DBInstanceMasterUsername}"}'
        GenerateStringKey: "password"
        PasswordLength: 20
        ExcludeCharacters: ':/@"\;`%$'''

  SecretDBInstanceAttachment:
    DependsOn: DBInstancePassword
    Type: AWS::SecretsManager::SecretTargetAttachment
    Properties:
      SecretId:
        Ref: DBInstancePassword
      TargetId:
        Ref: DBInstance
      TargetType: AWS::RDS::DBInstance

  DBInstanceRotationSchedule:
    DependsOn: SecretDBInstanceAttachment  
    Type: AWS::SecretsManager::RotationSchedule
    Properties:
      SecretId:
        Ref: DBInstancePassword
      HostedRotationLambda:
        RotationType: PostgreSQLSingleUser
        RotationLambdaName: !Sub "SecretsManager-Rotation-${DBInstanceIdentifier}"
        VpcSecurityGroupIds: !Ref SecurityGroup
        VpcSubnetIds:
          Fn::Join:
          - ","
          - - !ImportValue subnet-private-a
            - !ImportValue subnet-private-b
            - !ImportValue subnet-private-c
      RotationRules:
        Duration: 2h
        ScheduleExpression: 'cron(0 1 ? * SUN#1 *)'

Next we get to the database itself. First though, some additional Parameters to add to the top of the template. This is currently defaulting the version of postgreSQL to version 14.6 - check what the current versions supported are in AWS RDS for postgreSQL and adjust as appropriate.

  DBName:
    Description: Name of the database
    Type: String
    MinLength: '1'
    MaxLength: '255'
    Default: postgresdb
  DBInstanceType:
    Description: Type of the DB instance
    Type: String
    Default: db.t3.micro
  DBEngine:
    Description: DB Engine
    Type: String
    MinLength: '1'
    MaxLength: '255'
    Default: postgres
  DBEngineVersion:
    Description: PostgreSQL version.
    Type: String
    Default: '14.6'
  DBAllocatedStorage:
    Type: Number
    Default: 20
  DBBackupRetentionPeriod:
    Type: Number
    Default: 7
  DBPreferredBackupWindow:
    Description: The daily time range in UTC during which you want to create automated backups.
    Type: String
    Default: '06:00-06:30'
  DBPreferredMaintenanceWindow:
    Description: The weekly time range (in UTC) during which system maintenance can occur.
    Type: String
    Default: 'mon:07:00-mon:07:30'
  DBMultiAZ:
    Description: Specifies if the database instance is deployed to multiple Availability Zones
    Type: String
    Default: false
    AllowedValues: [true, false]
  DBParameterGroup:
    Description: Parameter Group
    Type: String
    MinLength: '1'
    MaxLength: '255'
    Default: 'default.postgres14'

Then add the following to the template

  DBInstance:
    DependsOn: DBInstancePassword
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: !Ref DBAllocatedStorage
      AllowMajorVersionUpgrade: false
      AutoMinorVersionUpgrade: true
      BackupRetentionPeriod: !Ref DBBackupRetentionPeriod
      CopyTagsToSnapshot: True
      DBInstanceClass: !Ref DBInstanceType
      DBName: !Ref DBName
      DBInstanceIdentifier: !Ref DBInstanceIdentifier
      DBParameterGroupName: !Ref DBParameterGroup
      DBSubnetGroupName: !Ref DBSubnetGroup
      DeletionProtection: true
      Engine: postgres
      EngineVersion: !Ref DBEngineVersion
      MasterUsername: !Ref DBInstanceMasterUsername
      MasterUserPassword: !Join [ '', [ '{{resolve:secretsmanager:', !Ref DBInstancePassword, ':SecretString:password}}' ] ]
      MasterUserSecret: 
        SecretArn: !Ref DBInstancePassword
      MonitoringInterval: 60
      MonitoringRoleArn: !Join [ "", [ "arn:aws:iam::", !Ref "AWS::AccountId", ":role/rds-monitoring-role" ] ]
      MultiAZ: !Ref DBMultiAZ
      PreferredBackupWindow: !Ref DBPreferredBackupWindow
      PreferredMaintenanceWindow: !Ref DBPreferredMaintenanceWindow
      PubliclyAccessible: false
      StorageEncrypted: true
      StorageType: gp2
      VPCSecurityGroups:
        - !Ref SecurityGroup

This will be enough to stand up a reasonably secure and usable postgreSQL RDS instance using the parameters either defaulted or overridden when deploying the template via CloudFormation. There are additional properties that can be specified - this example doesn't include all possible properties. Some properties that are included though that are worth calling out specifically are;

  • MultiAZ : The default parameter is false, but setting this to true when deploying will create a failover instance in a different AZ to the primary instance. Failover is handled automatically by RDS in case of either upgrade (the primary and failover are upgraded separately) or in case of an issue detected in the primary or primary AZ. Note that because this is a clone of the primary database, the costs will be twice the cost of a single RDS instance.
  • StorageType: This is defaulted to gp2 which is quite adequate, but if higher IOPS are known to be required, this will need to be modified.

At this point, the template can be considered complete. For ease of reference the entirety of the template so far is below. Further down are some additional features that can be added such as a read replica and an RDS Proxy.

AWSTemplateFormatVersion: '2010-09-09'
Transform: AWS::SecretsManager-2020-07-23
Description: PostgreSQL DB RDS Instance
Parameters:
  DBInstanceIdentifier:
    Description: Name of the RDS Instance.
    Type: String
    MinLength: '1'
    MaxLength: '50'
    Default: postgresdb
  DBInstanceMasterUsername:
    Description: Master username
    Type: String
    MinLength: '0'
    MaxLength: '255'
    Default: dbo
  DBName:
    Description: Name of the database
    Type: String
    MinLength: '1'
    MaxLength: '255'
    Default: postgresdb
  DBInstanceType:
    Description: Type of the DB instance
    Type: String
    Default: db.t3.micro
  DBEngine:
    Description: DB Engine
    Type: String
    MinLength: '1'
    MaxLength: '255'
    Default: postgres
  DBEngineVersion:
    Description: PostgreSQL version.
    Type: String
    Default: '14.6'
  DBAllocatedStorage:
    Type: Number
    Default: 20
  DBBackupRetentionPeriod:
    Type: Number
    Default: 7
  DBPreferredBackupWindow:
    Description: The daily time range in UTC during which you want to create automated backups.
    Type: String
    Default: '06:00-06:30'
  DBPreferredMaintenanceWindow:
    Description: The weekly time range (in UTC) during which system maintenance can occur.
    Type: String
    Default: 'mon:07:00-mon:07:30'
  DBMultiAZ:
    Description: Specifies if the database instance is deployed to multiple Availability Zones
    Type: String
    Default: false
    AllowedValues: [true, false]
  DBParameterGroup:
    Description: Parameter Group
    Type: String
    MinLength: '1'
    MaxLength: '255'
    Default: 'default.postgres14'

Resources:
  SecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupName: !Sub "${DBInstanceIdentifier}-rds-sg"
      GroupDescription: 'RDS security group'
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 5432
          ToPort: 5432
          CidrIp: !ImportValue vpc-cidr
          Description: Postgresql default port (Internal) for access via bastion host
      SecurityGroupEgress:
        - IpProtocol: tcp
          FromPort: 0
          ToPort: 65535
          CidrIp: 0.0.0.0/0
          Description: All outbound traffic
      VpcId: !ImportValue 'vpc-id'
	  
  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: !Sub "${DBInstanceIdentifier}-subnetgroup"
      DBSubnetGroupName: !Sub "${DBInstanceIdentifier}-subnetgroup"
      SubnetIds:
        - !ImportValue subnet-private-a
        - !ImportValue subnet-private-b
        - !ImportValue subnet-private-c

  DBInstancePassword:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: !Sub "${DBInstanceIdentifier}-master-instance-password"
      Description: !Sub "The master instance password for the ${DBInstanceIdentifier} RDS database"
      GenerateSecretString:
        SecretStringTemplate: !Sub '{"username": "${DBInstanceMasterUsername}"}'
        GenerateStringKey: "password"
        PasswordLength: 20
        ExcludeCharacters: ':/@"\;`%$'''

  SecretDBInstanceAttachment:
    DependsOn: DBInstancePassword
    Type: AWS::SecretsManager::SecretTargetAttachment
    Properties:
      SecretId:
        Ref: DBInstancePassword
      TargetId:
        Ref: DBInstance
      TargetType: AWS::RDS::DBInstance

  DBInstanceRotationSchedule:
    DependsOn: SecretDBInstanceAttachment  
    Type: AWS::SecretsManager::RotationSchedule
    Properties:
      SecretId:
        Ref: DBInstancePassword
      HostedRotationLambda:
        RotationType: PostgreSQLSingleUser
        RotationLambdaName: !Sub "SecretsManager-Rotation-${DBInstanceIdentifier}"
        VpcSecurityGroupIds: !Ref SecurityGroup
        VpcSubnetIds:
          Fn::Join:
          - ","
          - - !ImportValue subnet-private-a
            - !ImportValue subnet-private-b
            - !ImportValue subnet-private-c
      RotationRules:
        Duration: 2h
        ScheduleExpression: 'cron(0 1 ? * SUN#1 *)'
		
  DBInstance:
    DependsOn: DBInstancePassword
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: !Ref DBAllocatedStorage
      AllowMajorVersionUpgrade: false
      AutoMinorVersionUpgrade: true
      BackupRetentionPeriod: !Ref DBBackupRetentionPeriod
      CopyTagsToSnapshot: True
      DBInstanceClass: !Ref DBInstanceType
      DBName: !Ref DBName
      DBInstanceIdentifier: !Ref DBInstanceIdentifier
      DBParameterGroupName: !Ref DBParameterGroup
      DBSubnetGroupName: !Ref DBSubnetGroup
      DeletionProtection: true
      Engine: postgres
      EngineVersion: !Ref DBEngineVersion
      MasterUsername: !Ref DBInstanceMasterUsername
      MasterUserPassword: !Join [ '', [ '{{resolve:secretsmanager:', !Ref DBInstancePassword, ':SecretString:password}}' ] ]
      MasterUserSecret: 
        SecretArn: !Ref DBInstancePassword
      MonitoringInterval: 60
      MonitoringRoleArn: !Join [ "", [ "arn:aws:iam::", !Ref "AWS::AccountId", ":role/rds-monitoring-role" ] ]
      MultiAZ: !Ref DBMultiAZ
      PreferredBackupWindow: !Ref DBPreferredBackupWindow
      PreferredMaintenanceWindow: !Ref DBPreferredMaintenanceWindow
      PubliclyAccessible: false
      StorageEncrypted: true
      StorageType: gp2
      VPCSecurityGroups:
        - !Ref SecurityGroup

Adding a read replica

Its possible to create a read replica from the primary database to allow for off loading reads from the primary database. This is especially useful if the reads are large reads or computationally expensive that might impact the performance of the primary database. When a read replica is created, the instance is seen as a separate database but it only supports reads - not writes. The read replica is kept synchronised with the primary. However - there can be a small delay. In testing with db.t3.micro instances, this delay seemed to be approximately 2-3 minutes. Larger instance sizes are likely to be faster - just be aware that the synchronisation between primary and the read replica is not real time. To add the additional resources to the template to create a read replica of the primary database, add the following;

  ReadReplicaDBInstance:
    DependsOn: DBInstance
    Type: AWS::RDS::DBInstance
    Properties:
      SourceDBInstanceIdentifier: !GetAtt DBInstance.DBInstanceArn
      AllocatedStorage: !Ref DBAllocatedStorage
      AllowMajorVersionUpgrade: false
      AutoMinorVersionUpgrade: true
      CopyTagsToSnapshot: True
      DBInstanceClass: !Ref DBInstanceType
      DBInstanceIdentifier: !Sub '${DBInstanceIdentifier}-read-replica'
      DBParameterGroupName: !Ref DBParameterGroup
      DBSubnetGroupName: !Ref DBSubnetGroup
      DeletionProtection: true
      Engine: !Ref DBEngine
      EngineVersion: !Ref DBEngineVersion
      MonitoringInterval: 60
      MonitoringRoleArn: !Join [ "", [ "arn:aws:iam::", !Ref "AWS::AccountId", ":role/rds-monitoring-role" ] ]
      PreferredMaintenanceWindow: !Ref DBPreferredMaintenanceWindow
      PubliclyAccessible: false
      StorageEncrypted: true
      StorageType: gp2
      VPCSecurityGroups:
        - !Ref SecurityGroup

Once the template is deployed, the read replica will appear associated with the primary database similar to the example below.

AWS RDS console showing the read replica linked to the primary instance. Teh failover instance is not displayed and is somewhat hidden by AWS

Its worth noting that the test-postgresqldb above has a failover instance in ap-southeast-2a but it does not appear as an instance in the RDS console. The details for the primary will reference the failover, so you can see it has a failover, but at first glance you may overlook it.

Processes that only read from the database can then connect to the read replica directly.

Adding an RDS Proxy

AWS Lambdas that have spiky or unpredictable workloads can scale out very rapidly. When these Lambdas access a database, they can quickly compete for the limited database connections available - resulting in failures due to insufficient connections. An RDS Proxy can assist with managing a shared connection pool between the Lambdas and the RDS instance.

To add an RDS Proxy in front of the primary, start with adding secrets to Secrets Manager for each of the database users. In the database thats been created above there are only two users, an administrator user role with access to a few specific tables as well as delete permissions to the standard data tables, and a standard user who has limited access to the standard data tables with no delete permissions. These are database users, and different Lambdas access the tables using either the administrator role or the standard user role with the user specified in the connection strings used for the database connections. For RDS Proxy to be able to act as an intermediary, it will need access to these two users passwords stored in Secrets Manager

  DBAdminUserPassword:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: !Sub "${DBInstanceIdentifier}-administrator-password"
      Description: !Sub "The administrator password for the ${DBInstanceIdentifier} RDS database (differs from the master password)"
      GenerateSecretString:
        SecretStringTemplate: !Sub '{"username": "administrator"}'
        GenerateStringKey: "password"
        PasswordLength: 20
        ExcludeCharacters: ':/@"\;`%$'''

  DBStandardUserPassword:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: !Sub "${DBInstanceIdentifier}-standard-user-password"
      Description: !Sub "The standard user password for the ${DBInstanceIdentifier} RDS database (differs from the master password)"
      GenerateSecretString:
        SecretStringTemplate: !Sub '{"username": "standard_user"}'
        GenerateStringKey: "password"
        PasswordLength: 20
        ExcludeCharacters: ':/@"\;`%$'''

I have not included the additional steps to be able to schedule rotation of these passwords for the purpose of simplicity, but rotation for these users should also be considered. When doing so, consideration should be given to handling the case where a Lambda may be executing when the rotation occurs. Because each call to retrieve a secret from Secrets Manager costs a small amount, you certainly do not want to be reading the password with each lambda invocation, particularly if the lambda is highly concurrent and executing dozens or hundreds of times a minute! (I've hard horror stories of misconfigured lambdas causing multi thousand dollar bills over a weekend due to repeated reads from Secrets Manager). The better approach is to read the secret once for the lifetime of the lambda - however if the secret is rotated during the lifetime of the lambda then the lambda needs to account for this and refresh its local copy of the secret on a connection failure. Anyway - I digress.

Next is to add an IAM Role for the RDS Proxy to allow the proxy to read the secrets added above.

  DBProxyRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
        - Effect: Allow
          Principal:
            Service:
            - rds.amazonaws.com
          Action:
          - sts:AssumeRole
      Path: "/"
      Policies:
        - PolicyName: secretAccess
          PolicyDocument:
            Version: 2012-10-17
            Statement:
            - Effect: Allow
              Action:
              - secretsmanager:GetResourcePolicy
              - secretsmanager:GetSecretValue
              - secretsmanager:DescribeSecret
              - secretsmanager:ListSecretVersionIds
              Resource:
              - !Ref DBAdminUserPassword
              - !Ref DBStandardUserPassword
        - PolicyName: secretListAccess
          PolicyDocument:
            Version: 2012-10-17
            Statement:
            - Effect: Allow
              Action:
              - secretsmanager:GetRandomPassword
              - secretsmanager:ListSecrets
              Resource: "*"

Note: I'm not entirely sure that the proxy requires the ability to list all secrets (secretsmanager:ListSecrets), or generate a new password (secretsmanager:GetRandomPassword) but this did come from AWS documentation. At some stage I'll test to confirm that this is (or is not) in fact needed. For my testing of this process however it was left in for now.

Finally, the following is added to the template to create the RDS Proxy.

  DBProxy:
    DependsOn: DBProxyRole
    Type: AWS::RDS::DBProxy
    Properties:
      DebugLogging: true
      DBProxyName: !Sub '${DBInstanceIdentifier}-proxy'
      EngineFamily: POSTGRESQL
      IdleClientTimeout: 120 # this should be adjusted depending on your use case. 120 == 2 minutes which may be too short for some
      RoleArn:
        !GetAtt DBProxyRole.Arn
      Auth:
        - {AuthScheme: SECRETS, SecretArn: !Ref DBAdminUserPassword, IAMAuth: DISABLED}
        - {AuthScheme: SECRETS, SecretArn: !Ref DBStandardUserPassword, IAMAuth: DISABLED}
      VpcSubnetIds:
        - !ImportValue subnet-private-a
        - !ImportValue subnet-private-b
        - !ImportValue subnet-private-c
      VpcSecurityGroupIds: 
        - !Ref SecurityGroup

  ProxyTargetGroup:
    Type: AWS::RDS::DBProxyTargetGroup
    Properties:
      DBProxyName: !Ref DBProxy
      DBInstanceIdentifiers: [!Ref DBInstance]
      TargetGroupName: default
      ConnectionPoolConfigurationInfo:
          MaxConnectionsPercent: 100
          MaxIdleConnectionsPercent: 50
          ConnectionBorrowTimeout: 120

Lambdas or other services (EC2 services for example) then connect to the RDS Proxy rather than the primary database directly. This will (probably) be gone into more details in an upcoming brain dump, but the general gist would be the AWS Lambda (etc.) would use a connection string similar to the following (note the Host set to the RDS Proxy ID. And no, the database doesn't exist anymore.);

Host=test-postgresqldb-proxy.proxy-cokzyie9kbxs.ap-southeast-2.rds.amazonaws.com;Port=5432;Username=standard_user;Password=j,.GLyi_h6~4{e:T2roX;Database=testpostgresqldb;Timeout=14;Pooling=true;MinPoolSize=100;MaxPoolSize=200;

And there you have it. As stated in the tldr; section at the top, the entire Cloudformation template thats been built here is available at the Github Gist linked.

The full stack is probably a bit more involved than what a small prototype or hobby project requires, and almost certainly insufficient for a full sized enterprise application, but should be a good start point (I'd like to think) for a reasonably well secured, somewhat highly available and performant RDS instance. Enjoy.