Building out an AWS postgreSQL RDS instance
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.
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.