Preface

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.

Architecture

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.

The Role

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

---
# file: roles/aws/rds/tasks/main.yml

- name: Manage application Grants
  mysql_user:
    user: "{{ item.user }}"
    password: "{{ item.password }}"
    host: "{{ item.host }}"
    priv: "{{ item.privileges | join('/') }}"
    append_privs: true
    encrypted: yes
    login_host: "{{ database_aws_secret.host }}"
    login_user: "{{ database_aws_secret.username }}"
    login_password: "{{ database_aws_secret.password }}"
  with_items: "{{ required_application_grants }}"
  vars:
      database_aws_secret: "{{ lookup('aws_secret',aws_rds_identifier+'-admin-password', region='eu-west-1') }}"
Tasks to add mysql_users using the AWS Secret lookup plugin.

The Secret

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.

{
  "username": "admin",
  "engine": "mysql",
  "port": 3306,
  "host": "app-db-identity.ihjikovsaphg.eu-west-1.rds.amazonaws.com",
  "password": "thisIsSuperSecretShhhhhhhh",
  "dbname": "common",
  "dbInstanceIdentifier": "app-db-identity"
}
Example Secret Contents.

The Privileges

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.

---
# file: roles/aws/rds/vars/main.yml

# These are for the application on the web servers
required_application_grants:
  - user: appuser
    password: "{{ aws_rds_user_hash_app }}"
    privileges:
    - 'db1.*:SELECT,INSERT,UPDATE,DELETE,CREATE TEMPORARY TABLES,SHOW VIEW,EXECUTE,EVENT,TRIGGER,LOCK TABLES,REFERENCES'
    - 'db2.*:SELECT,INSERT,UPDATE,DELETE,CREATE TEMPORARY TABLES,SHOW VIEW,EXECUTE,EVENT,TRIGGER,LOCK TABLES,REFERENCES'
    - 'db3.*:SELECT,INSERT,UPDATE,DELETE,CREATE TEMPORARY TABLES,SHOW VIEW,EXECUTE,EVENT,TRIGGER,LOCK TABLES,REFERENCES'
    host: "{{ aws_rds_application_subnetmask }}"
    state: 'present'
  - user: mailuser
    password: "{{ aws_rds_user_mailuser }}"
    privileges:
    - 'maildb.*:SELECT,INSERT'
    host: "{{ aws_rds_application_subnetmask }}"
    state: 'present'
  - user: debugger
    password: "{{ aws_rds_user_debuggeruser }}"
    privileges:
    - '*.*:SELECT,INSERT,UPDATE,DELETE'
    host: "{{ aws_rds_application_subnetmask }}"
    state: 'absent'
RDS role vars file.

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.

---
# file: group_vars/myapplication

aws_rds_application_subnetmask: 10.71.0.0/255.255.255.0

aws_rds_user_hash_appuser: '*90BA3AC0BFDE07AE334CA523CB27167AE33825B9'
aws_rds_user_mailuser: '*B094120DF11C8B375C273D283E1467FAB97E32EC'
aws_rds_user_debuggeruser: '*A826E9B5669AA7C72E914F1C2A771DA015216435'

aws_rds_identifier: 'app-db-identity'
myapplication group_var file.

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.

---
# file: aws_rds.yml

- hosts: aws-rds
  become: false
  gather_facts: no
  roles:
    - aws/rds
Role definition file.
[aws_rds:children]
myapplication

[myapplication]
myapplication-db-master ansible_connection=local
host list file. hosts.aws_rds

We can then simply run the playbook from within that directory using the following making sure to run in checkmode (-C) first.

ansible-playbook --limit myapplication-db-master -i hosts.aws_rds aws_rds.yml -DC

In Conclusion

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.

atownsend247/blog-provisioning-rds-access-ansible
https://blog.atathome.me/provisioning-rds-access-via-ansible/ - atownsend247/blog-provisioning-rds-access-ansible