Vault local testing setup

When I was confirming the configurations for my vault management of database credentials, I used a local postgresql and vault server. This may also be useful for development (especially testing code that may exercise the vault and database interactions).

This can make it relatively easy to watch all of the pieces and throw away any side-effects that you don't want in your production or staging servers.

  1. If you're using vault elsewhere in your system, make sure you clear out your credentials from any cache while doing this work or you may accidentally modify a running system.

  2. Make sure you have a running postgresql server

  3. Start a vault development server: vault server -dev, which will automatically unseal itself and run in memory, so there's no need to clean up after yourself (although you'll have to start the whole process again if you kill the server)

  4. Create a new admin user in your local database. I use a separate user for vault so that I don't run into a problem in break-glass scenarios, but you can also do that with local user override. I use local-vault, and for the sake of this example, set the passwrd to `changeme``

  5. vault secrets enable database to turn on the database secret manager

  6. Create the database config:

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

    This command will create the record for localpg, setting it up to manage the local-vault credentials for the local vault

  7. Force rotation of the password (this will make it so that you don't know the root-standin password):

    vault write -force database/rotate-root/localpg
  8. Now, for each of the users that we want the database to be able to use, we need to create roles using the parameters we determined previously.

    For the ownership user, this is:

    vault write database/roles/owner-test \
     db_name=localpg \
     creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE \"owner\" NOINHERIT;" \
     revocation_statements="drop user \"{{name}}\";" \
     default_ttl=60s \

    The braced variables are replaced by vault, so this will result in a user with a name, password, and expiration chosen by vault, which has access to the abilities of the owner role, but only if it is explicitly assumed using set ROLE owner.

    NOTE: for real use, you want longer TTLs than 60s and 60m, but these were used here because it is a test environment and we want to verify both the renewal and expiration of the credentials.

  9. Now, you should be able to retrieve the credentials from vault manually

    vault read database/creds/owner-test

    Once you've created the credential, you should be able to access it and also see the credentials with \du in the psql interface. You should see expiration times for the credentials you create, and they should automatically disappear after that time.