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