Airflow Connections: Rotated Credentials That Broke DAGs

A database password rotated at 2 AM and every DAG died within minutes. Twenty files held the same hardcoded credential, and the rotation invalidated all of them at once. The fix took hours because the secret lived in code.

Connections exist so credentials live in one place. You'll learn how hooks, backends, and rotation turn 2 AM pages into non-events.

We'll build connections four ways and wire hooks properly. Secrets stop shipping with code.

What a Connection Stores

A connection stores everything a client needs: type, host, port, login, password, schema, and an extras JSON for knobs like warehouse or role. It lives under a conn_id such as analytics_db. Tasks name the id; Airflow resolves the secret.

Resolution order matters. Env vars override the metadata DB, and configured backends slot into the lookup chain. That layering lets local dev use simple vars while prod reads Vault through the same conn_id.

Treat conn_ids as public interface. Rename one and every consumer breaks, so standardize names per system and document them like API endpoints.

You'll reference them in templates as {{ conn.analytics_db.host }} and in code via BaseHook.get_connection("analytics_db"). Types span postgres, http, aws, snowflake, and custom provider types — full list lives under provider Connections docs. Extra JSON carries sslmode, IAM flags, and pool knobs.

Creating Connections: UI, CLI, Env Vars, YAML in 3.x

The UI path suits exploration: Admin, Connections, add, test. The CLI path suits automation: airflow connections add with JSON or URI forms, checked into setup scripts. Both land in the metadata DB.

Env vars suit containers: AIRFLOW_CONN_ANALYTICS_DB carries the URI without touching the DB. YAML suits versioned non-prod config in 3.x. Backends suit production, serving secrets at lookup time.

Choose per environment, keep conn_ids identical. A DAG referencing analytics_db runs unchanged from laptop to prod because only resolution differs.

Install the code first: pip install apache-airflow-providers-postgres ships PostgresHook and SQL operators. Env form is AIRFLOW_CONN_ANALYTICS_DB='postgres://etl_svc:pw@db.internal:5432/analytics?sslmode=require' (URL-encoded). PostgresHook defaults to conn_id postgres_default when you'd omit the arg.

# install the provider package first
pip install apache-airflow-providers-postgres
# scriptable: JSON form
AIRFLOW_HOME=~/airflow airflow connections add 'analytics_db' \
  --conn-json '{"conn_type": "postgres", "login": "etl_svc", "password": "REDACTED", "host": "db.internal", "port": 5432, "schema": "analytics", "extra": {"sslmode": "require"}}'
# URI form
AIRFLOW_HOME=~/airflow airflow connections add 'analytics_db' \
  --conn-uri 'postgres://etl_svc:REDACTED@db.internal:5432/analytics?sslmode=require'
# env form (containers): export AIRFLOW_CONN_ANALYTICS_DB='postgres://etl_svc:REDACTED@db.internal:5432/analytics'
# verify resolution
AIRFLOW_HOME=~/airflow airflow connections get analytics_db

Hooks: The Reusable Client Layer

A hook turns a connection into an authenticated client with retries, timeouts, and helper methods. PostgresHook.get_records runs queries, S3Hook moves bytes, SnowflakeHook manages warehouses. Tasks call hooks; hooks own the wire.

This layering pays twice. Client logic lives once instead of per task, and behavior stays uniform: every Postgres task shares timeouts and retry posture. Debugging means reading one helper, not six dialects.

Never construct raw clients in tasks. Raw psycopg2 or boto3 calls bypass connection resolution, ignore backends, and scatter credentials. Hooks are the sanctioned path.

Methods split cleanly: hook.run() for CREATE/INSERT/UPDATE/DELETE with no return, hook.get_records("SELECT ...") for list-of-tuples, hook.get_df("SELECT ...") for pandas, plus bulk_load/bulk_dump for files. You'll prefer CREATE TABLE IF NOT EXISTS for idempotent setup and parameterized queries over f-strings.

import pendulum
from airflow.sdk import dag, task
from airflow.providers.postgres.hooks.postgres import PostgresHook

@dag(
    schedule="@daily",
    start_date=pendulum.datetime(2026, 1, 1, tz="UTC"),
    catchup=False,
    tags=["analytics"],
)
def analytics_rollup():
    @task(retries=3)
    def rollup() -> int:
        hook = PostgresHook(postgres_conn_id="analytics_db")
        rows = hook.get_records(
            "SELECT count(*) FROM events WHERE event_date = %(d)s",
            parameters={"d": "2026-09-03"},
        )
        return int(rows[0][0])

    @task
    def publish(count: int) -> None:
        print(f"events: {count}")

    publish(rollup())

analytics_rollup()

Secret Backends: Stop Shipping Credentials

Secrets backends serve connections from Vault, AWS Secrets Manager, or SSM at lookup time. Airflow checks env, then backends, then the DB. Credentials rotate in the vault; DAGs never redeploy.

Configure once in the secrets section and scope policies tightly. Each environment reads its own vault path, so staging credentials cannot leak into prod lookups. Least privilege applies to pipelines too.

The UI only shows DB-stored values, so backend secrets stay invisible there by design. Verify through test tasks and backend audit logs, not the connections page.

Rotation Without Redeploy

Rotation without redeploy has three steps: update the vault entry, confirm a test task resolves it, and watch scheduled runs stay green. No merge, no image build, no restart.

Schedule drills quarterly. Rotate staging first, verify with dags test, then rotate prod during business hours. Each drill proves the path that 2 AM rotations will use.

Keep a conn_id inventory. Every DAG's connections list lives in code review, so auditors and on-call see exactly which systems each pipeline touches.

# rotation without redeploy: update the backend, tasks pick it up
vault kv put secret/airflow/connections/analytics_db \
  conn_type=postgres login=etl_svc password='NEW-REDACTED' host=db.internal port=5432 schema=analytics
# confirm a task resolves the fresh secret
AIRFLOW_HOME=~/airflow airflow dags test analytics_rollup 2026-09-03

Connection Testing in CI

CI catches missing connections cheaply. Build test connections from fixtures, run dags test for touched DAGs, and execute a hook get_conn check per conn_id. A missing staging connection fails the merge, not the midnight run.

DagBag import tests complement connectivity checks. Parsing proves structure; connection tests prove runtime. Together they gate the two failure classes that reach schedulers most.

Keep prod credentials out of CI. Test fixtures use throwaway databases and fake backends. The gate validates wiring and names, never real secrets.