Dynamically Generating DB Credentials via Vault

Every application has some use for various DBs right? Where are the db credentials kept though. In app config? In k8s secrets? Why not let Vault handle it?

Let’s do a quick and dirty start. To setup our DB Engine in Vault we can follow the below setups. We need

• Docker container for Postgres
• Running Vault somewhere (local is enough)

First we need to get our db secrets engine running in a path of our choosing.

let’s get the engine running

Let’s choose a root path for our engine.

basic configs and more

Now we need to configure a connection and create roles to use said connection. Click on create connection.

no db connection yet

We can make use of the following value as connection URL;

postgresql://{{username}}:{{password}}@localhost:8888/postgres?sslmode=disable

our first connection

Now you might be asking where did the username and pass below came from. We we need a db and what better way to get up and running then with a container. We made use of the official image which contained postgres14 and switched the port exposed to 8888 on local with -p 8888:5432 command. You can use whatever port you have available. Additionally I overrode the env var POSTGRES_PASSWORD with my non-default password.

docker run --name postgredb -p 8888:5432 -e POSTGRES_PASSWORD=mysecretpostgrespassword -d postgres

In the next step, Vault wants to take over the credential management and rotate current credentials we gave. Normally, you would be giving vault its own user and click Rotate and Enable but in our scenario, it is not really necessary. Though if something were to happen to our DB we could just nuke the container running it and restart which would refresh everything.

this is preferred if you have everything running

Our connection should look like this. We now need to add roles.

nice

When we select Add Role, we need to choose some basic settings.

• Role name to define what the role is, like read-only,
• Type of role which should be dynamic. Also TTL and max TTL which will define how long the role can live. Max TTL is used when vault lease is extended, it cannot extend the value defined there. (I haven’t put a renew statement out of nothing but laziness)
• Statements which include creation, which create a role valid until a set time and grants usage and select on db elements.

CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' INHERIT;
GRANT USAGE ON SCHEMA public TO "{{name}}";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";

• And revocation, which drop all grants owned by a user and the user itself in the end.

drop owned by "{{name}}";
drop user "{{name}}";

final result

When all is set, we can move to the main secret engine page and the following view should be greeting us. When we select Get Credentials and select the role we created from below we should be receiving our dynamic db creds.

everything is set

Great

Trying it out you should be able to connect immediately with the provided creds.

noice

If we were to simulate lease end by moving to the access tab and clicking revoke the db connection immediately drops.

standard lease nuking

noice

You may be asking, “Great, we created creds but are we going to hand them out manually?”. Not if you don’t want to. We can ask for dynamic creds programmatically as well using Vault API via something like: http://localhost:8200/v1/postgres14/creds/read-only or by cli as below

vault read postgres14/creds/read-only

our db token

Lease revocation can be done via cli (or using the endpoint as well) as below;

vault lease revoke postgres14/creds/read-only/q2hiKZVSQgp24mBS7nImsCMw

hands-on lease nuking

Thanks for reading and see you next time.

Bonus:

(Not Working) Setup Via CLI

Note: Ignore the CLI version because it doesn’t work. If you prefer to see why, keep reading. Just putting it here, if I solve it I’ll update this.

We can do the same stuff via Vault cli as well, at least that’s what Vault docs say. We first need to enable the path and type of our secrets engine as before.

vault secrets enable --path=postgres14cli database

Yay

Now we create the connection that’s going to be forming the basis of our credential generation. Notice that we use the path we just created first and finally the connection name.

vault write postgres14cli/config/postgres14cli-localdb plugin_name=postgresql-database-plugin connection_url="postgresql://{{username}}:{{password}}@localhost:8888/postgres?sslmode=disable" username="postgres" password="mysecretpostgrespassword"

silence is sometimes the best answer

Finally we should be getting roles up and running with the below command. Also notice that we can type any sort of role here and Vault just accepts it. Even the wrong ones (try one with wrong db) which we cannot delete afterwards

vault write postgres14cli/roles/readonly \
    db_name=postgres14cli-localdb \
    creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' INHERIT; \
 GRANT USAGE ON SCHEMA public TO \"{{name}}\"; \
 GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" \
 revocation_statements="drop owned by \"{{name}}\"; \
 drop user \"{{name}}\";" \
 default_ttl="1h" \
    max_ttl="24h"

not liking the look of this

And finally, we get to not being able to generate credentials due to some unknown bug with no logs on the Vault side. Creating the exact same role via UI nets us a working role.

of course it doesn’t work