Automate RDS database management in CloudFormation

[MikeTheCanuck]

Summary

Create a reusable CloudFormation template for creation of RDS databases.

Tasks

• [x] build a working example of creating an RDS instance using a nested CloudFormation template
• [x] integrate SSM to pull in RDS creds dynamically
• [ ] refactor the SSM {{resolve}} statements to call a project-specific path

Definition of Done

RDS instances are successfully created with MasterUsername and MasterPassword parameters that are retrieved from SSM parameters whose path differs by project name e.g. /production/2019/RDS/{projectname}/MASTER_PASSWORD:1

[MikeTheCanuck]

This is the prerequisite to being able to migrate all the manually-created RDS instances to CloudFormation in #232

[MikeTheCanuck]

We're now down to how to pass into the rds-postgres.yaml the dynamic value of the SSM parameter path corresponding to each unique RDS instance.

For example, when defining the value of the MasterUserPassword property of the AWS::RDS::DBInstance, the RDS instance for the 2019 Sandbox application might be stored in an SSM Parameter known as MASTERUSERPASSWORD in the SSM parameter path /production/2019/RDS/Sandbox/, whereas the same parameter for the 2019 Transporation application might be stored at /production/2019/RDS/Transportation/.

This means at some point in the YAML templates for our HackO CF stack of stacks, we'll need to have CF evaluate "{{resolve:ssm-secure:/production/2019/RDS/sandbox/MASTERUSERPASSWORD:1}}".

What works:

• defining MasterUserPassword: "{{resolve:ssm-secure:/production/2019/RDS/rdstestinstance/MASTERUSERPASSWORD:1}}" in the rds-postgres.yaml template

What doesn't work:

• defining MasterPassword: "{{resolve:ssm-secure:/production/2019/RDS/rdstestinstance/MASTERUSERPASSWORD:1}}" in the master.yaml template, that gets passed in to rds-postgres.yaml template as a String parameter - CF returns the following error:

  SSM Secure reference is not supported in: [AWS::CloudFormation::Stack/Properties/Parameters/MasterPassword]

I'm understanding that to mean that when we try to run the stack with this configuration, CF evaluates the {{resolve}} statement in master.yaml, received back a SecureString value, tries to pass in a SecureString value to the rds-postgres.yaml template and that isn't allowed.

I tried changing the MasterPassword Parameter's Type in rds-postgres.yaml from String to SecureString but that didn't seem to make any difference - I believe this reference I caught in the docs here is why: https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/parameters-section-structure.html#parameters-section-structure-properties-type

AWS CloudFormation doesn't currently support the SecureString Systems Manager parameter type.

So at best I believe I have two options available if we continue to use SSM Parameter Store for RDS database password secrets storage:

1. Construct the "{{resolve}}" statement in master.yaml using the Join function, and then pass it into rds-postgres.yaml as a String Parameter
2. Pass in the project variable (e.g. "2019/RDS/sandbox", "2019/RDS/transportation") to rds-postgres.yaml as a String and then perform the Join either directly in the MasterUserPassword Property or in some interim variable outside the Property and assign !Ref variable to the Property.

I haven't found any patterns for either of these constructions, so I may just have to brute-force trial-and-error my way into something that works.

[MikeTheCanuck]

Note: you'll notice in Example 2 that AWS documents the supportability of using SSM-based secure strings for the RDS MasterUserPassword: https://aws.amazon.com/blogs/mt/using-aws-systems-manager-parameter-store-secure-string-parameters-in-aws-cloudformation-templates/

[MikeTheCanuck]

Brute-forced trial-and-error determination of a compliant !Join call is complete.

So, for the record:

Firstly, based on this article, I understood that the {{resolve}} statement in a CloudFormation template had to be explicitly surrounded with single or double quotes, like so:

MasterUserPassword: "{{resolve:ssm-secure:/production/2019/RDS/rdstestinstance/MASTER_PASSWORD:1}}"

And my own experiments bore out that without those quotes, the call wouldn't resolve correctly - like so:

MasterUserPassword: {{resolve:ssm-secure:/production/2019/RDS/rdstestinstance/MASTER_PASSWORD:1}}

(Edit: it turns out that a string inside curly braces in YAML is known as a "flow mapping".]

Secondly, Once I tried to construct a parameterized SSM Parameter Store path using the Fn::Join CF function, I noticed that I was getting 400 errors back from CF when trying to find those SSM Parameters:

Invalid master user name (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue; Request ID: 487f7bd2-3527-4f68-9468-17cf10e0a113)

On other attempts at the combinatorics of escaping the " character, I was getting the following error:

The parameter MasterUserPassword is not a valid password. Only printable ASCII characters besides '/', '@', '"', ' ' may be used.

I finally determined that the output of the Fn::Join operation not only doesn't require the surrounding quotation marks, but in fact causes CF to fail (throwing one of the above error messages). I'm going to guess that the output of th eFn::Join (aka !Join) operation already has what it needs without having to fence off the string with quotes.

Thus, these two CF Properties are deemed equivalent in terms of practical success in creating an RDS instance:

MasterUsername: '{{resolve:ssm:/production/2019/RDS/rdstestinstance/MASTER_USERNAME:1}}'
MasterUsername: !Join [ "", ["{{resolve:ssm:", !Ref SsmParameterPath, "/MASTER_USERNAME:1}}"]]