This tutorial discusses and demonstrates best security practices for managing CockroachDB database credentials with HashiCorp Vault's database secrets engine PostgreSQL plugin.
In the first phase of the tutorial, we will act as administrators, provisioning access to a Vault dynamic secret endpoint for a CockroachDB client operator to use to provision credentials for an app that needs to access the CockroachDB database.
In the second phase, acting as client operator, we will pull credentials from Vault and use them to access the database via the CockroachDB client CLI.
See also:
- CockroachDB - HashiCorp Vault Integration overview page
- HashiCorp Vault database secrets engine tutorial.
Before you begin
To follow along with this tutorial you will need the following:
- The CockroachDB CLI installed locally.
- The Vault CLI installed locally.
- Access to a CockroachDB cluster as
admin
SQL user. This tutorial will use a CockroachDB Serverless cluster, but you may either Create a CockroachDB Serverless cluster or Start a Local Cluster (secure) in order to follow along. In either case you must have the public CA certificate for your cluster, and a username/password combination for theroot
SQL user (or another SQL user with theadmin
role. - Access to a Vault cluster with an admin token. This tutorial will use HashiCorp Cloud Platform, but you may either spin up a free cluster in HashiCorp Cloud Platform or start a development cluster locally.
Introduction
Sound strategy and proper tooling for managing database credentials are essential to the overall strength of your security posture. Managing your CockroachDB database credentials with HashiCorp Vault affords several security advantages.
The general advantages of using a delegated, consolidated secrets management solution include:
- Access is managed by a single coherent set of policies.
- Access events leave an audit trail.
- Secrets are properly encrypted in flight and in transit.
- There is no need to store or transmit secrets with multiple tools or tools not designed for the purpose, as typically done in the past.
Advantages of Vault's Dynamic Secrets model:
- Database credentials are generated and issued only on demand, from pre-configured templates, for specific clients and short validity durations, further minimizing both probability of credential compromise and possible impact of any compromise that does occur.
- Diligent rotation and revocation practices are automated by implication, requiring no additional effort or oversight.
Step 1: Prepare your shell for CockroachDB and Vault admin tasks
In this phase of the tutorial we will act as an administrator for our organization, provisioning access for a class of database clients. We'll need admin credentials for both a Vault cluster and a CockroachDB cluster, allowing you to create and manage access for roles in both domains.
Connect to CockroachDB.
Set your CockroachDB cluster credentials and other configuration information as environment variables. If you're using a CockroachDB Cloud cluster, you can find this information in the CockroachDB Cloud Console's cluster page.
Note:Your cluster name must include the numerical suffix, which is included in the connection string under the Connect tab in the console, but is not included when the cluster name is displayed in the console.
Export the variables to your shell:
export USER_NAME=tutorialadmin # replace with CockroachDB admin username export PASSWORD=1234asdf # replace with CockroachDB admin password export DB_NAME=defaultdb # replace with CockroachDB database name if different export CLUSTER_NAME=lilac-grizzly-684 # replace with CockroachDB cluster name export HOST=free-tier123.aws-us-west-2.crdb.io # replace with CockroachDB cluster host
Construct a database connection URL for your CockroachDB CLI to connect to the cluster, using your admin credentials:
Note:You must place the CockroachDB cluster's CA public certificate on the path specified by
sslrootcert
. In the following example, this is a file namedroot.crt
in the current directory.export TLS_OPTS="sslrootcert=root.crt&sslmode=verify-full export CLI_DB_CONNECTION_URL="postgresql://${USER_NAME}:${PASSWORD}@${HOST}:26257/${DB_NAME}?${TLS_OPTS}"
Obtain your CockroachDB cluster's CA public certificate:
- Visit the CockroachDB Cloud Console's cluster page.
- Select your cluster.
- Click the Connect button.
- Select "Download CA Cert (Required only once)" and use the generated
curl
command to download the certificate.
curl --create-dirs -o root.crt -O https://cockroachlabs.cloud/clusters/505a138c-37ff-46b7-9c50-4119cf0881f6/cert
Prove that your connection works by executing a SQL statement.
Recall that this command must be run in the directory where
root.crt
is located, as specified in the connection URL.cockroach sql --url "${CLI_DB_CONNECTION_URL}" --execute "show tables;"
SHOW TABLES 0 Time: 107ms
Connect to Vault.
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:
vault login
Success! You are now authenticated...
Enable the Vault database secrets engine:
Note:This only needs to be done once for an individual Vault cluster. For more information on using the Vault Secrets CLI, see Vault's documentation.
vault secrets enable database
Success! Enabled the database secrets engine at: database/
Step 2: Bind your Vault cluster to your CockroachDB cluster
The connection lies in a Vault configuration, which will store your CockroachDB admin credentials, allowing Vault to administrate CockroachDB credentials.
Create a Vault database configuration.
First, tweak the connection string to skip TLS server authentication, as there's no way to provision the Vault server with the cluster CA cert:
export VAULT_TLS_OPTS="sslmode=require" export VAULT_DB_CONNECTION_URL="postgresql://{{username}}:{{password}}@${HOST}:26257/${DB_NAME}?${VAULT_TLS_OPTS}"
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 for SQL credentials
A dynamic secret is a secret template, which will be used to generate particular short-lived secrets on demand, according to the template. 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
, 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:Note:db_name
is actually not the database name but the Vault database secrets engine namespace (i.e.,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"
Success! Data written to: database/roles/crdb-role
Query Vault for a list of database roles, revealing the newly created
crdb-role
:vault list database/roles/
Keys ---- crdb-role
Inspect the role, revealing the values we set for the role when creating it:
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 []
Generate credential pairs.
Recall that a role in Vault does not correspond exactly to a role in CockroachDB (i.e., a SQL role or user).
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.
When we "read" the Vault role called
crdb-role
, we are therefore not fetching credentials for a pre-existing SQL user, but requesting that Vault create a user for us, according to the template.To see this, first execute a SQL statement via the CockroachDB CLI to list existing users:
cockroach sql --url $CLI_DB_CONNECTION_URL --execute "show users;"
username | options | member_of ---------+---------+------------ admin | | {} docsrule | | {admin} root | | {admin}
Now, let's read some credentials from Vault, which will cause Vault to create SQL users on our CockroachDB cluster. 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
Now 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. Try revoking a lease. Recall that a lease on a role in Vault terms corresponds to an actual credential pair on the CockroachDB cluster, i.e., a username/password combination for a SQL user.
vault lease revoke database/creds/crdb-role/XIpIBRM8FkQxD5B0ndB1lszY.iMXtW
All revocation operations queued successfully!
Provision a CockroachDB-client Vault policy.
As admin, provision a Vault policy for CockroachDB client operators to access the client credentials.
The purpose of the previous work is to make a dynamic secret that can be used to access the CockroachDB database by generating credentials on demand. Performing the above work (establishing the connection between the CockroachDB cluster and the Vault cluster, creating the template for database client credentials, etc.) required admin privileges. But for the work to be meaningful, a Vault user with more limited permissions must be able to access the generated credentials.
This policy will be used to access CockroachDB client credentials. In keeping with the principle of least privilege, let's give 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.Our final act as Vault admin will be to provision an authentication token to assume the
crdb-role
: the Vault role with the sole purpose of providing credentials to a CockroachDB client.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: Connect to CockroachDB with Vault-provisioned credentials
In this phase of the tutorial, we will use credentials provisioned by Vault to access our CockroachDB cluster, emulating the flow that an application dev ops engineer or application service account might use to achieve database access. Therefore, unlike in the first phase, we will not use Vault admin credentials or CockroachDB credentials acquired other than through Vault, since these should not be required.
Authenticate to Vault as the CockroachDB user.
Use the client token to authenticate to Vault with limited permissions:
vault login $VAULT_CLIENT_TOKEN
Success! You are now authenticated. The token information displayed below is already stored in the token helper. You do NOT need to run "vault login" again. Future Vault requests will automatically use this token.
Confirm the limited permissions of the role.
To confirm that you have assumed a role with limited permissions, try listing the current leases on the
crdb-role
, as we did previously. You should see a permissions error.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
Pull the CockroachDB credentials.
The only thing this policy does have permission to do is pull credentials for the CockroachDB cluster. Let's do that:
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
Connect to the CockroachDB cluster with your Vault-generated dynamic secret credentials.
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"