Database Static Roles and Credential Rotation

19min
Vault
Video

Challenge

The Secrets as a Service: Dynamic Secrets tutorial demonstrated the use of Vault's database secrets engine to dynamically manage database credentials. Vault creates a unique set of username and password with specified time-to-live (TTL) every time a client (e.g. a user or application) requests. This allows each application to have its own database credentials.

But now, consider a classic use case where multiple applications use shared, static user accounts and periodically rotate the password (e.g. every 90 days). Because Vault creates a new set of credentials each time, adopting the database secrets engine requires some code change in those applications.

Solution

Database secrets engine enables organizations to automatically rotate the password for existing database users. This makes it easy to integrate the existing applications with Vault and leverage the database secrets engine for better secret management.

NOTE: The database secrets engine supports several database types. See the Database Capabilities table to check the availability of this feature.

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 OSS Vault environment
Docker to run a PostgreSQL container
jq installed
psql or libpq installed
ngrok installed and configured with an auth token (HCP Vault only)

Personas

The end-to-end scenario described in this tutorial involves two personas:

admin with privileged permissions to configure secrets engines
apps read the secrets from Vault

Policy requirements

NOTE: For the purpose of this tutorial, you can use 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" ]
}

# Write ACL policies
path "sys/policies/acl/*" {
  capabilities = [ "create", "read", "update", "delete", "list" ]
}

# Manage tokens for verification
path "auth/token/create" {
  capabilities = [ "create", "read", "update", "delete", "list", "sudo" ]
}

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

Scenario Introduction

In this tutorial, you are going to configure PostgreSQL secrets engine, and create a static read-only database role with username, vault-edu. The Vault generated PostgreSQL credentials will only have read permission.

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

Create a role named vault-edu.

$ docker exec -i \
    learn-postgres \
    psql -U root -c "CREATE ROLE \"vault-edu\" WITH LOGIN PASSWORD 'mypassword';"

Grant associated privileges for the role.

$ docker exec -i \
    learn-postgres \
    psql -U root -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"vault-edu\";"

Confirm role attributes.

$ docker exec -i learn-postgres psql -U root -c "\du"
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 root      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vault-edu |                                                            | {}

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 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 HCP Vault server is ready.

For HCP Vault 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: Setup the database secrets engine

(Persona: admin)

Enable the database secrets engine, and then configure it so that it can connect to the PostgreSQL server.

Execute the following command to enable the database secrets engine at database/ path.
```
$ vault secrets enable database
```
NOTE: This tutorial assumes that you enabled the database secrets engine at database. If you enabled it at a different path, be sure to use the correct path as you follow this tutorial.

Execute the following command to configure the database secrets engine which uses postgresql-database-plugin.

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

Execute the following command to rotate the root credentials.
```
$ vault write -force database/rotate-root/postgresql
```

Mount database secrets engine using /sys/mounts endpoint.
```
$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --request POST \
    --data '{"type":"database"}' \
    $VAULT_ADDR/v1/sys/mounts/database
```
This tutorial assumes that you enabled the database secrets engine at database. If you enabled it at a different path, be sure to use the correct path as you follow this tutorial.

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

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

Invoke the /database/config endpoint to configure the database secret engine which uses postgresql-database-plugin.

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

Execute the /database/rotate-root endpoint to rotate the root credentials.

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

Mount database secrets engine using /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
```
This tutorial assumes that you enabled the database secrets engine at database. If you enabled it at a different path, be sure to use the correct path as you follow this tutorial.

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

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

Invoke the /database/config endpoint to configure the database secret engine which uses postgresql-database-plugin.

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

Execute the /database/rotate-root endpoint to rotate the root credentials.

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

Open a web browser and launch the Vault UI (e.g. http://127.0.0.1:8200/ui) and then login with the token value root.
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.
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.
Uncheck Connection will be verified.
For Connection url, enter postgresql://{{username}}:{{password}}@localhost:5432/postgres?sslmode=disable.
For Username, enter root.
For Password, enter rootpassword.
Click Create database.
When prompted to rotate the root credentials, click Rotate and enable.

NOTE: As a best practice, this example is using templated credentials and rotates its root password immediately since the initial password was rootpassword which is too simple. For more details, refer to the Database Root Credential Rotation tutorial.

Step 2: Create a static role

(Persona: admin)

In this step, you are going to define a static role, "education" with database username, "vault-edu". Vault will manage its password, but the username remains static.

Create a file named, rotation.sql with following SQL statements.

$ tee rotation.sql <<EOF
ALTER USER "{{name}}" WITH PASSWORD '{{password}}';
EOF

Create a static role named education.
```
$ vault write database/static-roles/education \
    db_name=postgresql \
    rotation_statements=@rotation.sql \
    username="vault-edu" \
    rotation_period=86400
```
The above command creates a education static role with database username vault-edu whose password gets rotated every 86400 seconds (24 hours). The rotation.sql statement is passed as the rotation statement.
NOTE: For static roles, the db_name parameter is the database configuration name (not the database name). In this scenario, you configured database/config/postgresql; therefore, the db_name must be set to postgresql.

Verify the configuration of the education role.

$ vault read database/static-roles/education

Key                    Value
---                    -----
db_name                postgresql
last_vault_rotation    2019-06-24T10:18:39.766203-07:00
rotation_period        24h
rotation_statements    [ALTER USER "{{name}}" WITH PASSWORD '{{password}}';]
username               vault-edu

Create an API request payload containing the new static role definitions.

$ tee payload.json <<EOF
{
    "db_name": "postgresql",
    "rotation_statements": "ALTER USER \"{{name}}\" WITH PASSWORD '{{password}}';",
    "username": "vault-edu",
    "rotation_period": "86400"
}
EOF

Invoke the /database/static-roles endpoint to create a new static role named "education".
```
$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
     --request POST --data @payload.json \
     $VAULT_ADDR/v1/database/static-roles/education
```
The above API call creates a education static role with database username vault-edu whose password gets rotated every 86400 seconds (24 hours). The rotation.sql statement is passed as the rotation statement.
NOTE: For static roles, the db_name parameter is the database configuration name (not the database name). In this scenario, you configured database/config/postgresql; therefore, the db_name must be set to postgresql.

Verify the education role configuration.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    $VAULT_ADDR/v1/database/static-roles/education | jq

Example output:

{
  # ...snip...
  "data": {
    "db_name": "postgresql",
    "last_vault_rotation": "2019-06-24T10:18:39.766203-07:00",
    "rotation_period": 86400,
    "rotation_statements": [
      "ALTER USER \"{{name}}\" WITH PASSWORD '{{password}}';"
    ],
    "username": "vault-edu"
  },
  # ...snip...
}

Create an API request payload containing the new static role definitions.

$ tee payload.json <<EOF
{
    "db_name": "postgresql",
    "rotation_statements": "ALTER USER \"{{name}}\" WITH PASSWORD '{{password}}';",
    "username": "vault-edu",
    "rotation_period": "86400"
}
EOF

Invoke the /database/static-roles endpoint to create a new static role named "education".
```
$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
     --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
     --request POST --data @payload.json \
     $VAULT_ADDR/v1/database/static-roles/education
```
The above API call creates a education static role with database username vault-edu whose password gets rotated every 86400 seconds (24 hours). The rotation.sql statement is passed as the rotation statement.
NOTE: For static roles, the db_name parameter is the database configuration name (not the database name). In this scenario, you configured database/config/postgresql; therefore, the db_name must be set to postgresql.

Verify the education role configuration.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    $VAULT_ADDR/v1/database/static-roles/education | jq

Example output:

{
  # ...snip...
  "data": {
    "db_name": "postgresql",
    "last_vault_rotation": "2019-06-24T10:18:39.766203-07:00",
    "rotation_period": 86400,
    "rotation_statements": [
      "ALTER USER \"{{name}}\" WITH PASSWORD '{{password}}';"
    ],
    "username": "vault-edu"
  },
  # ...snip...
}

Step 3: Request PostgreSQL credentials

(Persona: apps)

To retrieve the credentials for the "vault-edu" static role, the client application needs to be able to read from the database/static-creds/education role endpoint. Therefore the application's token must have a policy granting the read permission.

Create a file named apps.hcl with the following policy.

$ tee apps.hcl <<EOF
path "database/static-creds/education" {
  capabilities = [ "read" ]
}
EOF

Create a policy named apps using the apps.hcl file.

$ vault policy write apps apps.hcl

Policy 'apps' written.

Generate a token so that you can authenticate as an apps persona and save the token as an environment variable.
```
$ APPS_TOKEN=$(vault token create -policy="apps" -format json | jq -r '.auth | .client_token')
```

Execute the following command to request credentials for role, vault-edu. Be sure to use the token you acquired in the previous step.

$ VAULT_TOKEN=$APPS_TOKEN vault read database/static-creds/education

Key                    Value
---                    -----
last_vault_rotation    2019-06-06T22:23:17.063096-07:00
password               A1a-jxH944nG0qHRpMNR
rotation_period        24h
ttl                    23h51m29s
username               vault-edu

Re-run the command and verify that returned password is the same with updated TTL.

$ VAULT_TOKEN=$APPS_TOKEN vault read database/static-creds/education

Key                    Value
---                    -----
last_vault_rotation    2019-06-06T22:23:17.063096-07:00
password               A1a-jxH944nG0qHRpMNR
rotation_period        24h
ttl                    23h47m35s
username               vault-edu

Save the returned password as an environment variable.

$ GEN_DB_PASS=$(VAULT_TOKEN=$APPS_TOKEN vault read \
    -format json  database/static-creds/education | jq -r '.data | .password')

Create an API payload containing the apps policy definition.

$ tee payload.json <<EOF
{
  "policy": "path \"database/static-creds/education\" {capabilities = [ \"read\" ]}"
}
EOF

Create a policy named apps.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" --request PUT \
    --data @payload.json \
    $VAULT_ADDR/v1/sys/policies/acl/apps

Generate a new token with apps policy and store it as an environment variable.

$ APPS_TOKEN=$(curl --header "X-Vault-Token: $VAULT_TOKEN" --request POST \
    --data '{"policies": ["apps"]}' \
    $VAULT_ADDR/v1/auth/token/create | jq -r '.auth | .client_token')

Invoke the database/static-creds/education endpoint to get credentials for the "vault-edu" role.

$ curl --header "X-Vault-Token: $APPS_TOKEN" \
    $VAULT_ADDR/v1/database/static-creds/education | jq

Example output:

{
  # ...snip...
  "data": {
    "last_vault_rotation": "2019-06-06T22:23:17.063096-07:00",
    "password": "A1a-jxH944nG0qHRpMNR",
    "rotation_period": 86400,
    "ttl": 84243,
    "username": "vault-edu"
  },
  # ...snip...
}

Re-run the command and save the password as an environment variable.

$ GEN_DB_PASS=$(curl --header "X-Vault-Token: $APPS_TOKEN" \
    $VAULT_ADDR/v1/database/static-creds/education | jq -r '.data | .password')

Create an API payload containing the apps policy definition.

$ tee payload.json <<EOF
{
  "policy": "path \"database/static-creds/education\" {capabilities = [ \"read\" ]}"
}
EOF

Create a policy named apps.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" --request PUT \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    --data @payload.json \
    $VAULT_ADDR/v1/sys/policies/acl/apps

Generate a new token with apps policy and store it as an environment variable.

$ APPS_TOKEN=$(curl --header "X-Vault-Token: $VAULT_TOKEN" --request POST \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    --data '{"policies": ["apps"]}' \
    $VAULT_ADDR/v1/auth/token/create | jq -r '.auth | .client_token')

Invoke the database/static-creds/education endpoint to get credentials for the "vault-edu" role.

$ curl --header "X-Vault-Token: $APPS_TOKEN" \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    $VAULT_ADDR/v1/database/static-creds/education | jq

Example output:

{
  # ...snip...
  "data": {
    "last_vault_rotation": "2019-06-06T22:23:17.063096-07:00",
    "password": "A1a-jxH944nG0qHRpMNR",
    "rotation_period": 86400,
    "ttl": 84243,
    "username": "vault-edu"
  },
  # ...snip...
}

Re-run the command and save the password as an environment variable.

$ GEN_DB_PASS=$(curl --header "X-Vault-Token: $APPS_TOKEN" \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    $VAULT_ADDR/v1/database/static-creds/education | jq -r '.data | .password')

Click the Policies tab, and then select Create ACL policy.
Toggle Upload file sliding switch, and click Choose a file to select your apps.hcl file you authored. This loads the policy and sets the Name to be apps.
Click Create Policy to complete.
Click the Vault CLI shell icon (>_) to open a command shell. Execute vault write auth/token/create policies=apps in the CLI shell to create a new token for the apps persona.
Copy the generated client token value.
Sign out of the Vault UI.
Now, sign into the Vault using the newly generated token you just copied.
Click the Vault CLI shell icon (>_) to open a command shell. Execute vault read database/static-creds/education in the CLI shell.
Re-run the command and verify that returned password is the same with updated TTL.
Copy the returned password (e.g. A1a-jxH944nG0qHRpMNR).

Validation

Verify that you can connect to the psql with username vault-edu and run a query.

$ psql postgresql://vault-edu:$GEN_DB_PASS@localhost:5432/postgres -c '\du';

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      | Superuser                                                  | {}
 vault-edu |                                                            | {}

Step 4: Manually rotate the password

(Persona: admin)

The password for the static role gets automatically rotated after a configured rotation period. However, there may be a situation requiring you to rotate the password immediately. Vault provides the /database/rotate-role/<role_name> endpoint to force an immediate password rotation.

Execute the following command to rotate the password for static role, "education".

$ vault write -f database/rotate-role/education
Success! Data written to: database/rotate-role/education

Now, read the credentials to verify that the password has been rotated.

$ vault read database/static-creds/education

Key                    Value
---                    -----
last_vault_rotation    2019-06-11T09:19:52.497767-07:00
password               A1a-9Lp1yoJMHPNGGL2J
rotation_period        24h
ttl                    23h59m46s
username               vault-edu

The returned password should be different from previous output, and the remaining TTL has been back to ~24 hours.

Invoke the database/rotate-role endpoint to rotate the credential for the static role, "education".

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

Read the credentials to verify that the password has been rotated.

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

Example output:

{
  # ...snip...
  "data": {
      "last_vault_rotation": "2019-06-11T09:34:30.633828-07:00",
      "password": "A1a-glMoEe7MgJY71v4E",
      "rotation_period": 86400,
      "ttl": 86305,
      "username": "vault-edu"
  },
  # ...snip...
}

The returned password should be different from previous output, and the remaining TTL is back to ~24 hours.

Invoke the database/rotate-role endpoint to rotate the credential for the static role, "education".

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    --request POST \
    $VAULT_ADDR/v1/database/rotate-role/education

Read the credentials to verify that the password has been rotated.

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

Example output:

{
  # ...snip...
  "data": {
      "last_vault_rotation": "2019-06-11T09:34:30.633828-07:00",
      "password": "A1a-glMoEe7MgJY71v4E",
      "rotation_period": 86400,
      "ttl": 86305,
      "username": "vault-edu"
  },
  # ...snip...
}

The returned password should be different from previous output, and the remaining TTL is back to ~24 hours.

Challenge

Solution

Prerequisites

Personas

Policy requirements

Scenario Introduction

Lab setup

Start PostgreSQL

Start Vault

Step 1: Setup the database secrets engine

Step 2: Create a static role

Step 3: Request PostgreSQL credentials

Validation

Step 4: Manually rotate the password

Help and Reference