Configure Vault dynamic secrets for databases with PostgreSQL and MySQL integration

Intermediate 45 min Apr 19, 2026 130 views
Ubuntu 24.04 Debian 12 AlmaLinux 9 Rocky Linux 9

Set up HashiCorp Vault's database secrets engine to automatically generate short-lived credentials for PostgreSQL and MySQL databases, improving security by eliminating static passwords and enabling automated credential rotation.

Prerequisites

  • Running Vault server with admin access
  • PostgreSQL or MySQL database server
  • Network connectivity between Vault and databases

What this solves

Static database passwords are a security risk that creates operational overhead when they need rotation. Vault's dynamic secrets engine generates temporary database credentials on-demand, automatically rotating them and revoking access when the lease expires. This eliminates the need to store long-lived database passwords in configuration files or environment variables.

Prerequisites

You'll need a running Vault server and PostgreSQL or MySQL database server. This tutorial assumes you have administrative access to both systems and basic familiarity with database user management.

Note: If you need to set up Vault first, check our guide on installing Vault for secrets management.

Step-by-step configuration

Install database clients

Install the database client tools needed to test connections and manage database users.

sudo apt update
sudo apt install -y postgresql-client mysql-client
sudo dnf install -y postgresql mysql

Enable the database secrets engine

Enable Vault's database secrets engine at the database/ path. This engine manages database connections and credential generation.

vault auth -method=userpass username=admin
vault secrets enable database

Create PostgreSQL database connection

Configure Vault to connect to your PostgreSQL database. Replace the connection details with your actual database server information.

vault write database/config/postgresql \
    plugin_name=postgresql-database-plugin \
    connection_url="postgresql://{{username}}:{{password}}@203.0.113.10:5432/postgres?sslmode=disable" \
    allowed_roles="readonly,readwrite" \
    username="vault" \
    password="vault_user_password"

Create MySQL database connection

Configure Vault to connect to your MySQL database using the MySQL database plugin.

vault write database/config/mysql \
    plugin_name=mysql-database-plugin \
    connection_url="{{username}}:{{password}}@tcp(203.0.113.20:3306)/" \
    allowed_roles="readonly,readwrite" \
    username="vault" \
    password="vault_user_password"

Create PostgreSQL database users

Create the Vault service user in PostgreSQL with permissions to create and manage other database users.

psql -h 203.0.113.10 -U postgres -c "CREATE USER vault WITH PASSWORD 'vault_user_password';"
psql -h 203.0.113.10 -U postgres -c "GRANT CREATE ON DATABASE postgres TO vault;"
psql -h 203.0.113.10 -U postgres -c "ALTER USER vault CREATEROLE;"

Create MySQL database users

Create the Vault service user in MySQL with permissions to create and manage other database users.

mysql -h 203.0.113.20 -u root -p -e "CREATE USER 'vault'@'%' IDENTIFIED BY 'vault_user_password';"
mysql -h 203.0.113.20 -u root -p -e "GRANT CREATE USER ON . TO 'vault'@'%' WITH GRANT OPTION;"
mysql -h 203.0.113.20 -u root -p -e "GRANT SELECT ON . TO 'vault'@'%';"
mysql -h 203.0.113.20 -u root -p -e "FLUSH PRIVILEGES;"

Create PostgreSQL readonly role

Define a readonly role that generates database users with SELECT permissions only. The SQL statements define what permissions the generated users will have.

vault write database/roles/postgresql-readonly \
    db_name=postgresql \
    creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
                        GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";"\
    default_ttl="1h" \
    max_ttl="24h"

Create PostgreSQL readwrite role

Define a readwrite role that generates database users with SELECT, INSERT, UPDATE, and DELETE permissions.

vault write database/roles/postgresql-readwrite \
    db_name=postgresql \
    creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
                        GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO \"{{name}}\";"\
    default_ttl="1h" \
    max_ttl="24h"

Create MySQL readonly role

Define a MySQL readonly role that creates users with SELECT permissions across all databases.

vault write database/roles/mysql-readonly \
    db_name=mysql \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}'; \
                        GRANT SELECT ON . TO '{{name}}'@'%';" \
    revocation_statements="DROP USER '{{name}}'@'%';" \
    default_ttl="1h" \
    max_ttl="24h"

Create MySQL readwrite role

Define a MySQL readwrite role that creates users with comprehensive data manipulation permissions.

vault write database/roles/mysql-readwrite \
    db_name=mysql \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}'; \
                        GRANT SELECT, INSERT, UPDATE, DELETE ON . TO '{{name}}'@'%';" \
    revocation_statements="DROP USER '{{name}}'@'%';" \
    default_ttl="1h" \
    max_ttl="24h"

Create access policies

Create Vault policies that control which applications can request specific database roles.

path "database/creds/postgresql-readonly" {
  capabilities = ["read"]
}

path "database/creds/mysql-readonly" {
  capabilities = ["read"]
}
vault policy write db-readonly /tmp/db-readonly-policy.hcl

Create readwrite policy

Create a more privileged policy for applications that need write access to databases.

path "database/creds/postgresql-readwrite" {
  capabilities = ["read"]
}

path "database/creds/mysql-readwrite" {
  capabilities = ["read"]
}
vault policy write db-readwrite /tmp/db-readwrite-policy.hcl

Test credential generation

Generate dynamic credentials for PostgreSQL and MySQL to verify the configuration works correctly.

vault read database/creds/postgresql-readonly
vault read database/creds/mysql-readonly

The output shows the generated username, password, and lease information.

Verify your setup

Test that the generated credentials work by connecting to your databases and verifying the appropriate permissions.

# Generate PostgreSQL credentials
vault read database/creds/postgresql-readonly


Test PostgreSQL connection (replace with actual generated credentials)

psql -h 203.0.113.10 -U v-token-postgre-2Rt7X9Y4kZ -d postgres -c "SELECT current_user;"


Generate MySQL credentials

vault read database/creds/mysql-readonly


Test MySQL connection (replace with actual generated credentials)

mysql -h 203.0.113.20 -u v-token-mysql-8Kx3M2N7qL -p -e "SELECT USER();"

Check that credentials automatically expire and are cleaned up:

# List active leases
vault list sys/leases/lookup/database/creds/postgresql-readonly


Check lease details

vault lease lookup database/creds/postgresql-readonly/lease_id_here

Configure credential rotation

Set up automatic rotation of the root database credentials that Vault uses to connect to your databases.

Enable root credential rotation

Configure Vault to automatically rotate the root database credentials periodically for enhanced security.

vault write database/config/postgresql \
    plugin_name=postgresql-database-plugin \
    connection_url="postgresql://{{username}}:{{password}}@203.0.113.10:5432/postgres?sslmode=disable" \
    allowed_roles="readonly,readwrite" \
    username="vault" \
    password="vault_user_password" \
    password_policy="vault_password_policy"

Create password policy

Define a password policy for generated credentials to ensure they meet security requirements.

length = 20

rule "charset" {
  charset = "abcdefghijklmnopqrstuvwxyz"
  min-chars = 1
}

rule "charset" {
  charset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  min-chars = 1
}

rule "charset" {
  charset = "0123456789"
  min-chars = 1
}

rule "charset" {
  charset = "!@#$%^&*"
  min-chars = 1
}
vault write sys/policies/password/vault_password_policy policy=@/tmp/password-policy.hcl

Rotate root credentials

Perform an immediate rotation of the root credentials to test the rotation functionality.

vault write -force database/rotate-root/postgresql
vault write -force database/rotate-root/mysql

Application integration examples

Here are examples of how applications can request and use dynamic database credentials.

Python application example

import hvac
import psycopg2
import os


Initialize Vault client

client = hvac.Client(url='http://127.0.0.1:8200')
client.token = os.environ['VAULT_TOKEN']


Get dynamic PostgreSQL credentials

response = client.read('database/creds/postgresql-readonly')
credentials = response['data']


Connect to database using dynamic credentials

conn = psycopg2.connect(
    host="203.0.113.10",
    database="postgres",
    user=credentials['username'],
    password=credentials['password']
)


Use the connection

cursor = conn.cursor()
cursor.execute("SELECT version();")
result = cursor.fetchone()
print(result)

Shell script example

#!/bin/bash


Get PostgreSQL credentials from Vault

CREDS=$(vault read -format=json database/creds/postgresql-readonly)
DB_USER=$(echo $CREDS | jq -r '.data.username')
DB_PASS=$(echo $CREDS | jq -r '.data.password')


Export for use by other applications

export PGUSER=$DB_USER
export PGPASSWORD=$DB_PASS
export PGHOST=203.0.113.10
export PGDATABASE=postgres

echo "Database credentials set for user: $DB_USER"

Common issues

Symptom Cause Fix
"permission denied for database" error Vault user lacks CREATE permissions Grant CREATE and CREATEROLE to vault user in PostgreSQL
"Access denied" in MySQL Vault user missing user management privileges Grant CREATE USER and WITH GRANT OPTION to vault MySQL user
"plugin not found" error Database plugin not available Verify Vault version includes database plugins or reinstall
Generated users can't connect Network connectivity or host restrictions Check firewall rules and database host-based authentication
Credentials expire too quickly Default TTL too short Increase default_ttl and max_ttl in role configuration
"role does not exist" error Role name mismatch or not created Verify role exists with vault list database/roles

Security considerations

Implement these security best practices when using Vault dynamic secrets in production:

Important: Never store the Vault token or database credentials in application code or configuration files. Use Vault's AppRole authentication or cloud-specific auth methods for applications.
  • Use TLS encryption for all Vault and database connections
  • Implement proper authentication methods like AppRole instead of tokens
  • Monitor credential usage and lease activity through Vault audit logs
  • Set appropriate TTL values based on application needs and security requirements
  • Regularly rotate the root database credentials used by Vault
  • Use network policies to restrict database access to authorized applications only

For enhanced security in production environments, consider implementing Vault PKI for certificate management alongside database secrets.

Next steps

Running this in production?

Want this handled for you? Setting up Vault dynamic secrets once is straightforward. Keeping it patched, monitored, backed up and tuned across environments is the harder part. See how we run infrastructure like this for European SaaS and fintech teams.

Automated install script

Run this to automate the entire setup

#vault #dynamic secrets #database security #postgresql #mysql

Related tutorials

security Intermediate

Configure automated compliance scanning with OpenSCAP and audit reporting

Set up OpenSCAP security compliance scanning with automated systemd timers, generate HTML and XML audit reports, and configure SCAP security profiles for continuous compliance monitoring.

25 min 4 distros 31 views
security Advanced

Configure encrypted network storage with LUKS and NFS for secure file sharing

Set up enterprise-grade encrypted network storage by combining LUKS disk encryption with NFS file sharing. This advanced configuration provides secure, centralized file access across multiple systems while maintaining data protection at rest.

45 min 4 distros 39 views
security Intermediate

Set up LUKS full disk encryption during Linux installation

Configure full disk encryption with LUKS during OS installation to secure your data at rest. Includes post-installation key management, performance optimization, and troubleshooting for production systems.

45 min 4 distros 76 views
Browse all tutorials in our complete directory →

Need help?

Don't want to manage this yourself?

We handle infrastructure security hardening for businesses that depend on uptime. From initial setup to ongoing operations.

Learn about our infrastructure security hardening Talk to an engineer