Summary

The get_current_user_with_permissions() function acquires a database session via Depends(get_db) at request start and stores it in the returned user context dict. This session is held for the entire request lifecycle, including during slow async operations (MCP backend calls, template rendering, plugin hooks). Under high load, this causes "idle in transaction" connections to accumulate, leading to connection pool exhaustion and cascading failures.

Symptoms Under Load (4000 concurrent users)

Error Messages

QueuePool limit of size 20 overflow 10 reached, connection timed out, timeout 60.00
OperationalError: (psycopg.errors.ProtocolViolation) idle transaction timeout

Root Cause

The RBAC middleware holds database sessions for entire request lifetimes:

# mcpgateway/middleware/rbac.py
async def get_current_user_with_permissions(..., db: Session = Depends(get_db)):
    return {
        "db": db,  # Session stored in user context, held until request completes
    }

Session lifecycle:

1. Request arrives → session acquired via Depends(get_db)
2. Permission check runs (fast, ~10ms)
3. Session stays open during: slow backend calls (1-30s), template rendering
4. Session released only when request completes
5. Under load: sessions accumulate in "idle in transaction" state
6. Pool exhaustion: new requests timeout waiting for connections

Proposed Solution

1. Remove db from get_current_user_with_permissions() return value
2. Update permission decorators to use fresh_db_session() for short-lived permission checks
3. Sessions released immediately after permission check, before slow operations

Files Affected

• mcpgateway/middleware/rbac.py - Core fix
• mcpgateway/admin.py - Remove user["db"] reference
• Test fixtures - Remove "db" from mock user contexts

Testing

• Unit tests: Verify permission decorators use fresh sessions
• Integration tests: Verify permission checks still work
• Load tests: Verify stable idle-in-transaction count under 4000 users