As an intro to his performance act, an “old school” entertainer Victor Borge once famously asked the audience: “Do you care for piano music?“, which was greeted by a crowd, only to be immediately followed by a self-ironic punch line – “Too bad.”

Security topics share a similar notion – namely, once you start caring for them, you get exposed to an almost unmanageable set of constraints, issues and hard choices. Thankfully, MySQL might provide you with some of the tools to help bridging that gap between your current setup and the proverbial security heaven. Enter Security 101 – taking care of your cryptographic keys.

Hiding secrets?

Most of your data lays cozily stored in some kind of database, perhaps in a cloud, or on your on-premise infra. One will employ many steps to better protect it – TLS client connections, password complexity/rotation, setting privileges, audit logging… You can also make your table data encrypted – in MySQL, for instance, you can use InnoDB Data-at-Rest encryption. This will ensure data within your database storage files is inaccessible without the encryption keys.

Now, we just need to store the keys somewhere. The role of the key storage/management in MySQL is handed over to the MySQL Keyring facility, which supports a unique interface to several key store backends, ranging from a simple file storage to a KMIP compatible backend. Today, we’re adding initial support for the ubiquitous Hashicorp Vault server to our Enterprise suite.

Say hello to keyring_hashicorp plugin!

Hashicorp Vault, from the mouth of the authors, is “a tool for securely accessing secrets”. Aside from storing and retrieving secrets (e.g. keys and/or similar sensitive data), it also supports a range of security features such as Dynamic Secrets, Data Encryption, Revocation – to name a few.

Starting with MySQL 8.0.18, among many other features, we’re adding keyring_hashicorp plugin which uses Hashicorp Vault as it’s backend. This is a short overview of the plugin features:

• implements MySQL Keyring interface for key management
• enables InnoDB to use it for storing table encryption keys
• supports Hashicorp Vault KV engine employing file backend
• uses Hashicorp Vault AppRole authentication style
• supports HTTPS link to Vault with optional CA verification
• provides optional in-memory key caching feature
• supports migration from, and to other existing backends

Let’s put it in action

First we need to setup the Hashicorp Vault instance. You’ll need to obtain the binary from the Hashicorp site. Although Vault supports development mode that enables access over HTTP connection (which should not be considered secure), the keyring_hashicorp plugin is designed to support only HTTPS. In order to setup a production-like Vault instance, you’ll need to create a key pair and a certificate for it. Also, your certificate authority will need to sign the Vault certificate itself. Plenty of resources on that topic are already available (including our own MySQL Reference Manual section), so we wont regurgitate them here.

Once you’ve obtained those files, it is required to prepare a configuration file (config.hcl) for Hashicorp Vault server:

Naturally, the filepaths should be replaced to match your current setup. After you’ve created the configuration file, issuing the following command should get your Vault instance happy and running:

Note: Depending on your configuration, Vault might complain about mlock syscall not working/available. Disabling mlock is not recommended for a production instance, but you may temporarily alleviate the issue by adding disable_mlock = true to the config.hcl file.

Configuring Vault instance

Vault server is running, but its configuration is not completed. Next step is to open another terminal and create unseal key(s):

Upon initialization, Vault server creates a series of unseal keys which are ideally to be given to the key custodians within your organization. Arguments of the “operator init” command govern the number of keys to be generated, as well as the minimum number of keys required to unseal the Vault after the startup. Store the unseal key, and the root token to a safe place.

We’re currently just experimenting, so we generated a single unseal key which is required to unseal the Vault instance. Vault initialization is done once, and the Vault unseal operation is done on each Vault server startup as followed:

Note1: If your Vault’s CA certificate is not available in the OS certificate store, the vault binary run as a client will refuse to connect to running Vault server instance due to the untrusted Certificate Authority. If you are not able, or willing to add your CA certificate, you can temporarily (not recommended) set the environment variable VAULT_SKIP_VERIFY to 1 before issuing vault commands.

Note2: As shown here, vault binary will try to connect to the default Vault server address (https://127.0.0.1:8200). If your setup differs from the default values, you should set the VAULT_ADDR environment variable accordingly.

Enable AppRole authentication and backend storage

At this point you’ll be able to connect to the Vault server both by using command line and browser interface. We’ll continue by using command line interface, but you can always perform equivalent actions through Vault UI available at https://127.0.0.1:8200/ui.

First you should login to the Vault server instance, using your root token:

On successful login, you can issue the vault status command to verify everything’s looking good. Once we’re here, let’s enable AppRole authentication style and enable KV version 1 secret storage:

The final command added a new Vault server role (mysql) for use with our keyring_hashicorp plugin. Next we’ll provide some additional rights to that role.

Assigning policy to role and retrieving credentials

At this point we should create a policy file, which we will import into Vault server instance. It will enable Vault server users (having Vault’s mysql role) to read and write secrets to/from a specific store path under Vault server. Store following content under the file name mysql.hcl:

Finalizing the Vault instance setup, we need to import the policy file, and retrieve credentials for our new role:

In order to configure MySQL keyring_hashicorp plugin (which we will do next), you’ll need the AppRole role_id and secret_id credentials retrieved in the last step.

Note: The policy allows a single MySQL server instance to connect to Vault server and read/write secrets under a particular path. Multiple MySQL server instances may use the same Vault server instance, albeit each MySQL instance should be assigned a different path (e.g. “kv/mysql/instance1”, “kv/mysql/instance2”, etc.) This is especially to be considered in replication setup context.

Configuring the plugin

Once you’ve successfully configured the Vault server instance, it’s time we start using it with MySQL. Minimum configuration that’ll get you up and going will look similar to this:

To verify that plugin is up and running, and that it was able to establish a connection to the Vault server instance, you should query the state of the variables exposed by the plugin (variable prefix below shortened intentionally for better overview):

There are two sets of information that the plugin exposes – first is the intended configuration, and the second is the currently applied/commited configuration. If everything went well, the committed configuration in the upper listing should match the intended. Otherwise, you’ll see applied configuration set to Not Committed. If this happens to you, you may try and investigate some of the hints in our Troubleshooting section below.

While MySQL is up, you may need to change some of the configuration variables, for which you can use SET GLOBAL directive. The effect of the changes won’t be seen until you’ve issued the special UDF:

The plugin will try to use the new values in the intended configuration to form a new connection to the Vault server. If such connection is successful, the new connection will replace the current one, and the committed configuration will get updated. On failure, the committed configuration will remain the same as before issuing the UDF command. That means that the plugin will continue to function properly with the old settings until it is able to verify the new settings.

Additional features

Key caching

By default, MySQL keys are stored directly to the Vault server instance. This has the advantage of safeguarding the keys, and being able to revoke authentication rights almost immediately. But also, MySQL won’t be able to retrieve keys in an event of a networking issue, and so may fail to retrieve encrypted data and keys, especially on startup.

In order to mitigate this issue, keyring_hashicorp plugin exposes a key caching feature which will maintain an in-memory key cache during server operation. This is governed by setting the keyring-hashicorp-caching on.

Certificate validation

By default, keyring_hashicorp plugin will open an https connection to any endpoint, trusting the delivered Vault server certificate implicitly. If you want MySQL server to explicitly validate Vault certificate using a CA certificate file, you may use keyring-hashicorp-ca-path to provide the plugin with the file path of CA certificate. In such case, MySQL will refuse to connect to any Vault server for which the certificate chain was not successfully verified.

By default, the plugin will use https://127.0.0.1:8200 as a Vault address. If your setup differs from this setting, you should change the keyring-hashicorp-server-url.

Custom authentication URL

Similar to the server url endpoint, the Vault AppRole authentication endpoint may reside on a path different from default (if your Vault instance is setup that way). You can configure the keyring-hashicorp-auth-path to point to appropriate path within the Vault server instance.

Use the keyring infrastructure

From this point forward, you’ll be able to use Hashicorp Vault as a storage backend for the MySQL Keyring infrastructure. For quick tests of the feature, you may install a set of keyring UDF’s that’ll allow you to store/retrieve keys via command line.

Troubleshooting

As with most of the things, you may bump into some roadblocks during the setup. Here are some of the tips to help you get back on the right track.

1) Double check your certificate and steps to produce them

• check the IP/domain parts of your certificate, and how they apply to your setup

2) Ensure that Vault instance is up and unsealed

• check the vault binary output for possible warnings/errors
• check Vault status over console to verify it’s unsealed

3) Check that you’re not bumping into a firewall/proxy issues

• access Vault using a command line tool such as wget/curl
• you should get response from Vault, not the proxy or similar

4) Check that your role_id/secret_id are valid

• try authenticating via command line from your server
• you should be able to obtain authentication token

5) Check that you can access store path within Vault, using token obtained through your AppRole credentials

• first obtain authentication token as in 4)
• try storing/accessing secrets in KV storage via curl CLI

Conclusion

We always strive to provide you with best tools for your workflow. Let us know how you like our new keyring backend – we’d also appreciate if you have some ideas for improving it. And as always, thank you for your support and using MySQL!