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
