Rotation of credentials for the Airflow database

[ebundy]

I read the documentation (https://airflow.apache.org/docs/apache-airflow/stable/security/secrets/secrets-backend/index.html#secrets-backend-configuration, https://airflow.apache.org/docs/apache-airflow/stable/howto/set-config.html) and some other posts (which #19217) and I am always stuck: at the initialization the db connection is created via the specified command or the specified (custom) secret manager (I tried both ways) but the db connection is never re-created.
Here with the cmd way :

    spec:
      containers:
        - name: webserver
          image: myregistry.org:5000/my-airflow:2.9.1-python3.11
          imagePullPolicy: Always
          command: [ "/bin/bash", "-c" ]
          args:
            [ "env; airflow webserver" ]
          env:
            - name: AIRFLOW__DATABASE__SQL_ALCHEMY_CONN_CMD
              value: /opt/airflow/create_connection.sh

When the generated credentials expire, the db connection become unusable, which is expected :

[2024-10-19T20:26:48.959+0000] {base.py:691} DEBUG - Error on connect(): connection to server at "foo.com" (192.168.49.1), port 5433 failed: FATAL: password authentication failed for user "v-kubernet-my-role-1fgP0FKEnemhfOWfGU9b-1729369356"

The traceback shows that the db connction params (in fact the kwargs after some debbging) are always the same.

[2024-10-19T20:26:55.851+0000] {app.py:1744} ERROR - Exception on /home [GET]
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3383, in _wrap_pool_connect
    return fn()
           ^^^^
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 327, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 894, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 497, in checkout
    with util.safe_reraise():
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    dbapi_connection = rec.get_connection()
                       ^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 659, in get_connection
    self.__connect()
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 690, in __connect
    with util.safe_reraise():
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 686, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 574, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.11/site-packages/psycopg2/__init__.py", line 123, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.OperationalError: connection to server at "foo.com" (192.168.49.1), port 5433 failed: FATAL:  password authentication failed for user "v-kubernet-my-role-15e9ob6KUTaIPRQ2NTd8-1729369358"

But the problem is that these exceptions loop forever. The cmd /opt/airflow/create_connection.sh is not called any longer, so the old and expired credentials are always used.
I tried to play with the recycle pool param by using a lower value such as 2mn but not better. I have even tried to invalidate the connections but the problem persists because the generated credentials the first time are always used then.
Do you have an idea on how fix that and force a new call to the cmd associated to the env AIRFLOW__DATABASE__SQL_ALCHEMY_CONN_CMD when the credentials expire ?
It seems a so basic requirement. So I probably missed an important point.

If I don't find something, i will probably modify some Airflow source files of my docker Image to force the thing in a dirty way but it is not the right way, so I ask before.

[ebundy]

I solved the problem by creating an airflow plugin that force the call to the script at each new SQLAlchemy connection.