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

»Prerequisites

NOTE: Currently, the following database types support static roles: MySQL, PostgreSQL, MongoDB, Oracle, Redshift, and Couchbase.

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

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:

# 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" ]
}

# Mount secrets enginespath "sys/mounts/*" {  capabilities = [ "create", "read", "update", "delete", "list" ]}
# Configure the database secrets engine and create rolespath "database/*" {  capabilities = [ "create", "read", "update", "delete", "list" ]}
# Write ACL policiespath "sys/policies/acl/*" {  capabilities = [ "create", "read", "update", "delete", "list" ]}
# Manage tokens for verificationpath "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.

Scenario

»Step 1: Run PostgreSQL in Docker container

(Persona: admin)

Let's run PostgreSQL Docker image in a container.

NOTE: If you already have a running PostgreSQL server that you can connect to, ignore this step.

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

# 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

$ docker psCONTAINER ID        IMAGE            ...         PORTS                    NAMESbefcf913da91        postgres         ...         0.0.0.0:5432->5432/tcp   postgres

Let's connect to the postgres container.

$ docker exec -it postgres bash

$ 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

/# 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.

Execute the following command to enable the database secrets engine at database/ path.
```
$ vault secrets enable database
```
```
$ vault secrets enable database
```
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.

$ 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"

$ 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"

Execute the following command to rotate the root credentials.

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

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

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

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

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

NOTE: For static roles, the db_name parameter is the database configuration name (not the database name). In this scenario, you configured database/config/postgresql; therefore, the db_name must be set to postgresql.

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

$ vault read database/static-roles/education
Key                    Value---                    -----db_name                postgresqllast_vault_rotation    2019-06-24T10:18:39.766203-07:00rotation_period        24hrotation_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" ]
}

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

Create a policy named apps.

$ vault policy write apps apps.hcl
Policy 'apps' written.

$ vault policy write apps apps.hclPolicy 'apps' written.

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"]

$ vault token create -policy="apps"
Key                  Value---                  -----token                s.NN5Izfj9ok3VuZiaP9N9QJ1Vtoken_accessor       l1QxSKs80HfrzR1gfy5zm7aytoken_duration       768htoken_renewable      truetoken_policies       ["apps" "default"]identity_policies    []policies             ["apps" "default"]

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

$ VAULT_TOKEN=s.NN5Izfj9ok3VuZiaP9N9QJ1V vault read database/static-creds/education
Key                    Value---                    -----last_vault_rotation    2019-06-06T22:23:17.063096-07:00password               A1a-jxH944nG0qHRpMNRrotation_period        24httl                    23h51m29susername               vault-edu

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

$ VAULT_TOKEN=s.NN5Izfj9ok3VuZiaP9N9QJ1V vault read database/static-creds/education
Key                    Value---                    -----last_vault_rotation    2019-06-06T22:23:17.063096-07:00password               A1a-jxH944nG0qHRpMNRrotation_period        24httl                    23h47m35susername               vault-edu

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

»Validation

Connect to the postgres container.

$ docker exec -it postgres bash

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

# psql -d postgres -U vault-edu -WPassword 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

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.

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

$ vault write -f database/rotate-role/educationSuccess! Data written to: database/rotate-role/education

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

$ vault read database/static-creds/education
Key                    Value---                    -----last_vault_rotation    2019-06-11T09:19:52.497767-07:00password               A1a-9Lp1yoJMHPNGGL2Jrotation_period        24httl                    23h59m46susername               vault-edu

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

Infrastructure

Security

Networking

Applications

»Challenge

»Solution

»Prerequisites

»Personas

»Policy requirements

»Scenario Introduction

»Step 1: Run PostgreSQL in Docker container

»Step 2: Setup the database secrets engine

»Step 3: Create a static role

»Step 4: Request PostgreSQL credentials

»Validation

»Step 5: Manually rotate the password

»Help and Reference