Connection pooling with Vault dynamic credentials

[dtseiler]

I have a feeling the answer will be "no", and I believe I discussed with with JP a couple of years ago.

I'm wondering if it's possible (or even makes sense) to use connection pools with dynamic credentials, for example those created by Vault. I'm guessing since each login is unique then there wouldn't be any possibility to pool anything, as the pools are based on user/role name and database name.

It's been a while since I looked into it and just thought I'd see if any magic has happened since then.

[jesperpedersen]

There are many layers to this.

First, we need a simple implementation for "in-pgagroal" passwords (https://wiki.postgresql.org/wiki/GSoC_2023#pgagroal:_Simple_vault), then it is a SPI for external solutions like https://www.vaultproject.io/ and the like, and then the actual plugins.

There is the discussion of front-end and backend passwords as well -- backend is a bit difficult at the moment for pooled connections. But, I think front-end is the important part.

It is going to happen, but nothing yet. Hopefully we will get further this year !

[dtseiler]

Thanks for the reply!

I'm very curious how you will pool dynamic connections together. Will it just be pooling any vault connections with the same database together? Maybe further segregate by application_name?

[jesperpedersen]

We have to figure that out - right now everything is off the username/password/database.

It would be great if you could post your use-cases so we can work towards those...

[dtseiler]

We have the fairly typical use case with Hashicorp Vault and postgresql. The simple explanation:

1. there is a vault role in the postgres DB that can create roles.
2. apps send a request to vault for DB credentials
3. vault connects to that DB and creates a new dynamic role with the appropriate parent role membership
4. vault returns those credentials to the app
5. app connects to the DB
6. after a TTL elapses (eg 4 hours), vault drops the role

Unfortunately, on the databases where we use a connection pooler (pgbouncer), we've had to just use a static app role. pgbouncer was creating a new pool for every dynamic login, so we'd have thousands of pools with 1 connection. As we look to start using connection poolers on more databases, we want to know how it might impact our plans for Vault dynamic credential usage.

[jesperpedersen]

Hmm, yeah we don't want that. We need to remove connections after that TTL happens.

Thanks for sharing !