Support for authenticating to airflow metadata database like postgres sql with cloud specific authentication mechanism

[harshit283]

Description

We are deploying latest version of airflow on Azure Kubernetes cluster with components of airflow like web/worker/scheduler etc. running on different AKS pods, this is achieved using helm charts
We are using Azure Postgres SQL service as Airflow Metadata Database

Now for the airflow pods to authenticate to Postgres SQL a connection string is used which comprises of host, username, password, port etc. Code reference where the connection string is read from the config to initialize a DB session object for handling DB operations

This static connection string is not the safest of options and we want to use Azure AAD based authentication which eliminates the need to store static passwords.
If we leverage this managed identity authentication mechanism instead of static password we can generate on demand short lived tokens and use those as passwords while connecting to Postgres which essentially means connection string will be a dynamic value

One possible way to solve this issue is that since connection string is formed using environment variables we can update the environment variables and restart airflow pods but restarting pods on every token refresh is not efficient
Hence we are looking into an optimal way to handle this in airflow code by perhaps a provider specific implementation of this Session class but not sure that is the right way, we are completely new to Airflow hence some guidance will really help :)
Hoping the above details should be sufficient

Note - This is different use case than airflow hooks as hooks will be beneficial if the running DAGs are trying to connect to some external data store, here we want the airflow components themselves to connect using custom cloud specific authentication

Use case/motivation

Provides an ability to the clients of airflow to use Custom Authentication schemes while connecting to appropriate backend deta stores

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

[potiuk]

You can use _CMD variant of retrieving value of https://airflow.apache.org/docs/apache-airflow/stable/cli-and-env-variables-ref.html#envvar-AIRFLOW__-SECTION-__-KEY-_CMD - this can point to a script that will return your secret connection retrieved from an external system. You can point it to any executable script/program that you run and which will return the connection string as it's output (make sure that you do not print EOL and flush your output). You can then use workflow identity or whatever other form of authentication you find appropriate for the binary to make sure that only your binaries running on your machines can retrieve the connection string.

[potiuk]

Converted to discussion if needed.