Auth manager cannot express dag authorization as SQL, so list views enumerate every dag

[1fanwang]

Description

BaseAuthManager can only answer "which dags may this user see" as a set[str], and core's only consumer of that set turns it straight into a SQL predicate:

class PermittedDagFilter(OrmClause[set[str]]):
    def to_orm(self, select: Select) -> Select:
        return select.where(DagModel.dag_id.in_(self.value or set()))

core_api/security.py#L246-L251

So a manager is asked to materialize every authorized dag id in the deployment purely so one IN (...) clause can be built. The set is never used for anything else.

Two properties make that expensive:

1. It is deployment-wide, not page-wide. get_authorized_dag_ids reads every dag row with no limit, groups by team, then calls filter_authorized_dag_ids per team (base_auth_manager.py#L643-L666).
2. It resolves before pagination exists. It is a FastAPI dependency (security.py#L332-L343), so limit and offset are applied after the authorized set is built.

A manager whose policy lives in the database cannot say "join against my table". The return type cannot express it.

Who this hits

It is already reported well below large-deployment scale:

• Very slow Keycloak auth manager responses #61686 — Keycloak, 600 dags, ~25s responses. Closed after per-manager caching and connection pooling; the reporter noted a real fix "is quite complicated to implement".
• Multi Team: /dags screen is very slow to load with multiple teams #69041 — multi-team, hundreds of dags across 10 teams, ~10s /dags load.
• Implement bulk authorization methods in KeycloakAuthManager #70582 — bulk authorization methods added to the Keycloak manager.

Every fix so far has been inside one manager, so each new manager rediscovers the problem.

It also makes the documented answer expensive. #23638 asked for dag permissions by tag or owner rather than by name, and the Airflow 3 answer is to write an auth manager over get_authorized_dag_ids, with tags and bundles as the suggested attributes. Those are rows in the metadata database, and get_db_manager exists so a manager can add tables of its own — but none of it can be expressed as a query.

Proposal

Let a manager return a select instead of a set, with today's behaviour as the default:

def get_authorized_dag_ids_select(self, *, user, method="GET") -> Select | None:
    """A select producing the dag ids this user may access.

    Returning None, the default, keeps the existing behaviour: core calls
    get_authorized_dag_ids and builds an IN clause from the result.
    """
    return None

Core applies it as dag_id IN (subquery), so filtering and pagination happen in one statement. Managers that do not override it are untouched.

What this does and does not fix

Fixes: any manager whose policy is in the metadata database — a tag- or bundle-based manager, one with its own table via get_db_manager, and FAB, which currently enumerates when its permission tables are joinable.

Does not fix: managers backed by an external PDP (Amazon Verified Permissions, Keycloak Authorization Services). Their policy is not SQL, so they still enumerate, and #61686 would not be solved by this. Those need either a reverse-lookup API on the PDP ("which resources may this principal access?") or a local projection of the policy, both outside Airflow.

This is the database-backed half of the problem, which is the half Airflow can fix on its own.

Measured

A tag-based manager returning select(DagTag.dag_id).where(DagTag.name == ...), against MySQL 8 with 41,606 dags. Page of 50, median of 15 rounds:

Tag the manager authorizes on	Matching dags	Materialize	Subquery
Two team tags	418	5.4 ms	1.4 ms
An environment tag	37,446	316.1 ms	0.8 ms

FAB on the same database, 1,610 roles and 66,529 per-dag grants: 351.8 ms → 0.6 ms for a user authorized on all dags, which is any role with a global can_read.

Implemented in #71341.

[1fanwang]

Closing. Discussed on #71341: the fix needs a hook in base_auth_manager, and FAB is the only DB-backed manager, so that's a core change for a provider that's being removed rather than optimised.