[I] providers-fab: GET /auth/fab/v1/users and /roles leave PostgreSQL sessions idle in transaction after #68100 [airflow]

["ktopcuoglu (via GitHub)" <gi...@apache.org>]

ktopcuoglu opened a new issue, #72361:
URL: https://github.com/apache/airflow/issues/72361

   ### 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: commits-unsubscribe@airflow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] providers-fab: GET /auth/fab/v1/users and /roles leave PostgreSQL sessions idle in transaction after #68100 [airflow]

["vincbeck (via GitHub)" <gi...@apache.org>]

vincbeck closed issue #72361: providers-fab: GET /auth/fab/v1/users and /roles leave PostgreSQL sessions idle in transaction after #68100
URL: https://github.com/apache/airflow/issues/72361


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] providers-fab: GET /auth/fab/v1/users and /roles leave PostgreSQL sessions idle in transaction after #68100 [airflow]

["boring-cyborg[bot] (via GitHub)" <gi...@apache.org>]

boring-cyborg[bot] commented on issue #72361:
URL: https://github.com/apache/airflow/issues/72361#issuecomment-5489581407

   Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.
   


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org