What would be really nice for such cases is support for Kerberos and
delegated Kerberos credentials. Having pgpool support that would remove
the need to deal with passwords at all.
Since nearly all systems with some kind of load nowadays use connection poolers (pgpool-II or pgbouncer) between applications and postgres, it is a pretty big pain to re-implement all authentication methods supported by postgres in such poolers. Kerberos is cool but not the only thing that should be supported by FDWs or connection poolers. I.e. many users would want to have support for LDAP and SCRAM. And every time when there would be some changes in postgres auth methods, exactly the same work (or even worse) should be done in many (at least two) other products widely used by people.
It seems that postgres either should provide connection pooling feature in core or give external poolers a kind of generic mechanism to transparently proxy auth requests/responses, so that authentication would be fully managed by postgres and that would be the only place where changes in auth methods should be done. Yes, in this case connection pooler actually behaves like man in the middle so it should be done very carefully but it seems that there is no other way.
--
May the force be with you…