Vault dynamic roles

[larrywax]

Hello! First of all, thanks for this amazing project, it's really impressive.
I would like to adopt pgDog in my current organisation, but we currently can't because all our roles are dynamically generated by Vault, which is a strict requirement from our security team.

Would you be open to a contribution that adds support for dynamic roles? I'd be happy to work on this if it's something you'd like to see in the project
Thanks!

[levkk]

Hey! Thanks!

Could you tell me a bit more about how this could work / what we need to add?

[larrywax]

Here is what I’ve put together, though I'm not familiar with this codebase and probably have overlooked or oversimplified things.
Take it as a starting point to kick off the discussion.

Disclaimer: Since users are inherently dynamic when using vault's database secrets engine, I'm assuming they are being granted a static role to keep object ownership and default privileges consistent across different set of credentials with the same access level.
Example vault DDL:

CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE "my-app-role"

backend side: Vault credential provider per role

pgdog generates its own Vault credentials for each configured backend pool. This follows the same pattern as pgdog's existing RDS IAM and Azure Workload Identity backend auth providers.

The key difference from per-connection token generation (RDS IAM model) is per-role credential:

  Vault Credential Manager (runs in pgdog, one per role)

  my-app-role pool:
    username: v-approle-my-app-pQrStU
    password: s3cr3t-a
    TTL: XXh
  
  my-other-role pool:
    username: v-approle-my-other-AbCXyZ
    password: s3cr3t-b
    TTL: YYh

A background task will be responsible for refreshing credentials before expiration

client side: pg_auth_members for role resolution

Like I said before, assuming vault creates PG roles like v-approle-my-app-xYzAbC and grants them membership in a parent role (e.g., my-app-role). This membership is stored in PostgreSQL's own system catalog.

PostgreSQL role hierarchy (created by Vault):

  my-app-role (parent, permanent)
    └── v-approle-my-app-xYzAbC  (member, ephemeral)

  my-other-app-role (parent, permanent)
    └── v-approle-my-other-aBcDeF  (member, ephemeral)

  readonly-role (parent, permanent)
    └── v-approle-ro-gHiJkL   (member, ephemeral)

When a client connects with Vault-generated credentials, pgdog:

1. Authenticates the client via passthrough auth (PostgreSQL verifies the password)
2. Resolves the role by querying pg_auth_members — this tells us which parent role (= which pool) the user belongs to
3. Routes the client to the corresponding backend pool

  Client connects with
  v-approle-my-app-xYzAbC / password
           │
           ▼
  ┌─────────────────────────────────────────────────────┐
  │                      pgdog                           │
  │                                                      │
  │  1. Passthrough auth ──▶ PostgreSQL verifies pw ✓    │
  │                                                      │
  │  2. Catalog pool query:                              │
  │     SELECT r.rolname FROM pg_auth_members m          │
  │     JOIN pg_roles r ON r.oid = m.roleid              │
  │     WHERE m.member = (                               │
  │       SELECT oid FROM pg_roles WHERE rolname = $1)   │
  │     → returns: my-app-role                           │
  │                                                      │
  │  3. Route to my-app-role backend pool ───────────────┼──▶ PostgreSQL
  │                                                      │
  └─────────────────────────────────────────────────────┘

Let me know what you think!

[levkk]

Nice. It might even be simpler than what you're suggesting!

A background task will be responsible for refreshing credentials before expiration

This task can dynamically update the connection pools, by changing the username/password for the affected pool and running a config reload.

Basically the whole implementation can be this:

use crate::config::config;
use crate::databases::{reload_from_existing, set};

fn update_user_from_vault_credential(vault_cred: VaultCredential) -> Result<(), Error> {
    let mut config = (*config()).clone();
    let user = config.users.iter_mut().find(|user| user.name == vault_cred.postgres_role);
    if let Some(user) = user {
        user.server_user = vault_cred.user;
        user.password = vault_cred.password;
    }
    set(config)?;
    reload_from_existing()?;
}

Since the user/password changed, this will trigger pgdog to reconnect to Pg immediately. Do this gracefully, with a back-off, and we can rotate credentials without much impact.

[larrywax]

Nice! That looks neat.
Would you like me to draft the implementation of the backend part? Looks like the easiest to integrate to me

[levkk]

That would be great! I'm happy to review it and help test it.

[larrywax]

Here is a first draft, thanks!