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.
![DB Creds Rotation](/_next/image?url=https%3A%2F%2Fcontent.hashicorp.com%2Fapi%2Fassets%3Fproduct%3Dtutorials%26version%3Dmain%26asset%3Dpublic%252Fimg%252Fvault%252Fvault-db-rotate-1.png%26width%3D906%26height%3D387&w=1920&q=75)
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:
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.
![Scenario](/_next/image?url=https%3A%2F%2Fcontent.hashicorp.com%2Fapi%2Fassets%3Fproduct%3Dtutorials%26version%3Dmain%26asset%3Dpublic%252Fimg%252Fvault%252Fvault-db-rotate-1.png%26width%3D906%26height%3D387&w=1920&q=75)
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
.Create a Postgres database with a root user named
root
with the passwordrootpassword
.Create a role named
vault-edu
.Grant associated privileges for the role.
Confirm role attributes.
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.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.Set the
VAULT_NAMESPACE
environment variable toadmin
.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.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.
Example output:
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://
.
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.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
.Execute the following command to rotate the root credentials.
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.Create a static role named
education
.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.
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.Create a policy named
apps
using theapps.hcl
file.Generate a token so that you can authenticate as an
apps
persona and save the token as an environment variable.Execute the following command to request credentials for role,
vault-edu
. Be sure to use the token you acquired in the previous step.Re-run the command and verify that returned password is the same with updated TTL.
Save the returned password as an environment variable.
Validation
Verify that you can connect to the psql
with username vault-edu
and run a query.
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".
Now, read the credentials to verify that the password has been rotated.
The returned password should be different from previous output, and the remaining TTL has been back to ~24 hours.