Configuring HashiCorp Vault to Create Static PostgreSQL Credentials


HashiCorp Vault is an open source tool designed to securely store secrets and sensitive data in dynamic cloud environments. It provides strong data encryption, identity-based access through customizable policies.

Vault provides a secrets mechanism that can be configured to create a set of static credentials with a hard-scoped TTL (time to live) set.

Checking Vault Status

$ vault status

We print the storage

$ vault operator unseal

Log in

$ vault login
Token (will be hidden):

Configuring Vault

Enabling the secret store for the database

$ vault secrets enable -path=postgresql database

We indicate which plugin for Vault we will use, and information about the connection

$ vault write postgresql/config/connection 

In this example, PostgreSQL is on the same server with Vault, hence “@localhost: 5432”

You can see what has been recorded

$ vault read postgresql/config/connection
Key                                   Value
---                                   -----
allowed_roles                         [*]
connection_details                    map[connection_url:postgresql://{{username}}:{{password}}@localhost:5432/postgres?sslmode=disable username:postgres]
password_policy                       n/a
plugin_name                           postgresql-database-plugin
root_credentials_rotate_statements    []

To reset the password for the postgres user, run:

$ vault write -force postgresql/rotate-root/connection
Success! Data written to: postgresql/rotate-root/connection

Create a role in the database and assign privileges (without this, the next vault request will not work)

$ sudo su - postgres
$ psql
# CREATE ROLE "vault-edu" WITH LOGIN PASSWORD 'mypassword';
# q
$ exit

Now let’s create a static role education in the Hashicorp Vault

$ vault write postgresql/static-roles/education 
    rotation_statements="ALTER USER "{{name}}" WITH PASSWORD '{{password}}';" 
Success! Data written to: postgresql/static-roles/education

The above command creates a static role called education with username vault-edu, whose password changes every 86400 seconds (24 hours).

Run the following command to read the definition of the role education

$ vault read postgresql/static-roles/education
Key                    Value
---                    -----
db_name                postgresql
last_vault_rotation    2019-06-24T10:18:39.766203-07:00
rotation_period        24h
rotation_statements    [ALTER USER "{{name}}" WITH PASSWORD '{{password}}';]
username               vault-edu

Vault Access Policy

To get the credentials for the “vault-edu” static role, the client application must be able to read from the postgresql / static-creds / education role endpoint. Therefore, the application token must have a policy granting read permission.

Let’s create a file apps.hcl with the following content

$ nano apps.hcl
# Get credentials from the database secrets engine
path "postgresql/static-creds/education" {
  capabilities = [ "read" ]

Create an apps policy in Vault

$ vault policy write apps apps.hcl
Success! Uploaded policy: apps

Let’s create a token so that we can authenticate to read static passwords

$ vault token create -policy="apps"
Key                  Value
---                  -----
token                s.osFnGR3JAUMI00pmnebMG40g
token_accessor       8UWwnO12IMrN6gIIqsLZXuK5
token_duration       10h
token_renewable      true
token_policies       ["apps" "default"]
identity_policies    []
policies             ["apps" "default"]

Run the following command to request credentials for the vault-edu role. Be sure to use the token that you received in the previous step.

$ VAULT_TOKEN=s.osFnGR3JAUMI00pmnebMG40g vault read postgresql/static-creds/education
Key                    Value
---                    -----
last_vault_rotation    2021-03-08T19:39:04.336858834+03:00
password               Wed-OfKj2pdWQnnYcBAe
rotation_period        24h
ttl                    23h52m38s
username               vault-edu

Checking through psql

$ psql -h 
    -d postgres 
    -U vault-edu 
Password for user vault-edu: Wed-OfKj2pdWQnnYcBAe
postgres=> du
                                   List of roles
 Role name |                         Attributes                         | Member

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vault-edu |                                                            | {}

The password for a static role is automatically changed after a specified rotation period. However, a situation may arise that requires an immediate password change.

Run the following command to change the password for the “education” static role

$ vault write -f postgresql/rotate-role/education
Success! Data written to: postgresql/rotate-role/education

Now let’s read the credentials to make sure the password has been changed

$ vault read postgresql/static-creds/education
Key                    Value
---                    -----
last_vault_rotation    2021-03-08T19:51:35.606636934+03:00
password               QYpe-8w1-Dc7kG0szQxZ
rotation_period        24h
ttl                    23h59m11s
username               vault-edu

The returned password should be different from the previous output and the remaining TTL is back to ~ 24 hours