Securely Managing Database Secrets With Vault

In this post, I will show how to setup the root and user credentials for a Postgres database in a truly secure way, leveraging a secret management system.

Why You Need A Secret Management System

When I develop a web application, I always need a database to store data. The database setup needs to define a root user with a password. This root user is the database admin, and it must be used to create new users and grant them permission to access the database.

I usually use environment variables to store database credentials. But environment variables are not secure, they can be accessed by any process running on the server. It can also happen that I accidentally commit them to a public repository. If the root password is compromised, the attacker can access the database and steal all the data.

Secret management systems like Vault can create temporary database credentials with specific privileges. Vault is a great tool to securely manage secrets. It can be used to store database credentials, API keys, certificates, and many other secrets. It provides a secure and easy way to access secrets.

Installing and Configuring Vault

For the purpose of this post, I will use the docker images of Vault and PostgreSQL. Here is the docker-compose.yml file I used :

version: "3"

services:
  vault:
    image: vault:1.11.0
    container_name: vault
    ports:
      - "8200:8200"
    volumes:
      - ${PWD}/vault:/vault/file/
      - ./config.hcl:/vault/config/config.hcl
    cap_add:
      - IPC_LOCK
    command: server

  postgres:
    image: postgres:14
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_PASSWORD=password

I must create a configuration file called config.hcl (hcl extension means HashiCorp Configuration Language) to define how Vault is listening to API requests and where it is storing data. As I am using the docker image, I have to mount this file in /vault/config/ folder of the container. This is where Vault will read the configuration at startup.

Here is the configuration file I used:

listener "tcp" {
  address     = "0.0.0.0:8200"
  tls_disable = 1
}

storage "file" {
  path = "/vault/file"
}

In the docker-compose file, I also add the IPC_LOCK capability to allow the Vault process to lock memory. This is a security feature that prevents Vault from swapping data to disk. Indeed, if some data is swapped to a disk that is not encrypted, it can be stolen by an attacker.

I start the containers with docker-compose up -d. Vault is available on port 8200, and PostgreSQL is available on port 5432. The PostgreSQL container is configured with the default user and database postgres and the password password. But don’t worry, I will change this unsafe password in the next steps.

I can access the database from my host with psql -h localhost -p 5432 -U postgres. After entering the password I am connected to the database with root access.

Initializing Vault

I will use the Vault CLI to communicate with Vault, but all the following operations can also be done using the Vault API with a tool like Postman.

First, I need to make sure that the Vault CLI is installed on my system. Then, I have to set the Vault address in my environment variables:

export VAULT_ADDR=http://localhost:8200

On the first startup, Vault requires some keys that will serve to encrypt/decrypt the secrets it stores. These keys are called unseal keys. To generate them, I have to run the vault operator init command:

vault operator init -key-shares=5 -key-threshold=3

This command generates 5 unseal keys and a root token that will be used to authenticate to Vault with root privileges. The -key-threshold=3 option means that 3 unseal keys are required to unseal the Vault. The output of the command looks like this:

Unseal Key 1: 8FGP4Oa/yhHPcXdt8hLF2owLWta4NfsnlAEkEzH7fW/I
Unseal Key 2: yFlzvwYvbCKkF9kpA3okLF5CaijBnurBmsT5RdPKU8kP
Unseal Key 3: 06O8NNtMB3872rEw+0r1HdNEM0o7ZaWWQR2+dLNoO1Ja
Unseal Key 4: v9+uIn0IzTE5FEfTr8IGSWIaQs/pDJkMtDozNkJEe7R9
Unseal Key 5: glc1oY4ytrz4ntVoYDlOJjF0ulC13qlNwbEpjhbjBeQ5

Initial Root Token: hvs.CXPrUmR6vZkrjwYNwvRxusaJ

It is a good practice to generate more than one unseal key and to set a threshold also greater than one. This way, I can give each key to a different people, and even if one key has been stolen, the attacker will not be able to unseal the Vault. In this example, I have generated 5 unseal keys, and 3 of them are required to unseal the Vault.

When the Vault server starts, it is sealed. It means that it has no access to encrypted data. I must unseal it after each restart with the following command:

vault operator unseal

I am then prompted to enter one of the unseal keys. As I set the key-threshold to 3, I have to repeat this command 3 times with 3 different keys to unseal Vault. After each command, the seal status is displayed:

Key                Value
---                -----
Seal Type          shamir
Initialized        true
Sealed             true
Total Shares       5
Threshold          3
Unseal Progress    1/3
Unseal Nonce       0c115cc4-ace5-6867-40a4-2817d4e5ea89
Version            1.11.0
Build Date         2022-06-17T15:48:44Z
Storage Type       file
HA Enabled         false

For the next step, I must be logged in with the root token provided at the initialization step. I can do so with the vault login command:

vault login hvs.CXPrUmR6vZkrjwYNwvRxusaJ

I am now logged in with root privileges.

Creating Vault Roles

HachiCorp recommends using root token only for initial setup or emergencies. For all other operations, I must create a role with a policy that allows performing the required operations.

For the next steps, I will need two different roles:

• db-admin will be used to mount the database secret engine, configure it and create database roles.
• app will be used by the application to get credentials to access the database.

I create a file db-admin-policy.hcl to describe the policy of the db-admin role:

# Mount database secrets engines
path "sys/mounts/database" {
  capabilities = [ "create", "update" ]
}

# Configure the database secrets engine and create roles
path "database/*" {
  capabilities = [ "create", "update" ]
}

Now, I can create the db-admin role with the vault policy write command:

vault policy write db-admin db-admin-policy.hcl

To log in with the db-admin role for the next step, I will need a token that I create with the following command:

vault token create -policy=db-admin

The output of the command looks like this:

Key                  Value
---                  -----
token                hvs.CAESIEGTmPiq0QwsDKlZifZMp9uudQt5KeeTH5NO2OoWRtBAGh4KHGh2cy5zUkRkckNnZWdhT1NwcVZWUzJPN1RtUGQ
token_accessor       jTdfOmPgcAZDjSWXPP9UGd4L
token_duration       768h
token_renewable      true
token_policies       ["db-admin" "default"]
identity_policies    []
policies             ["db-admin" "default"]

I follow the same steps to create the app role with an app-policy.hcl file which describes the policy of the app role:

# Read database credentials
path "database/creds/db-app" {
  capabilities = [ "read" ]
}

db-app in the above file is the name of the database role that I will create in the next step. My app Vault role will only be able to obtain database credentials with privileges defined in the db-app role.

Managing Database Root Credentials

For this step, I will use the db-admin role. I must log in with the token created in the previous step:

vault login hvs.CAESIEGTmPiq0QwsDKlZifZMp9uudQt5KeeTH5NO2OoWRtBAGh4KHGh2cy5zUkRkckNnZWdhT1NwcVZWUzJPN1RtUGQ

Vault provides a secrets engine to manage database credentials. I enable it with the following command:

vault secrets enable database

Now, I have to configure this database secrets engine with the vault write command. To do that, I must provide the database root connection information. As I am using a PostgreSQL database, I use the postgresql-database-plugin Vault plugin :

vault write database/config/my-postgres-database \
     plugin_name=postgresql-database-plugin \
     connection_url="postgresql://{{username}}:{{password}}@postgres:5432/postgres" \
     allowed_roles="*" \
     username="postgres" \
     password="password"

my-postgres-database is an arbitrary name that I give to the database secrets engine configuration. username and password are the current database root credentials defined in the docker-compose file.

Now, Vault has root access to the database. Remember, I said earlier that I must change the password password as soon as possible. It is now time to do it. I rotate the root credentials with the following command:

vault write -force database/rotate-root/my-postgres-database

To check it, I try to access again the database from my host with psql -h localhost -p 5432 -U postgres. After entering the password password, I see that the authentication fails. So now, only Vault knows the new root password.

If only Vault has access to the database, how can my application access it? In the next step, I will set up a database role that will be used by Vault to create a new database user when my application will ask Vault for database credentials.

Creating Database Role For Our Application

I will create the db-app database role that will only have the SELECT privilege on all tables in the public schema.

First, I create a readonly.sql file containing the SQL statements to create this role:

CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";

The values of the {{name}}, {{password}} and {{expiration}} placeholders will be provided by Vault when it creates the database user.

Then, I create the db-app database role with the following command:

vault write database/roles/db-app \
      db_name=my-postgres-database \
      creation_statements=@readonly.sql \
      default_ttl=1h \
      max_ttl=24h

Each time my application asks Vault for database credentials, Vault will create a new user with the creation_statements. This user will be valid for 1 hour and can be renewed for a maximum of 24 hours.

Getting Database Credentials

To get database credentials, our application has to send a GET request to the http://localhost:8200/v1/database/creds/db-app endpoint with the token of the app role in the X-Vault-Token header.

The response body to this request will look like this:

{
  "request_id": "f4b4e3e2-af46-dace-5ca6-b4791e479068",
  "lease_id": "database/creds/db-app/aJIeIYbTPe8IN5BjPSeWCrp1",
  "renewable": true,
  "lease_duration": 3600,
  "data": {
    "password": "1IsVCSk1JwYex-KtpdyU",
    "username": "v-token-db-app-0dBW8SFZLpPDbyWxWUdi-1671800140"
  },
  "wrap_info": null,
  "warnings": null,
  "auth": null
}

I can use the username and password to access the database from my host with psql -h localhost -p 5432 -d postgres -U v-token-db-app-0dBW8SFZLpPDbyWxWUdi-1671800140. After entering the password, I see that password authentication succeeds and I am only authorized to execute SELECT queries as I defined in the creation_statements of the db-app role.

I now have to configure my application to use Vault to get database credentials. In my case, I am using an API built with the NestJS framework. I create a getDatabaseConfig function that will send a request to Vault to get database credentials. This function returns a configuration object that will be used to connect to the database:

import "dotenv/config";
import { DataSourceOptions } from "typeorm";
import { join } from "path";
import { HttpService } from "@nestjs/axios";
import { firstValueFrom } from "rxjs";

const getDatabaseConfig = async (): Promise<DataSourceOptions> => {
  const httpService = new HttpService();
  const res = await firstValueFrom(
    httpService.get(`${process.env.VAULT_ADDR}/v1/database/creds/api`, {
      headers: {
        "X-Vault-Token": process.env.VAULT_TOKEN,
      },
    }),
  );
  const { username, password } = res.data.data;
  return {
    type: "postgres",
    host: process.env.DATABASE_HOST,
    port: parseInt(process.env.DATABASE_PORT, 10),
    username,
    password,
    database: process.env.DATABASE_NAME,
    entities: [join(__dirname, "..", "**", "*.entity.{js,ts}")],
    migrations: [join(__dirname, "..", "migrations", "*.{js,ts}")],
    synchronize: false,
    migrationsRun: false,
  };
};

export default getDatabaseConfig;

This getDatabaseConfig function is called in the app.module.ts file of my application:

import { Module } from "@nestjs/common";
import { TypeOrmModule, TypeOrmModuleOptions } from "@nestjs/typeorm";
import getDatabaseConfig from "./database/databaseConfig";

@Module({
  imports: [
    TypeOrmModule.forRootAsync({
      useFactory: async (): Promise<TypeOrmModuleOptions> => {
        return getDatabaseConfig();
      },
    }),
  ],
})
export class AppModule {}

Now, when my application starts, it sends a request to Vault to get database credentials and use them to connect to the database.

Conclusion

In this article, I have shown how to use Vault to securely manage database credentials. Once Vault is configured, its usage is very simple. The only thing to do is to send a request to Vault to get database credentials and use them to connect to the database.

Here, I configured it just to manage database credentials but it could be great to manage all secrets of a project (API keys, passwords, certificates, SSH keys, …) in order to completely replace .env files.