Trying Out Vault for Postgres Credentials

Install Vault, as well as JQ for JSON parsing

brew install vault jq

Start the dev server

vault server -dev

Then open another window. For this demo, we’ll create a new Postgres database.

createdb myapp

Create a Postgres user for Vault to manage other users

psql -c "CREATE USER vault WITH CREATEROLE ENCRYPTED PASSWORD 'secret';" myapp

And create a role to grant to temporary users. This is where you should configure privileges (omitted).

psql -c "CREATE ROLE app;" myapp

Configure Vault. We set a default TTL of 10 seconds for users to test.

export VAULT_ADDR='http://127.0.0.1:8200'

vault mount database

vault write database/config/postgresql \
    plugin_name=postgresql-database-plugin \
    allowed_roles="app" \
    connection_url="postgresql://vault:secret@localhost:5432/myapp?sslmode=disable"

vault write database/roles/app \
    db_name=postgresql \
    creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';" \
    default_ttl="10s" \
    max_ttl="24h"

Fetch temporary credentials

vault read -format=json database/creds/app

Save the result as environment variables (with JQ)

data=`vault read -format=json database/creds/app`
export PGUSER=`echo $data | jq -r '.data.username'`
export PGPASSWORD=`echo $data | jq -r '.data.password'`

Test the new user

psql -c "SELECT current_user;" myapp

Wait 10 seconds and re-run the command to confirm the user no longer exists

Published November 5, 2017 · Tweet


You might also enjoy

PgBouncer Setup

Securing Database Traffic with PgBouncer and Amazon RDS

Package Your JavaScript Libraries With Rollup


All code examples are public domain.
Use them however you’d like (licensed under CC0).