I’ve been talking a lot about automation and its place in the database industry.

Today, I want to show one example of automation that comes with the complexity of horizontal scaling.

We’ll see how to use Ansible to manage database users.

Database user management overview

Look, it’s a good idea to have a repeatable process for managing database users.

You can do this for security reasons to have an audit trail, or due to complexity of horizontal scaling.

Whatever the reasons, if you are looking to simplify your database user management look no further than Ansible.

All you have to do is install Ansible and the PyMySQL package from pip, and you are good to go.

Database user management with ansible

To start with, define your database inventory so Ansible can connect.

The inventory defines a docker setup of two replicated environments and a ProxySQL instance.

With a simple playbook file that handles encrypted passwords easily, you can add your users to your database.

---
- hosts: sources
  connection: local
  gather_facts: no
  tasks:
    - name: "Configure user"
      community.mysql.mysql_user:
        name: "{{ item }}"
        host: "{{ users[item].host }}"
        password: "{{ users[item].password }}"
        priv: "{{ users[item].priv }}"
        state: present
        login_host: "127.0.0.1"
        login_port: "{{ mysql_port }}"
        login_user: "root"
        login_password: "{{ root.password }}"
      with_items: "{{ users }}"

Ansible will execute the tasks idempotently. This guarantees the state of the user, and won’t modify anything if it doesn’t need to.

If the users change, just re-run the playbook.

This sample playbook does not currently handle removal of users. So that is left as an exercise to the reader.

Things to consider

I would highlight a couple of points from this playbook.

First, make sure to apply user changes in MySQL to the source database instance, not the replicas.

Otherwise, Ansible may apply the user first to the replica and then replication can break when its applied to the source.

Second, my playbook has encrypted the user passwords using ansible_vault.

In the demonstration, the vault password to decrypt the password file is given at runtime.

$ ansible-playbook --ask-vault-pass -e @passwords.yml set_users.yml

This would need to be modified if the playbook is running in a non-interactive method, such as a cron job.

Conclusion

You might be looking for a way to manage users in your database due to security reasons or scaling complexity, or something else.

I’ve shown how simple it is to use Ansible to make the process repeatable. And putting the playbook in a repository will make user changes auditable.

Let me know in the comments if you found this useful.

And if you have other suggestions to simplify database user management, I’d love to hear them!