Least-Privilege Connections

Least-Privilege Connections and Roles

Every task so far connected as postgres, a superuser. Because any DAG can use any connection, Airflow 129 cannot keep a task away from a credential, so the limit has to live in the target system: one database role per pipeline step, with only the privileges that step needs.

roles.sql: a login role that can do the load step and nothing elseSQL

CREATE ROLE booknest_loader LOGIN PASSWORD :'pw' CONNECTION LIMIT 4;
GRANT USAGE ON SCHEMA public TO booknest_loader;
GRANT SELECT, INSERT, DELETE ON orders, order_items TO booknest_loader;  -- no UPDATE, no DDL
ALTER ROLE booknest_loader SET statement_timeout = '5min';

dags/booknest_secure.py loads 28 June through the vault's booknest_loader connection with the unchanged load_orders() of BookNest's Daily Pipeline, then does what a careless or compromised task might, reading customers' e-mail addresses:

Output of 55

$ airflow dags test booknest_secure 2026-06-28
Done. Returned value was: 186 orders, 269 lines replaced
psycopg.errors.InsufficientPrivilege: permission denied for table customers
DagRun Finished: state=failed

People need the same treatment. The FAB auth manager's roles nest: Viewer reads DAGs, runs and logs; User also triggers and clears runs; Op also manages connections, variables and pools; Admin also manages users. Give analysts Viewer and pipeline owners User, and keep Op for the platform team, because whoever can edit a connection can point it at their own server and collect the password.

MENU