With PassthroughAuth set to `enabled_plain`, changing a user's password prevents new connections from being established

[rissson]

We're running a setup where PostgreSQL passwords are auto-rotated every month, and we found that PgDog doesn't support connecting with a changed password without restarting. I'm suspecting some kind of cache to avoid going back to postgres to validate the new password.

To reproduce

The setup is thus:

# compose.yml
services:
  postgresql:
    image: docker.io/library/postgres:18
    command: "-c log_statement=all"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - 5432:5432
    restart: always
  pgdog:
    image: ghcr.io/pgdogdev/pgdog:v0.1.32
    volumes:
      - ./pgdog.toml:/pgdog/pgdog.toml:ro
    ports:
      - 6432:6432
    restart: always

# pgdog.toml
[general]
host = "[::]"
port = 6432
passthrough_auth = "enabled_plain"
prepared_statements = "extended_anonymous"
pub_sub_channel_size = 8192

[admin]
password = "admin"
[[databases]]
host = "postgresql"
name = "postgres"

After starting the above stack, run the following steps:

Create a user:

PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres postgres -c "create user my_user with password 'first_password'"

Verify it can connect to both PostgreSQL directly and via PgDog:

PGPASSWORD=first_password psql -h localhost -p 5432 -U my_user postgres -c '\q'
PGPASSWORD=first_password psql -h localhost -p 6432 -U my_user postgres -c '\q'

Then change the password of the user via PostgreSQL directly (not sure if that matters):

PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres postgres -c "alter user my_user with password 'second_password'"

Verify the user can still connect directly to PostgreSQL with the new password:

PGPASSWORD=second_password psql -h localhost -p 5432 -U my_user postgres -c '\q'

However, it cannot connect via PgDog anymore:

PGPASSWORD=second_password psql -h localhost -p 6432 -U my_user postgres
psql: error: connection to server at "localhost" (::1), port 6432 failed: FATAL:  password for user "my_user" and database "postgres" is wrong, or the database does not exist

[levkk]

Ah yeah, the classic Postgres password rotation problem. Currently, it's very difficult to rotate passwords with zero downtime, in general, not just with PgDog. PgDog specifically caches the password, like you guessed, and retrieves it from the first client that connects to that specific database.

We could cache the password error and remove the pool, but we still need a client to connect with the new password, but this presents a race condition - existing client querying the pool will re-create it with a stale password. We could disconnect all clients with the stale password and force them to reconnect, but that would cause quite a few errors at the application. That being said, if you're rotating passwords in production, I suspect you are okay with periodic downtime? I'd be curious to learn more, might help design a solution.

[rissson]

That being said, if you're rotating passwords in production, I suspect you are okay with periodic downtime? I'd be curious to learn more, might help design a solution.

Exactly. The applications on which this is running have persistent connections, so when a password change is triggered (every 30 days), the existing connections are still functioning as expected, and only new connections will use the new password. It currently takes ~1.5 minute for the password change to propagate to all applications, so we might break on new connections, but we accept that given that old connections will still be able to handle incoming requests.

[rissson]

I should've mentioned that this is done with Vault's postgresql engine and static roles.

[levkk]

Gotcha. I think this might not be that difficult to implement: whenever we see a client connect with a different password than what we currently have, which we can quickly check, invalidate the pool and re-create it with the new password. This is related to #238, so this could fix two issues in one PR 👍

I think this would also not have that much downtime because, at full utilization, the connection pool doesn't need to create new connections (e.g. min_pool_size = default_pool_size). So, thinking through this again, we could probably do this with zero downtime!

[rissson]

Awesome! I'm happy to try out anything you need me to. I also looked at bit at your integration tests, I can write one with the scenario above if you want.

[levkk]

I won't say no to a test, yes please. On the roadmap this goes. I'll try to push something for this next week, things are a tiny bit busy here, but this is important so we'll get it done!