Dynamic secrets: 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.
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 passwordrootpassword
.$ 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 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.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
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"
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 <<EOFCREATE 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 namedro
. This PostgreSQL role was created when PostgreSQL was started.Create the role named
readonly
that creates credentials with thereadonly.sql
.$ vault write database/roles/readonly \ db_name=postgresql \ creation_statements=@readonly.sql \ default_ttl=1h \ max_ttl=24h
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/readonlyKey Value--- -----lease_id database/creds/readonly/fyF5xDomnKeCHNZNQgStwBKDlease_duration 1hlease_renewable truepassword A1a-ckirtymYaXACpIHnusername v-token-readonly-6iRIcGv8tLpu816oblPY-1556567086
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/readonlyKeys----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_IDKey Value--- -----lease_id database/creds/readonly/IQKUMCTg3M5QTRZ0abmLKjTXlease_duration 1hlease_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_IDAll revocation operations queued successfully!
List the existing leases.
$ vault list sys/leases/lookup/database/creds/readonlyNo 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/readonlyKey Value--- -----lease_id database/creds/readonly/P6tTTiWsR1fVCp0btLktU0Dmlease_duration 1mlease_renewable truepassword A1a-pfgGk7Ptb0TxGBJIusername 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 ofdatabase/creds/readonly
.List the existing leases.
$ vault list sys/leases/lookup/database/creds/readonlyNo value found at sys/leases/lookup/database/creds/readonly/
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/readonlyKey Value--- -----lease_id database/creds/readonly/ZxoKlbklsliYA4hZs7umoPIzlease_duration 1hlease_renewable truepassword 9MSegMz7N1Fr69ZTyb#Dusername 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/readonlyKey Value--- -----lease_id database/creds/readonly/NOCGtSbz7g4FFjcztX6Bqh3Slease_duration 1hlease_renewable truepassword -h3B-JteYjgOPYIC6dGQusername myorg-readonly-1616447348-af9eHMWD
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.](/_next/image?url=https%3A%2F%2Fcontent.hashicorp.com%2Fapi%2Fassets%3Fproduct%3Dtutorials%26version%3Dmain%26asset%3Dpublic%252Fimg%252Fvault%252Fdiagram-managed-static-roles.png%26width%3D768%26height%3D347&w=1920&q=75)
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](/_next/image?url=https%3A%2F%2Fcontent.hashicorp.com%2Fapi%2Fassets%3Fproduct%3Dtutorials%26version%3Dmain%26asset%3Dpublic%252Fimg%252Fvault%252Fdiagram-self-managed-static-roles.png%26width%3D783%26height%3D516&w=1920&q=75)
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 thecities
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 configuresdatabase
secrets engine to use the dedicated connection for thestaticuser
role.Create the
staticuser
role specified inallowed_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 thepostgres-db-rootless
database connection, and credentials for static roles are automatically rotated based on therotation_period
. Theusername
field is referring to thestaticuser_pg
on the PostgresSQL side.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
Unset the
VAULT_TOKEN
,VAULT_ADDR
,POSTGRES_URL
and thePGCONNURL
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.