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.
For the postgres_fdw, LDAP and SCRAM just work. In the case of SCRAM (and MD5), it would be nice if you could store something other than the plain-text password, but that is a different matter. If other FDW connect to something which can do LDAP or SCRAM, I don't see why those FDW would have any difficulty, either.
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.
That is not all that often.
It seems that postgres either should provide connection pooling feature in core
That would be nice, but since pgpool and pgbouncer co-exist with each other, I see no reason to think they wouldn't continue to exist even if there were an in-core pooler.
