Dynamic MySQL credentials with vault

Posted in: MySQL, Technical Track

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.

email

Interested in working with Derek? Schedule a tech call.

About the Author

Director of Managed Services, Open Source Databases
Derek Downey is the Director of Managed Services for the OpenSource Database practice at Pythian, helping to align technical and business objectives for the company and for our clients. Derek loves automating MySQL, implementing visualization strategies and creating repeatable training environments.

3 Comments. Leave new

Alvin Chettiar
June 14, 2016 4:34 am

Hi,

The vault token that is used for each request is still exposed in the code which falsifies the idea about creating encrypted credentials. If anyone gets the root access to my server, they can use the use the vault token to get data and create dynamic mysql users. Is there a method to work around this issue? I cannot use vault if there is no work around.

Reply

@Alvin:
you’re running up against the ages old question of “do I want to expose secrets and automate?” and “how much configuration do I allow separate from my code?”.

Here are options:
1. have Puppet/Chef/Salt/Ansible put the token into a environment variable for your code, making it external to the source code.
2. Use SSL Client Auth. This allows you to ship the “credentials” with your code, but obviously it’s only more secure than exposing the token in the code if you use a passphrase for the secret key that you enter manually each time you start your daemon.

Vault is more of an audit mechanism than secure *automated* storage.

Reply

@Alvin Yes, if someone gets root access to your app servers, they can get data and create the same type of MySQL user your apps can create. That’s why I’d recommend only granting the app access to create very-specific privileges, and implement historical monitoring on the number of vault users created. Also, I would recommend not storing tokens in environment variables or in the code, where possible. For example, you can use consul-template (https://www.hashicorp.com/blog/using-vault-with-consul-template.html) depending on your app’s language.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *