database

Database Credentials with HashiCorp Vault

Dynamic database credentials provisioning and lifecycle management

🚀 About

In this HashiQube DevOps lab, you'll get hands-on experience with HashiCorp Vault's database secrets engine. Learn how Vault can securely provision and manage credentials for your databases, eliminating the need for static credentials and enhancing your security posture.

This lab will guide you through integrating three popular database systems with HashiCorp Vault:

• Oracle MySQL
• Microsoft SQL Server
• PostgreSQL

📚 Database Engines

🔵 Oracle MySQL

Provision MySQL

Github Codespace

bash docker/docker.sh
bash vault/vault.sh
bash database/mysql.sh

Vagrant

vagrant up --provision-with basetools,docker,docsify,vault,mysql

Docker Compose

docker compose exec hashiqube /bin/bash
bash hashiqube/basetools.sh
bash docker/docker.sh
bash docsify/docsify.sh
bash vault/vault.sh
bash database/mysql.sh

Verifying MySQL Installation

Let's verify that our MySQL container is up and accepting connections:

vagrant ssh -c "mysql -h 127.0.0.1 -u root -ppassword -e \"show databases;\""

Output:

mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| db                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Configuring Vault with MySQL

1. Start Vault

   vagrant up --provision-with basetools,docker,docsify,vault

2. Unseal Vault and Login

   Use the unseal keys and root token provided in the output to unseal Vault and log in.

   

   Vault UI after successful login

3. Enable the Database Secrets Engine

   Navigate to "Enable new Engine" in the top right, select "Database" and click "Next".

   

   Select the Database secrets engine

   

   Confirm and enable the Database secrets engine

4. Create Vault User in MySQL

   vagrant ssh -c "mysql -h 127.0.0.1 -u root -ppassword -e \"CREATE USER 'vault'@'%' IDENTIFIED BY 'password';\""
   
   vagrant ssh -c "mysql -h 127.0.0.1 -u root -ppassword -e \"GRANT ALL PRIVILEGES ON *.* TO 'vault'@'%' WITH GRANT OPTION;\""
   
   vagrant ssh -c "mysql -h 127.0.0.1 -u root -ppassword -e \"GRANT CREATE USER ON *.* to 'vault'@'%';\""

5. Configure MySQL in Vault

   vagrant ssh -c "vault write database/config/db plugin_name=mysql-database-plugin connection_url='{{username}}:{{password}}@tcp(localhost:3306)/' allowed_roles='mysql-role' username='vault' password='password'"

6. Create a Database Role

   vagrant ssh -c "vault write database/roles/mysql-role db_name=db creation_statements=\"CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT ALL PRIVILEGES ON db.* TO '{{name}}'@'%';\" default_ttl='1h' max_ttl='24h'"

   Output:

   Success! Data written to: database/roles/mysql-role

Generating and Using Dynamic Credentials

1. Check Current MySQL Users

   vagrant ssh -c "mysql -h 127.0.0.1 -u root -ppassword -e \"SELECT User, Host from mysql.user;\""

   Output:

   mysql: [Warning] Using a password on the command line interface can be insecure.
   +------------------+-----------+
   | User             | Host      |
   +------------------+-----------+
   | root             | %         |
   | vault            | %         |
   | mysql.infoschema | localhost |
   | mysql.session    | localhost |
   | mysql.sys        | localhost |
   | root             | localhost |
   +------------------+-----------+

2. Generate Credentials from Vault

   vagrant ssh -c "vault read database/creds/mysql-role"

   Output:

   Key                Value
   ---                -----
   lease_id           database/creds/mysql-role/IhHPq0RcdmDdTIjsfLBePLcp
   lease_duration     1h
   lease_renewable    true
   password           A1a-0bdhOg0OiZQV0TTP
   username           v-root-mysqlrole-zV7t3V0bJFZZJTg

3. Verify New User Existence

   vagrant ssh -c "mysql -h 127.0.0.1 -u root -ppassword -e \"SELECT User, Host from mysql.user;\""

   Output:

   mysql: [Warning] Using a password on the command line interface can be insecure.
   +-----------------------------------+-----------+
   | User                              | Host      |
   +-----------------------------------+-----------+
   | root                              | %         |
   | v-root-mysql-role-zV7t3V0bJFZZJTg | %         |
   | vault                             | %         |
   | mysql.infoschema                  | localhost |
   | mysql.session                     | localhost |
   | mysql.sys                         | localhost |
   | root                              | localhost |
   +-----------------------------------+-----------+

4. Retrieve Credentials via API

   vagrant ssh -c "curl --header 'X-Vault-Token:s.h7kojucmDDULDmxHAyr7jhrE' http://localhost:8200/v1/database/creds/mysql-role"

   Output:

   {
   "request_id":"23116091-f72b-80f9-fb0e-6ce5418bae1d",
   "lease_id":"database/creds/mysql-role/7wMxCUzNcEaOrvCspBhXnjTM",
   "renewable":true,
   "lease_duration":3600,
   "data":{
     "password":"A1a-XhNU8s4P0Ph5Se9O",
     "username":"v-root-mysql-role-kmFADTyAAfv7LS0"
   },
   "wrap_info":null,
   "warnings":null,
   "auth":null
   }

5. Using Credentials in Applications

   response=$(curl --header "X-Vault-Token:s.h7kojucmDDULDmxHAyr7jhrE" http://localhost:8200/v1/database/creds/mysql-role)
   export DBPASSWORD=$(echo $response | jq -r .data.password)
   export DBUSERNAME=$(echo $response | jq -r .data.username)
   
   docker run --name webapp -d -p 8080:80 --rm -e DATABASE_URL=mysql+pymysql://DBUSERNAME:[email protected]/db webapp:latest

6. Credential Lifecycle Management

   After the lease expires (1 hour in our configuration), Vault automatically revokes the credentials:

   vagrant ssh -c "mysql -h 127.0.0.1 -u root -ppassword -e \"SELECT User, Host from mysql.user;\""

   Output after credential expiration:

   mysql: [Warning] Using a password on the command line interface can be insecure.
   +------------------+-----------+
   | User             | Host      |
   +------------------+-----------+
   | root             | %         |
   | vault            | %         |
   | mysql.infoschema | localhost |
   | mysql.session    | localhost |
   | mysql.sys        | localhost |
   | root             | localhost |
   +------------------+-----------+

MySQL Provisioner Script

The script below automates the setup of MySQL in your HashiQube environment:

#!/bin/bash

# Print the commands that are run
set -x

# Stop execution if something fails
set -e

# This script provisions MySQL

if [[ $(docker ps | grep mysql) ]]; then
  echo "MySQL already running..."
  if [[ $(docker ps | grep -v mysql) ]]; then
    echo "MySQL not running, starting it up..."
    docker start mysql
  fi
else
  # You can also set up docker-compose without passwords as well if thats a step too far
  echo "Starting MySQL..."
  docker run --name mysql \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=password \
    -e MYSQL_DATABASE=db \
    -d mysql:5.7 \
    --character-set-server=utf8mb4 \
    --collation-server=utf8mb4_unicode_ci
fi

🔴 Microsoft SQL Server

Provision Microsoft SQL Server

GitHub Codespaces

bash docker/docker.sh
bash vault/vault.sh
bash database/mssql.sh

Vagrant

vagrant up --provision-with basetools,docker,docsify,vault,mssql

Docker Compose

docker compose exec hashiqube /bin/bash
bash hashiqube/basetools.sh
bash docker/docker.sh
bash docsify/docsify.sh
bash vault/vault.sh
bash database/mssql.sh

Configuring Microsoft SQL Server with Vault

1. Create a Database

   vagrant ssh
   docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P P@ssw0rd -Q "CREATE DATABASE mssql"

2. Verify Database Creation

   docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P P@ssw0rd -Q "SELECT name, database_id, create_date FROM sys.databases"

   Output:

   name                                                                                                                             database_id create_date
   -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------------------
   master                                                                                                                                     1 2003-04-08 09:13:36.390
   tempdb                                                                                                                                     2 2019-11-20 03:23:54.157
   model                                                                                                                                      3 2003-04-08 09:13:36.390
   msdb                                                                                                                                       4 2018-06-13 18:27:29.220
   mssql                                                                                                                                      5 2019-11-20 03:24:03.043
   
   (5 rows affected)
   

3. Enable Database Secrets Engine in Vault (if not already enabled)

   vault secrets enable database

4. Configure MSSQL in Vault

   vault write database/config/mssql \
     plugin_name=mssql-database-plugin \
     connection_url='sqlserver://{{username}}:{{password}}@localhost:1433' \
     allowed_roles="mssql" \
     username="sa" \
     password="P@ssw0rd"

5. Create a Database Role

   vault write database/roles/mssql \
     db_name=mssql \
     creation_statements="CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}'; \
     CREATE USER [{{name}}] FOR LOGIN [{{name}}]; \
     GRANT SELECT ON SCHEMA::dbo TO [{{name}}];" \
     default_ttl="1h" \
     max_ttl="24h"

Generating and Using Dynamic Credentials

1. Generate Credentials from Vault

   vault read database/creds/mssql

2. Verify Credential Creation

   docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U v-root-mssql-5nBk5IA9hydRgzOkgB8M-1574220338 -P A1a-dninssZ6v3mNBOfK -Q "SELECT * FROM sys.server_principals"

3. Credential Lifecycle Management

   After the lease expires (1 hour in our configuration), attempting to use the credentials will fail:

   docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U v-root-mssql-5nBk5IA9hydRgzOkgB8M-1574220338 -P A1a-dninssZ6v3mNBOfK -Q "SELECT * FROM sys.server_principals"

   Output after credential expiration:

   Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'v-root-mssql-5nBk5IA9hydRgzOkgB8M-1574220338'..

Microsoft SQL Server Provisioner Script

The script below automates the setup of Microsoft SQL Server in your HashiQube environment:

#!/bin/bash

# Print the commands that are run
set -x

# Stop execution if something fails
set -e

# This script provisions Microsoft SQL Server
cd /home/vagrant

if [[ $(docker ps | grep mssql) ]]; then
  echo "Microsoft SQL Server already running..."
  if [[ $(docker ps | grep -v mssql) ]]; then
    echo "Microsoft SQL Server not running, starting it up..."
    docker start mssql
  fi
else
  echo "Starting Microsoft SQL Server..."
  docker run --name mssql \
    -e "ACCEPT_EULA=Y" \
    -e "SA_PASSWORD=P@ssw0rd" \
    -p 1433:1433 \
    -d mcr.microsoft.com/mssql/server:2019-latest
fi

🟢 PostgreSQL

Provision PostgreSQL

GitHub Codespaces

bash docker/docker.sh
bash vault/vault.sh
bash database/postgresql.sh

Vagrant

vagrant up --provision-with basetools,docker,docsify,vault,postgresql

Docker Compose

docker compose exec hashiqube /bin/bash
bash hashiqube/basetools.sh
bash docker/docker.sh
bash docsify/docsify.sh
bash vault/vault.sh
bash database/postgresql.sh

PostgreSQL Provisioner Script

The script below automates the setup of PostgreSQL in your HashiQube environment:

#!/bin/bash

# Print the commands that are run
set -x

# Stop execution if something fails
set -e

# This script provisions PostgreSQL
cd /home/vagrant

# Check if we already have postgres
if [[ $(docker ps | grep postgres) ]]; then
  echo "PostgreSQL already running..."
else
  echo "Starting PostgreSQL..."
  docker run --name postgres \
    -e POSTGRES_PASSWORD=hashiqube \
    -e POSTGRES_USER=hashiqube \
    -e POSTGRES_DB=hashiqube \
    -p 5432:5432 \
    -d postgres:latest
fi

🔑 Key Benefits

1. Enhanced Security - Eliminate static, long-lived credentials that can be compromised
2. Automatic Rotation - Credentials are automatically rotated based on configured TTLs
3. Fine-grained Access Control - Create specific roles with limited permissions
4. Audit Trail - Track who accessed which credentials and when
5. Simplified Credential Management - Centralized management of all database credentials
6. Reduced Operational Overhead - No need to manually rotate credentials or manage password reset schedules

🔍 How It Works

1. Vault is configured with connection details to the database, using a privileged account that can create users
2. When an application needs database access, it authenticates to Vault and requests credentials
3. Vault creates a unique set of credentials with the requested permissions and returns them to the application
4. The credentials have a time-to-live (TTL) setting, after which Vault automatically revokes them
5. If needed, the application can renew the credentials before they expire

🛠️ Additional Use Cases

• CI/CD Pipelines - Provide temporary credentials for database migrations and tests
• Microservices - Each service gets its own set of credentials with minimal required permissions
• Development Environments - Easily create and manage credentials for developers without sharing access
• Cross-environment Consistency - Use the same workflow in development, staging, and production

📚 Further Reading

• HashiCorp Vault Database Secrets Engine
• Dynamic Secrets: Database Credentials Rotation
• Database Root Credential Rotation