Support IAM auth for a database when connecting via CLI

[andrii-korotkov-verkada]

Description

Right now it looks like a username and password is needed to connect, e.g. https://airflow.apache.org/docs/apache-airflow/stable/howto/connection.html#connection-cli. It would be great to support IAM auth with auto-refreshing credentials.

Use case/motivation

Modern security practices include moving away from username/password to IAM auth, e.g. to avoid stale unrotated credentials and risks of leaking them. In some companies, access to the database via username/password might not even be supported anymore. To have CLI usable, IAM auth needs to be supported. Technically, people can get a temporary password for 15 min, but refreshing it manually would be pretty frustrating.

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[nathadfield]

@andrii-korotkov-verkada Airflow supports connections for many databases and services with a wide variety of authentication methods. Are you referring to something specific?

[potiuk]

Indeed. There is nothing to prevent using those mechanisms you mention @andrii-korotkov-verkada - for example Google And AWS provider already support what you advocate for. Please double check all the authentication mechanism. What exactly you think is not possible or problematic in the current setup ?

[potiuk]

Converting it in a discussion as custom schemes are entirely possible now - more discussion is needed to understand what it is about.

[andrii-korotkov-verkada]

I want to integrate IAM auth for Postgres DB. In my specific case it's about AWS one https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html, but could be more generic.
I've managed to find docs for that https://airflow.apache.org/docs/apache-airflow-providers-amazon/stable/connections/aws.html#session-factory, custom factory session. However, earlier section of the doc https://airflow.apache.org/docs/apache-airflow-providers-amazon/stable/connections/aws.html#configuring-the-connection makes it look like you are responsible for refreshing the credentials, which is not as convenient. Postgres doc https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/connections/postgres.html also suggests using username and password. Overall, this looks like a case where a documentation can be improved. I'll submit a PR later.