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.

NOTE: The database secrets engine supports several database types. See the Database Capabilities table to check the availability of this feature.

»Prerequisites

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

HCP or OSS Vault environment
Docker to run a PostgreSQL container
jq installed
psql or libpq installed
ngrok installed and configured with an auth token (HCP Vault only)

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

»Lab setup

»Start PostgreSQL

The tutorial requires a Postgres database. Docker provides a Postgres server image that satisfies this requirement.

Open a new terminal and pull a Postgres server image with docker.
```
$ docker pull postgres:latest
```
```
$ docker pull postgres:latest
```

Create a Postgres database with a root user named root with the password rootpassword.

$ docker run \
      --name learn-postgres \
      --env POSTGRES_USER=root \
      --env POSTGRES_PASSWORD=rootpassword \
      --detach  \
      --publish 5432:5432 \
      postgres

$ docker run \
      --name learn-postgres \
      --env POSTGRES_USER=root \
      --env POSTGRES_PASSWORD=rootpassword \
      --detach  \
      --publish 5432:5432 \
      postgres

Create a role named vault-edu.

$ docker exec -i \
    learn-postgres \
    psql -U root -c "CREATE ROLE \"vault-edu\" WITH LOGIN PASSWORD 'mypassword';"

$ docker exec -i \
    learn-postgres \
    psql -U root -c "CREATE ROLE \"vault-edu\" WITH LOGIN PASSWORD 'mypassword';"

Grant associated privileges for the role.

$ docker exec -i \
    learn-postgres \
    psql -U root -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"vault-edu\";"

$ docker exec -i \
    learn-postgres \
    psql -U root -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"vault-edu\";"

Confirm role attributes.

$ docker exec -i learn-postgres psql -U root -c "\du"
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 root      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vault-edu |                                                            | {}

$ docker exec -i learn-postgres psql -U root -c "\du"
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 root      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vault-edu |                                                            | {}

The PostgreSQL server is ready.

»Start Vault

In a new terminal start a Vault dev server with root as the root token.
```
$ vault server -dev -dev-root-token-id root
```
```
$ vault server -dev -dev-root-token-id root
```
The Vault dev server defaults to running at 127.0.0.1:8200. The server is initialized and unsealed.
Insecure operation: Do not run a Vault dev server in production. This approach starts a Vault server with an in-memory database and runs in an insecure way.
In a new terminal export an environment variable for the vault CLI to address the Vault server.
```
$ export VAULT_ADDR=http://127.0.0.1:8200
```
```
$ export VAULT_ADDR=http://127.0.0.1:8200
```
Export an environment variable for the vault CLI to authenticate with the Vault server.
```
$ export VAULT_TOKEN=root
```
```
$ export VAULT_TOKEN=root
```
The Vault server is ready.
NOTE: For these tasks, you can use Vault's root token. However, it is recommended that root tokens are only used for enough initial setup or in emergencies. As a best practice, use an authentication method or token that meets the policy requirements.

Set an environment variable for the PostreSQL address.

$ export POSTGRES_URL=127.0.0.1:5432

$ export POSTGRES_URL=127.0.0.1:5432

You are ready to proceed with the lab.

»Step 1: Setup the database secrets engine

(Persona: admin)

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
```
NOTE: 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}}@$POSTGRES_URL/postgres?sslmode=disable" \
    username="root" \
    password="rootpassword"

$ vault write database/config/postgresql \
    plugin_name=postgresql-database-plugin \
    allowed_roles="*" \
    connection_url="postgresql://{{username}}:{{password}}@$POSTGRES_URL/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 2: 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.

Create a file named, rotation.sql with following SQL statements.

$ tee rotation.sql <<EOF
ALTER USER "{{name}}" WITH PASSWORD '{{password}}';
EOF

$ tee rotation.sql <<EOF
ALTER USER "{{name}}" WITH PASSWORD '{{password}}';
EOF

Create a static role named 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.

Verify the configuration of the education role.

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

Create a file named apps.hcl with the following policy.

$ tee apps.hcl <<EOF
path "database/static-creds/education" {
  capabilities = [ "read" ]
}
EOF

$ tee apps.hcl <<EOF
path "database/static-creds/education" {
  capabilities = [ "read" ]
}
EOF

Create a policy named apps using the apps.hcl file.

$ vault policy write apps apps.hcl

Policy 'apps' written.

$ vault policy write apps apps.hcl

Policy 'apps' written.

Generate a token so that you can authenticate as an apps persona and save the token as an environment variable.

$ APPS_TOKEN=$(vault token create -policy="apps" -format json | jq -r '.auth | .client_token')

$ APPS_TOKEN=$(vault token create -policy="apps" -format json | jq -r '.auth | .client_token')

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=$APPS_TOKEN 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=$APPS_TOKEN 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

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

$ VAULT_TOKEN=$APPS_TOKEN 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=$APPS_TOKEN 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

Save the returned password as an environment variable.

$ GEN_DB_PASS=$(VAULT_TOKEN=$APPS_TOKEN vault read \
    -format json  database/static-creds/education | jq -r '.data | .password')

$ GEN_DB_PASS=$(VAULT_TOKEN=$APPS_TOKEN vault read \
    -format json  database/static-creds/education | jq -r '.data | .password')

»Validation

Verify that you can connect to the psql with username vault-edu and run a query.

$ psql postgresql://vault-edu:$GEN_DB_PASS@localhost:5432/postgres -c '\du';

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

$ psql postgresql://vault-edu:$GEN_DB_PASS@localhost:5432/postgres -c '\du';

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

»Step 4: 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/education
Success! 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: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.

Infrastructure

Security

Applications

Networking

cloud

»Challenge

»Solution

»Prerequisites

»Personas

»Policy requirements

»Scenario Introduction

»Lab setup

»Start PostgreSQL

»Start Vault

»Step 1: Setup the database secrets engine

»Step 2: Create a static role

»Step 3: Request PostgreSQL credentials

»Validation

»Step 4: Manually rotate the password

»Help and Reference