At DoltHub, we're building Dolt, the world's first fully version controlled SQL database. It's a MySQL compatible SQL database that supports Git-like version control features including commit, branch, diff, merge, as well as Git-like remote features like push, pull, fetch and clone.

Because Dolt is MySQL compatible, it works with a lot of off-the-shelf tools that support MySQL. Recently, one of our customers wanted to add federated authentication to their Dolt sql-servers. A common tool across the industry for secret management is Vault. We set out to find out whether Vault would work for their use case and if there was anything necessary to do to make it work well with Dolt.

We were happy to find that Vault's MySQL/MariaDB secrets engine worked off the shelf with Dolt. This is a short blog post describing how to get started with Vault as a secrets engine for user and password management against Dolt.

The Setup

Vault is a secrets engine which allows for securely storing and retrieving secrets. It supports a wide variety of authentication mechanisms and allows for finely attenuated permissions on access to the secrets stored. Vault also supports a plugin architecture involving secrets engines, which allows for Vault to run custom code on a given piece of a data in order to generate a new secret or response to give to an authenticated client.

Secrets engines are quite flexible, and so a wide variety of functionality can be implemented in terms of them. One of the most common types of secrets engine is an engine which generates dynamic credentials which can be provided to an authenticated client to allow it to access another system. In the context of Dolt, this is the MySQL/MariaDB database secrets engine.

It can be registered as a database credentials provider within Vault. When an authenticated user makes a request to Vault asking for database credentials corresponding to a configured database role, the secrets engine will create a new user on the target database, with a new unique password, and will grant it the desired SQL grants. It will return the username and password for the new user to the calling client, which can go ahead and connect to the database using them. Once the lease on the temporary credentials expires, the Vault secrets engine will delete the temporary user cooresponding to the database access so that those credentials can no longer be used to access the database.

Vault itself supports many authentication mechanisms, including user- and operator- focused ones such as fixed user and password, OIDC, and SAMLv2, as well as system or service role focused ones, including approle, OIDC, mutual TLS, Kubernetes, AWS, and GCP. Since Vault supports so many different authentication mechanisms, it can be a centralized place to manage federated access to various server resources.

After being configured, the flow for getting and using the database credentials will look like:

This blog post is just a quick demonstration of how the MySQL/MariaDB database secrets engine works with Dolt. Things like production hardening or federated authentication to Vault itself are not covered here, but by combining and extending the configuration fragments shown below with further configuration of authentication and permissions on the Vault side, it's easy to achieve federated access to multiple Dolt sql-server instances, both for programmatic access in the server role context and for human access in the operator context.

Steps

To get started, we will need both a dolt sql-server running, as well as a Vault server. For the purposes of this exposition, we will run them both as standalone Docker containers.

1. We can run dolt sql-server as so:

$ docker pull dolthub/dolt-sql-server
$ docker run --rm -ti -p 3306:3306 \
    -e DOLT_ROOT_PASSWORD=rootpassword \
    -e DOLT_ROOT_HOST=% \
    dolthub/dolt-sql-server

This will bring up a dolt sql-server process, listening on port 3306, and with a preconfigured root user having a fixed password. Passing the root password on the command line is not recommended for a production deployment but it is convenient for the purposes of this demonstration.

2. We run vault similarly:

docker pull hashicorp/vault:1.19
docker run --rm -ti -p 8200:8200 hashicorp/vault:1.19

This will bring up vault running in -dev mode. This is not a reasonable way to run a production Vault deployment but it is convenient for the purposes of this demonstration.

As Vault comes up, it will print the Root Token:. You should take note of it, as we will need it for later.

3. Next we need to create the initial database state in Dolt. This initial database state includes a few things. We create the application db, webappdb in this case, and we create the bootstrap database user which Vault will use to manage the temporary users and grants which it will create. Our temporary user is named vaultadmin and has a hard-coded password.

$ mysql -u root -prootpassword -h 127.0.0.1
mysql> create database webappdb;
mysql> create user 'vaultadmin'@'%' identified by 'vaultpassword';
mysql> grant all privileges on webappdb.* TO 'vaultadmin'@'%' with grant option;
mysql> grant create user on *.* to 'vaultadmin'@'%';
mysql> \quit

4. Take the root token which was printed to the terminal in step 2. We are going to configure some environment variables to use in the Vault CLI invocations below:

$ cat <<EOF > env
VAULT_ADDR=http://host.docker.internal:8200
VAULT_TOKEN=**INSERT_ROOT_TOKEN_VALUE_HERE**
EOF

5. Create the Vault policy and roles for accessing our vault and for accessing our database. In this case, we're going to do the following:

• Create a policy for our theoretical webapp, called webapp.
• Enable the approle authentication mechanism.
• Create an approle for our theoretical webapp, called webapp.
• Give our webapp approle a hard coded secret_id, webapp_secret_id. The secret_id is like the password for the approle. By using approle authentication with a hard coded secret_id, we have essentially adopted username and password semantics for authenticating to Vault as this role. This is not the ideal setup for a production deployment, but it works for demonstration purposes. In reality, a deployment would probably rather use AWS IAM authentication, or Kubernetes authentication, or another third-party external identity provider that removed the need for bootstrapping secrets onto the deployed infrastructure for its initial authentication to the Vault instance.
• Enable the database secrets engine.
• Configure a database connection in the database secrets engine, called dolt-webapp, which knows how to connect to our running dolt sql-server instance.
• Configure a database role in the database secrets engine which will create and vend the temporary credentials on dolt-webapp. This role is named dolt-webapp-role.

$ cat <<EOF > webapp-policy.hcl 
path "auth/approle/login" {
 capabilities = ["create", "read"]
}

path "database/creds/dolt-webapp-role" {
 capabilities = ["read"]
}
EOF
$ docker run --rm -ti --env-file env \
   -v `pwd`/webapp-policy.hcl:/webapp-policy.hcl \
   hashicorp/vault:1.19 \
   policy write webapp /webapp-policy.hcl
$ docker run --rm -ti --env-file env \
   hashicorp/vault:1.19 \
   auth enable approle
$ docker run --rm -ti --env-file env \
   hashicorp/vault:1.19 \
   write auth/approle/role/webapp policies=webapp role_id=webapp_role_id
$ docker run --rm -ti --env-file env \
   hashicorp/vault:1.19 \
   write auth/approle/role/webapp/custom-secret-id secret_id=webapp_secret_id
$ docker run --rm -ti --env-file env \
   hashicorp/vault:1.19 \
   secrets enable database
$ docker run --rm -ti --env-file env \
   hashicorp/vault:1.19 \
   write database/config/dolt-webapp \
   plugin_name=mysql-database-plugin \
   connection_url="{{username}}:{{password}}@tcp(host.docker.internal:3306)/" \
   allowed_roles="dolt-webapp-role" \
   username="vaultadmin" \
   password="vaultpassword"
$ docker run --rm -ti --env-file env \
   hashicorp/vault:1.19 \
   write database/roles/dolt-webapp-role \
   db_name=dolt-webapp \
   creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}'; GRANT ALL ON webappdb.* TO '{{name}}'@'%';" \
   revocation_statements="DROP USER '{{name}}'@'%';" \
   default_ttl="5m"

5. With the database role created, we can simulate an application accessing the database using the role. First we login to our approle using the secret_id we created above. Then we read some database credentials associated with the database role we created.

$ login_resp=`docker run --rm -ti --env-file env \
    hashicorp/vault:1.19 \
    write -format=json auth/approle/login \
    role_id=webapp_role_id secret_id=webapp_secret_id`
$ echo VAULT_TOKEN=`echo "$login_resp" | grep -v IPC_LOCK | jq -r '.auth.client_token'` >> env
$ dbcreds_resp=`docker run --rm -ti --env-file env \
    hashicorp/vault:1.19 \
    read -format=json database/creds/dolt-webapp-role`
$ mysql \
    -u `echo "$dbcreds_resp" | grep -v IPC_LOCK | jq -r '.data.username'` \
    -p`echo "$dbcreds_resp" | grep -v IPC_LOCK | jq -r '.data.password'` \
    -h 127.0.0.1 \
    webappdb

As you can see, the MySQL client ends up connected to the Dolt database as a temporary user:

mysql> select current_user();
+------------------------------------+
| current_user()                     |
+------------------------------------+
| v-approle-dolt-webap-Iuc2J4ujOMd@% |
+------------------------------------+
1 row in set (0,00 sec)

After the lease on the temporary credentials is expired, the database secrets engine runs the revocation_statements associated with the role. In this case, we simply have it delete the temporary user, which also revokes all of that user's grants.

Further Steps

Multiple databases can be managed by writing more configs as subpaths of the database/config/ path. Multiple roles can be managed by writing new database role configs into subpaths of database/roles/. With different creation_statements configs, the different roles can be granted different permissions against the Dolt database.

Federated authentication can be configured for administrators by configuring Google OAuth, Github OAuth, LDAP authentication or some other way for users to authenticate to Vault. Federated authentication for service accounts can be further extended by enabling and configuring AWS IAM authentication, Kubernetes JWT authentication, GCP IAM authentication, or some other third-party platfrom solution for authenticating the acting services.

Finer grained grants for the given database access roles can be configured by changing the creation_statements used by the secrets engine to create the users associated with the role.

Developing against Dolt when using this approach will require getting these temporary credentials to your running application. A somewhat standard way to approach that is to use Vault Agent, which can run alongside your application and make the credentials available to it. It can be configured to authenticate to the Vault API and to fetch certain credentials, making them available to programs running on the local host. It will handle all of the authentication, refreshing and credential lease management, while your application can just read the credentials from a local file, for example.

When we first tested Vault against Dolt, we found the grants worked as expected but that lease revocation failed with an error. There are some REVOKE GRANT statements which the default recovation_statements used by the MySQL secrets engine uses and which Dolt does not yet support. We hope to add support for these statements to Dolt in the future.

Are you developing against Dolt and have a use case for service accounts or federated authentication? Reach out to us on Discord, we would love to hear from you!