Dynamic MySQL credentials with vault

Recently I have been looking at the Vault project as a means to manage secrets for applications and end-users. One of the use cases that immediately drew my attention was the ability to create dynamic role-based MySQL credentials.

Why Dynamic MySQL Credentials?

There are a few reasons why dynamic credentials would be beneficial, all of which can be handled by Vault, including:

• The database environment is too large to manage individual users.
• A need to authenticate on an external service, such as LDAP or GitHub organization.
• Provide credentials to external resources, such as auditors or outside consultants that automatically expire.
• Compliance requirements for strict audit logs for database access.

A High-Level Overview of Vault

Vault is a fairly new project by HashiCorp, the folks behind projects such as Vagrant and Consul. The goal is to decouple the handling of secrets from applications to enforce access control, encryption standards, and create an audit trail.

There are several components to Vault:

• Authentication such as LDAP, GitHub or custom app-id.
• Authorization using path-based ACL policies.
• Encrypted storage backend using one of several options such as Consul, etcd, Zookeeper, S3, or MySQL.
• Secret backends that define how secrets are stored or generated. Options include MySQL and AWS IAM credentials, among others.
• Audit logging of token generation and secrets access.

To begin working with a Vault deployment, Vault must be initialized and unsealed. Unsealing Vault is a very important aspect of Vault’s security model, but is beyond the scope of this post.

After Vault is unsealed, users can begin interacting with Vault either by a CLI tool or HTTP API. Users, whether they are human or applications, are authenticated based on tokens. These tokens can be revoked at any time, be given a time-to-live (TTL) or a specific number of uses.

Authentication methods are associated with access control list (ACL) policies to define which secrets the token will have access to.

Certain secret backends can generate actual credentials upon request. This includes the MySQL secret backend, which creates users with specific grants based on their role and passes only the generated user credentials to the requesting token.

Creating Dynamic Credentials with Vault

Let’s generate a read-only MySQL credential using Vault. To follow along with this exercise, you will need to install Docker Toolbox and clone my vault repository. The docker-compose file will look like this:

Next, we will bring up the vault and mysql containers. 

The initiate-vault.sh script will initiate and unseal the vault, then set the environment variable ‘VAULT_TOKEN’ that we will use later. It also creates a ‘vault’ alias that allows us to interact with the Vault CLI within the docker container. The output will look like this:

Now that Vault is unsealed, we can create the MySQL backend. Vault must be provided with the credentials of a MySQL user with GRANT OPTION privilege. For the purpose of our example, we will use the root user.

With the MySQL backend configured, we can finally create our dynamic credentials. The generated credentials will be valid for 10 minutes, then expire.

Final Thoughts

Overall, Vault is a promising new technology to decouple secrets from the application. Perhaps we can use it to begin to move beyond the idea that it’s OK to store credentials in environment variables.

Of course, implementing Vault does add yet another dependency that must be highly available. Care must be taken to deploy Vault in a fault-tolerant manner.

One concern I have with the current workflow of secret handling is that each GET request to /mysql/readonly creates a new user. So a busy environment could thrash the database server with CREATE USER USER commands, which will be cleaned up later with DROP USER commands.

The way I expected it to work is that if the same Vault token requested the same credential endpoint, Vault would return an unexpired credential instead of generating an entirely new user.

To work around this, the user must keep track of the ‘lease-id’ returned from the initial read request and renew the lease before it expires. The user can do this up until the lease_max period.

Discover more about our expertise with MySQL.