Dynamic Secrets with Vault & Boundary

When setting up our first version of our internal Nomad cluster 6 years ago, we quickly realized that we also needed a secrets management solution. Vault, being another product from Hashicorp, was the natural fit for our needs.

In the past, we only used Vault to manage static secrets. When we upgraded and reworked our Nomad infrastructure last year, I decided to make sure that we migrated our workloads to dynamic secrets for hardening our setup even more.

Hashicorp has an excellent tutorial how to set-up and configure Vault dynamic secrets for a PostgreSQL database but misses a few important pieces, I think. That's why I felt I should document our set-up in this blogpost.

PostgreSQL setup

Let's start by creating a database and a role for the application. For consistency reasons, let's name the database and role like our Nomad job my-nomad-job:

CREATE ROLE "my-nomad-job";
CREATE DATABASE "my-nomad-job" OWNER "my-nomad-job";
GRANT ALL PRIVILEGES ON DATABASE "my-nomad-job" TO "my-nomad-job";
ALTER DEFAULT PRIVILEGES GRANT SELECT, UPDATE, INSERT ON TABLES TO "my-nomad-job";
GRANT ALL ON SCHEMA public TO "my-nomad-job";

Vault configuration

We create a database connection and a role in Vault for each Nomad job. Again, we use the same name for the Nomad job for consistency reasons.

Before running the Vault CLI commands, you need to set VAULT_ADDR with the URL to your Vault instance and VAULT_TOKEN with your Vault access token:

export VAULT_ADDR="..."
export VAULT_TOKEN="..."

Create a database connection with the following command:

vault write database/config/my-nomad-job \
     plugin_name=postgresql-database-plugin \
     connection_url="postgresql://{{username}}:{{password}}@127.0.0.2/my-nomad-job?sslmode=disable" \
     allowed_roles="*" \
     username="postgres" \
     password="12345678!"

For simplicity reasons, we use static credentials for the PostgreSQL root account in this blog post. Ideally, you follow the Database root credential rotation guide to learn how to also rotate the root password for your database server.

Before creating a Vault role for the database connection, we need to create a file containing the SQL code to create the dynamic user accounts in PostgreSQL. This is the template accessdb.sql to use:

CREATE USER "{{name}}" WITH ENCRYPTED PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "{{name}}";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "{{name}}";
GRANT ALL ON SCHEMA public TO "{{name}}";
GRANT "my-nomad-job" to "{{name}}";

The SQL statements will create a user with a given password that is valid for the defined time. The most important part is the last statement: The user inherits the my-nomad-job role we created earlier. This ensures that all users can see all the data in the database and not only the data owned by the user itself.

Next, we can create the role in Vault:

vault write database/roles/my-nomad-job db_name=my-nomad-job creation_statements=@accessdb.sql default_ttl=1h max_ttl=24h

Let's test it by creating a new user account via Vault:

vault read database/creds/my-nomad-job

This returns the following output:

Key                Value
---                -----
lease_id           database/creds/my-nomad-job/pdCb4AxvJR5lXGv2RsZaDDZN
lease_duration     1h
lease_renewable    true
password           Rvbr4bkaXBcQ0J-EeOt8
username           v-root-bitai.pr-KNAExz8DVm0r2T0lQIj6-1739443665

With the username and password, I can connect to the database with my PostgreSQL client. Do I want my developers to use the Vault CLI? Maybe not, and this is where Boundary comes into play.

Permissions ins PostgreSQL

While this setup works fine for reading data, we run into issues when writing data, e.g., when creating new database tables. By default, PostgreSQL will make the current user the table owner, which is not what we want.

The owner should be the my-nomad-job role we created earlier and not the role v-root-bitai.pr-KNAExz8DVm0r2T0lQIj6-1739443665 created by Vault temporarily.

To fix this, your application can send the SET ROLE my-nomad-job when connecting to the database. If that is not possible, Magnus Hagander came up with this solution to change the owner via a trigger when the database table gets created:

CREATE OR REPLACE FUNCTION trg_create_set_owner()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOP
    EXECUTE format('ALTER TABLE %s OWNER TO my-nomad-job', obj.object_identity);
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_set_owner
 ON ddl_command_end
 WHEN tag IN ('CREATE TABLE')
 EXECUTE PROCEDURE trg_create_set_owner();

Boundary configuration

We using Hashicorp Boundary to simplify server access based on user identity. And server access can also mean access to a PostgreSQL database.

Bonus point of Boundary: Users have a nice overview of all resources they can access based on their assigned groups in a simple Web UI.

Before we can create the Vault token for Boundary, we need to make sure that the boundary policy (your policy may have a different name!) has the rights to read and list what's stored in Vault's database/creds/* path (which is where Vault by default stores the database connection credentials).

In the Vault Web UI navigate to ACL policies and pick the policy you want to use to create the token. Add the following rule to the policy:

path "database/creds/*" {
  capabilities = ["read", "list"]
}

Now, we can create the Vault token for Boundary. The token you create must renew periodically and be marked as an orphan token:

vault token create -policy=boundary -orphan=true -period=30m

All you need to do in Boundary is to create a new credential store in your organization by specifying the Vault address and the token you just created.

After creating the credential store, add a new credential library that uses the Vault path database/creds/my-nomad-job.

Next, create a new target to connect to and select the credential library that you just created.

Nomad configuration

And what about Nomad? Since our Nomad cluster is configured to use Workload identity feature, we also need to extend the Nomad policy in Vault.

Edit the respective role in Vault and add the following path:

path "database/creds/{{identity.entity.aliases.auth_jwt_3fe8bcd6.metadata.nomad_job_id}}" {
  capabilities = ["read"]
}

The auth_jwt_3fe8bcd6 part of the identity string depends on your Vault authentication method configuration. Adjust it accordingly.