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
- Create a serverless tier on TiDB Cloud console
- 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
- You can also try out TiDB Cloud Dedicated tier with Vault on the similar workflow.
- 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.
Top comments (0)