providers-fab: GET /auth/fab/v1/users and /roles leave PostgreSQL sessions idle in transaction after #68100 #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

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:

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: Add session cleanup middleware to FAB FastAPI app #61480, Fix FAB auth_manager load_user causing PendingRollbackError and Inter… #61943, fix: fab deserialize issue #62153, providers-fab: Unhandled MySQLdb.OperationalError in cleanup_session_middleware Session.remove() causes 500 on api-server #62335, providers-fab: Handle database errors in cleanup_session_middleware Session.remove() #62336, PendingRollbackError: Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error) #62402, fix(fab): recover from first idle MySQL disconnect in token auth #62919, Fix fab deserialize user session leak #68100, First authenticated request after MySQL idle disconnect returns 500: RevokedToken.is_revoked runs on a stale session before FAB's recovery (#62919) can kick in #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

• I agree to follow this project's Code of Conduct