Support IAM auth when using a managed PostgreSQL from AWS or others

### Description

Existing database setup seems to only suggest username/password auth for Postgres https://airflow.apache.org/docs/apache-airflow/stable/howto/set-up-database.html#setting-up-a-postgresql-database. It would be great to support IAM auth as well.

### Use case/motivation

Modern security practices include moving away from username/password auth, e.g. to avoid stale credentials and potential credentials leaks. Some companies may not even support connecting with username/password anymore.

### Related issues

https://github.com/apache/airflow/issues/31759

### Are you willing to submit a PR?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)

Start with the PostgreSQL database setup documentation linked in the issue and read related issue #31759 for existing context. Determine the scope of IAM authentication for managed PostgreSQL providers such as AWS, including the affected Airflow database connection paths. Done should include agreed support and documentation for the supported authentication flow, with validation for the relevant providers.

Written by the indexing model from the issue text.