Error with Hashicorp Vault for PostgreSQL dynamic credentials

Question

I want to use Vault to generate dynamic credentials for my PostgreSQL database. I did the steps given here: https://www.vaultproject.io/docs/secrets/databases/postgresql.html. Platform versions given below:

Execution platform: CentOS Linux 7.3. Version: Vault v1.1.2 ('0082501623c0b704b87b1fbc84c2d725994bac54'). PostgreSQL version: 10.6 hosted in AWS RDS.

1. Started Vault server in dev mode with command: "vault server -dev".

2. Validated that I am able to connect to the PostgreSQL database using psql with command: "psql -h my-rds-end-point.rds.amazonaws.com -p 5432 -d alerts -U master"

3. Enabled the database secrets engine with command: "vault secrets enable database".

4. Created database connection config with command: A. With username, password in connection_url AND separately.

[root@vault-server ~]# vault write database/config/my-postgresql-database \
>     plugin_name=postgresql-database-plugin \
>     allowed_roles="my-role" \
>     connection_url="postgresql://master:pg_master_password@my-rds-endpoint.rds.amazonaws.com:5432/alerts" \
>     username="master" \
>     password="pg_master_password"
WARNING! The following warnings were returned from Vault:

  * Password found in connection_url, use a templated url to enable root
rotation and prevent read access to password information.

B. With username, password only in connection_url, NOT separately.

[root@vault-server ~]# vault write database/config/my-postgresql-database \
>     plugin_name=postgresql-database-plugin \
>     allowed_roles="my-role" \
>     connection_url="postgresql://master:pg_master_password@my-rds-end-point.rds.amazonaws.com:5432/alerts"
WARNING! The following warnings were returned from Vault:

  * Password found in connection_url, use a templated url to enable root rotation and prevent read access to password information.

C. With username, password removed from connection_url but given separately.

[root@vault-server ~]# vault write database/config/my-postgresql-database \
>     plugin_name=postgresql-database-plugin \
>     allowed_roles="my-role" \
> connection_url="postgresql://my-rds-end-point.rds.amazonaws.com:5432/alerts" \
>     username="master" \
>     password="pg_master_password"
Error writing data to database/config/my-postgresql-database: Error making API request.

URL: PUT http://127.0.0.1:8200/v1/database/config/my-postgresql-database
Code: 400. Errors:

* error creating database object: error verifying connection: pq: password authentication failed for user "root"

5. Created role with command:

vault write database/roles/my-role \
    db_name=my-postgresql-database \
    creation_statements="CREATE ROLE \"vault_role\" WITH LOGIN PASSWORD 'vault_role_password' VALID UNTIL '2019-06-30'; \
        GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"vault_role\";" \
    default_ttl="1h" \
    max_ttl="24h"

6. Queried credentials with command:

[root@vault-server ~]# vault read database/creds/my-role
Key                Value
---                -----
lease_id           database/creds/my-role/WNdcEQ0YYZODGWzYxikRNztl
lease_duration     1h
lease_renewable    true
password           A1a-T19Eh8eKKGOZCLQt
username           v-root-my-role-qWqOv4j34Sa3rQ3g35nJ-1559024979

Observations:

1) When I tried to connect to the database with command below, it failed. I though Vault created a user named 'v-root-my-role-qWqOv4j34Sa3rQ3g35nJ-1559024979'. However when I did "\du" when login as master (the super-user) of the PostgreSQL, I do not find the user in list of users.

[root@vault-server ~]# psql -h my-rds-end-point.rds.amazonaws.com -p 5432 -d alerts -U v-root-my-role-qWqOv4j34Sa3rQ3g35nJ-1559024979
Password for user v-root-my-role-qWqOv4j34Sa3rQ3g35nJ-1559024979:
psql: FATAL:  password authentication failed for user "v-root-my-role-qWqOv4j34Sa3rQ3g35nJ-1559024979"
FATAL:  password authentication failed for user "v-root-my-role-qWqOv4j34Sa3rQ3g35nJ-1559024979"

Questions: What am I missing here? How to get a set of credential (username/password) that work? Does Vault actually create a user/role that I can see using "\du" psql command?

2) When I tried to fetch another set of credentials with command below, it failed.

[root@vault-server ~]# vault read database/creds/my-role
Error reading database/creds/my-role: Error making API request.

URL: GET http://127.0.0.1:8200/v1/database/creds/my-role
Code: 500. Errors:

* 1 error occurred:
        * pq: role "vault_role" already exists

Questions: What am I missing here? How to fetch multiple credentials?

3) Step4A, 4B is throwing a Warning, while 4C leads to error. What is the correct way to create database connection config mentioned in Step 4 above?

I searched the documentation, help, internet, forums etc. but could not figure out a way to make this use case work.

Can someone please guide, provide hint on how I could configure Vault to be able to generate dynamic credentials to connect to PostgreSQL as a particular role?

Kind regards, Shashi

Answer 1

The problem was resolved by following suggestions discussed in this thread: https://groups.google.com/forum/#!msg/vault-tool/SaEIFGoWmHg/KWIgXczDBAAJ.

Conclusion: fields mentioned in "{{}}" in the reference documentation (https://www.vaultproject.io/docs/secrets/databases/postgresql.html) must be left as it is i.e. not be updated with actual values.