Dynamic secrets for database credential management

25min
|
Vault
Interactive

Deployment

To protect data, it is critical to have a database credential management strategy. Often, organizations have a policy to periodically rotate the credentials. Also, you want to assign a different set of permissions granted for each user, system, or application that accesses data.

Vault's database secrets engine provides a database credential management solution so that the username and password can be dynamically generated upon request, and you can control the lifecycle of the credentials.

Dynamic Secret Workflow

Each app requests unique credentials so that they don't have to share. By making those credentials short-lived, you reduce the chance that they might be compromised. If an app was compromised, the credentials used by the app can be revoked rather than changing more global sets of credentials. You can also automate continuous credential rotation to minimize risk.

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

Launch Terminal

This tutorial includes a free interactive command-line lab that lets you follow along on actual cloud infrastructure.

Personas

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

admin: with privileged permissions to configure secrets engines
developer: ensures apps can read secrets from Vault

Prerequisites

This lab was tested on macOS using an x86_64 based processor. If you are running macOS on an Apple silicon-based processor, click the Start interactive lab button or perform the steps using 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
ngrok installed and configured with an auth token (HCP Vault Dedicated only)
Rootless static role password rotation requires HashiCorp Vault Enterprise and an Enterprise license key. For more information on installing a Vault enterprise license, refer to the Install a HashiCorp Enterprise license page.

Lab setup

Start PostgreSQL

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

Note

This tutorial works for an existing PostgreSQL database given appropriate credentials and connection information.

Pull a PostgreSQL server image with docker.
```
$ docker pull postgres:latest
```

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

$ docker run \
    --detach \
    --name learn-postgres \
    -e POSTGRES_USER=root \
    -e POSTGRES_PASSWORD=rootpassword \
    -p 5432:5432 \
    --rm \
    postgres

Verify that the PostgreSQL container is running.
```
$ docker ps -f name=learn-postgres --format "table {{.Names}}\t{{.Status}}"
NAMES            STATUS
learn-postgres   Up 5 seconds
```
The credentials generated by the Vault role in the Create a role step requires a role named ro that has been granted the ability to read all tables.

Create a role named ro.

$ docker exec -i \
    learn-postgres \
    psql -U root -c "CREATE ROLE \"ro\" NOINHERIT;"

Grant the ability to read all tables to the role named ro.
```
$ docker exec -i \
    learn-postgres \
    psql -U root -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"ro\";"
```
The database is available and the role is created with the appropriate permissions.

Start Vault

In another 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.
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
```
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
```

The Vault server is ready to proceed with the lab.

Enable the database secrets engine

(Persona: admin)

The database secrets engine generates database credentials dynamically based on configured roles.

Enable the database secrets engine at the database/ path.

$ vault secrets enable database

Enable the database secrets engine at the database/ path.

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

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.

Configure PostgreSQL secrets engine

(Persona: admin)

The database secrets engine supports many databases through a plugin interface. To use a PostgreSQL database with the secrets engine requires further configuration with the postgresql-database-plugin plugin and connection information.

Tip

This task uses the connection information defined in the Start PostgreSQL step.

Configure the database secrets engine with the connection credentials for the PostgreSQL database.

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

Create an API request payload with the database configuration.

$ 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

Configure the database secrets engine with the connection credentials for the PostgreSQL database.

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

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}}@$POSTGRES_URL/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.

The secrets engine is configured to work with PostgreSQL.

Tip

Users of Vault version 1.11.0 and beyond can specify multiple comma-separated postgres server URLs in the value of connection_url, and Vault will retry communication with each server in the list until it can connect to one that is actively handling requests.

Read the Database Root Credential Rotation tutorial to learn about rotating the root credential immediately after the initial configuration of each database.

Create a role

(Persona: admin)

In configure PostgreSQL secrets engine step, you configured the PostgreSQL secrets engine with the allowed role named readonly. A role is a logical name within Vault that maps to database credentials. These credentials are expressed as SQL statements and assigned to the Vault role.

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.

Define the SQL used to create credentials.
```
$ tee readonly.sql <<EOF
CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' INHERIT;
GRANT ro TO "{{name}}";
EOF
```
The SQL contains the templatized fields {{name}}, {{password}}, and {{expiration}}. These values are provided by Vault when the credentials are created. This creates a new role and then grants that role the permissions defined in the PostgreSQL role named ro. This PostgreSQL role was created when PostgreSQL was started.

Create the role named readonly that creates credentials with the readonly.sql.

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

Create an API request payload containing the database role definition.

$ tee payload.json <<EOF
{
   "db_name": "postgres",
   "creation_statements": [
   "CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' INHERIT;",
   "GRANT ro TO \"{{name}}\";"
],
   "default_ttl": "1h",
   "max_ttl": "24h"
}
EOF

Create the role named readonly that creates credentials with the creation_statements defined in the payload.

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

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 Creation statements, enter the following SQL statements:

CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' INHERIT;
GRANT ro TO "{{name}}";

Role settings part one

Click Create role.

The role generates database credentials with a default TTL of 1 hour and max TTL of 24 hours.

Request PostgreSQL credentials

(Persona: developer)

The applications that require the database credentials read them from the secret engine's readonly role.

Read credentials from the readonly database role.

$ vault read database/creds/readonly

Key                Value
---                -----
lease_id           database/creds/readonly/fyF5xDomnKeCHNZNQgStwBKD
lease_duration     1h
lease_renewable    true
password           A1a-ckirtymYaXACpIHn
username           v-token-readonly-6iRIcGv8tLpu816oblPY-1556567086

On the readonly role page, select Generate credentials.
On the credentials page, click the clipboard icon to copy the Username value to your system clipboard and then record the value somewhere safe to use in the next steps.
On the credentials page, click the clipboard icon to copy the Password value to your system clipboard and then record the value somewhere safe to use in the next steps.
Use the copied values to validate the connection in the next step.

The PostgreSQL credentials are displayed as username and password. The credentials are identified within Vault by the lease_id.

Validation

Connect to the PostgreSQL database and list all database users.

$ docker exec -i \
    learn-postgres \
    psql -U root -c "SELECT usename, valuntil FROM pg_user;"

The output displays a table of all the database credentials generated. The credentials that were recently generated appear in this list.

                     usename                      |        valuntil
--------------------------------------------------+------------------------
 root                                             |
 v-token-readonly-ExP3fop3xpzCoZkzdiT7-1635943853 | 2021-11-04 12:50:58+00
(2 rows)

Manage leases

(Persona: admin)

The credentials are managed by the lease ID and remain valid for the lease duration (TTL) or until revoked. Once revoked the credentials are no longer valid.

List the existing leases.

$ vault list sys/leases/lookup/database/creds/readonly

Keys
----
IQKUMCTg3M5QTRZ0abmLKjTX

All valid leases for database credentials are displayed.

Create a variable that stores the first lease ID.

$ LEASE_ID=$(vault list -format=json sys/leases/lookup/database/creds/readonly | jq -r ".[0]")

Renew the lease for the database credential by passing its lease ID.

$ vault lease renew database/creds/readonly/$LEASE_ID

Key                Value
---                -----
lease_id           database/creds/readonly/IQKUMCTg3M5QTRZ0abmLKjTX
lease_duration     1h
lease_renewable    true

The TTL of the renewed lease is set to 1h.

Revoke the lease without waiting for its expiration.

$ vault lease revoke database/creds/readonly/$LEASE_ID
All revocation operations queued successfully!

List the existing leases.

$ vault list sys/leases/lookup/database/creds/readonly
No value found at sys/leases/lookup/database/creds/readonly/

The lease is no longer valid and is not displayed.

Read new credentials from the readonly database role.

$ vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/P6tTTiWsR1fVCp0btLktU0Dm
lease_duration     1m
lease_renewable    true
password           A1a-pfgGk7Ptb0TxGBJI
username           v-token-readonly-9blxDY3dIKXsFMkv8kvH-1600278284

All leases associated with a path may be removed.

Revoke all the leases with the prefix database/creds/readonly.
```
$ vault lease revoke -prefix database/creds/readonly
```
The prefix flag matches all valid leases with the path prefix of database/creds/readonly.

List the existing leases.

$ vault list sys/leases/lookup/database/creds/readonly
No value found at sys/leases/lookup/database/creds/readonly/

All the leases with this path as a prefix have been revoked.

List the existing lease IDs.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
      --request LIST \
      $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq -r ".data.keys"

All valid leases for database credentials are displayed.

Create a variable that stores the first (and only) lease ID.

$ LEASE_ID=$(curl --header "X-Vault-Token: $VAULT_TOKEN" \
      --request LIST \
      $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq -r ".data.keys[0]")

Renew the lease for the database credential by passing its lease ID.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request PUT \
   --data "{ \"lease_id\": \"database/creds/readonly/$LEASE_ID\" }" \
   $VAULT_ADDR/v1/sys/leases/renew | jq

The TTL of the renewed lease is set to 1h.

Revoke the lease without waiting for its expiration.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
      --request PUT \
      --data "{ \"lease_id\": \"database/creds/readonly/$LEASE_ID\" }" \
      $VAULT_ADDR/v1/sys/leases/revoke

List the existing leases.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
      --request LIST \
      $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq

The lease is no longer valid and is not displayed.

Example output:

{
   "errors": []
}

Read new credentials from the readonly database role.

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

All leases associated with a path may be removed.

Revoke all the leases with the prefix database/creds/readonly.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
      --request PUT \
      $VAULT_ADDR/v1/sys/leases/revoke-prefix/database/creds/readonly

The prefix flag matches all valid leases with the path prefix of database/creds/readonly.

List the existing leases.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
      --request LIST \
      $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq

All the leases with this path as a prefix have been revoked.

Define a username template

The database secret engines generate usernames that adhere to a default pattern. You can create a username template to meet the needs of your organization.

Tip

Ensure that custom username templates include enough randomness to prevent the same username being generated multiple times.

Read the User configurable password generation for secret engines tutorial to learn how to configure a password policy if the default format does not fulfill your organization's need.

Read credentials from the readonly database role.
```
$ vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/ZxoKlbklsliYA4hZs7umoPIz
lease_duration     1h
lease_renewable    true
password           9MSegMz7N1Fr69ZTyb#D
username           v-token-readonly-wGLPkpDyc6AgqBfMZTD3-1604195404
```
The generated username, v-token-readonly-wGLPkpDyc6AgqBfMZTD3-1604195404, uses the default pattern expressed as a Go template, {{ printf "v-%s-%s-%s-%s" (.DisplayName | truncate 8) (.RoleName | truncate 8) (random 20) (unix_time) | truncate 63 }}.
Refer to the Username Templating documentation to learn more functions that can be applied.
Configure the database secrets engine with the username template.
```
$ vault write database/config/postgresql \
   username_template="myorg-{{.RoleName}}-{{unix_time}}-{{random 8}}"
```
This username template is prefixed with myorg-, uses the name of role, readonly, the unix timestamp in seconds, and a random sequence of 8 characters.

Read credentials from the readonly database role.

$ vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/NOCGtSbz7g4FFjcztX6Bqh3S
lease_duration     1h
lease_renewable    true
password           -h3B-JteYjgOPYIC6dGQ
username           myorg-readonly-1616447348-af9eHMWD

Read credentials from the readonly database role.
```
$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
      --request GET \
      $VAULT_ADDR/v1/database/creds/readonly | jq
```
The generated username uses the default pattern expressed as a Go template {{ printf "v-%s-%s-%s-%s" (.DisplayName | truncate 8) (.RoleName | truncate 8) (random 20) (unix_time) | truncate 63 }}.
Refer to the Username Templating documentation to learn more functions that can be applied.

Create an API request payload with the database configuration.

$ 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",
"password_policy": "example",
"username_template": "myorg-{{.RoleName}}-{{unix_time}}-{{random 8}}"
}
EOF

This username template is prefixed with myorg-, uses the name of role, readonly, the unix timestamp in seconds, and a random sequence of 8 characters.

Re-configure the database secrets engine with the connection credentials for the PostgreSQL database.

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

Read credentials from the readonly database role.

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

Navigate to Secrets → database → Connections.
Click the ellipsis ... and select Edit connection.
Click *Edit configuration**.
Scroll down and click the Username template toggle.
Enter the username template code myorg-{{.RoleName}}-{{unix_time}}-{{random 8}} into the text field.
Click Save.

Read credentials from the readonly database role.

Navigate to Secrets → database → Roles.
Click the ellipsis ... and select Generate credentials.
Click the sensitive information toggle to reveal the templated username.

The username generated adheres to the template provided to the configuration.

Rootless static role rotation

Minimum version required

To perform the steps in this section, use Vault Enterprise 1.18 or later. Exit your Vault instance and PostgreSQL container (see Clean up), then start up a Vault enterprise dev server, and rerun Lab Setup with a Vault enterprise binary and licence. Once you have finished the Lab setup section, run the Enable the database secrets engine step before continuing. Ensue that the POSTGRES_URL environment variable is set.

A database secrets engine requires the configuration of a root connection with privileged database account. With this root connection, users are able to use Vault features like access-control and lease management to allow database roles and create new database users/credentials. The Databases section of the Vault documentation has details on the database secrets engine feature-set.

Initialization of a new database configuration creates this highly privileged connection over which all database resources are managed. These "Vault-managed" connections are responsible for making all the necessary queries/commands to the database to create, read, update, and delete users/credentials within the database.

Not only do these connections have excessive privileges, but each database connection requires individual setup. At a large enterprise, this results in hundreds of CLI commands or giant Terraform modules.

Administering hundreds of privileged accounts is also untenable, with creations and the maintenance of these accounts being a burden on the Vault operator.

Each static role has a privileged connection.

Static roles are a 1-to-1 mapping of a Vault role to a user in a database. With static roles, Vault stores and automatically rotates passwords for the associated database user based on a configurable period of time or rotation schedule. Static roles use the default database connection, which unnecessarily high privileges for most uses.

Rootless static role rotation would allow customers to configure dedicated connections to external "self-managed" database accounts with lower-levels of privilege. Each connection maps to a static role with limited privileges, instead of managing all resources in Vault with a single "Vault-managed" dedicated connection to a highly privileged account.

Each static roles manages it's limited privilege connection

Rootless static roles offer these advantages:

Create static roles as needed so no long-lived user accounts, reducing risk of leaked credentials
Root database credentials not in Vault
Database admins can trace actions back to specific users

Set up an environment variable for a PostgreSQL connection URL.

$ export PGCONNURL="postgresql://root:rootpassword@$POSTGRES_URL"

Create a new user for use with rootless static roles.

$ docker run --rm --net host postgres psql  $PGCONNURL -c "CREATE ROLE staticuser_pg WITH LOGIN PASSWORD 'staticpassrootless';"

Verify creation of the user by querying the pg_roles table.

$ docker run --rm --net host postgres psql  $PGCONNURL -c "select * from pg_roles;"

Create a table on the database.

$ docker exec -i learn-postgres \
   psql -U root -c "CREATE TABLE cities (name varchar(80),location point);"

Grant staticuser_pg SELECT privileges on the cities table.

$ docker exec -i learn-postgres psql -U root -c \
   "GRANT SELECT ON cities TO \"staticuser_pg\";"

Check the permissions for staticuser_pg.

$ docker exec -i learn-postgres psql -U root -c \
   "SELECT grantee, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'staticuser_pg';"

Example output:

grantee       | table_name | privilege_type
--------------+------------+----------------
staticuser_pg | cities     | SELECT

Create a connection configuration for the postgresql-database-plugin.

$ vault write database/config/postgres-db-rootless \
   plugin_name=postgresql-database-plugin \
   allowed_roles=staticuser \
   connection_url="postgresql://{{username}}:{{password}}@$POSTGRES_URL/postgres?sslmode=disable" \
   verify_connection=false \
   self_managed=true

The self_managed field on root configuration configures database secrets engine to use the dedicated connection for the staticuser role.

Create the staticuser role specified in allowed_roles field in the last step.
```
$ vault write database/static-roles/staticuser \
   db_name=postgres-db-rootless \
   username="staticuser_pg" \
   self_managed_password="staticpassrootless" \
   rotation_period=5m
```
The db_name field indicates that this user has access to the postgres-db-rootless database connection, and credentials for static roles are automatically rotated based on the rotation_period. The username field is referring to the staticuser_pg on the PostgresSQL side.

Now read the credentials from the statcuser role.

$ vault read database/static-creds/staticuser
Key                    Value
---                    -----
last_vault_rotation    2024-09-19T14:29:26.934329-05:00
password               onBp9x5sVhLWQ7u6XV-S
rotation_period        5m
ttl                    5m
username               staticuser_pg

Notice the username is the PostgreSQL user created earlier, it has an initial password that is different than was originally set (staticpassrootless) and the password will change in 5 minutes.

Clean up

Unset the VAULT_TOKEN, VAULT_ADDR, POSTGRES_URL and the PGCONNURL environment variable.

$ unset VAULT_TOKEN && unset VAULT_ADDR && unset POSTGRES_URL && unset PGCONNURL

Stop the PostgreSQL container.

$ docker stop $(docker ps -f name=learn-postgres -q)

Use CTRL+C to stop the server process in the terminal window where you started the server, or use this command to kill the server process from any local terminal session:
```
$ grep -f vault | xargs kill
```

Next step

There are some tools available to help integrate your applications with Vault's database secrets engine. Using those tools, the existing applications may require minimum to no code change to work with Vault.

Refer to the Vault Agent caching tutorial.

Help and reference

Collection Overview

Database credentials

Database root credential rotation

This tutorial also appears in:

2 tutorials

Vault 1.18 release highlights
The listed tutorials were updated to showcase the new enhancements introduced in Vault 1.18.
- Vault