Dynamic secrets for database credential management

15min
|
Vault
Interactive

Deployment

You should have a credential management strategy to protect critical data. Often, organizations have a policy to periodically rotate credentials. Also, you want to assign a different set of permissions granted for each user, system, or application that accesses data following the principle of least privilege.

Vault's database secrets engine provides a 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

In this set of tutorials, you will follow the HashiCups organization as they configure Vault to provide dynamic credentials using the database secrets engine.

This collection is designed to help you learn about Vault's dynamic secrets capabilities and apply various concepts to your environment. If you are already familiar with how to configure and request dynamic credentials, use the left navigation menu to go to any of the other tutorials to learn more about specific features.

Scenario

HashiCups requires each app use unique credentials so that they are not shared between services. By making the credentials short-lived, you reduce the chance that they might be compromised. If an app becomes compromised, the credentials used by the app can be revoked rather than changing more global sets of credentials across multiple services. You can also automate continuous credential rotation to minimize risk.

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

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

Operations: Oliver from the operations team with privileged permissions configures Vault and the database secrets engine.
Developer: Danielle with the development tests the credentials to ensure apps can read secrets from Vault and access the PostgreSQL database.

Prerequisites

This lab was tested on macOS using an x86_64 based and Apple silicon-based processors. You may also run this tutorial by clicking the Start interactive lab button.

Launch Terminal

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

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)

Set up the lab

Start PostgreSQL

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

Pull a PostgreSQL Docker image.
```
$ 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 requires a role named ro that has been granted the ability to read all tables.

Create a role named ro which can read all tables.

$ 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

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>
```
Note
For these tasks, you can use HCP Vault's admin token. However, it is recommended that admin 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 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...
```
For Vault Dedicated to interact with resources running on your local machine, you must create a connection to the PostgreSQL database. In this tutorial you will use ngrok to create a tunnel to the PostgreSQL database. In production environments this might be a peering connection or VPN.

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>
```

The Vault Dedicated server is ready to proceed with the lab.

Configure the database secrets engine

(Persona: Operations)

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

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.

Enable the database secrets engine at the database/ path.
```
$ vault secrets enable database
```

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

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

Enable the database secrets engine at the database/ path.

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

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 \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --data @payload.json \
   $VAULT_ADDR/v1/database/config/postgres

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.
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 postgres.
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 enabled and 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: Operations)

In the configure database secrets engine section, you configured the database secrets engine with the allowed role named readonly. A Vault role is a logical name within Vault that maps to database credentials. These credentials have capabilities defined by SQL statements 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" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --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 postgres.
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 dynamic 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

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;"

Example output:

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

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

Read credentials from the readonly database role.

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

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;"

Example output:

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

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

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.
The PostgreSQL credentials are displayed as username and password. The credentials are identified within Vault by the lease_id.

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;"

Example output:

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

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

Clean up

Unset the VAULT_TOKEN, VAULT_ADDR, POSTGRES_URL and the PGCONNURL environment variable.
```
$ unset VAULT_TOKEN VAULT_ADDR POSTGRES_URL 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:
```
$ pgrep -f vault | xargs kill
```

Summary

In this tutorial, you followed the HashiCups operations team as they enabled the database secrets engine, and configured the PostgreSQL plugin.

The development team then requested credentials from the readonly role. Vault created the credentials dynamically and provided the username and password to access the PostgreSQL database.

Help and reference

There are 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.

For more information on the database secrets engine, refer to the Vault documentation.

Collection Overview

Database credentials

Manage dynamic credential leases

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