HashiCorp Vault — PostgreSQL & MySQL

3 minute read

Hands-on setup of dynamic database credentials for PostgreSQL and MySQL — the two most common relational databases.

PostgreSQL — Complete Setup

Prerequisites

# Start PostgreSQL (Docker)
docker run -d --name postgres \
    -e POSTGRES_USER=vault_admin \
    -e POSTGRES_PASSWORD=vault_admin_pass \
    -e POSTGRES_DB=myapp \
    -p 5432:5432 \
    postgres:16

# Verify connectivity
psql -h 127.0.0.1 -U vault_admin -d myapp -c "SELECT 1"

Configure Vault

# Enable database secrets engine
vault secrets enable database

# Configure PostgreSQL connection
vault write database/config/myapp-postgres \
    plugin_name="postgresql-database-plugin" \
    allowed_roles="pg-readonly,pg-readwrite" \
    connection_url="postgresql://:@127.0.0.1:5432/myapp?sslmode=disable" \
    username="vault_admin" \
    password="vault_admin_pass"

# Rotate root credentials (Vault takes ownership)
vault write -f database/rotate-root/myapp-postgres

Create Roles

# Read-only role
vault write database/roles/pg-readonly \
    db_name="myapp-postgres" \
    creation_statements="CREATE ROLE \"\" WITH LOGIN PASSWORD '' VALID UNTIL ''; \
        GRANT CONNECT ON DATABASE myapp TO \"\"; \
        GRANT USAGE ON SCHEMA public TO \"\"; \
        GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"\"; \
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"\";" \
    revocation_statements="REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM \"\"; \
        REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM \"\"; \
        REVOKE USAGE ON SCHEMA public FROM \"\"; \
        REVOKE CONNECT ON DATABASE myapp FROM \"\"; \
        DROP ROLE IF EXISTS \"\";" \
    default_ttl="1h" \
    max_ttl="24h"

# Read-write role
vault write database/roles/pg-readwrite \
    db_name="myapp-postgres" \
    creation_statements="CREATE ROLE \"\" WITH LOGIN PASSWORD '' VALID UNTIL ''; \
        GRANT CONNECT ON DATABASE myapp TO \"\"; \
        GRANT USAGE ON SCHEMA public TO \"\"; \
        GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO \"\"; \
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO \"\";" \
    revocation_statements="REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM \"\"; \
        REVOKE USAGE ON SCHEMA public FROM \"\"; \
        REVOKE CONNECT ON DATABASE myapp FROM \"\"; \
        DROP ROLE IF EXISTS \"\";" \
    default_ttl="1h" \
    max_ttl="24h"

Generate and Test Credentials

# Generate read-only credentials
vault read database/creds/pg-readonly

Expected output:

Key                Value
---                -----
lease_id           database/creds/pg-readonly/abc123
lease_duration     1h
lease_renewable    true
password           A1a-randompassword123
username           v-token-pg-readon-abc123-1234567890

# Test the credentials
psql -h 127.0.0.1 -U v-token-pg-readon-abc123-1234567890 -d myapp

# Can SELECT
SELECT * FROM my_table;

# Cannot INSERT (read-only)
INSERT INTO my_table (name) VALUES ('test');
-- ERROR: permission denied

MySQL — Complete Setup

Prerequisites

# Start MySQL (Docker)
docker run -d --name mysql \
    -e MYSQL_ROOT_PASSWORD=root_pass \
    -e MYSQL_DATABASE=myapp \
    -e MYSQL_USER=vault_admin \
    -e MYSQL_PASSWORD=vault_admin_pass \
    -p 3306:3306 \
    mysql:8

# Verify connectivity
mysql -h 127.0.0.1 -u vault_admin -pvault_admin_pass myapp -e "SELECT 1"

Configure Vault

# Configure MySQL connection
vault write database/config/myapp-mysql \
    plugin_name="mysql-database-plugin" \
    allowed_roles="mysql-readonly,mysql-readwrite" \
    connection_url=":@tcp(127.0.0.1:3306)/myapp" \
    username="vault_admin" \
    password="vault_admin_pass"

# Rotate root credentials
vault write -f database/rotate-root/myapp-mysql

Create Roles

# Read-only role
vault write database/roles/mysql-readonly \
    db_name="myapp-mysql" \
    creation_statements="CREATE USER ''@'%' IDENTIFIED BY ''; \
        GRANT SELECT ON myapp.* TO ''@'%';" \
    revocation_statements="REVOKE ALL PRIVILEGES, GRANT OPTION FROM ''@'%'; \
        DROP USER IF EXISTS ''@'%';" \
    default_ttl="1h" \
    max_ttl="24h"

# Read-write role
vault write database/roles/mysql-readwrite \
    db_name="myapp-mysql" \
    creation_statements="CREATE USER ''@'%' IDENTIFIED BY ''; \
        GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO ''@'%';" \
    revocation_statements="REVOKE ALL PRIVILEGES, GRANT OPTION FROM ''@'%'; \
        DROP USER IF EXISTS ''@'%';" \
    default_ttl="1h" \
    max_ttl="24h"

Generate Credentials

vault read database/creds/mysql-readonly

Application Integration

Python Example

import hvac
import psycopg2

# Authenticate to Vault
client = hvac.Client(url='http://127.0.0.1:8200', token='my-token')

# Get dynamic credentials
creds = client.secrets.database.generate_credentials('pg-readonly')
username = creds['data']['username']
password = creds['data']['password']
lease_id = creds['lease_id']

# Connect to database
conn = psycopg2.connect(
    host='127.0.0.1',
    database='myapp',
    user=username,
    password=password
)

# Use the connection...
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")

# When done, revoke the lease
client.sys.revoke_lease(lease_id)

Quick Reference

Command Description
vault write database/config/NAME ... Configure DB connection
vault write -f database/rotate-root/NAME Rotate root password
vault write database/roles/ROLE ... Create role
vault read database/creds/ROLE Generate credentials
vault lease revoke LEASE_ID Revoke credentials early

Next: AWS Secrets Engine

You may also enjoy