[Bug 2154897] Re: Keystone leaks DB connections when uncached federation service providers are queried

[Launchpad Bug Tracker]

Status changed to 'Confirmed' because the bug affects multiple users.

** Changed in: keystone (Ubuntu Resolute)
       Status: New => Confirmed

-- 
You received this bug notification because you are a member of Ubuntu
OpenStack, which is subscribed to keystone in Ubuntu.
https://bugs.launchpad.net/bugs/2154897

Title:
  Keystone leaks DB connections when uncached federation service
  providers are queried

Status in OpenStack Identity (keystone):
  Invalid
Status in keystone package in Ubuntu:
  Confirmed
Status in python3.14 package in Ubuntu:
  Confirmed
Status in keystone source package in Resolute:
  Confirmed

Bug description:
  Keystone appears to leak one SQLAlchemy connection each time token issuance
  recomputes the enabled federation service provider list with Keystone caching
  disabled.

  The leak is reproducible with sequential `openstack token issue` calls. The
  database sessions are idle (`Sleep`) and accumulate until SQLAlchemy reaches
  the pool limit:

  ```text
  sqlalchemy.exc.TimeoutError: QueuePool limit of size 1 overflow 5 reached,
  connection timed out, timeout 5.00
  ```

  The evidence points to
  `keystone.federation.backends.sql.Driver.get_enabled_service_providers()`
  returning a lazy SQLAlchemy `Query` from inside a `session_for_read()` context.
  The caller then iterates that query after the context has exited.

  ## Observed Versions

  Reproduced in one environment with:

  ```text
  Keystone: 29.0.0
  SQLAlchemy: 2.0.45
  oslo.db: 18.0.0
  PyMySQL: 1.1.1
  Cache: disabled/unset
  Python: 3.14.3-0ubuntu2
  Ubuntu: 26.04
  ```

  Also reproduced in a DevStack environment with Keystone caching
  disabled:

  ```text
  Keystone: 29.0.2.dev18
  SQLAlchemy: 2.0.46
  oslo.db: 18.0.0
  PyMySQL: 1.1.2
  Cache: disabled
  Python: 3.14.3-0ubuntu2
  Ubuntu: 26.04
  ```

  ## Reproducer

  On a test Keystone deployment, disable Keystone caching or leave it unset. Use
  a deliberately small database pool so the leak fails quickly:

  ```ini
  [database]
  max_pool_size = 1
  max_overflow = 5
  pool_timeout = 5
  ```

  Restart Keystone, source admin credentials, then run:

  ```bash
  for i in $(seq 1 80); do
    echo "iteration $i $(date -Is)"
    openstack token issue >/dev/null || break
  done
  ```

  ## Actual Result

  The loop fails around iteration 6. At failure, MySQL has exactly six idle
  Keystone connections, matching `max_pool_size + max_overflow`:

  ```sql
  SELECT DB, COMMAND, COUNT(*)
  FROM information_schema.PROCESSLIST
  WHERE DB = 'keystone'
  GROUP BY DB, COMMAND;
  ```

  ```text
  DB        COMMAND  COUNT(*)
  keystone  Sleep    6
  ```

  With the default `max_overflow=50`, the same issue fails around 51 retained
  connections for one Keystone process.

  In the DevStack comparison with cache disabled and the same tiny pool, the
  loop failed at iteration 15 with six idle Keystone connections.

  ## Expected Result

  Sequential token issuance should not monotonically increase checked-out
  SQLAlchemy connections. Connections should be returned to the pool after each
  request.

  ## Evidence

  Temporary SQLAlchemy `Pool` event instrumentation showed
  requests completing normally while six pool checkouts remained unmatched:

  ```text
  POOL_CHECKOUT count: 151
  POOL_CHECKIN count: 145
  unmatched checkouts: 6
  REQ_START count: 14
  REQ_END count: 14
  ```

  All unmatched checkouts had the same relevant stack:

  ```text
  keystone/federation/core.py", line 152, in get_enabled_service_providers
    return [normalize(sp) for sp in service_providers]
  sqlalchemy/orm/query.py", line 2843, in __iter__
  sqlalchemy/orm/query.py", line 2857, in _iter
  sqlalchemy/orm/session.py", line 2351, in execute
  ```

  The SQL backend currently returns the lazy query:

  ```python
  def get_enabled_service_providers(self):
      with sql.session_for_read() as session:
          service_providers = session.query(ServiceProviderModel)
          service_providers = service_providers.filter_by(enabled=True)
          return service_providers
  ```

  The caller iterates it outside that session context:

  ```python
  service_providers = self.driver.get_enabled_service_providers()
  return [normalize(sp) for sp in service_providers]
  ```

  ## Mitigation Test

  As a local test, materialising the query inside the session context stopped the
  leak:

  ```python
  def get_enabled_service_providers(self):
      with sql.session_for_read() as session:
          service_providers = session.query(ServiceProviderModel)
          service_providers = service_providers.filter_by(enabled=True)
          return list(service_providers)
  ```

  With cache disabled and the same tiny pool (`max_pool_size=1`,
  `max_overflow=5`), 20 sequential token requests passed and MySQL stayed flat at
  one idle Keystone connection.

  ## Cache Comparison

  The same token loop did not exhaust the pool in a comparison run with Keystone
  caching enabled:

  ```ini
  [cache]
  enabled = True
  backend = dogpile.cache.memcached
  memcache_servers = localhost:11211
  ```

  This should be interpreted as masking the repeated growth, not as proof that
  the leak disappears. With cache enabled, this lookup can still run on cache
  miss, warmup, invalidation, or expiry. It just does not run on every token
  request, so a short sequential token loop may retain only the first leaked
  connection and never reach the pool cap.

  ## Why This Does Not Look Like Capacity

  The retained DB sessions are idle and stop exactly at the SQLAlchemy pool cap,
  so this does not look like database saturation or slow queries.

  ## Suggested Fix

  `get_enabled_service_providers()` should not return a lazy SQLAlchemy query
  whose execution happens outside the `session_for_read()` context. It should
  materialize the result, or return plain data, while the SQL session context is
  still active.

  This bug reports has been assisted by Codex GPT 5.5

To manage notifications about this bug go to:
https://bugs.launchpad.net/keystone/+bug/2154897/+subscriptions