Access TiDB Cloud by Hashicorp Vault

Hashicorp Vault can be used to manage database access.

As a MySQL-compatible Database. TiDB Cloud Serverless Tier can use the MySQL database Secrets Engine as a plugin to generate database credentials dynamically.

The following figure shows the workflow of using Vault's Dynamic Secrets. This article will demonstrate how to implement the workflow with TiDB Cloud.

Prerequest

1. Create a serverless tier on TiDB Cloud console
2. Install Vault

Starting the Server

Start a Vault server in development mode (dev server) with root as the root token

 vault server -dev -dev-root-token-id root

Launch a new terminal session and set environment variables:

export VAULT_ADDR='http://127.0.0.1:8200'

Verify the Server is Running:

➜  ~ vault status
Key             Value
---             -----
Seal Type       shamir
Initialized     true
Sealed          false
Total Shares    1
Threshold       1
Version         1.12.2
Build Date      2022-11-23T12:53:46Z
Storage Type    inmem
Cluster Name    vault-cluster-7368793c
Cluster ID      d892d1b1-f975-8bbb-cc73-369d1f48f3ad
HA Enabled      false

Configure secrets engine (admin role)

Launch a new terminal session and perform the admin role.

Export an environment variable to authenticate with the root token:

export VAULT_ADDR='http://127.0.0.1:8200'
export VAULT_TOKEN=root

Enable the database secrets engine:

vault secrets enable database

Configure Vault with the following command:

vault write database/config/my-tidb-database \
    plugin_name=mysql-database-plugin \
    connection_url="{{username}}:{{password}}@tcp(gateway01.us-east-1.prod.aws.tidbcloud.com:4000)/" \
    allowed_roles="readonly" \
    username="8kwxfAybggzi***.root" \
    password="***"\
    username_template="8kwxfAybggzi***.{{.RoleName | truncate 8}}_{{random 7}}" \
    tls_ca=@/etc/ssl/cert.pem

• A templated connection_url is required when using root credential rotation.
• An username_template is required because Serverless Tier's username must be started with 8kwxfAybggzi*** and no longer than 32. You can adjust it according to Vault official doc.
• a tls_ca is required because Serverless Tier must be connected with TLS. You need to change the path according to your OS, here is a reference

Configure a role that maps a name in Vault to an SQL statement to execute to create the database credential. I create a user and grant SELECT privileges for read-only.

vault write database/roles/readonly \
    db_name=my-tidb-database \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT SELECT ON *.* TO '{{name}}'@'%';" \
    revocation_statements="DROP USER '{{name}}'@'%'; " \
    default_ttl="1h" \
    max_ttl="24h"

Create a readonly token (admin role)

Now, we need to create a token with a suitable policy for our customers.

create a readonly policy

➜  ~ vim readonly.hcl

# Get credentials from the database secrets engine 'readonly' role.
path "database/creds/readonly" {
  capabilities = [ "read" ]
}

write the policy:

vault policy write readonly-policy readonly.hcl

create a token with the policy

➜ ~ vault token create -policy=readonly-policy
Key                  Value
---                  -----
token                hvs.CAESIOMvEUz3UuoUyp_nv5YV5KdyzeSnowZp_UoBAzDh64VdGh4KHGh2cy5FNllaUWFRN1I4VnBNdDAyZFNjTkI1U0Q
token_accessor       4CZMNFZ8PNKUPsLZFQJ2dXvQ
token_duration       768h
token_renewable      true
token_policies       ["default" "readonly-policy"]
identity_policies    []
policies             ["default" "readonly-policy"]

Now, the admin can control the permission by giving the readonly token.

Use dynamic secrets (app role)

Launch a new terminal session and perform the app role.

Export an environment variable to authenticate with the readonly token:

export VAULT_ADDR='http://127.0.0.1:8200'
export VAULT_TOKEN=hvs.CAESIOMvEUz3UuoUyp_nv5YV5KdyzeSnowZp_UoBAzDh64VdGh4KHGh2cy5FNllaUWFRN1I4VnBNdDAyZFNjTkI1U0Q

Generate a new credential by reading from the /creds endpoint with the readonly role:

➜  ~ vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/3PNLHrgWyoRLg5QVWblDFiPP
lease_duration     1h
lease_renewable    true
password           DTuVZ-rvWlbMVG8ST***
username           8kwxfAybggzi***.readonly_MVmoAos

Now, you can use the password and username to connect to TiDB Cloud. And it will only have read permission.

Other operators such as creating a role or reading with another role will be denied.

➜  ~ vault write database/roles/readonly2 \
    db_name=my-tidb-database \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT SELECT ON *.* TO '{{name}}'@'%';" \
    default_ttl="1h" \
    max_ttl="24h"
Error writing data to database/roles/readonly2: Error making API request.

URL: PUT http://127.0.0.1:8200/v1/database/roles/readonly2
Code: 403. Errors:

* 1 error occurred:
    * permission denied

using root token to create a writeonly role:

vault write database/roles/writeonly \
    db_name=my-tidb-database \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT INSERT ON *.* TO '{{name}}'@'%';" \
    revocation_statements="DROP USER '{{name}}'@'%'; " \
    default_ttl="2m" \
    max_ttl="24h"

using readonly token to read the writeonly role:

➜  ~ vault read database/creds/writeonly
Error reading database/creds/writeonly: Error making API request.

URL: GET http://127.0.0.1:8200/v1/database/creds/writeonly
Code: 403. Errors:

* 1 error occurred:
    * permission denied

About the leases

The credential generated with the readonly role will expire after the 1h because readonly role's default_ttl is 1h.

For admin role:

• One way to avoid the expiration of credential is to delete the revocation_statements when admin creates the readonly role.
• admin can revoke the credential at any time.
• admin can renew the credential before it expires. But can't renew more than readonly role's max_ttl.

For app role:

• app role can ask for the renew permission from admin. Then app role can renew the credential.
• Once the credential expires, app role needs to generate a new credential with the readonly token by vault read database/creds/readonly command. For example, use a cron job to reload the credential for each ttl.

Prospect

1. You can also try out TiDB Cloud Dedicated tier with Vault on the similar workflow.
2. TiDB Cloud Serverless tier may modify the mechanism of user management in the future. We may also provide a TiDB Cloud plugin in that time. Thus, this doc may not always work.