Vaulting Database Credentials

Over the past year, I've been experimenting with Hashicorp Vault, using the open-source/community version for some internal experiments, including some with high availability.

In a separate article, I'll go over a test configuration of Vault, but all of the notes here are agnostic to the use of HCP (Hashicorp's cloud services) or a private instance.

Setting up database connections

Contrary to what I originally thought, you only need to set up a single database configuration for each database server/cluster. (You can create more if you need to silo your controls further, but doing so can make the role map unnecessarily confusing). Specifically, the connection_url from the database configuration does not limit database access to the roles created through or administered by the vault database connection.

In most cases, set up a single database config to your postgres database and use it as the connection for all of your static and dynamic roles.

I'll walk through setting up a local vault and database connection for a test scenario in Local Testing.

Vault connection user

You'll definitely want to create a separate admin user for use by vault in managing credentials. Once you configure your connection with this user and rotate the password, you'll not have access to that password and as such it won't be available for break-glass or other administrative duties.

Recommendations from the internet are that the vault admin user just be able to provision users (CREATEROLE privilege can grant membership in other roles), and then create a postgresql role which can be used (assumed) by the vault role in the event that it needs to create database objects that have ownership (tables, sequences, etc.). Your specific use case may vary, but least privilege would be the goal with multiple roles.

Static vs dynamic roles

Vault has two types of roles for accessing databases, static and dynamic. The static roles have user-specified usernames, whereas the dynamic roles create a new (usually short-lived) user while being used. The biggest difference is that the dynamic roles are deleted once they're no longer in use (see caveat below on ownership).

A static role can adopt a role already in a database and is automatically rotated after a specified period of time.

A dynamic role is a unique user id that lasts for a shorter period of time, with extensions possible through extending the lease, and are deleted after their expiration time. Generally speaking, this is the target state, as they provide isolationa dn limit usefulness of leaked credentials.

Ownership and roles

One problem with dynamic roles comes into play when creating objects. Quoting from the postgresql documentation on DROP ROLE:

Because roles can own database objects and can hold privileges to access other objects, dropping a role is often not just a matter of a quick DROP ROLE. Any objects owned by the role must first be dropped or reassigned to other owners; and any permissions granted to the role must be revoked.

So, although the dynamic roles give us the safety we desire, they do create some complications when objects may be owned by those roles. To solve for this, we add some complexity, by creating a new role to hold the priviliges, then give the dynamic roles role membership in that role, and finally we assume the role for each database session, ensuring that we are acting on behalf of the role we assume, including its priviliges and ownership.

Creating the roles may take a little time, but it has a couple of other nice side effects, including placing the determination of specific access to the database instead of hard-coding that into your vault configuration.