Introduction

There is a learning page for turning on database secret engine for postgresql but for my environment it is MariaDB, this post records on how to enable database engine to automatically rotate secrets for MariaDB user. This feature is very similar to CyberArk on password rotation for nix servers and database.

For this lab, I will enable database secret engine to manage secret for MariaDB user – vault, and the mariadb server hostname cyruslab, the environment to group the database servers will be known as db_lab.

Create user vault in mariadb

With root account, logon to mariadb, create user vault:
CREATE USER 'vault' IDENTIFIED BY 'P@ssw0rd';

Enable database secret engine

This part I will use the environment name db_lab as the name to enable database engine.

To enable with REST API, use the POST method, with this uri /v1/sys/mounts/db_lab. Because my server is 192.168.1.50:8200 the entire path is https://192.168.1.50::8200/v1/sys/mounts/db_lab. On subsequent sections only the uri will be mentioned, and it is understood that the uri and the server has to be joined to be a full URL path.

The payload to enable the database secret engine is:

{
	"type": "database"
}

In order to post to the REST API, you need to insert the root token to the header with X-Vault-Token as key.

Hashicorp vault will not return response, but if successfully created 204 will be the status.

In the vault you will see db_lab is created.

Submit Mysql/MariaDB configuration

Use, POST request, uri is /v1/db_lab/config/cyruslab.
db_lab is the mount point in Hashicorp vault’s context and cyruslab is the path under db_lab, as mentioned in the introduction the plan is to setup an environment name db_lab and group the database servers under this envionrment, and one of the database server hostname is cyruslab.

The json body to post the request:

{
  "plugin_name": "mysql-database-plugin",
  "allowed_roles": "*",
  "connection_url": "{{username}}:{{password}}@tcp(192.168.1.50:3306)/",
  "max_open_connections": 5,
  "max_connection_lifetime": "5s",
  "username": "vault",
  "password": "P@ssw0rd"
}

The response will be like this:

Create static role

This role allows the vault to rotate the password of user vault.
POST method, uri is /v1/db_lab/static-roles/rotate_vault, rotate_vault is an user chosen name for the role.

The json body for POST:

{
    "db_name": "cyruslab",
    "rotation_statements": "SET PASSWORD FOR '{{name}}'@'%' = PASSWORD('{{password}}');",
    "username": "vault",
    "rotation_period": "86400"
}

db_name is the name of the path or the hostname of my server.
rotation_statements is the SQL syntax to change user vault password.
rotation_period is the duration of the password, once this duration expires a new password is generated.

It looks like Hashicorp vault is using Jinja2 template engine to fill up the username and password in the sql syntax.

There is no response, the http status is 204.

To verify if the POST is successful you can use GET to read back with the same uri.

Request cyruslab’s credential from vault

Create a policy first:
Use the PUT method, uri is /v1/sys/policies/acl/for_apps where for_apps is the name of choice for the policy.
The json payload is:

{
  "policy": "path \"db_lab/static-creds/rotate_vault\" {capabilities = [ \"read\" ]}"
}

"path \"db_lab/static-creds/rotate_vault\" {capabilities = [ \"read\" ]} has to be like this, only accepts double quotes otherwise the vault will give an error that it cannot parse the information.

Successful PUT will give 204.

Within the Vault you will see a new ACL policy:

Generate a new token for vault

POST method, uri is /v1/auth/token/create.
The json body for the POST request is:

{
	"policies": ["for_apps"]
}

The response will look like this:

Take notice on the client_token field in the response body, which is this s.BeHQCgFV2moyjfjy2oyzjpf3, of course this will be different for your environment.

This client_token is used to get the credential of the vault in the database.

Acquire the user vault’s credential

GET request, uri is /v1/db_lab/static-creds/rotate_vault, replace the header X-Vault-Token with the client_token (although the root token can also GET the response). db_lab is the mount path which is the environment name which I created earlier, and the rotate_vault is the role name I created, and also I have set up a policy to allow read on the path which is the same as uri.

The response will give you the password of user vault of the database server – cyruslab.
The screenshot shows the header changed to client_token and also the response from Hashicorp vault.

Now the vault has changed the password of user vault, I tested with the password found in the response: A1a-C4Rar10VCRg2MaoC

Manual rotate password

You can change the password by sending POST request to the vault.
POST request, no json body, uri is /v1/db_lab/rotate-role/rotate_vault. As the client_token can only read it cannot do changes, so you need to change the X-Vault-Token in the header to root token.