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 secret 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 secret engines
path "sys/mounts/*" {
  capabilities = [ "create", "read", "update", "delete", "list" ]
}

# Configure the database secret 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.

Steps

In this guide, you are going to configure PostgreSQL secret engine, and create a static read-only database role with username, "vault-edu". The Vault generated PostgreSQL credentials will only have read permission.

  1. Run PostgreSQL in Docker container
  2. Setup the database secret engine
  3. Create a static role
  4. Request PostgreSQL credentials
  5. Manually rotate the password

Scenario

Step 1: Run PostgreSQL in Docker container

(Persona: admin)

For the purpose of this tutorial, 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 postgres

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

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

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

postgres=# \q

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

Step 2: Setup the database secret engine

(Persona: admin)

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

CLI command / API call using cURL

CLI command

  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 secret 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
    

API call using cURL

  1. Mount database secret engine using /sys/mounts endpoint:

    $ curl --header "X-Vault-Token: ..." \
           --request POST \
           --data '{"type":"database"}' \
           https://127.0.0.1:8200/v1/sys/mounts/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. Invoke the /database/config endpoint to configure the database secret engine which uses postgresql-database-plugin.

    # Create the HTTP request payload
    $ tee payload.json <<EOF
    {
      "plugin_name": "postgresql-database-plugin",
      "allowed_roles": "*",
      "connection_url": "postgresql://{{username}}:{{password}}@localhost:5432/postgres?sslmode=disable",
      "username": "root",
      "password": "rootpassword"
    }
    EOF
    
    # Invoke the database/config/postgresql endpoint
    $ curl --header "X-Vault-Token: ..." \
           --request POST \
           --data @payload.json \
           http://127.0.0.1:8200/v1/database/config/postgresql
    
  3. Execute the /database/rotate-root endpoint to rotate the root credentials:

    $ curl --header "X-Vault-Token: ..." \
           --request POST \
           http://127.0.0.1:8200/v1/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}}';

CLI command / API call using cURL

CLI command

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

API call using cURL

Invoke the /database/static-roles endpoint to create a new static role named "education":

# Create the HTTP request payload
$ tee payload.json <<EOF
{
    "db_name": "postgresql",
    "rotation_statements": [ALTER USER "{{name}}" WITH PASSWORD '{{password}}';],
    "username: "vault-edu",
    "rotation_period": "86400"
}
EOF

# Invoke the database/roles API endpoint
$ curl --header "X-Vault-Token: ..." \
       --request POST --data @payload.json \
       http://127.0.0.1:8200/v1/database/static-roles/education

The above API call 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, read back the education role definition:

$ curl --header "X-Vault-Token: ..." \
       http://127.0.0.1:8200/v1/database/static-roles/education | jq
{
  ...
  "data": {
    "db_name": "postgresql",
    "last_vault_rotation": "2019-06-24T10:18:39.766203-07:00",
    "rotation_period": 86400,
    "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 secret engine
path "database/static-creds/education" {
  capabilities = [ "read" ]
}

CLI command / API call using cURL / Web UI

CLI command

  1. Create an apps policy, and generate a token so that you can authenticate as an apps persona.

    # Create "apps" policy
    $ vault policy write apps apps.hcl
    Policy 'apps' written.
    
    # Create a new token with app policy
    $ 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"]
    
  2. 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
    
  3. 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).

API call using cURL

  1. First create an apps policy, and generate a token so that you can authenticate as an app persona.

    # Create the HTTP request payload
    $ tee payload.json <<EOF
    {
      "policy": "path \"database/static-creds/education\" {capabilities = [ \"read\" ]}"
    }
    EOF
    
    # Create "apps" policy
    $ curl --header "X-Vault-Token: ..." --request PUT \
           --data @payload.json \
           http://127.0.0.1:8200/v1/sys/policies/acl/apps
    
    # Generate a new token with apps policy
    $ curl --header "X-Vault-Token: ..." --request POST \
           --data '{"policies": ["apps"]}' \
           http://127.0.0.1:8200/v1/auth/token/create | jq
    {
       ...
       "auth": {
         "client_token": "s.qbloIGiHddxc3chdtE2TrGX7",
         "accessor": "8zrb4SIVJ4DMZUsfr6uRsqdu",
         "policies": [
           "apps",
           "default"
         ],
         "token_policies": [
           "apps",
           "default"
         ],
         ...
       }
    }
    
  2. Invoke the database/static-creds/education endpoint to get credentials for the "vault-edu" role:

    $ curl --header "X-Vault-Token: s.qbloIGiHddxc3chdtE2TrGX7" \
           http://127.0.0.1:8200/v1/database/static-creds/education | jq
    {
       ...
       "data": {
         "last_vault_rotation": "2019-06-06T22:23:17.063096-07:00",
         "password": "A1a-jxH944nG0qHRpMNR",
         "rotation_period": 86400,
         "ttl": 84243,
         "username": "vault-edu"
       },
       ...
    }
    
  3. Re-run the command and verify that returned password is the same with updated TTL.

    $ curl --header "X-Vault-Token: s.qbloIGiHddxc3chdtE2TrGX7" \
           http://127.0.0.1:8200/v1/database/static-creds/education | jq
    {
       ...
       "data": {
         "last_vault_rotation": "2019-06-06T22:23:17.063096-07:00",
         "password": "A1a-jxH944nG0qHRpMNR",
         "rotation_period": 86400,
         "ttl": 84137,
         "username": "vault-edu"
       },
       ...
    }
    

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

Web UI

  1. Open a web browser and launch the Vault UI (e.g. http://127.0.0.1:8200/ui) and then login.

  2. Click the Policies tab, and then select Create ACL policy.

  3. Toggle Upload file sliding switch, and click Choose a file to select your apps.hcl file you authored. This loads the policy and sets the Name to be apps.

  4. Click Create Policy to complete.

  5. Click the Vault CLI shell icon (>_) to open a command shell. Execute vault write auth/token/create policies=apps in the CLI shell to create a new token: Create token

  6. Copy the generated client token value.

  7. Sign out of the Vault UI.

  8. Now, sign into the Vault using the newly generated token you just copied.

  9. Click the Vault CLI shell icon (>_) to open a command shell. Execute vault read database/static-creds/education in the CLI shell.

  10. Re-run the command and verify that returned password is the same with updated TTL.

  11. 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.

CLI command / API call using cURL / Web UI

CLI command

  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.

API call using cURL

  1. Invoke the database/rotate-role endpoint to rotate the credential for the static role, "education":

    $ curl --header "X-Vault-Token: ..." \
           --request POST \
           http://127.0.0.1:8200/v1/database/rotate-role/education
    
  2. Now, read the credentials to verify that the password has been rotated.

    $ curl --header "X-Vault-Token: ..." \
           http://127.0.0.1:8200/v1/database/static-creds/education | jq
    {
       ...
       "data": {
          "last_vault_rotation": "2019-06-11T09:34:30.633828-07:00",
          "password": "A1a-glMoEe7MgJY71v4E",
          "rotation_period": 86400,
          "ttl": 86305,
          "username": "vault-edu"
       },
       ...
    }
    

    The returned password should be different from previous output, and the remaining TTL has been back to ~24 hours.

Web UI

  1. Open a web browser and launch the Vault UI (e.g. http://127.0.0.1:8200/ui) and then login with admin token.

  2. Click the Vault CLI shell icon (>_) to open a command shell. Execute vault write -f database/rotate-role/education in the CLI shell to rotate the password for static role, "education".

  3. Now, execute vault read database/static-creds/education in the CLI shell.

The returned password should be different from previous output, and the remaining TTL has been back to ~24 hours.

Help and Reference