Is it feasible for PostgreSQL Scaler to support token authentication?

[Ferdinanddb]

Hi,

Let me explain what I am facing:

• I recently succeeded in deploying Airflow on an AKS cluster with a PostgreSQL flexible server backend (hosted in Azure).
• I had to "hack" a bit the Airflow container image so that Airflow could connect to the PostgreSQL database backend using access token instead of using password. For that, I did the following:
  • Create an Azure Managed Identity,
  • Grant access to my newly created Azure Managed Identity to interact with the PostgreSQL flexible server,
  • Create a federated identity to link my Azure Managed Identity to a Kubernetes service account that will be used by Airflow.
  • Create a custom plugin that I install in the Dockerfile of my Airflow image in order to generate a token each time a process is trying to connect to the PostgresSQL database, thanks to the SQLAlchemy decorator:

  @event.listens_for(engine, "do_connect")
  def provide_token(dialect, conn_rec, cargs, cparams):
     ...

  • This works well so far.

---

My problem is the following:

Based on the documentation here that showcase an Airflow example and the code here , it seems that what I did presents some limits because, as I explained, I don't have any password for my Azure Managed Identity (I generate a token each time a process needs to interact with the PostgreSQL database).

As of now, the supported options seem to be that I provide a password as a hard-coded value, or I provide the name of an environment variable which value is either the password or the connection (that include the password).

I would like to know if it would be a good idea to support a new way of dealing with the password that looks like the following in pseudocode:

func parsePostgreSQLMetadata(config *scalersconfig.ScalerConfig) (*postgreSQLMetadata, error) {
  ...
  	switch {
	case config.AuthParams["connection"] != "":
		meta.connection = config.AuthParams["connection"]
	case config.TriggerMetadata["connectionFromEnv"] != "":
		meta.connection = config.ResolvedEnv[config.TriggerMetadata["connectionFromEnv"]]
	default:
		host, err := GetFromAuthOrMeta(config, "host")
		if err != nil {
			return nil, err
		}

		...

		var password string
		if config.AuthParams["password"] != "" {
			password = config.AuthParams["password"]
		} else if config.TriggerMetadata["passwordFromEnv"] != "" {
			password = config.ResolvedEnv[config.TriggerMetadata["passwordFromEnv"]]
		} else if config.TriggerMetadata["generateToken"] == true { // <-- ADDED PART
                        cred, err := azidentity.NewDefaultAzureCredential(nil)
                        if err != nil {
                             ...
                        }
                        password = cred.GetToken(..., opts: policy.TokenRequestOptions{Scopes: []string{"https://ossrdbms-aad.database.windows.net/.default"}}).Token
                }
                // Build connection str
		var params []string
		params = append(params, "host="+escapePostgreConnectionParameter(host))
		params = append(params, "port="+escapePostgreConnectionParameter(port))
		params = append(params, "user="+escapePostgreConnectionParameter(userName))
		params = append(params, "dbname="+escapePostgreConnectionParameter(dbName))
		params = append(params, "sslmode="+escapePostgreConnectionParameter(sslmode))
		params = append(params, "password="+escapePostgreConnectionParameter(password))
		meta.connection = strings.Join(params, " ")
	}
	meta.triggerIndex = config.TriggerIndex
	return &meta, nil
}

In my snippet, cred, err := azidentity.NewDefaultAzureCredential(nil) authenticates seamlessly as the clientID specified when deploying the Helm chart with helm upgrade -i .... --set podIdentity.azureWorkload.enabled=true --set podIdentity.azureWorkload.clientId={azure-ad-client-id} --set podIdentity.azureWorkload.tenantId={azure-ad-tenant-id}, as described here.

If such an option is added, then it would be possible to use it in the official Airflow Helm chart here.

Would it be a good idea to include that kind of mechanism in order to connect to PostgreSQL in general?

My example is really specific to Azure, but I think that the behavior can be generalized for many cloud providers, isn't it?

I have the feeling that it could be a good feature (if it does not already exist) because that way we don't even have to manage password anymore.

[zroubalik]

@tomkerkhove @JorTurFer FYI

[Ferdinanddb]

For info I just wrote a message in the KEDA's Slack channel to better explain what I described in my original post, here is the message 😄 :

---

I recently wrote a discussion post on KEDA’s GitHub repo about the possibility to use access token credentials for the KEDA’s Postgres scaler authentication. My initial post may not have been entirely clear, and the code proposal I shared might not have been the best approach to address the issue.

Let me briefly recap my requirement:
For my specific use case, I need KEDA to connect to an Azure Postgres Flexible Server using a managed identity’s access token. But perhaps this functionality could also be beneficial for other cloud providers that support this type of authentication?

Since I wrote the discussion post, I forked and tried to implement the feature myself, I drew inspiration from the Azure Pipelines scaler’s implementation, and now I believe I have a viable approach to propose in a PR. It’s worth noting that my implementation deviates from the code snippet I initially described in the discussion.

But I have the following interrogation though :

• Would it be more appropriate to develop a new KEDA scaler specifically for the Azure Postgres Flexible Server resource?
  • This approach would keep it isolated from the existing Postgres scaler, though it may lead to significant code duplication.
• Alternatively, should I integrate this feature into the existing Postgres scaler?
  • This would prevent code duplication, but the downside is that incorporating similar logic for other cloud providers might complicate and clutter the Postgres scaler codebase.

I am eager to contribute by implementing this feature along with all the necessary tests, but your guidance on how best to proceed would be greatly appreciated!