Dynamic secrets for database credential management

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.

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.

Start interactive lab

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.

1. Pull a PostgreSQL server image with docker.

   $ docker pull postgres:latest

2. 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

3. Verify that the PostgreSQL container is running.

   $ docker ps -f name=learn-postgres --format "table {{.Names}}\t{{.Status}}"NAMES            STATUSlearn-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.

4. Create a role named ro.

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

5. 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

1. 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.

2. Export an environment variable for the vault CLI to address the Vault server.

   $ export VAULT_ADDR=http://127.0.0.1:8200

3. 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.

4. 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.

$ vault secrets enable database

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

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.

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

The secrets engine is configured to work with PostgreSQL.

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.

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.

$ vault read database/creds/readonly Key                Value---                -----lease_id           database/creds/readonly/fyF5xDomnKeCHNZNQgStwBKDlease_duration     1hlease_renewable    truepassword           A1a-ckirtymYaXACpIHnusername           v-token-readonly-6iRIcGv8tLpu816oblPY-1556567086

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

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.

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.

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.

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

Rootless static role rotation

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.

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.

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

1. Set up an environment variable for a PostgreSQL connection URL.

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

2. 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';"

3. Verify creation of the user by querying the pg_roles table.

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

4. Create a table on the database.

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

5. 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\";"

6. 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

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

8. 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.

9. Now read the credentials from the statcuser role.

   $ vault read database/static-creds/staticuserKey                    Value---                    -----last_vault_rotation    2024-09-19T14:29:26.934329-05:00password               onBp9x5sVhLWQ7u6XV-Srotation_period        5mttl                    5musername               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

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

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

2. Stop the PostgreSQL container.

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

3. 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

• Secrets Engines - Databases
• Role API
• Database Root Credential Rotation