Manage Your MySQL Database Credentials with Vault

Any software system requires secrets to run, be it credentials to communicate with databases, tokens to access resources, or encryption keys that need to be distributed. Secrets management means to deal with all kinds of secrets in a structured and secure way. In this blog entry we will show how to use Hashicorp Vault to provision database credentials in MySQL and how to access the database from an example application using these credentials. We will run MySQL, Vault, and our example application with docker compose in a fully self-contained setup.

Why Homogenise Your Workflows Around a Central Secrets Management System

There are multiple reasons for homogenising secrets handling:

    • Security

If your secrets are currently stored unencrypted or even checked into git then switching to a central secrets management system will without doubt increase security.

    • Process

Most likely the process for handling secrets varies significantly depending on who in an organisation is assigned a particular task. This is improved by offering one solution to handle secrets, i.e. a standardised way of handling sensitive information.

    • Automation

Last but not least the increased push for automation and the growing adoption of development tools in the ops area (DevOps) is a strong incentive to minimise manual workflows (and secret generation and distribution usually is a manual process).

Secrets management has been gaining momentum in the context of increasingly dynamic environments fueled by the ubiquitiousness of cloud computing. Major cloud providers offer secrets management such as KMS (aws) or Key Vault (azure) as managed services. Self hosted solutions include Hashicorp Vault and Cloudfoundry’s CredHub.

Vault

Hashicorp’s Vault is a tool for securely accessing secrets. The ability to host Vault on premise and its relative maturity as well as its good fit in the overall Hashicorp ecosystem can be seen as compelling arguments and has made it the tool of choice for many. It has the following capabilities:

  • Secure secret storage
  • Dynamic secrets
  • Data encryption
  • Leasing and renewal
  • Revocation

Generally speaking Vault is based on providing tokens to both users and applications. Every token is connected to policies which specify what actions can be performed using the token (i.e. what secrets to read or write). In our context both how to acquire a token and how to use a token to retrieve database credentials are relevant.

We will focus on dynamic secrets (i.e. secrets generated on-demand for some systems, such as AWS or SQL databases) and touch on leasing and renewal. Together these components provide a strong set of tools for secrets management.

Database Credentials Management

The traditional way of creating database users and distribution of their passwords is quite static: a dba creates the user manually and picks their favourite way of sending a user the password (tools for this include but are not limited to sending an email, writing a post-it note, or pasting in chat systems). This is often the end of life-cycle management. The credentials are seldom changed afterwards.

Not only is this process very static, often leading to database credentials that virtually never expire, it is also very ill-suited for automation of any kind. And this is exactly where dynamic secrets come in.

Dynamic secrets allow:

  • Applications to stop hardcoding database credentials
  • Unique credentials per service, facilitating easier auditing
  • Central revocation systems based on leases

In our context we show how to apply this to handling and life-cycle management of database credentials.

Docker Compose Setup

In order to have a fully working example we show how to deploy:

  • MySQL server container
  • Vault container
  • Vault admin container
  • Example application container

Our example shows how to run a fully containerised setup and is fully self-contained. In the following we briefly describe each component in turn before we show how to actually run the example using docker compose later on.

MySQL Server Container

We first deploy a MySQL server container. We run the container and set up an initial user by letting the following sql file be executed inside the container:

CREATE DATABASE dbwebappdb;
CREATE USER 'vault'@'%' IDENTIFIED BY 'vault';
GRANT ALL PRIVILEGES ON dbwebappdb.* TO 'vault'@'%' WITH GRANT OPTION;
GRANT CREATE USER ON *.* to 'vault'@'%';

The only MySQL user in that container is the vault one; it will be used by Vault to generate new users on demand and requires appropriate privileges.

Vault Container

The Vault component is deployed in development mode. This means that there is no persistent storage of secrets and that the Vault server is unsealed by default (usually a Vault server needs to be manually unsealed by one or more operators before being functional). Any production deployment should be significantly hardened (tls, encryption keys, storage backend, network level security).

Vault Admin Container

In addition we use a docker container to “replace” a human operator. Typical admin tasks are to create initial accounts in the database and the administration of Vault itself. This means that we reuse the Vault root token and pass it to this container. Hardcoding and passing credentials this way should not be done for anything else than an example setup. All operations performed by the admin container should be done by an actual admin and measures should be taken to protect and possibly revoke the root token once Vault is set up. This is the step that requires most configuration, in our example we do:

  • Create a policy
  • Create a role used for authentication
  • Add a secret id to the role
  • Enable and configure the database secret backend

Vault access control is based on paths and permissions: a particular secret is stored at a given path and policies are permissions applied to that path (e.g. “/secrets/mysecret”). Our policy consists of two paths, one to authenticate to acquire a token, one to acquire database credentials using the token:


path "auth/approle/login" {
  capabilities = ["create", "read"]
}
  
path "database/creds/vault-mysql-role" {
  capabilities = ["read"]
}

This results in the following Vault calls (using the policy file we just wrote):


vault policy write dbwebapp /policies/dbwebapp-policy.hcl
vault write auth/approle/role/dbwebapp policies=dbwebapp role_id=dbrole
vault write auth/approle/role/dbwebapp/custom-secret-id secret_id=testsecret1
  
vault secrets enable database
vault write database/config/mysql-database \
    plugin_name=mysql-database-plugin \
    connection_url="{{username}}:{{password}}@tcp(mysql-server:3306)/" \
    allowed_roles="vault-mysql-role" \
    username="vault" \
    password="vault";
vault write database/roles/vault-mysql-role \
    db_name=mysql-database \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT SELECT ON dbwebappdb.* TO '{{name}}'@'%';" \
    default_ttl="1h"

Basically we say that our app is allowed to acquire a token using the approle id and secret. Once the application has a token, it is allowed to get database credentials. The credentials are provisioned in the specified database server. When not renewed (a periodic api call using the token) the credentials expire after one hour.

As a result we have everything we need to get started on the application side of things.

Example Application Container

We extended our example client application (a simple application connecting to a database) to be able to talk to Vault. It now basically:

  • Waits for Vault to be available
  • Authenticates to Vault using the given role id and secret id
  • Exchanges it for token
  • Exchanges token for database credentials
  • Connects to database
  • Extends the lease on the token (to avoid the credentials to be revoked by Vault)

Now imagine we deploy multiple instances of our application. We now no longer share the database credentials across deployments. We always know exactly which credentials are used to access the database. All unused database credentials get revoked by Vault after one hour.

Running the Example

Run the example by checking it out from github and running docker-compose:

git clone https://github.com/neumayer/mysql-docker-compose-examples.git
cd mysql-docker-compose-examples/vault-mysql
docker-compose pull
docker-compose up

Docker compose will automatically download the required images and run the containers (the pull command will update old versions of images you might have locally, both mysql/mysql-server:8.0 and neumayer/dbwebapp have been updated over the last couple of weeks). The output from the example app should be something like this:

dbwebapp_1      | 2018/04/27 08:14:33 Pinging http://vault:8200.
dbwebapp_1      | 2018/04/27 08:14:33 Connected to http://vault:8200.
dbwebapp_1      | 2018/04/27 08:14:33 Connecting to v-approle-vault-mysq-5z2y56v4xps:xxxx@tcp(mysql-server:3306)/
dbwebapp_1      | 2018/04/27 08:14:33 Pinging mysql-server.
dbwebapp_1      | 2018/04/27 08:14:33 Renewing lease database/creds/vault-mysql-role/69d4ae80-f873-5a20-069a-ec2df6a9a2d6.
dbwebapp_1      | 2018/04/27 08:14:33 Connected to mysql-server.
dbwebapp_1      | 2018/04/27 08:14:33 Starting dbwebapp server.
dbwebapp_1      | 2018/04/27 08:14:33 Scheduling regular renewal for lease database/creds/vault-mysql-role/69d4ae80-f873-5a20-069a-ec2df6a9a2d6 every 58m20s

Our application is a bit lenient when it comes to the availability of services we rely on. If they’re not up yet, we try a few more times. Once they are we go trough the authentication exchanges and connect to the database. The output shows that we wait for Vault to become available first. We then retrieve our database credentials (Vault created a v-approle-vault-mysq-5z2y56v4xps user for us) and connect to the database. While we are up and running we renew the lease for the token.

Outlook

We showed how to use MySQL in a dynamic database credential setting backed by Vault. Everything is deployed in containers and we use the official MySQL image. Employing a process such as described here will achieve what we set out to do:

  • Applications to stop hardcoding database credentials

Instead of giving applications database credentials directly we can deploy all applications the same way, backed by a secrets engine.

  • Unique credentials per service, facilitating easier auditing

Every instance of a service has unique credentials to the database, helping with auditing and discouraging the creation of unlimited credentials.

  • Central revocation systems based on leases

Vault tokens can be revoked, leading to the MySQL credentials being revoked as well. Even credentials whose tokens are not explicitly revoked will expire after a certain period of time unless the leases are renewed (one hour in this example).

While the initial cost of setting up a secrets management tool such as Vault might seem considerable there are many use cases for dynamic credential provisioning. A common way of handling secrets across an organisation could have a positive impact on the overall engineering culture and facilitate easier security auditing. In any case, manual secrets management can be seen as a blocker for automation, so there’s a clear scalability aspect.

In a production setting all components should be hardened from a security perspective, and especially volume management should be in place before looking at deployments. Both MySQL and Vault need to keep their state stored persistently.

References

Dbwebapp on github

Docker compose vault MySQL example

Demo 12 Factor application that utilizes Hashicorp tools

Hashicorp Vault

Authenticating Applications with HashiCorp Vault AppRole

KMS (aws)

Key Vault (azure)

Cloudfoundry CredHub

Robert Neumayer

About Robert Neumayer

Software engineer with a passion for infrastructure and automation. I have hands-on experience in all steps of the development process, particularly focusing on streamlining deployment processes and supporting agile teams. I am working on release engineering, devops integration, and deployment of MySQL products across the MySQL engineering organisation.

Leave a Reply

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

Please enter * Time limit is exhausted. Please reload the CAPTCHA.