Secrets Management

Database Static Roles and Credential Rotation

Challenge

The Secrets as a Service: Dynamic Secrets guide 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

Vault 1.2 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

Prerequisites

To perform the tasks described in this guide, 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 guide 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 guide, 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 guide.

Scenario Introduction

In this guide, 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

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 guide, 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.

  1. Execute the following command to enable the database secrets engine at database/ path.

    $ vault secrets enable database
    

    This guide 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 guide.

  2. 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}}@localhost:5432/postgres?sslmode=disable \
            username="root" \
            password="rootpassword"
    
  3. Execute the following command to rotate the root credentials.

    $ vault write -force database/rotate-root/postgresql
    

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}}';

Execute the following command to create a static role, 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 username vault-edu whose password gets rotated every 86400 seconds (24 hours). The rotation.sql statement is passed as the rotation statement.

Optional: To verify, execute to following command to read back the education role definition.

$ 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" ]
}
  1. Create a policy named apps.

    $ vault policy write apps apps.hcl
    Policy 'apps' written.
    
  2. Generate a token so that you can authenticate as an apps persona.

    $ vault token create -policy="apps"
    
    Key                  Value
    ---                  -----
    token                s.NN5Izfj9ok3VuZiaP9N9QJ1V
    token_accessor       l1QxSKs80HfrzR1gfy5zm7ay
    token_duration       768h
    token_renewable      true
    token_policies       ["apps" "default"]
    identity_policies    []
    policies             ["apps" "default"]
    
  3. 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=s.NN5Izfj9ok3VuZiaP9N9QJ1V 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
    
  4. Re-run the command and verify that returned password is the same with updated TTL.

    $ VAULT_TOKEN=s.NN5Izfj9ok3VuZiaP9N9QJ1V 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
    

    Copy the returned password (e.g. A1a-jxH944nG0qHRpMNR).

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.

  1. 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
    
  2. 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.

Help and Reference