Cloudformation template for a postgreSQL AWS RDS database, optional Multi-AZ deployment, read replica, RDS Proxy, scheduled credential rotation

postgresdb.yaml

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
  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
  DBInstanceMasterUsername:
    Description: Master username
    Type: String
    MinLength: '0'
    MaxLength: '255'
    Default: dbo
  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'

Metadata:
  AWS::CloudFormation::Interface:
    ParameterGroups:
    - Label:
        default: DB Details
      Parameters:
      - DBEngineVersion
      - DBInstanceType
      - DBParameterGroup
    - Label:
        default: DB Configuration
      Parameters:
      - DBInstanceIdentifier
      - DBName
      - DBInstanceMasterUsername
      - DBMultiAZ
    - Label:
        default: DB Storage
      Parameters:
      - DBAllocatedStorage
    - Label:
        default: DB Backup Retention
      Parameters:
      - DBPreferredMaintenanceWindow
      - DBPreferredBackupWindow
      - DBBackupRetentionPeriod

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
        - IpProtocol: tcp
          FromPort: 5432
          ToPort: 5432
          SourceSecurityGroupId: sg-0xxxxxxxxxxxxxxxx #replace this
          Description: Postgresql default port (Internal) for access from lambda security group
      SecurityGroupEgress:
        - IpProtocol: tcp
          FromPort: 0
          ToPort: 65535
          CidrIp: 0.0.0.0/0
          Description: All outbound traffic
      VpcId: !ImportValue 'vpc-id' #replace this

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

  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 #replace this
            - !ImportValue subnet-private-b #replace this
            - !ImportValue subnet-private-c #replace this
      RotationRules:
        Duration: 2h
        ScheduleExpression: 'cron(0 0 ? * 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

  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

  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": "user"}'
        GenerateStringKey: "password"
        PasswordLength: 20
        ExcludeCharacters: ':/@"\;`%$'''

  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: "*"

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

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