Configure dedicated connections to external database accounts

In the dynamic secrets tutorial, you configured Vault to generate dynamic credentials for a PostgreSQL database. In this tutorial, you will learn how to configure Vault Enterprise to manage existing database accounts.

If you are not familar with how to configure Vault for dynamic credentials, follow the database secrets engine tutorial before you begin.

Scenario

HashiCups configured Vault to generate dynamic credentials for PostgreSQL. Steve and the SRE team needs to access PostgreSQL for troubleshooting with existing roles where the role name does not change, but want Vault to manage and rotate passwords.

Oliver from the operations team will need to configure Vault to manage credentials for static database roles. These roles are not created by Vault, but Vault will manage the passwords for these roles.

Prerequisites

This lab was tested on macOS using an x86_64 based and Apple silicon-based processors. You may also run this tutorial by clicking the Start interactive lab button.

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

• Docker to run a Vault and PostgreSQL container.
• Vault binary installed.
• Git installed.
• Rootless static role password rotation requires HashiCorp Vault Enterprise and license key. For more information on installing a Vault enterprise license, refer to the Install a HashiCorp Enterprise license page.

Set up the lab

1. Open a terminal and export an environment variable with a valid Vault Enterprise license.

   $ export VAULT_LICENSE=0S3ASM3STC0OKI3....
   

2. Clone the learn-vault-dynamic-credentials repository.

   $ git clone git@github.com:hashicorp-education/learn-vault-dynamic-credentials.git
   

3. Change into the learn-vault-dynamic-credentials directory.

   $ cd learn-vault-dynamic-credentials
   

4. Deploy the Vault Enterprise and PostgreSQL containers.

   $ terraform -chdir=vault-dynamic-creds-docker/ init && \
     terraform -chdir=vault-dynamic-creds-docker/ apply \
       -var "VAULT_EDITION=vault-enterprise" \
       -var "VAULT_LICENSE=$VAULT_LICENSE" \
       -auto-approve
   

   Example output:

   Initializing the backend...
   Initializing provider plugins...
   - Finding kreuzwerker/docker versions matching "3.0.2"...
   - Installing kreuzwerker/docker v3.0.2...
   ...snip...
   Apply complete! Resources: 4 added, 0 changed, 0 destroyed.
   
   Outputs:
   
   POSTGRES_URL = "export TF_VAR_POSTGRES_URL=172.17.0.2:5432"
   VAULT_ADDR = "export VAULT_ADDR=http://127.0.0.1:8200"
   VAULT_TOKEN = "export VAULT_TOKEN=root"
   

5. Copy the export command from the Terraform output and export the environment variables.

   Example:

   $ export VAULT_ADDR=http://127.0.0.1:8200 \
      VAULT_TOKEN=root \
      TF_VAR_POSTGRES_URL=172.17.0.2:5432
   

6. Verify the PostgreSQL and Vault containers have started.

   $ docker ps -f name=learn --format "table {{.Names}}\t{{.Status}}"
   NAMES            STATUS
   learn-postgres   Up 4 minutes
   learn-vault      Up 4 minutes
   

   Vault and PostgreSQL are running. You are ready to proceed with the tutorial.

Rootless static role rotation

A database secrets engine requires the configuration of a root connection with privileged database account. With this root connection, users are able to use Vault features like access-control and lease management to allow database roles and create new database users/credentials. The Databases section of the Vault documentation has details on the database secrets engine feature-set.

Initialization of a new database configuration creates this highly privileged connection over which all database resources are managed. These "Vault-managed" connections are responsible for making all the necessary queries/commands to the database to create, read, update, and delete users/credentials within the database.

Not only do these connections have excessive privileges, but each database connection requires individual setup. At a large enterprise, this results in hundreds of CLI commands or giant Terraform modules.

Administering hundreds of privileged accounts is also untenable, with creation and maintenance of these accounts being a burden on the Vault operator.

Static roles are a 1-to-1 mapping of a Vault role to a user in a database. With static roles, Vault stores and automatically rotates passwords for the associated database user based on a configurable period of time or rotation schedule. Static roles use the default database connection with unnecessarily high privileges for most use cases.

Rootless static role rotation enables customers to configure dedicated connections to external "self-managed" database accounts with lower-levels of privilege. Each connection maps to a static role with limited privileges, instead of managing all resources in Vault with a single "Vault-managed" dedicated connection to a highly privileged account.

Rootless static roles offer these advantages:

• Create static roles as needed to prevent long-lived user accounts, and reduce risk of leaked credentials.
• Vault does not store the root database credentials.
• Database administrators can trace actions back to specific users.

Configure PostgreSQL

1. Create a new user for use with rootless static roles.

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

2. Verify the user creation.

   $ docker exec -i \
       learn-postgres \
       psql -U root -c "\du"
   

3. Create a table in the root database.

   $ docker exec -i learn-postgres \
      psql -U root -c "CREATE TABLE cities (name varchar(80),location point);"
   

4. Write sample data to the table.

   $ docker exec -i learn-postgres psql -U root -c \
      "INSERT INTO cities (name, location)
      VALUES ('San Francisco', '37.7749,122.4194');"
   

5. Grant staticuser_pg SELECT privileges on the cities table.

   $ docker exec -i learn-postgres psql -U root -c \
      "GRANT SELECT ON cities TO \"staticuser_pg\";"
   

6. Check the permissions for staticuser_pg.

   $ docker exec -i learn-postgres psql -U root -c \
      "SELECT grantee, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'staticuser_pg';"
   

   Example output:

   grantee       | table_name | privilege_type
   --------------+------------+----------------
   staticuser_pg | cities     | SELECT
   

Configure Vault for rootless static role rotation

1. Enable the database secrets engine.

   $ vault secrets enable database
   

2. Create a connection configuration for the postgresql-database-plugin.

   $ vault write database/config/postgres-db-rootless \
      plugin_name=postgresql-database-plugin \
      allowed_roles=staticuser \
      connection_url="postgresql://{{username}}:{{password}}@$TF_VAR_POSTGRES_URL/postgres?sslmode=disable" \
      verify_connection=false \
      self_managed=true
   

   The self_managed field on root configuration configures database secrets engine to use the dedicated connection for the staticuser role.

3. Create the staticuser role specified in allowed_roles field in the database configuration.

   $ vault write database/static-roles/staticuser \
      db_name=postgres-db-rootless \
      username="staticuser_pg" \
      self_managed_password="staticpassrootless" \
      rotation_period=5m
   

   The db_name field indicates that this user has access to the postgres-db-rootless database connection, and credentials for static roles are automatically rotated based on the rotation_period. The username field is referring to the staticuser_pg on the PostgresSQL side.

4. Read the credentials from the statcuser role.

   $ vault read database/static-creds/staticuser
   Key                    Value
   ---                    -----
   last_vault_rotation    2024-09-19T14:29:26.934329-05:00
   password               onBp9x5sVhLWQ7u6XV-S
   rotation_period        5m
   ttl                    5m
   username               staticuser_pg
   

   Notice the username is the PostgreSQL user created earlier, it has an initial password that is different than was originally set (staticpassrootless) and the password will change in 5 minutes.

5. Log in and query the cities table to verify the connection to PostgreSQL with the new staticuser_pg password.

   $ psql -h localhost -p 5432 -U staticuser_pg root -c "select * from cities;"
   

6. Enter the password from the vault read command.

   Example:

   Password for user staticuser_pg:
        name      |      location
   ---------------+--------------------
    San Francisco | (37.7749,122.4194)
   

   The static user staticuser_pg can access the cities table with the password managed by Vault.

Clean up

1. Destroy the Terraform resources.

   $ terraform -chdir=vault-dynamic-creds-docker/ destroy -auto-approve
   

2. Unset the environment variables.

   $ unset VAULT_ADDR VAULT_TOKEN TF_VAR_POSTGRES_URL VAULT_LICENSE
   

Summary

In this tutorial, you learned how to configure Vault Enterprise to manage static roles for existing database accounts. You created a new user in PostgreSQL and configured Vault to manage the role's password.

Help and reference

• Dynamic secrets tutorial
• PostgreSQL database plugin API documentation