Pipeline Secrets and Connections Management

The Problem: Secrets in Code

Before diving into the Airflow-specific solutions, it's vital to understand the problem we're solving. Hardcoding sensitive information like database passwords, API keys, or user credentials directly into your Python scripts or configuration files is a significant security risk and a maintenance nightmare.

The video below explains why this practice is so problematic, especially when using version control systems like Git.

Just as we used a .env file to separate the configuration of our Docker services, we will now apply the same principle to the credentials our application (Airflow) uses.

Airflow's Solution: Connections and Variables

Airflow provides two distinct features to manage configuration and secrets, which can be managed through the UI, CLI, or, most relevantly for us, environment variables.

• Connections: These are structured objects designed specifically to hold the information needed to connect to an external system. This includes the hostname, port, username, password, and other parameters. They are the standard way to manage database, hook, and API authentications.

• Variables: These are simple key-value pairs for storing any other arbitrary information. This could be a file path, a configuration flag, an API endpoint URL, or a project name. Airflow has a neat feature where it automatically masks the values of variables in the UI and logs if their key contains sensitive keywords like password, secret, api_key, etc.

The following video gives a great visual overview of creating and using both Connections and Variables through the Airflow UI. While we will be using environment variables, seeing the UI helps solidify the concepts.

Defining Connections and Variables with Environment Variables

For a reproducible, version-controlled project using Docker, defining Connections and Variables via environment variables is the modern standard. It allows you to keep your secrets out of your code and manage different configurations for development, testing, and production environments easily.

The Airflow documentation and a helpful Stack Overflow thread detail how this works.

Here's the pattern:

• For Connections: AIRFLOW_CONN_{CONN_ID_UPPERCASE}
• For Variables: AIRFLOW_VAR_{VAR_NAME_UPPERCASE}

The value is typically provided as a Connection URI string or a simple string for variables.

Putting It into Practice

Let's apply this to our project. We need to create a connection that our future Airflow DAGs can use to interact with our pipeline_db in PostgreSQL.

1. Open your .env file.

2. Add a new line to define the connection. We'll name our connection pipeline_db. The CONN_ID becomes PIPELINE_DB.

   
   
   
   # ... your existing variables for POSTGRES_USER, etc. ...
   
   
   
   
   # Airflow Connection for the pipeline's PostgreSQL database
   AIRFLOW_CONN_PIPELINE_DB="postgresql+psycopg2://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres-db:5432/${POSTGRES_DB}"
   

Let's break down this Connection URI:

• postgresql+psycopg2://: The connection type and the Python driver Airflow should use.
• ${POSTGRES_USER}:${POSTGRES_PASSWORD}: The user and password, dynamically substituted from the other variables in our .env file.
• @postgres-db:5432: The host and port. This is a critical step. Inside the Docker network, services communicate using their service names. We use postgres-db (the service name from our docker-compose.yml), not localhost. The port is the internal port the container exposes, which is 5432.
• /${POSTGRES_DB}: The specific database name within the PostgreSQL server, also substituted from our .env file.

3. Ensure Docker Compose passes the variable. Your docker-compose.yml file already uses ${VAR} substitution to read from the .env file for the service configurations. This same mechanism will automatically make AIRFLOW_CONN_PIPELINE_DB available to the Airflow services when they start. No changes are needed in docker-compose.yml as long as the Airflow services' environment sections are configured to use variables from the .env file.

4. Restart your services. To make sure the Airflow containers pick up the new environment variable, run:

   docker compose down
   docker compose up -d
   

Verifying the Connection

As noted in the documentation, connections defined via environment variables do not appear in the Airflow UI's connection list. This is a security feature. The correct way to verify that Airflow recognizes our new connection is to use the Airflow CLI from within the container.

1. Get a shell inside the Airflow scheduler container:

   docker compose exec airflow-scheduler bash
   

2. Use the airflow connections command to test the connection:

   airflow connections get pipeline_db
   

You should see output that describes your connection, with the password field correctly masked with ******. This confirms that Airflow has successfully parsed the environment variable and registered the connection.

conn_id:      pipeline_db
conn_type:    postgres
description:
host:         postgres-db
login:        postgres
password:     ******
port:         5432
schema:       pipeline_db
extra:

You can exit the container shell by typing exit.

An Alternative: Local Filesystem Secrets Backend

While environment variables are perfect for our needs, it's worth knowing about another powerful method for more complex scenarios: the Local Filesystem Secrets Backend. This approach allows you to define connections and variables in separate YAML or JSON files, which can be easier to manage if you have a large number of them.

For our project, we will stick with the environment variable approach due to its simplicity and direct integration with our existing Docker setup.

Conclusion

In this lesson, we have established a secure and scalable pattern for managing the credentials our pipeline services need to communicate. By abstracting connection details away from our code, we've made our pipeline more secure, maintainable, and portable.

Here are the key takeaways:

• Airflow Connections are for storing credentials to external systems, while Airflow Variables are for general-purpose key-value configuration.
• Defining these as environment variables (AIRFLOW_CONN_... and AIRFLOW_VAR_...) is the best practice for containerized development, keeping secrets out of version control.
• Within a Docker network, services connect using their service name (e.g., postgres-db) as the host, not localhost.
• You can verify environment-variable-based connections using the airflow connections get command inside the Airflow container.

With our local development environment fully configured and all services able to communicate securely, we are now ready to start building the pipeline itself. In the next module, we will dive into Implementing Batch Ingestion Patterns, where you'll write your first Python scripts to pull data from APIs and files, using the pipeline_db connection we've just created to load that data into PostgreSQL.