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
.Create a PostgreSQL database with a root user named
root
with the passwordrootpassword
.Verify that the PostgreSQL container is running.
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
.Grant the ability to read all tables to the role named
ro
.The database is available and the role is created with the appropriate permissions.
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.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://
.
The Vault Dedicated 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.
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.
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.
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
.
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.
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.
The output displays a table of all the database credentials generated. The credentials that were recently generated appear in this list.
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.
All valid leases for database credentials are displayed.
Create a variable that stores the first lease ID.
Renew the lease for the database credential by passing its lease ID.
The TTL of the renewed lease is set to
1h
.Revoke the lease without waiting for its expiration.
List the existing leases.
The lease is no longer valid and is not displayed.
Read new credentials from the
readonly
database role.All leases associated with a path may be removed.
Revoke all the leases with the prefix
database/creds/readonly
.The
prefix
flag matches all valid leases with the path prefix ofdatabase/creds/readonly
.List the existing leases.
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.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.
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.
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%2Fmktg-content-api.vercel.app%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%2Fmktg-content-api.vercel.app%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.
Create a new user for use with rootless static roles.
Verify creation of the user by querying the
pg_roles
table.Create a table on the database.
Grant
staticuser_pg
SELECT privileges on thecities
table.Check the permissions for
staticuser_pg
.Example output:
Create a connection configuration for the
postgresql-database-plugin
.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.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.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.Stop the PostgreSQL container.
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:
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.