Thread: md5 password deprecation might cause problems with PgBouncer setups

First of all, I'm definitely in favor of sunsetting md5 password auth myself.

However, I would like to share a possible issue that users might run
into while we're doing this: Apparently the overhead of scram-256 is
much higher in some PgBouncer setups. I expect this to be mostly
setups where there are many short lived connections, but that's a
fairly normal scenario for PgBouncer. The bitnami docker image of
PgBouncer changed the default auth_type to scram-256 around two years
ago[1]. This still results in people reporting perf regressions when
upgrading PgBouncer[2][3].

I'm not sure how to improve this situation though. Maybe PgBouncer
will continue supporting md5 a while longer. Or we should start
recommending password auth. The single-threaded nature of PgBouncer
also makes these types of perf regressions extra problematic, because
once you hit 100% of the core that PgBouncer is running on, the only
way out is complicating your setup with multiple PgBouncer instances.

[1]: https://github.com/bitnami/containers/commit/190481f04144fe8ff1247da6fc7ed605951352b4
[2]: https://github.com/pgbouncer/pgbouncer/issues/912
[3]: https://github.com/pgbouncer/pgbouncer/issues/1332

Hi,

On 2025-06-06 23:48:18 +0200, Jelte Fennema-Nio wrote:
> First of all, I'm definitely in favor of sunsetting md5 password auth myself.
>
> However, I would like to share a possible issue that users might run
> into while we're doing this: Apparently the overhead of scram-256 is
> much higher in some PgBouncer setups. I expect this to be mostly
> setups where there are many short lived connections, but that's a
> fairly normal scenario for PgBouncer. The bitnami docker image of
> PgBouncer changed the default auth_type to scram-256 around two years
> ago[1]. This still results in people reporting perf regressions when
> upgrading PgBouncer[2][3].

I assume this is due to the fairly high iteration count we use by default?  I
know the iteration count is from the RFC, but the explanation in the RFC [1] seems
to be aimed at interactive use cases, where a few tens to hundreds of
milliseconds or such aren't a significant issue. That's obviously somewhat
different for a server application that connects a lot.


> I'm not sure how to improve this situation though. Maybe PgBouncer
> will continue supporting md5 a while longer. Or we should start
> recommending password auth. The single-threaded nature of PgBouncer
> also makes these types of perf regressions extra problematic, because
> once you hit 100% of the core that PgBouncer is running on, the only
> way out is complicating your setup with multiple PgBouncer instances.

Which leads me to wonder if what pgbouncer should do is to recommend a
different number of scram iterations? That seems better than recommending md5
or password auth.

Greetings,

Andres Freund

[1] https://datatracker.ietf.org/doc/html/rfc7677#section-4

On Sat, 7 Jun 2025 at 00:12, Andres Freund <andres@anarazel.de> wrote:
> I assume this is due to the fairly high iteration count we use by default?

Maybe... But looking closer at the PgBouncer code another option that
seems pretty likely is that PgBouncer is regenerating a secret over
and over again, if users store the password in plaintext in the
pgbouncer auth file (which I believe has to be the case for all of the
users that switched from md5 to scram-sha256 without their knowledge).

I've done no profiling, but by simply looking at this function[1] it
doesn't seem particularly cheap to do on every authentication attempt.
At the very least it's doing those 4096 iterations an additional time.

It seems like it should be fairly easy to cache that. So I've created
an issue for that[2].

[1]: https://github.com/pgbouncer/pgbouncer/blob/ed7ecfb9213a2bec138f560aa7d8e4dcb3bc0f62/src/scram.c#L742
[2]: https://github.com/pgbouncer/pgbouncer/issues/1335