# `personal_access_token_last_used_ips` exceeds the documented 5-IP cap ## Background The `last_used_ips` attribute on access tokens is documented and intended to hold "the five most recent unique IP addresses" that have authenticated with a token. `PersonalAccessTokens::LastUsedService` (`NUM_IPS_TO_STORE = 5`) is meant to enforce this by trimming older rows on every write. While preparing the rollout of the `expose_last_used_ips_for_access_tokens` feature flag, we found that this cap does not hold in production. Measured on a Database Lab clone of `gitlab-production-main` (snapshot 2026-08-15): | Metric | Value | | --- | --- | | Tokens with any recorded IP | 4,077,192 | | Tokens with more than 5 IPs | 583,414 (~14%) | | Tokens with more than 100 IPs | 9 | | Tokens with more than 1000 IPs | 0 | This is a live bug, not only historical accumulation. There are two populations. Around 307k tokens are frozen legacy: their most recent IP is months old, so they never append a new IP, and the trim (which only runs on append) never re-fires. But 88,248 tokens appended a new IP within the last 7 days and are still above 5 (172,532 within 30 days). A working trim would snap those back to 5 on that append, so the write path is actively failing, not just leaving old data behind. Both render paths are unbounded (`last_used_ips.map(&:ip_address)` in the REST entity and in the GraphQL type), so the excess is served, not only stored. About 14% of tokens therefore return more than the documented five IPs. ### Root cause (verified) The `DELETE` itself is correct. The problem is the count that guards it. `update_pat_ip` appends the new IP, then reads `ip_count` and only trims when it exceeds the cap: ```ruby ip_count = @personal_access_token.last_used_ips.where(...).count return unless ip_count > NUM_IPS_TO_STORE ``` That runs inside `without_sticky_writes`. In `Gitlab::Database::LoadBalancing::Session`, `without_sticky_writes` is aliased to `ignore_writes`, and `write!` early-returns on `@ignore_writes` before calling `use_primary!`. So the append's `INSERT` does not stick later reads to the primary, and the `ip_count` read is served by a replica that can lag the row just written. It undercounts, the `> NUM_IPS_TO_STORE` guard is false, and the trim is skipped. `without_sticky_writes` was added deliberately as a hot-path optimization; the count guard was added later and unknowingly placed a consistency-requiring read inside a block designed to relax consistency. Verified by reproduction: the `DELETE` trims correctly (12 to 5) in a single-database test, which is why an earlier local check wrongly concluded there was no bug. Only production replica lag exposes it. ### Not a performance issue This is a correctness and storage-growth defect, not a performance one. The same investigation confirmed: - No N+1: query counts are bounded and flat across every token endpoint (Kibana, one week). The count does not scale with the number of tokens. - No latency creep: endpoint latency is flat at ~43ms and per-SQL latency at ~1ms over 30 days (Grafana). The lookup is an indexed `IN (...)` over a tiny per-token row set, so table growth has not slowed it. ## Proposed fix Three independent MRs. Each is separately reviewable and mergeable; the only ordering constraint is that step 3 merges after step 2. ### 1. Cap the exposed IPs to the 5 most recent (read-side `limit(5)`) Bound the render to the 5 most recent IPs (order by `created_at` descending, take 5) in both the REST entity and the GraphQL type. This fixes the contract immediately and everywhere, regardless of what is stored or whether the write path is ever fixed. Highest correctness value, ship first. Note: bounding the render is straightforward; bounding the preloaded rows per token (loading only 5 newest per parent) needs a lateral join or windowed query and can be a follow-up if wanted. ### 2. Make the write-path trim independent of the stale count Drop the `ip_count` read and its guard. Trim with a single `DELETE` that removes everything beyond the five most recent, via a relation subquery so it is one statement executed on the primary and cannot be misled by replica lag: ```ruby ids_to_keep = @personal_access_token.last_used_ips .order(created_at: :desc, id: :desc).limit(NUM_IPS_TO_STORE).select(:id) @personal_access_token.last_used_ips.where.not(id: ids_to_keep).delete_all ``` This also trims any pre-existing excess to five the next time a token appends a new IP. Prevents future accumulation. ### 3. Remediate existing instances (batched background migration) Clean up the tokens already over the cap (~583k on GitLab.com), trimming each to its 5 most recent IPs. Use a batched background migration, not an inline post-deployment migration, because of the table size and self-managed installs. Only meaningful after step 2, otherwise the table refills. The changelog should note that deleting rows beyond the newest five matches the documented behavior, so it is not a surprising loss of audit data. ## References - Feature flag rollout: https://gitlab.com/gitlab-org/gitlab/-/work_items/603636 - Fold `last_used_ips` into the base entity + remove the flag: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/250307 - Preload fix (prerequisite for the rollout): https://gitlab.com/gitlab-org/gitlab/-/merge_requests/250445