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 
    plugin_name=postgresql-database-plugin 
    allowed_roles="*" 
    connection_url=postgresql://{{username}}:{{password}}@localhost:5432/postgres?sslmode=disable 
    username="postgres" 
    password="mysuperpassword"

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';
# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "vault-edu";
# q
$ exit

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

$ vault write postgresql/static-roles/education 
    db_name=connection 
    rotation_statements="ALTER USER "{{name}}" WITH PASSWORD '{{password}}';" 
    username="vault-edu" 
    rotation_period=86400
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 127.0.0.1 
    -d postgres 
    -U vault-edu 
    -W
Password for user vault-edu: Wed-OfKj2pdWQnnYcBAe

postgres=> du
                                   List of roles
 Role name |                         Attributes                         | Member
 of
-----------+------------------------------------------------------------+-------

 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