Vault: PostgreSQL dynamic credentials not working
Shashi Gokhale
Hello All,
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.
# 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.
# 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.
# 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:
# 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-1559024979Observations:
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.
# 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.
# 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 existsQuestions: 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
Paul Knox
Shashibhushan Gokhale
--
This mailing list is governed under the HashiCorp Community Guidelines - https://www.hashicorp.com/community-guidelines.html. Behavior in violation of those guidelines may result in your removal from this mailing list.
GitHub Issues: https://github.com/hashicorp/vault/issues
IRC: #vault-tool on Freenode
---
You received this message because you are subscribed to a topic in the Google Groups "Vault" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/vault-tool/SaEIFGoWmHg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to vault-tool+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/vault-tool/438b7343-594b-41b8-a99c-e50674f1400c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Becca Petrin
- vault read database/config/my-postgresql-database
- vault read database/roles/my-role
- vault read database/creds/my-role
To unsubscribe from this group and all its topics, send an email to vault...@googlegroups.com.