Report when older db_key_base salts can be retired

Rémy Coutablerequested to merge
631458-db-key-base-token-digests-status into master

What does this MR do and why?

Adds Gitlab::Encryption::DbKeyBaseRotation::TokenDigestsStatus. It reports when the old db_key_base salt can be retired for token digests. EE adds runner controller tokens.

Token digests don't tell which key salted them. Rehashing on use (!259034 (merged)) rewrites a digest found under an older salt when the token is used. So only active tokens created before the rotation and not used since may still need an older salt.

  • .new(rotated_at:) raises ArgumentError unless rotated_at is a time in the past. It must be when the last node switched to the new key, plus the rehash lease timeout.
  • #execute returns one result per digest token model with remaining, expired, without_expiry and latest_expiry. It iterates each table in batches of 1,000 over the primary key, with one aggregate query per batch. The result is memoized.
  • #retirement_date returns today when none remain, the day after the latest expiry date when all remaining tokens expire, or nil while a remaining token never expires. Runner controller tokens have no expiry, so only use, revocation or rotation clears them.

Nothing calls it yet. The gitlab:db_key_base:status rake task (https://gitlab.com/gitlab-org/gitlab/-/work_items/634003) will, for runbook step 5. It doesn't depend on that code, but is only meaningful once the rehash ships. That task should refuse to report while the rehash isn't enabled, and warn about expired tokens.

When the report can over-count or under-count
  • Over-counts (safe): last_used_at updates are throttled (10 minutes for personal access tokens), so a recently used token may still be counted. Tokens created or used exactly at rotated_at are counted.
  • rotated_at too early (under-counts): a node still on the old key creates old-salt tokens after it, and can rehash tokens back to the old key. Hence "last node switched, plus the rehash lease timeout".
  • Failed rehash (accepted, under-counts): a token whose rehash failed on its last use since the rotation is missed, because last_used_at still moves. A later use retries the rehash once its 10-minute lease expires. The remaining risk is a token used once since the rotation, with that one rehash failing.
  • Expired tokens: they are reported as expired but don't affect retirement_date. Extending their expiry after retirement (for example with gitlab:tokens:edit) would break them.
  • Day after the latest expiry: a personal access token stops working at 00:00 UTC on its expires_at date. The extra day keeps instances ahead of UTC from retiring a few hours early.
What retiring the salt doesn't cover
  • Replaying a revoked (rotated) personal access token revokes its token family. After retirement, a revoked token from before the rotation can't be found, so it gets a plain 401 instead.
  • db_key_base also salts Users::CreditCardValidation hashes and web_hook_logs.url_hash. This report covers token digests only.

Database

Each table is iterated with each_batch over its primary key, in batches of 1,000, because no index covers revoked, created_at and last_used_at. Plans are from Database Lab (gitlab-production-main and gitlab-production-ci), with rotated_at = 2026-10-01.

personal_access_tokens batch boundary

SELECT "personal_access_tokens"."id"
FROM "personal_access_tokens"
WHERE "personal_access_tokens"."id" >= 10000000
ORDER BY "personal_access_tokens"."id" ASC
LIMIT 1 OFFSET 1000

Index Only Scan on personal_access_tokens_pkey: 620 buffers, 18 ms cold.

personal_access_tokens aggregate, per batch

SELECT COUNT(*) FILTER (WHERE expires_at IS NULL OR expires_at > NOW()),
       COUNT(*) FILTER (WHERE expires_at <= NOW()),
       COUNT(*) FILTER (WHERE expires_at IS NULL),
       MAX(expires_at)
FROM "personal_access_tokens"
WHERE "personal_access_tokens"."id" >= 15000000
  AND "personal_access_tokens"."id" < (SELECT id FROM personal_access_tokens WHERE id >= 15000000 ORDER BY id OFFSET 1000 LIMIT 1)
  AND ("personal_access_tokens"."revoked" = FALSE OR "personal_access_tokens"."revoked" IS NULL)
  AND "personal_access_tokens"."created_at" <= '2026-10-01 00:00:00'
  AND ("personal_access_tokens"."last_used_at" IS NULL OR "personal_access_tokens"."last_used_at" <= '2026-10-01 00:00:00')
LIMIT 1

each_batch passes the upper bound as a literal id. I ran it as a subquery so each batch holds exactly 1,000 rows, because ids are sparse. The timings below include that subquery, about 20 ms.

Batch Buffers Time
1,000 rows from id 15M, cold 1,771 670 ms
1,000 rows from id 5M, cold 1,430 610 ms
Same batch, warm 1,430 3.5 ms
1,000 rows, cold (@terrichu: 175134, 175067) 1,002 0.26-0.66 s

Each row costs about one heap page, so buffers and time grow with the batch size. For comparison, a 10,000-row batch read about 10,000 buffers and took 5.8-8.6 s cold (175132).

Plan: 1,000 rows from id 15M, cold 
 Limit  (cost=150609.31..150609.32 rows=1 width=28) (actual time=670.085..670.088 rows=1 loops=1)
   Buffers: shared hit=834 read=937 dirtied=17
   I/O Timings: read=665.742 write=0.000
   InitPlan 1
     ->  Limit  (cost=30.92..30.95 rows=1 width=4) (actual time=20.757..20.759 rows=1 loops=1)
           Buffers: shared hit=764 read=28 dirtied=17
           I/O Timings: read=19.869 write=0.000
           ->  Index Only Scan using personal_access_tokens_pkey on public.personal_access_tokens personal_access_tokens_1  (cost=0.44..283032.32 rows=9285831 width=4) (actual time=1.622..20.716 rows=1001 loops=1)
                 Index Cond: (personal_access_tokens_1.id >= 15000000)
                 Heap Fetches: 21
                 Buffers: shared hit=764 read=28 dirtied=17
                 I/O Timings: read=19.869 write=0.000
   ->  Aggregate  (cost=150578.36..150578.37 rows=1 width=28) (actual time=670.083..670.083 rows=1 loops=1)
         Buffers: shared hit=834 read=937 dirtied=17
         I/O Timings: read=665.742 write=0.000
         ->  Index Scan using index_personal_access_tokens_on_id_and_created_at on public.personal_access_tokens  (cost=0.44..149563.18 rows=50759 width=4) (actual time=23.557..669.646 rows=761 loops=1)
               Index Cond: ((personal_access_tokens.id >= 15000000) AND (personal_access_tokens.id < (InitPlan 1).col1) AND (personal_access_tokens.created_at <= '2026-10-01 00:00:00'::timestamp without time zone))
               Filter: (((NOT personal_access_tokens.revoked) OR (personal_access_tokens.revoked IS NULL)) AND ((personal_access_tokens.last_used_at IS NULL) OR (personal_access_tokens.last_used_at <= '2026-10-01 00:00:00+00'::timestamp with time zone)))
               Rows Removed by Filter: 239
               Buffers: shared hit=834 read=937 dirtied=17
               I/O Timings: read=665.742 write=0.000

ci_runner_controller_tokens (EE, CI database)

SELECT COUNT(*)
FROM "ci_runner_controller_tokens"
WHERE "ci_runner_controller_tokens"."id" >= 1 AND "ci_runner_controller_tokens"."id" < 1001
  AND "ci_runner_controller_tokens"."status" = 0
  AND "ci_runner_controller_tokens"."created_at" <= '2026-10-01 00:00:00'
  AND ("ci_runner_controller_tokens"."last_used_at" IS NULL OR "ci_runner_controller_tokens"."last_used_at" <= '2026-10-01 00:00:00')

The table is empty on GitLab.com: 0.08 ms for the each_batch start (175130) and 0.07 ms for the count (175131).

Expected total runtime on GitLab.com

personal_access_tokens has about 16.7M rows in 56 GB (from @terrichu's review), so a full run is about 16,700 batches and reads the whole heap.

  • All cold, at about 0.65 s per batch: about 3 hours.
  • All warm, at about 4 ms per batch: about a minute.

A real run falls between the two. Where it runs on GitLab.com (primary or replica) is still open in the review. GitLab.com is out of scope for the current spike.

References

Screenshots or screen recordings

Not applicable, backend only.

How to set up and validate locally

  1. Run the specs:

    bin/rspec spec/lib/gitlab/encryption/db_key_base_rotation ee/spec/lib/ee/gitlab/encryption/db_key_base_rotation

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

🤖 Generated with Claude Code

Edited by Rémy Coutable

Merge request reports