Integrating Airflow with Hashicorp Vault

[cbuffett]

I'm working on integrating Airflow (version 1.10.14, python 3.7.9) with Hashicorp's Vault service in order to retrieve connection credentials that will be used to create connection objects in Airflow. Everything appears in order when I deploy out a fresh server and DB. I can see the connections in the Airflow UI and view the details of each. Where things go wrong is when I redeploy the webserver to an existing DB. If I attempt to view the connections where I've updated the username and password, I get the following:

-------------------------------------------------------------------------------
Node: 4bc7e1bb63d5
-------------------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps
    return f(self, *args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/views.py", line 602, in edit
    widgets = self._edit(pk)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/baseviews.py", line 1226, in _edit
    form = self.edit_form.refresh(obj=item)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/forms.py", line 329, in refresh
    form = self(obj=obj)
  File "/usr/local/lib/python3.7/site-packages/wtforms/form.py", line 208, in __call__
    return type.__call__(cls, *args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/flask_wtf/form.py", line 87, in __init__
    super(FlaskForm, self).__init__(formdata=formdata, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/wtforms/form.py", line 274, in __init__
    self.process(formdata, obj, data=data, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/wtforms/form.py", line 126, in process
    if obj is not None and hasattr(obj, name):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 365, in __get__
    retval = self.descriptor.__get__(instance, owner)
  File "/usr/local/lib/python3.7/site-packages/airflow/models/connection.py", line 192, in get_password
    return fernet.decrypt(bytes(self._password, 'utf-8')).decode()
  File "/usr/local/lib/python3.7/site-packages/cryptography/fernet.py", line 182, in decrypt
    raise InvalidToken
cryptography.fernet.InvalidToken

I suspect what's happening is that the credentials are being updated, but the password is not being encrypted using the fernet_key. I update the connection information as part of my db_init python script using the SqlAlchemy Session object like so

self.session.query(Connection)
    .filter(Connection.conn_id == attributes['conn_id'])
    .update({'login': user, 'password': password})

The db_init script is run as part of my docker container's entrypoint script

case "$1" in
  webserver)
    airflow upgradedb
    airflow pool -s default_pool 128 'default pool'
    python scripts/db_init.py

Google has yielded some information, but I'm unclear as to if this is the correct approach for updating the connection credentials or if I need to fetch the fernet_key and encrypt the password before updating the record. I've seen mention of using a DAG to update the connection, though that does have some implications due to the lifetime of the tokens used to fetch the username and password.

[cbuffett]

I believe I've been able to resolve this by changing the update implementation to use BaseHook.get_connection().set_password() instead of using the SqlAlchemy sessions directly. However, the limitation is that it can only set the password. If at some point in the future the username changes, it will require manually changing it in Airflow along with updating the credentials in Vault. One could argue that a username is not a secret and doesn't warrant being stored in Vault, but I find it good practice to keep the credentials co-located.

[cbuffett]

I'm not mirroring the connections themselves. I store the connection credentials in Vault, but the connections themselves are stored in the Airflow meta DB. I would certainly prefer to store the connections themselves in Vault, but I will need to do some testing to see if this approach is feasible given then authentication requirements imposed by my organization.

[potiuk]

The secret backends/Vault integration was specifically implement to be able to store both credentials and connection infornation i secret storage. They are inseparable from the point of view of Airlfow.

Even if you would like to keep them separately you are still completely free to write your own Custom Secret Backend that will use existing Hook and will read connection details from Airlfow Metadata DB and add the credentials retrieved from Vault on the flight - without having to use fernet key, nor storing the credentials in the metadata DB. There is simply no need for that. You can actually even NOT use the Hook and write your own credential retrieval from Vault when needed with your own way. None of that requires to store the credentials in the metastore DB (which BTW is not really a good idea from any security requirements I could imagine - why should I keep credentials retrueved from Secret store in another database?

What authentication requirements you have in mind that could prevent you from doing it ?

[cbuffett]

Part of storing the credentials in the meta DB is so that they don't need to be fetched from Vault each time they are needed. There's a cost associated with having to authenticate each time as our operational policy limits TTL for tokens. Rather, the token is fetched once during the CI/CD pipeline when deploying Airflow and used to obtain the credentials, which are subsequently stored in the meta DB (as part of the connection info) for easier access during DAG execution.

[potiuk]

If you want to embed credentials, then you can use FileSecretBackend and embed it in secret of Kubernetes and read it fro there ( if you are using Kubernetes) or embed it in the Image or mount it as volume. There is absolutely no need to store those connections and passwords in the db

[potiuk]

The only reason you want to use db is if you want to modify those passwords via Airflow UI and make those credentials stored there, the single source of truth.

But if you don't - simply use secrets backend. They are designed for these vere purpose and you can literally implement ANY scenario where you need read-only credentials using those. NOT metadata db. It's just wrong way of thinking about the metadata db.