Use HashiCorp Vault's Dynamic Secrets

Contribute

• View Page Source
• Edit This Page
• Report Doc Issue

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://{{username}}:{{password}}@${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 \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
           GRANT ALL ON DATABASE defaultdb TO \"{{name}}\";" \
       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 "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT ALL ON DATABASE defaultdb TO "{{name}}";]
   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"
   

Was this helpful?