Re: md5 password deprecation might cause problems with PgBouncer setups

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