When creating MariaDB RDS (Relational Database Service) databases it will automatically create a main admin user which you specify a password upon creation. You can also store this password in AWS Secrets Manager which has added functionality of enabling automatic secret rotation which I will cover in another blog article.
However for initially provisioning and ongoing management of these I wasn't able to find an AWS elegant solution. I had been previously managing virtual machine database access for personal projects through Ansible and the MySQL Module. Using this knowledge I looked about a way of automating the retrieval of the administrator password from AWS Secrets Manager and then feeding that into an Ansible playbook so it can automate the provisioning of database access.
The above diagram shows the flow Me invoking the Ansible Playbook, which will retrieve the database credential information from AWS Secrets Manager and then provision access against the RDS instance.
Diving into the Ansible role, there is a handy lookup plugin called aws_secret This will retrieve a Secret from Secrets Manager based on the parameters supplied to it. This allows us to simply specify the secret name and then have it available to the task by using it along side vars, like the code example below
What should I be storing in the secret? Is it just the password? The short answer is likely no, but does depend on your use case. If you're looking to make use of the Automatic Secret Rotation it is best practice to store other data alongside the secret, the following is what I tend to store alongside the password such that it can work with the Secret Rotation Lambda.
So we've touched upon the Ansible role itself, the Secret it is going to use to gain access, now to talk about the credentials its going to create. You may have noticed in the Role code there was a reference to the
required_application_grants variable. This is an array of objects that will be iterated over and applied, I have stored these in
roles/aws/rds/vars/main.yml as they make use of the
encrypted: yes property which refers to the MySQL Hash of the password.
The other variables worthy of note are the
application_access_subnetmask and the password hashes, I have them stored in
group_vars generally but I will drop in an example file below.
Running the Playbook
Now we have all of the components in their constituent parts how do we plug them all together?
So to run the Playbook all we need to do is setup a Role definition file and a host list entry.
There is a prerequisite of this I have missed out and that is communication with both the AWS RDS instance and using the correct IAM user. For this to successfully work you need to have access to both the direct RDS instance with the correct Security Group parameters and the IAM user locally (using the AWS CLI) has access to retrieve the secret from Secrets Manager.
To test the secrets manager access you can call this directly using the AWS CLI
aws secretsmanager get-secret-value --secret-id 'thesecretkey'. Providing this returns the expected value you can then move forward with running the Playbook.
We can then simply run the playbook from within that directory using the following making sure to run in checkmode (
ansible-playbook --limit myapplication-db-master -i hosts.aws_rds aws_rds.yml -DC
As part of this post I have talked through the architecture of setting this process up, creating the Ansible role which will apply the provided MySQL grants, using the secret defined in AWS Secrets Manager directly to the RDS instance. I include below an example ansible role based on the above code snippets, which can easily be used as a base repository for your own databases once configured and expanded as required.