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:
Before you begin
Before you start this tutorial:
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.
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
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}"
Execute a SQL statement to test your connection.
cockroach sql --url "${CLI_DB_CONNECTION_URL}" --execute "show tables;"
SHOW TABLES 0
Time: 107ms
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
Authenticate to your Vault, providing the admin token when prompted:
Success! You are now authenticated...
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.
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
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.
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"
Query Vault for a list of database roles, revealing the newly created role:
vault list database/roles/
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 []
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}
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
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
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 | {}
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!
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
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.
Authenticate to Vault using the client token:
vault login $VAULT_CLIENT_TOKEN
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
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
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}"
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
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 GRANT and REVOKE) are schema changes. As such, they inherit the limitations of schema changes.
For example, schema changes wait for concurrent transactions using the same resources as the schema changes to complete. In the case of role memberships 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 session variable allow_role_memberships_to_change_during_transaction to true. To learn more, refer to How to speed up user / role assignment and GRANT.
Was this helpful?
