ktopcuoglu opened a new issue, #72362: URL: https://github.com/apache/airflow/issues/72362
### Under which category would you file this issue? Providers ### Apache Airflow version 3.2.2 ### What happened and how to reproduce it? **Issue description** On Airflow 3.2.2 with `apache-airflow-providers-fab==3.7.2` and PostgreSQL, authenticated `GET /auth/fab/v1/users` and `GET /auth/fab/v1/roles` leave the api-server's metadata-DB session in PostgreSQL `idle in transaction` after the HTTP request has already returned 200. `pg_stat_activity` shows `state = 'idle in transaction'`, `wait_event = ClientRead`, and the last SQL is the FAB `ab_user` / `ab_role` / `ab_permission` / `ab_view_menu` join used to serialize users or roles (including nested actions). The backend holds only `AccessShareLock` and typically has a null `backend_xmin`, so it does not block ordinary DML, but it pins a connection for as long as PostgreSQL allows. This is the same class of leak as #61480 / #68100 / #62402, but it is **not** fixed by #68100. That PR wraps `FabAuthManager.deserialize_user` in `create_session()` (FAB 3.7.0+). The collection endpoints still use `security_manager.session` with no commit / rollback / `remove()`: https://github.com/apache/airflow/blob/providers-fab/3.7.2/providers/fab/src/airflow/providers/fab/auth_manager/api_fastapi/services/users.py https://github.com/apache/airflow/blob/providers-fab/3.7.2/providers/fab/src/airflow/providers/fab/auth_manager/api_fastapi/services/roles.py ```python session = security_manager.session total_entries = session.scalars(select(func.count(User.id))).one() stmt = select(User).order_by(ordering).offset(offset).limit(limit) users = session.scalars(stmt).unique().all() return UserCollectionResponse(...) ``` The FastAPI `cleanup_session_middleware` from #61480 calls `settings.Session.remove()` in a `finally` on the event-loop thread. These handlers are sync FastAPI routes, so SQLAlchemy work runs on a threadpool thread. `scoped_session` is thread-local, so the middleware does not close the session that ran the query. If PostgreSQL `idle_in_transaction_session_timeout` is `0` or very large (RDS engine default is 24h), the session just sits until the worker is recycled. If the timeout is short, the backend is killed and a later request on the same scoped session can raise `OperationalError` then `PendingRollbackError` (the failure mode described in #61480 / #62153 / #62402). This is distinct from #71395 (`RevokedToken.is_revoked` / MySQL `wait_timeout` on the JWT path). **Steps to reproduce** 1. Run Airflow 3.2.2 with FAB auth manager and PostgreSQL (official Helm chart or Breeze `--backend postgres`). 2. Optionally shorten the leak window: `ALTER SYSTEM SET idle_in_transaction_session_timeout = '10s'; SELECT pg_reload_conf();` 3. Obtain a JWT (`POST /auth/token`) and call: - `GET /auth/fab/v1/users?limit=100&offset=0` - `GET /auth/fab/v1/roles?limit=100&offset=0` 4. Immediately query PostgreSQL: ```sql SELECT pid, state, now() - state_change AS idle_for, wait_event, left(query, 120) FROM pg_stat_activity WHERE state = 'idle in transaction'; ``` Expected after the HTTP 200: no idle-in-transaction row for that FAB SELECT. Observed: the users/roles query remains `idle in transaction` / `ClientRead`. 5. If a short `idle_in_transaction_session_timeout` is set, wait past it and reuse the same api-server worker (single worker makes this easier). Subsequent authenticated requests can 500 with `OperationalError` / `PendingRollbackError` until the poisoned scoped session is discarded. A scheduled client that paginates those two endpoints (FAB caps page size at 100) will re-create the leak on every run. ### What you think should happen instead? Each FAB FastAPI request should end the SQLAlchemy transaction on the **same thread** that opened it (`create_session()` / explicit `commit`+`remove()`, or equivalent), so PostgreSQL does not retain `idle in transaction` after 200 OK. `#68100` already does this for `deserialize_user`. The same pattern should apply to `FABAuthManagerUsers.get_users`, `FABAuthManagerRoles.get_roles`, and any other FAB collection handler that uses `security_manager.session` without a context manager. The existing HTTP middleware is not sufficient for sync routes dispatched to a threadpool. ### Operating System Linux ### Deployment Official Apache Airflow Helm Chart ### Apache Airflow Provider(s) fab ### Versions of Apache Airflow Providers ``` apache-airflow-providers-fab==3.7.2 ``` Confirmed the leaky `get_users` / `get_roles` bodies are unchanged at the `providers-fab/3.7.2` tag. `#68100` is present in that release (`deserialize_user` uses `create_session()`). ### Official Helm Chart version Not Applicable ### Kubernetes Version Not Applicable ### Helm Chart configuration Not Applicable ### Docker Image customizations Not Applicable. Reproduced against Airflow 3.2.2 with FAB provider 3.7.2 from the published constraints. ### Anything else? - Occurs whenever those collection endpoints run (UI security pages or any client that lists users/roles). Frequency matches however often those GETs are issued. - Related: #61480, #61943, #62153, #62335, #62336, #62402, #62919, #68100, #71395. - `#68100` explicitly notes that earlier middleware-only fixes do not close the worker-thread transaction; that PR only changed `deserialize_user`. - A small follow-up along the lines of `#68100` (`create_session()` around the users/roles service methods, plus a regression test that the pool checkout is not left `idle in transaction` after `GET /auth/fab/v1/users`) looks sufficient. ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
