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
In a new 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.
In a new terminal 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
The Vault server is ready.
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
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.