Configuring HashiCorp Vault to Create Dynamic PostgreSQL Credentials

HashiСorp 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 dynamic credentials with a hard-scoped TTL (Time to Live).

Checking the Vault status

$  vault status
Key             Value
---             -----
Seal Type       shamir
Initialized     true
Sealed          true     <-- хранилище запечатано
Total Shares    5
Threshold       3
Version         1.6.3
Storage Type    postgresql
Cluster Name    vault
Cluster ID      6991c997-0e90-e38b-2ecb-d8ab4a677fa4
HA Enabled      false

We print the storage

$ vault operator unseal
Unseal Key (will be hidden):
$ vault operator unseal
Unseal Key (will be hidden):
$ vault operator unseal
Unseal Key (will be hidden):

We enter 3 keys, since when installing Vault, we indicated this

Log in to Vault from the console

$ vault login

Enabling the secret store for the database

$ vault secrets enable -path=psql database

Specify the plugin and connection information

$ vault write psql/config/my-postgresql-database 
    plugin_name=postgresql-database-plugin 
    allowed_roles="developer-role" 
    connection_url="postgresql://{{username}}:{{password}}@localhost:5432/postgres?sslmode=disable" 
    username="postgres" 
    password="mysuperpasswd"

You can see what has been recorded

$ vault read psql/config/my-postgresql-database
Key                                   Value
---                                   -----
allowed_roles                         [developer-role]
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    []

Set up a role that maps a name in Vault to an SQL query to create database accounts

$ vault write psql/roles/developer-role 
    db_name=my-postgresql-database 
    creation_statements="CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; 
        GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";" 
    default_ttl="1h" 
    max_ttl="24h"

Trying to create a new account

$ vault read psql/creds/developer-role
Key                Value
---                -----
lease_id           psql/creds/developer-role/bUpCqnFUq8EXF2TYhX529EWB
lease_duration     1h
lease_renewable    true
password           ZOhXBsoZcb6uz5ACFge-
username           v-root-develope-N2QYxWSbvc5ZFduq6JyY-1615063136

Checking the connection to PostgreSQL via the console client psql

$ psql -h 127.0.0.1 
    -d postgres 
    -U v-root-develope-N2QYxWSbvc5ZFduq6JyY-1615063136 
    -W
Password: ZOhXBsoZcb6uz5ACFge-
psql (13.2)
Type "help" for help.

postgres=>

The list of created accounts can be viewed:

$ vault list sys/leases/lookup/psql/creds/developer-role
Keys
----
B8hooXbcQMuY3GqNoDaWDubV
P0Q3txgk1Fp0NyEBl6m8f521
bUpCqnFUq8EXF2TYhX529EWB

Revoke an account by specifying its ID:

$ vault lease revoke psql/creds/developer-role/bUpCqnFUq8EXF2TYhX529EWB
All revocation operations queued successfully!

After revoking the account, we check the connection to PostgreSQL through the console client psql

$ psql -h 127.0.0.1 
    -d postgres 
    -U v-root-develope-N2QYxWSbvc5ZFduq6JyY-1615063136 
    -W
Password: ZOhXBsoZcb6uz5ACFge-
FATAL: password authentication failed for user "v-root-develope-N2QYxWSbvc5ZFduq6JyY-1615063136"

Revoke all accounts

$ vault lease revoke -prefix psql/creds/developer-role
All revocation operations queued successfully!
Sidebar