Use HashiCorp Vault's Dynamic Secrets

Managing database credentials centrally is a recommended security practice. This tutorial shows how to manage CockroachDB database credentials using Hashicorp Vault’s PostgreSQL database secrets engine, which provides centralized, secure, and auditable management of database credentials.

See also:

• Vault’s Dynamic Secrets model
• Vault’s tutorial for Database Secrets Engine.

Before you begin

Before you start this tutorial:

• Either or . Make a note of the cluster’s name.

  For a cluster in CockroachDB Cloud, download the cluster’s CA certificate locally. Refer to .

• Create a SQL user and make a note of the username and password. Refer or .

• Create a Vault cluster in HashiCorp Cloud or start a development Vault cluster locally.

• .

• Install the Vault CLI.

Step 1: Configure your local environment

In this phase, an administrator of your organization provisions access to a class of database clients by creating a set of credentials and propagating them to Vault.

1. Set your CockroachDB cluster credentials and other configuration information as environment variables using the information you gathered previously.

   export CA_CRT_PATH=/path/to/root.crt # Path to the CA certificate you downloaded
   export USER_NAME=tutorialadmin # SQL username
   export PASSWORD=1234asdf # SQL user password
   export DB_NAME=defaultdb # Database name
   export CLUSTER_NAME=lilac-grizzly-684 # Cluster name
   export HOST=my-cluster.aws-us-west-2.crdb.io # Cluster host
   

2. Construct a database connection URL for your CockroachDB CLI to connect to the cluster

   export TLS_OPTS="&sslrootcert=${CA_CRT_PATH}&sslmode=verify-full
   export CLI_DB_CONNECTION_URL="postgresql://${USER_NAME}:${PASSWORD}@${HOST}:26257/${DB_NAME}?${TLS_OPTS}"
   

3. Execute a SQL statement to test your connection.

   cockroach sql --url "${CLI_DB_CONNECTION_URL}" --execute "show tables;"
   
   SHOW TABLES 0
   
   Time: 107ms
   

4. Set your Vault target and the admin Vault namespace.

   You can fetch your target URL and generate a token from the HashiCorp Vault console, under the Access Vault tab.

   export VAULT_ADDR=https://roach-test-vault.vault.bfb2290a-670b-4a10-bedf-5aab18e84d69.aws.HashiCorp.cloud:8200 # your Vault cluster URL
   export VAULT_NAMESPACE=admin
   

5. Authenticate to your Vault, providing the admin token when prompted:

   vault login
   
   Success! You are now authenticated...
   

6. Enable the Vault database secrets engine

   vault secrets enable database
   

For more information on using the Vault Secrets CLI, refer to Vault’s documentation.

Step 2: Connect Vault to your cluster

In this step, you save your CockroachDB credentials in Vault so that it can perform administrative tasks on the cluster.

1. Modify the $VAULT_TLS_OPTS environment variable you created earlier to skip TLS server authentication. The Vault server cannot use the CockroachDB cluster’s CA certificate. In this connection string, the username and password fields are Vault variables.

   export VAULT_TLS_OPTS="sslmode=require"
   export VAULT_DB_CONNECTION_URL="postgresql://:@${HOST}:26257
   

2. Write the crdb-config database configuration to Vault, specifying admin credentials that will be used by Vault to create credentials for your defined role:

   vault write database/config/crdb-config \
   plugin_name=postgresql-database-plugin \
   allowed_roles="crdb-role" \
   username=${USER_NAME} \
   password=${PASSWORD} \
   connection_url=${VAULT_DB_CONNECTION_URL}
   
   Success! Data written to: database/config/crdb-config
   

Step 3: Provision Dynamic Secrets

In Vault, a dynamic secret is a secret template that can be used to generate particular short-lived secrets on demand. The secret type we’ll be using is /database/role. A Vault database role does not correspond to a single role or user in SQL, but a template for creating short-lived roles or users.

For a SQL role, the template is defined by its creation_statements, which are SQL statements that create the role, define its options and grant its permissions.

1. Create a Vault database role.

   For example, create a role that has all privileges on the defaultdb database. In this command, db_name is the name of the the Vault database secrets engine namespace ( crdb-config in the example).

   vault write database/roles/crdb-role \
       db_name=crdb-config \
       creation_statements="CREATE ROLE \"\" WITH LOGIN PASSWORD '' VALID UNTIL ''; \
           GRANT ALL ON DATABASE defaultdb TO \"\";" \
       default_ttl="1h" \
       max_ttl="24h"
   

2. Query Vault for a list of database roles, revealing the newly created role:

   vault list database/roles/
   
   Keys
   ----
   crdb-role
   

3. Inspect the role:

   vault read database/roles/crdb-role
   
   Key                      Value
   ---                      -----
   creation_statements      [CREATE ROLE "" WITH LOGIN PASSWORD '' VALID UNTIL ''; GRANT ALL ON DATABASE defaultdb TO "";]
   db_name                  crdb-config
   default_ttl              1h
   max_ttl                  24h
   renew_statements         []
   revocation_statements    []
   rollback_statements      []
   

4. Show the list of Vault users:

   cockroach sql --url $CLI_DB_CONNECTION_URL --execute "show users;"
   
   username | options | member_of
   ---------+---------+------------
   admin    |         | {}
   docsrule |         | {admin}
   root     |         | {admin}
   

5. A Vault role is a defined template for credential pairs (SQL user/role name and password), which will be generated on demand and quickly expired. Reading a Vault role does not fetch credentials for a pre-existing SQL user, but requests that Vault create a user on demand, according to the template. Run the following command several times to generate several credential pairs:

   vault read database/creds/crdb-role
   

   Notice that the validity duration is the default value of 1 hour.

   Key                Value
   ---                -----
   lease_id           database/creds/crdb-role/5rPhNN6aG0dNRs97UmRYMmJH.iMXtW
   lease_duration     1h
   lease_renewable    true
   password           a1qKnD-ZjFG-oUMcSFtx
   username           v-token-hc-crdb-rol-1pcS5YcSaSS4Fgy4BiXp-1653512968
   

6. List the active credentials (or leases, in Vault terms) for the Vault role:

   vault list sys/leases/lookup/database/creds/crdb-role/
   
   Keys
   ----
   5rPhNN6aG0dNRs97UmRYMmJH.iMXtW
   ATKZIXMXMTcNQBkR4vwzvyd8.iMXtW
   WHtZ4JKGLC8KYssMn5mCxKqU.iMXtW
   lcAOenZ7s03BpPi8XKS0vsK3.iMXtW
   yyufcaLu4eKWcnGzhes30t6M.iMXtW
   

7. Fetch the list of currently active SQL roles from the CockroachDB client:

   cockroach sql --url $CLI_DB_CONNECTION_URL --execute "show users;"
   
                           username                       |                options                | member_of
   ------------------------------------------------------+---------------------------------------+------------
     admin                                               |                                       | {}
     root                                                |                                       | {admin}
     v-token-hc-crdb-rol-uqtqyca6neplilakhv55-1653513157 | VALID UNTIL=2022-05-25 22:12:42+00:00 | {}
     v-token-hc-crdb-rol-0gzk3hwi7k7a9w1ggggq-1653515525 | VALID UNTIL=2022-05-25 22:52:10+00:00 | {}
     v-token-hc-crdb-rol-varnevyjgjg9zgf62kps-1653515522 | VALID UNTIL=2022-05-25 22:52:07+00:00 | {}
     v-token-hc-crdb-rol-wltbv25napuroomvzmok-1653515524 | VALID UNTIL=2022-05-25 22:52:09+00:00 | {}
   

8. Revoke credentials by revoking a lease. A lease on a role in Vault terms corresponds to an actual credential pair for a SQL user on the CockroachDB cluster.

   vault lease revoke database/creds/crdb-role/XIpIBRM8FkQxD5B0ndB1lszY.iMXtW
   
   All revocation operations queued successfully!
   

9. Next, provision a Vault policy for CockroachDB client operators so that they can access the client credentials without having the admin role on the CockroachDB cluster.

   This policy will be used to access CockroachDB client credentials. This example grants it only the required ability to read the required credential.

   Vault policies are specified using HashiCorp Configuration Language (HCL). The following configuration specifies a policy of read access for the crdb-role credential:

   vault policy write roach-client - <<hcl
   path "database/creds/crdb-role" {
     capabilities = [ "read" ]
   }
   hcl
   
   Success! Uploaded policy: roach-client
   

10. Generate an authentication token for the crdb-role Vault user.

     vault token create -policy=roach-client
    
    Key                  Value
    ---                  -----
    token                hvs.CAESIK4TK7JcSJuKxOgwEa3mYhvfN356Uhikij821K4E4XnWGigKImh2cy5MeUJ5dGNxMjRzNk9qNmVDYWtFYjRUd2QuaU1YdFcQ1cAT
    token_accessor       36udxC5m0hmA0niBYyCThk0k.iMXtW
    token_duration       1h
    token_renewable      true
    token_policies       ["default" "roach-client"]
    identity_policies    []
    policies             ["default" "roach-client"]
    

    You can either copy the token from the output of the previous command, or capture a token with the following command (which requires the shell utility jq).

     VAULT_CLIENT_TOKEN=`vault token create -policy=roach-client -format=json | jq .auth.client_token | tr -d '"'`
    

Step 4: Authenticate with Vault-provisioned credentials

This step shows how to use credentials provisioned by Vault to access a CockroachDB cluster, emulating the flow that an application engineer or application service account might use to access the database. This step does not use Vault admin credentials or CockroachDB credentials acquired outside of Vault.

1. Authenticate to Vault using the client token:

   vault login $VAULT_CLIENT_TOKEN
   

2. Confirm the limited permissions of the role:

   vault list sys/leases/lookup/database/creds/crdb-role/
   
   Error listing sys/leases/lookup/database/creds/crdb-role: Error making API request.
   
   Namespace: admin/
   URL: GET https://roach-test-vault.vault.bfb2290a-670b-4a10-bedf-5aab18e84d69.aws.hashicorp.cloud:8200/v1/sys/leases/lookup/database/creds/crdb-role?list=true
   Code: 403. Errors:
   
   * 1 error occurred:
     * permission denied
   

3. Read the CockroachDB credentials:

   vault read database/creds/crdb-role
   
   Key                Value
   ---                -----
   lease_id           database/creds/crdb-role/V3T4UVxeQ9RYsJAk3jZF1Dhl.iMXtW
   lease_duration     1h
   lease_renewable    true
   password           FlOo0p7jMTXjT27hlZZ-H
   username           v-token-crdb-rol-thfLPlFwex0k9Op0P8qA-1653528652
   

4. Using the previous output, add the crdb-role credentials to your environment:

   export USER_NAME=v-token-crdb-rol-thfLPlFwex0k9Op0P8qA-1653528652 # generated CockroachDB client username
   export PASSWORD=FlOo0p7jMTXjT27hlZZ-H # generated CockroachDB client password
   export DB_NAME=defaultdb
   export CLUSTER_NAME=lilac-grizzly-684 # generated CockroachDB client password
   export HOST=free-tier21.aws-us-west-2.crdb.io
   export TLS_OPTS="sslrootcert=root.crt&sslmode=verify-full"
   export CLI_DB_CONNECTION_URL="postgresql://$USER_NAME:$PASSWORD@${HOST}:26257/${DB_NAME}?${TLS_OPTS}"
   

5. List all the tables in database defaultdb to confirm you can connect to your CockroachDB cluster:

   cockroach sql --url "${CLI_DB_CONNECTION_URL}" --execute "show tables;"
   
   SHOW TABLES 0
   
   Time: 120ms
   

6. To confirm that the credentials have been properly limited, attempt a forbidden operation. crdb-role does not have permission to list users, so try that in order to generate a permissions error:

   cockroach sql --url "${CLI_DB_CONNECTION_URL}" --execute "show users;"
   
   ERROR: user v-token-crdb-rol-thflplfwex0k9op0p8qa-1653528652 does not have SELECT privilege on relation users
   SQLSTATE: 42501
   Failed running "sql"
   

Speed up role management operations

User/role management operations (such as and ) are . As such, they inherit the .

For example, schema changes wait for concurrent using the same resources as the schema changes to complete. In the case of being modified inside a transaction, most transactions need access to the set of role memberships. Using the default settings, role modifications require schema leases to expire, which can take up to 5 minutes.

If you want user/role management operations to finish more quickly, and do not care whether concurrent transactions will immediately see the side effects of those operations, set the allow_role_memberships_to_change_during_transaction to true. To learn more, refer to and .