Database Root Credential Rotation

Vault's database secrets engine provides a centralized workflow for managing credentials for various database systems. By leveraging this, every service instance gets a unique set of database credentials instead of sharing one. Having those credentials tied directly to each service instance and live only for the life of the service, any abnormal access pattern can be mapped to a specific service instance and its credential can be revoked immediately.

This reduces the manual tasks performed by the database administrator and makes the database access more efficient and secure.

The Secret as a Service: Dynamic Secrets tutorial demonstrates the primary workflow.

»Challenge

Because Vault is managing the database credentials on behalf of the database administrator, it must also be given a set of highly privileged credentials which can grant and revoke access to the database system. Therefore, it is very common to give Vault your root credentials.

However, these credentials are often long-lived and never change once configured on Vault. This may violate the Governance, Risk and Compliance (GRC) policies surrounding that data stored in the database.

»Solution

Use the Vault's /database/rotate-root/:name API endpoint to rotate the root credentials stored for the database connection.

»Prerequisites

To perform the tasks described in this tutorial, you need to have a Vault environment. Refer to the Getting Started tutorial to install Vault. Make sure that your Vault server has been initialized and unsealed.

»PostgreSQL

This tutorial requires that you have a PostgreSQL server to connect to. If you don't have one, install PostgreSQL.

• Refer to the PostgreSQL documentation for details
• PostgreSQL Wiki gives you a summary of basic commands to get started.

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

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

If you are not familiar with policies, complete the policies tutorial.

»Step 1: Enable the database secrets engine

$ vault secrets enable database

$ vault secrets enable database

»Step 2: Configure PostgreSQL secrets engine

Previously:

In the Secret as a Service: Dynamic Secrets tutorial, the PostgreSQL plugin was configured with its root credentials embedded in the connection_url (root and rootpassword) as below:

$ vault write database/config/postgresql \
      plugin_name=postgresql-database-plugin \
      allowed_roles="*" \
      connection_url=postgresql://root:rootpassword@postgres.host.address:5432/postgres

$ vault write database/config/postgresql \
      plugin_name=postgresql-database-plugin \
      allowed_roles="*" \
      connection_url=postgresql://root:rootpassword@postgres.host.address:5432/postgres

The database credential is embedded into the connection URL which is not ideal.

Templated Credentials:

Instead of hard-coding the database credentials into the connection URL, leverage the database root credential rotation feature using the templated credentials: {{username}} and {{password}}.

$ vault write database/config/postgresql \
     plugin_name=postgresql-database-plugin \
     connection_url="postgresql://{{username}}:{{password}}@postgres.host.address:5432/postgres" \
     allowed_roles="*" \
     username="root" \
     password="rootpassword"

$ vault write database/config/postgresql \
     plugin_name=postgresql-database-plugin \
     connection_url="postgresql://{{username}}:{{password}}@postgres.host.address:5432/postgres" \
     allowed_roles="*" \
     username="root" \
     password="rootpassword"

»Step 3: Rotate the root credentials

Vault provides an API endpoint to easily rotate the initial root database credentials.

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

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

This is all you need to do.

To verify that the root credential was rotated.

$ psql -h postgres.host.address -p 5432 -U root postgres
Password for user root:

$ psql -h postgres.host.address -p 5432 -U root postgres
Password for user root:

Entering the initial password (e.g. rootpassword) will not work since the password was rotated by the Vault.

You can invoke the database/rotate-root/:name endpoint periodically to secure the root credential.

»Step 4: Verify the configuration

You can create a role and verify that the database secrets engine dynamically generates credentials as expected.

If you are unfamiliar with database secrets engine, refer to the Secrets as a Service: Dynamic Secrets tutorial for more detailed instructions.

$ tee readonly.sql <<EOF
CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";
EOF

$ tee readonly.sql <<EOF
CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";
EOF

$ vault write database/roles/readonly db_name=postgresql \
         creation_statements=@readonly.sql \
         default_ttl=1h max_ttl=24h

$ vault write database/roles/readonly db_name=postgresql \
         creation_statements=@readonly.sql \
         default_ttl=1h max_ttl=24h

$ vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/999c43f0-f79e-ba90-24a8-4de5af33a2e9
lease_duration     1h
lease_renewable    true
password           A1a-u7wxtrpx09xp40yq
username           v-root-readonly-x6q809467q98yp4yx4z4-1525378026e

$ vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/999c43f0-f79e-ba90-24a8-4de5af33a2e9
lease_duration     1h
lease_renewable    true
password           A1a-u7wxtrpx09xp40yq
username           v-root-readonly-x6q809467q98yp4yx4z4-1525378026e

Make sure that you can connect to the database using the Vault generated credentials.

$ psql -h postgres.host.address -p 5432 \
       -U v-root-readonly-x6q809467q98yp4yx4z4-1525378026e postgres
Password for user v-root-readonly-x6q809467q98yp4yx4z4-1525378026:

postgres=> \du
Role name                                       |                         Attributes                         | Member of
------------------------------------------------+------------------------------------------------------------+----------
postgres                                        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
v-root-readonly-x6q809467q98yp4yx4z4-1525378026 | Password valid until 2018-05-03 21:07:11+00                | {}
v-root-readonly-x7v65y1xuprzxv9vpt80-1525378873 | Password valid until 2018-05-03 21:21:18+00                | {}

postgres=> \q

$ psql -h postgres.host.address -p 5432 \
       -U v-root-readonly-x6q809467q98yp4yx4z4-1525378026e postgres
Password for user v-root-readonly-x6q809467q98yp4yx4z4-1525378026:

postgres=> \du
Role name                                       |                         Attributes                         | Member of
------------------------------------------------+------------------------------------------------------------+----------
postgres                                        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
v-root-readonly-x6q809467q98yp4yx4z4-1525378026 | Password valid until 2018-05-03 21:07:11+00                | {}
v-root-readonly-x7v65y1xuprzxv9vpt80-1525378873 | Password valid until 2018-05-03 21:21:18+00                | {}

postgres=> \q

This confirms that the Vault successfully connected to your PostgreSQL server and created a new user based on the privilege defined by readonly.sql.

»Help and Reference

• Secret as a Service: Dynamic Secrets
• Database Secrets Engine (API)
• PostgreSQL Database Plugin HTTP API