Using Terraform to create RDS (Amazon Aurora PostgreSQL) with features (IAM auth, rotating master pw)

Question

I have been given a task at work to create an RDS cluster module using Terraform that will allow for consumers to spin up their own clusters/dbs etc. This is all fairly straight forward but it is the second lot of requirements that has me pulling my hair out. The DBAs want to know how to do the following:

1. Store and rotate the master password in secrets manager.
2. Create additional dbs, users etc via automation (nothing is to be clickops'd).
3. Utilise IAM authentication so that users do not have to be created/auth'd.

I have looked at a number of different ways of doing this and as i'm fairly new to this, nothing seems to stick out as "the best solution". Would anyone be able to give me a rundown of how they may have approached a similar task? Did you store and rotate password using a lambda function or did you assign the master user to an IAM role? Are you using the TF postgres provider to create roles or did you write your own code to automate?

I really appreciate any guidance. Thanks heaps

Answer 1

The problem described is rather generic, but in my view you could keep almost everything under direct controll of terraform.

1. Store and rotate the master password in secrets manager.

Secret manager is the way to go. However, the password rotation will be an issue. When you enable rotation in AWS console, AWS magically provisions a lambda for you. If you don't use console, command line steps are a bit more involving as they require the use of aws serverless repo (SAR). Sadly, official support for SAR is not yet avaiable in terraform. Thus you would have to use local-exec provisioner to run aws cli to create rotation lambda as in the linked documentation using SAR.

2. Create additional dbs, users etc via automation (nothing is to be clickops'd).

As you already pointed out, the TF PostgreSQL Provider would the first thing to consider.

3. Utilize IAM authentication so that users do not have to be created/auth'd.

This can be enable using iam_database_authentication_enabled. But you should know that there are some limitations when using IAM auth. Most notably, only PostgreSQL versions 9.6.9 and 10.4 or higher are supported and your number of connections per second my suffer.

Answer 2

A follow up on point 1 for anyone in the future who wants to do a similar thing.

I ended up using a cloudformation_stack terraform resource to create the secret attachment and secret rotation - passing them parameter values from my terraform resources.

Works perfectly and easily switched out when/if terraform introduce these resources.