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.

»Prerequisites

To perform the tasks described in this tutorial, you need to have:

• A Vault environment of version 1.2 or later.

• A PostgreSQL environment you can connect, or Docker to run a PostgreSQL in a container.

  • An existing database user in PostgreSQL

»Personas

The end-to-end scenario described in this tutorial involves two personas:

• admin with privileged permissions to configure secrets engines
• apps read the secrets from Vault

»Policy requirements

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.

»Step 1: Run PostgreSQL in Docker container

(Persona: admin)

Let's run PostgreSQL Docker image in a container.

Execute the following command to start a postgres instance which listens to port 5432, and the superuser (root) password is set to rootpassword.

# For the purpose of this demo, keeping it simple
$ docker run --name postgres -e POSTGRES_USER=root \
     -e POSTGRES_PASSWORD=rootpassword \
     -d -p 5432:5432 postgres

Verify that the postgres container is running.

$ docker ps
CONTAINER ID        IMAGE            ...         PORTS                    NAMES
befcf913da91        postgres         ...         0.0.0.0:5432->5432/tcp   postgres

Let's connect to the postgres container.

$ docker exec -it postgres bash

To perform the tasks in this tutorial, create a user, vault-edu with password, mypassword.

/# psql -U root

root=# CREATE ROLE "vault-edu" WITH LOGIN PASSWORD 'mypassword';

root=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "vault-edu";

root=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      | Superuser                                                  | {}
 vault-edu |                                                            | {}

root=# \q

Now, type exit to exit out of the container, or open another terminal to continue.

»Step 2: Setup the database secrets engine

(Persona: admin)

First, enable the database secrets engine, and then configure it so that it can connect to the PostgreSQL server.

»Step 3: 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.

First, create a file named, rotation.sql with following SQL statements.

ALTER USER "{{name}}" WITH PASSWORD '{{password}}';

$ vault write database/static-roles/education \
    db_name=postgresql \
    rotation_statements=@rotation.sql \
    username="vault-edu" \
    rotation_period=86400

$ 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 4: 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.

First, create a file named, apps.hcl with following policy.

# Get credentials from the database secrets engine
path "database/static-creds/education" {
  capabilities = [ "read" ]
}

»Validation

Connect to the postgres container.

$ docker exec -it postgres bash

Verify that you can connect to the psql with username, vault-edu.

# psql -d postgres -U vault-edu -W
Password for user vault-edu:

When prompted, enter the password you copied in the last step. You should be able to connect successfully.

postgres=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      | Superuser                                                  | {}
 vault-edu |                                                            | {}

# \q

Execute exit to exit out of the postgres container.

»Step 5: 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.

»Help and Reference

• Secrets Engines - Databases
• Secrets as a Service: Dynamic Secrets
• Database Root Credential Rotation