Setting sql_alchemy_conn via secrets backend fails

[metropolis-ameer]

Apache Airflow version: 2.0.2, 1.10.15

Kubernetes version (if you are using kubernetes) (use kubectl version): not used

Environment: docker python:3.7-slim-buster

• Cloud provider or hardware configuration: container image in docker
• OS (e.g. from /etc/os-release): Debian GNU/Linux 10 (buster)
• Kernel (e.g. uname -a): Linux f215c7ef4950 4.19.121-linuxkit Improving the search functionality in the graph view #1 SMP Thu Jan 21 15:36:34 UTC 2021 x86_64 GNU/Linux
• Install tools: airflow official dockerfile
• Others:

What happened:

I am unable to set sql_alchemy_conn via the environment variable AIRFLOW__CORE__SQL_ALCHEMY_CONN_SECRET using the secrets backend through AWS SystemsManagerParameterStore. Different versions produce different error message.
2.0.2 produces the following error message:

2.0.2 error

Traceback (most recent call last): File "/home/airflow/.local/bin/airflow", line 5, in from airflow.__main__ import main File "/home/airflow/.local/lib/python3.7/site-packages/airflow/__init__.py", line 34, in from airflow import settings File "/home/airflow/.local/lib/python3.7/site-packages/airflow/settings.py", line 37, in from airflow.configuration import AIRFLOW_HOME, WEBSERVER_CONFIG, conf # NOQA F401 File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 1098, in conf = initialize_config() File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 860, in initialize_config conf.validate() File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 199, in validate self._validate_config_dependencies() File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 227, in _validate_config_dependencies is_sqlite = "sqlite" in self.get('core', 'sql_alchemy_conn') File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 328, in get option = self._get_environment_variables(deprecated_key, deprecated_section, key, section) File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 394, in _get_environment_variables option = self._get_env_var_option(section, key) File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 298, in _get_env_var_option return _get_config_value_from_secret_backend(os.environ[env_var_secret_path]) File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 83, in _get_config_value_from_secret_backend secrets_client = get_custom_secret_backend() File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 999, in get_custom_secret_backend secrets_backend_cls = conf.getimport(section='secrets', key='backend') NameError: name 'conf' is not defined

1.10.15 produces the following error message:

1.10.15 error

Traceback (most recent call last): File "/home/airflow/.local/bin/airflow", line 25, in from airflow.configuration import conf File "/home/airflow/.local/lib/python3.7/site-packages/airflow/__init__.py", line 31, in from airflow.utils.log.logging_mixin import LoggingMixin File "/home/airflow/.local/lib/python3.7/site-packages/airflow/utils/__init__.py", line 24, in from .decorators import apply_defaults as _apply_defaults File "/home/airflow/.local/lib/python3.7/site-packages/airflow/utils/decorators.py", line 36, in from airflow import settings File "/home/airflow/.local/lib/python3.7/site-packages/airflow/settings.py", line 38, in from airflow.configuration import conf, AIRFLOW_HOME, WEBSERVER_CONFIG # NOQA F401 File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 737, in conf.read(AIRFLOW_CONFIG) File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 425, in read self._validate() File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 217, in _validate self._validate_config_dependencies() File "/home/airflow/.local/lib/python3.7/site-packages/airflow/configuration.py", line 251, in _validate_config_dependencies self.get('core', 'executor'))) airflow.exceptions.AirflowConfigException: error: cannot use sqlite with the CeleryExecutor

What you expected to happen:

I expect the variable to be set via secrets backend as documented in aws_secrets_manager.py

How to reproduce it:

docker build . \
  --build-arg PYTHON_BASE_IMAGE="python:3.7-slim-buster" \
  --build-arg PYTHON_MAJOR_MINOR_VERSION=3.7 \
  --build-arg AIRFLOW_INSTALLATION_METHOD="apache-airflow" \
  --build-arg AIRFLOW_VERSION="2.0.2" \
  --build-arg AIRFLOW_INSTALL_VERSION="==2.0.2" \
  --build-arg AIRFLOW_CONSTRAINTS_REFERENCE="constraints-2.0.2" \
  --build-arg AIRFLOW_BRANCH="v2-0-stable" \
  --build-arg AIRFLOW_SOURCES_FROM="empty" \
  --build-arg AIRFLOW_SOURCES_TO="/empty" \
  --build-arg AIRFLOW_EXTRAS="password,apache.presto,amazon,slack,celery,docker,mysql,postgres,redis,crypto,jdbc,ssh,statsd,virtualenv" \
  --build-arg ADDITIONAL_PYTHON_DEPS="scp" \
  --tag "company/airflow_2-0-2_base"

next docker image:
have aws credential file available

in secrets:
set backend in airflow.cfg = airflow.contrib.secrets.aws_systems_manager.SystemsManagerParameterStoreBackend
set backend_kwargs in airflow.cfg = {"connections_prefix": "/airflow/connections", "variables_prefix": "/airflow/variables", "config_prefix": "/airflow/config", "profile_name": "default"}

in aws, create in parameter store: /airflow/config/sql_alchemy_conn = postgresql+psycopg2://user:pass@database-host:5432/airflowdb

create environment variable in entrypoint.sh: AIRFLOW__CORE__SQL_ALCHEMY_CONN_SECRET = sql_alchemy_conn

FROM company/airflow_2-0-2_base

COPY --chown=airflow:root aws ${AIRFLOW_USER_HOME_DIR}/.aws
COPY --chown=airflow:root scripts/entrypoint.sh /entrypoint
COPY --chown=airflow:root config/airflow.cfg ${AIRFLOW_HOME}/airflow.cfg

ENV PYTHONPATH=${AIRFLOW_HOME}/:$PYTHONPATH

EXPOSE 8080 5555 8793

USER ${AIRFLOW_UID}
WORKDIR ${AIRFLOW_HOME}
ENTRYPOINT ["/usr/bin/dumb-init", "--", "/entrypoint"]

execute: docker build --rm -t company/airflow .
execute: docker run -it company/airflow bash
execute: airflow config list

Anything else we need to know:

tagging @kaxil as requested in slack discussion: https://apache-airflow.slack.com/archives/CSS36QQS1/p1618965236306400

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template!

[metropolis-ameer]

Just curiously digging around configuration.py in v2-0-stable, it looks like def get has _get_option_from_config_file before _get_option_from_commands or _get_option_from_secrets.
Should it be after _get_option_from_secrets and before _get_option_from_default_config?
This is around line 336.

[jhtimmins]

@metropolis-ameer are you interested in taking this on? We can target the next release with this.

[metropolis-ameer]

It may be a while (multiple months) before I could attempt it.

[jhtimmins]

@metropolis-ameer thanks, that's actually super helpful. We'll see if someone else can take this one

[uranusjr]

On 2.x this has been fixed in #16088.

[uranusjr]

The 1.10.x issue is different though and require a different fix.

[potiuk]

The 1.10.x issue is different though and require a different fix.

I am afraid it's not critical enough to make into our "critical fixes" policy for 1.10.* . And since 1.10 End-Of-Life is just around the corner (June) it's highly unlikely we will fix it there. We recommend to migrate to latest 2.* Airflow to fix it (when released).