Database root credential rotation

13min
|
Vault

Vault's database secrets engine provides a centralized workflow for managing credentials for various database systems. By leveraging this, every service instance gets a unique set of database credentials instead of sharing one. Having those credentials tied directly to each service instance and live only for the life of the service, any abnormal access pattern can be mapped to a specific service instance and its credential can be revoked immediately.

This reduces the manual tasks performed by the database administrator and makes the database access more efficient and secure.

The Secret as a Service: Dynamic Secrets tutorial demonstrates the primary workflow.

Challenge

Because Vault is managing the database credentials on behalf of the database administrator, it must also be given a set of highly privileged credentials which can grant and revoke access to the database system. Therefore, it is very common to give Vault your root credentials.

However, these credentials are often long-lived and never change once configured on Vault. This may violate the Governance, Risk and Compliance (GRC) policies surrounding that data stored in the database.

Solution

Use the Vault's /database/rotate-root/:name API endpoint to rotate the root credentials stored for the database connection.

Best Practice

Use this feature to rotate the root credentials immediately after the initial configuration of each database.

Prerequisites

This lab was tested on macOS using an x86_64 based processor. If you are running macOS on an Apple silicon-based processor, use a x86_64 based Linux virtual machine in your preferred cloud provider.

To perform the tasks described in this tutorial, you need to have:

HCP or Vault Community Edition environment
Docker to run a PostgreSQL container
jq installed
psql or libpq installed
ngrok installed and configured with an auth token (HCP Vault Dedicated only)

Policy requirements

Note

For the purpose of this tutorial, you can use your root token to work with Vault. However, it is recommended that root tokens are only used for just enough initial setup or in emergencies. As a best practice, use tokens with appropriate set of policies based on your role in the organization.

To perform all tasks demonstrated in this tutorial, your policy must include the following permissions:

# Mount secrets engines
path "sys/mounts/*" {
  capabilities = [ "create", "read", "update", "delete", "list" ]
}

# Configure the database secrets engine and create roles
path "database/*" {
  capabilities = [ "create", "read", "update", "delete", "list" ]
}

If you are not familiar with policies, complete the policies tutorial.

Lab setup

Start PostgreSQL

The tutorial requires a Postgres database. Docker provides a Postgres server image that satisfies this requirement.

Open a new terminal and pull a Postgres server image with docker.
```
$ docker pull postgres:latest
```

Create a Postgres database with a root user named root with the password rootpassword.

$ docker run \
      --name learn-postgres \
      --env POSTGRES_USER=root \
      --env POSTGRES_PASSWORD=rootpassword \
      --detach  \
      --publish 5432:5432 \
      postgres

The PostgreSQL server is ready.

Start Vault

In a new terminal start a Vault dev server with root as the root token.
```
$ vault server -dev -dev-root-token-id root
```
The Vault dev server defaults to running at 127.0.0.1:8200. The server is initialized and unsealed.
Insecure operation
Do not run a Vault dev server in production. This approach starts a Vault server with an in-memory database and runs in an insecure way.
In a new terminal export an environment variable for the vault CLI to address the Vault server.
```
$ export VAULT_ADDR=http://127.0.0.1:8200
```
Export an environment variable for the vault CLI to authenticate with the Vault server.
```
$ export VAULT_TOKEN=root
```
The Vault server is ready.
Note
For these tasks, you can use Vault's root token. However, it is recommended that root tokens are only used for enough initial setup or in emergencies. As a best practice, use an authentication method or token that meets the policy requirements.
Set an environment variable for the PostreSQL address.
```
$ export POSTGRES_URL=127.0.0.1:5432
```

You are ready to proceed with the lab.

Note

If you do not have access to an HCP Vault Dedicated cluster, visit the Create a Vault Cluster on HCP tutorial.

Launch the HCP Portal and login.
Click Vault in the left navigation pane.
In the Vault clusters pane, click vault-cluster.
Under Cluster URLs, click Public Cluster URL.
In a terminal, set the VAULT_ADDR environment variable to the copied address.
```
$ export VAULT_ADDR=<Public_Cluster_URL>
```
Return to the Overview page and click Generate token.
Within a few moments, a new token will be generated.
Copy the Admin Token.
Return to the terminal and set the VAULT_TOKEN environment variable.
```
$ export VAULT_TOKEN=<token>
```
Set the VAULT_NAMESPACE environment variable to admin.
```
$ export VAULT_NAMESPACE=admin
```
The admin namespace is the top-level namespace automatically created by HCP Vault. All CLI operations default to use the namespace defined in this environment variable.

Type vault status to verify your connectivity to the Vault cluster.

$ vault status

Key                      Value
---                      -----
Recovery Seal Type       shamir
Initialized              true
Sealed                   false
Total Recovery Shares    1
Threshold                1
Version                  1.9.2+ent
Storage Type             raft
...snipped...

The Vault Dedicated server is ready.

For Vault Dedicated to interact with resources running on your local machine, a tunnel needs to be established.

In another terminal, start ngrok and connect to PostgreSQL.

$ ngrok tcp 127.0.0.1:5432

Example output:

ngrok                                                                                                                                                              (Ctrl+C to quit)

Session Status                online
Account                       username (Plan: Free)
Update                        update available (version 3.0.5, Ctrl-U to update)
Version                       3.0.3
Region                        United States (us)
Latency                       32.791235ms
Web Interface                 http://127.0.0.1:4040
Forwarding                    tcp://d12b-34-567-89-10.ngrok.io:12345 -> 127.0.0.1:5432

Connections                   ttl     opn     rt1     rt5     p50     p90
                              0       0       0.00    0.00    0.00    0.00

Copy the ngrok forwarding address.
Return to the terminal where you set the VAULT_ADDR environment variable and set an environment variable for the ngrok address. Do not include tcp://.
```
$ export POSTGRES_URL=<actual-address-from-ngrok>
```

You are ready to proceed with the lab.

Step 1: Enable the database secrets engine

Enable the database secrets engine.

$ vault secrets enable database

NOTE: This example enables the database secrets engine at the /database path in Vault.

Enable the database secrets engine using the /sys/mounts endpoint.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
       --request POST \
       --data '{"type":"database"}' \
       $VAULT_ADDR/v1/sys/mounts/database

NOTE: This example mounts database secrets engine at /database, and passes the secrets engine type ("database") in the request payload.

Enable the database secrets engine using the /sys/mounts endpoint.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    --request POST \
    --data '{"type":"database"}' \
    $VAULT_ADDR/v1/sys/mounts/database

NOTE: This example mounts database secrets engine at /database, and passes the secrets engine type ("database") in the request payload.

Open a web browser and sign into the Vault UI.
Navigate to Secrets and click Enable new engine.
From the Enable a Secrets Engine page Infra section, select Databases and click Next.
Leave all fields as-is, and click Enable Engine.
Note
This tutorial assumes that you enabled the database secrets engine at database. If yours is enabled at a different path, be sure to use the correct path as you follow this tutorial.

Step 2: Configure PostgreSQL secrets engine

Configure the database secrets engine.

$ vault write database/config/postgresql \
     plugin_name=postgresql-database-plugin \
     connection_url="postgresql://{{username}}:{{password}}@$POSTGRES_URL/postgres?sslmode=disable" \
     allowed_roles="*" \
     username="root" \
     password="rootpassword"

Notice that the connection_url value contains uses templated credentials, and username and password parameters are also passed to initiate the connection.

Create an API request payload containing the database secrets engine configurations.

$ tee payload.json <<EOF
{
    "plugin_name": "postgresql-database-plugin",
    "connection_url": "postgresql://{{username}}:{{password}}@$POSTGRES_URL/postgres?sslmode=disable",
    "allowed_roles": "readonly",
    "username": "root",
    "password": "rootpassword"
}
EOF

Invoke the database/config/postgresql endpoint to configure the database secrets engine.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
       --request POST \
       --data @payload.json \
       $VAULT_ADDR/v1/database/config/postgresql

Notice that the connection_url value uses templated credentials, and username and password parameters are also passed to initiate the connection.

Create an API request payload containing the database secrets engine configurations.

$ tee payload.json <<EOF
{
    "plugin_name": "postgresql-database-plugin",
    "connection_url": "postgresql://{{username}}:{{password}}@$POSTGRES_URL/postgres?sslmode=disable",
    "allowed_roles": "readonly",
    "username": "root",
    "password": "rootpassword"
}
EOF

Invoke the database/config/postgresql endpoint to configure the database secrets engine.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    --request POST \
    --data @payload.json \
    $VAULT_ADDR/v1/database/config/postgresql

Notice that the connection_url value uses templated credentials, and username and password parameters are also passed to initiate the connection.

The database secrets engine is enabled, but there is not yet an available connection from Vault to the database. Create the connection from Vault to the PostgreSQL server by clicking Create connection.
For Database plugin, select PostgreSQL.
For Connection name, enter postgresql.
Enter the following in the Connection url field replacing <host:port> with the value of the POSTGRES_URL environment variable postgresql://{{username}}:{{password}}@<host:port>/postgres?sslmode=disable.
Uncheck Connection will be verified.
For Username, enter root.
For Password, enter rootpassword.
Click Create database.
When prompted to rotate the root credentials, click Rotate and enable.

Step 3: Rotate the root credentials

Vault provides an API endpoint to easily rotate the initial root database credentials.

$ vault write -force database/rotate-root/postgresql

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
       --request POST \
       $VAULT_ADDR/v1/database/rotate-root/postgresql

To verify that the root credential was rotated, attempt to log into PostgreSQL.

$ psql -h localhost -p 5432 -U root postgres
Password for user root:

Entering the initial password (e.g. rootpassword) will not work since the password was rotated by the Vault.

You can invoke the database/rotate-root/:name endpoint periodically to secure the root credential.

Note

Once the root credential was rotated, only the Vault knows the new root password. This is the same for all root database credentials given to Vault. Therefore, you should create a separate superuser dedicated to the Vault usage which is not used for other purposes.

Step 4: Verify the configuration

You can create a role and verify that the database secrets engine dynamically generates credentials as expected.

If you are unfamiliar with database secrets engine, refer to the Secrets as a Service: Dynamic Secrets tutorial for more detailed instructions.

Note

Important: when you define the role in a production deployment, you must create user creation_statements, revocation_statements, renew_statements, and rotation_statements, which are valid for the database you've configured. If you do not specify statements appropriate to creating, revoking, or rotating users, Vault inserts generic statements which can be unsuitable for your deployment.

Create a file named readonly.sql containing the SQL statement to create a new role.

$ tee readonly.sql <<EOF
CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";
EOF

Create a role named 'readonly' with TTL of 1 hour.

$ vault write database/roles/readonly db_name=postgresql \
         creation_statements=@readonly.sql \
         default_ttl=1h max_ttl=24h

Get a new set of database credentials.

$ vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/999c43f0-f79e-ba90-24a8-4de5af33a2e9
lease_duration     1h
lease_renewable    true
password           A1a-u7wxtrpx09xp40yq
username           v-root-readonly-x6q809467q98yp4yx4z4-1525378026e

Create an API request payload containing the parameters to create a new role.

$ tee payload.json <<EOF
{
    "db_name": "postgresql",
    "creation_statements": "CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";",
    "default_ttl": "1h",
    "max_ttl": "24h"
}
EOF

Create a new role named readonly.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
       --request POST \
       --data @payload.json \
       $VAULT_ADDR/v1/database/roles/readonly

Generate a new set of credentials.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
       $VAULT_ADDR/v1/database/creds/readonly | jq

Example output:

{
   "request_id": "527970fd-f5e8-4de5-d4ed-1b7970eaef0b",
   "lease_id": "database/creds/readonly/ac79265e-668c-242f-4f67-1dae33da094c",
   "renewable": true,
   "lease_duration": 3600,
   "data": {
     "password": "A1a-0tr8u15y0us2u08v",
     "username": "v-root-readonly-x7v65y1xuprzxv9vpt80-1525378873"
   },
   "wrap_info": null,
   "warnings": null,
   "auth": null
}

Create an API request payload containing the parameters to create a new role.

$ tee payload.json <<EOF
{
    "db_name": "postgresql",
    "creation_statements": "CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";",
    "default_ttl": "1h",
    "max_ttl": "24h"
}
EOF

Create a new role named readonly.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    --request POST \
    --data @payload.json \
    $VAULT_ADDR/v1/database/roles/readonly

Generate a new set of credentials.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    --request GET \
    $VAULT_ADDR/v1/database/creds/readonly | jq

Example output:

{
   "request_id": "527970fd-f5e8-4de5-d4ed-1b7970eaef0b",
   "lease_id": "database/creds/readonly/ac79265e-668c-242f-4f67-1dae33da094c",
   "renewable": true,
   "lease_duration": 3600,
   "data": {
     "password": "A1a-0tr8u15y0us2u08v",
     "username": "v-root-readonly-x7v65y1xuprzxv9vpt80-1525378873"
   },
   "wrap_info": null,
   "warnings": null,
   "auth": null
}

Navigate to Secrets → database → Roles.
Click Create role.
For Role name, enter readonly.
For Database name, choose postgresql.
For Type of role, select dynamic.
For Username, enter root.

For Creation statements, enter the following SQL statements:

CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";

Role settings part one

Click Create role.
On the readonly role page, select Get credentials.
On the credentials page, click the clipboard icon to copy the token value to your system clipboard.
Use the copied token value to validate the connection in the next step.

Make sure that you can connect to the database using the Vault generated credentials.

Example

$ psql -h localhost -p 5432 \
       -U v-root-readonly-x7v65y1xuprzxv9vpt80-1525378873 postgres
Password for user v-root-readonly-x7v65y1xuprzxv9vpt80-1525378873:

postgres=> \du
Role name                                       |                         Attributes                         | Member of
------------------------------------------------+------------------------------------------------------------+----------
postgres                                        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
v-root-readonly-x6q809467q98yp4yx4z4-1525378026 | Password valid until 2018-05-03 21:07:11+00                | {}

postgres=> \q

This confirms that the Vault successfully connected to your PostgreSQL server and created a new user based on the privilege defined by readonly.sql.

The user credentials generated by the Vault has a limited TTL based on your configuration (default_ttl). In addition, you can revoke them if necessary.

Help and Reference

Database secrets engine

Username templating