Vault
Database static roles and credential rotation
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 Vault Community Edition environment
- Docker to run a PostgreSQL container
- jq installed
- psql or
libpq
installed - ngrok installed and configured with an auth token (HCP Vault Dedicated only)
Personas
The end-to-end scenario described in this tutorial involves two personas:
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 passwordrootpassword
.$ 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
Note
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>
Set the
VAULT_NAMESPACE
environment variable toadmin
.$ 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 Vault Dedicated server is ready.
For Vault Dedicated 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 includetcp://
.$ 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
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 usernamevault-edu
whose password gets rotated every 86400 seconds (24 hours). Therotation.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 configureddatabase/config/postgresql
; therefore, thedb_name
must be set topostgresql
.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
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 theapps.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')
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.