Add refresh tokens to the IAM reconciliation worker

What does this MR do and why?

Adds OAuth refresh tokens to the Layer 4 reconciliation sweep. A second cron job runs Authn::IamReplication::ReconciliationWorker with oauth_access_token. It walks every token that has a refresh token and an application, and upserts it to IAM with the new OauthAccessTokenReplicator#upsert.

How it works:

  • Upsert: one Upsert per token, with the refresh token digest, client id, subject, scopes, created_at, revoked_at (only when set), the cell id and the Rails id. No expires_at, because Doorkeeper refresh tokens do not expire.
  • Every token format: Rails sends the refresh token as it is (SHA-512 or PBKDF2), with no skip before the RPC. IAM will accept every format: https://gitlab.com/gitlab-org/gitlab/-/work_items/631544
  • No application: the scope drops tokens without an application_id, because IAM needs a client for each refresh token. A follow-up issue covers them.
  • Revoked before the sweep: the scope skips tokens revoked before REVOCATION_CUTOFF. They were never live in IAM.
  • No owner or scope checks: Doorkeeper issues refresh tokens only with a resource owner, and a token's scopes come from its application, which caps them at 2048 characters.
  • FAILED_PRECONDITION: IAM returns it while the application of the token is not in IAM yet. The worker counts and logs it, like invalid_request, and continues. The next pass retries the row.
  • Scheduling: one cron entry per entity type, every 5 minutes. concurrency_limit follows the number of entity types, so the two sweeps do not block each other. The database health check uses the table of the job's entity type.
  • Batching: the worker runs each_batch over the unfiltered table and applies the scope inside each batch, like OauthAccessTokenArchiveWorker. The cursor is the highest id of the batch, so it also moves past batches with no row in scope. This applies to the application sweep too.
  • Logging: the run summary has a new rows field with the total rows handled.
  • Outbox: oauth_access_token is added to Authn::IamOutbox::ALLOWED_ENTITY_TYPES. No code writes token outbox rows yet. Layers 1-3 for tokens: #632073 (closed)

The shared Authn::IamService::GrpcClient reports every gRPC error except unavailable and timeout to error tracking, so each FAILED_PRECONDITION row also creates an event. A follow-up of #602679 (closed) will change this.

References

How to set up and validate locally

Prerequisites:

  1. In the Rails console, create an application with replication off, so it is not in IAM yet. Then create tokens for it:

    Feature.disable(:iam_data_replication)
    
    org = Organizations::Organization.first
    user = User.first
    app = Authn::OauthApplication.create!(name: 'l4-token-test', redirect_uri: 'http://gdk.test:3000/callback',
      scopes: 'api', owner: user, organization: org)
    tokens = Array.new(3) do
      OauthAccessToken.create!(application: app, resource_owner: user, organization: org, scopes: 'api',
        expires_in: 2.hours, use_refresh_token: true)
    end
    tokens.second.revoke
    
    Feature.enable(:iam_data_replication)
    tokens.map(&:id)
  2. Run the token sweep within 5 minutes of step 1. The application is not in IAM, so IAM refuses its tokens. In GDK, a cron job sweeps the applications every 5 minutes, so after that the application can already be in IAM.

    worker = Authn::IamReplication::ReconciliationWorker.new
    worker.perform('oauth_access_token')
    worker.logging_extras

    Expected: failed_precondition counts the 3 tokens.

  3. Run the application sweep, then the token sweep again:

    Authn::IamReplication::ReconciliationWorker.new.perform('oauth_application')
    worker = Authn::IamReplication::ReconciliationWorker.new
    worker.perform('oauth_access_token')
    worker.logging_extras

    Expected: no failed_precondition.

  4. Check the IAM database, with the token ids from step 1:

    SELECT source_id, revoked_at FROM doorkeeper_refresh_tokens WHERE source_id IN (<token ids>);

    Expected: the 3 tokens, and only the second has revoked_at.

Debugging

If nothing is replicated, check:

  • Authn::IamAuthService.enabled?
  • Feature.enabled?(:iam_data_replication, :instance)
  • The skip_reason values in log/auth_json.log, for rows that IAM rejects as invalid_request or failed_precondition
  • The Sidekiq fail line, in case IAM is unavailable

Screenshots or screen recordings

Not applicable. This MR has no UI change.

Query plan

The token sweep runs each_batch over the unfiltered oauth_access_tokens table and applies OauthAccessTokenReplicator.reconciliation_scope inside each batch (app/workers/authn/iam_replication/reconciliation_worker.rb:45-47). This is the shape of OauthAccessTokenArchiveWorker (app/workers/authn/data_retention/oauth_access_token_archive_worker.rb:29). The batch bounds use only the primary key, so a range with few tokens in scope does not slow them down. The application scope has no filter, so the application sweep keeps its bound queries (plans in !257644 (merged)). Its batch load loses the ORDER BY, and it gets the cursor query of query 5.

The SQL is captured from a spec run of the worker on this branch. Plans were captured on the gitlab-production-main clone. Queries 1 to 5 use the range with the lowest share of tokens in scope, ids 697,300,000 to 697,400,000. There, the previous shape filtered inside the bound query and took 7.4 s.

1. Start of the scan

SELECT: app/models/concerns/each_batch.rb:65.

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/69767/commands/175086

SELECT "oauth_access_tokens"."id" FROM "oauth_access_tokens" WHERE (id > 697299999) ORDER BY "oauth_access_tokens"."id" ASC LIMIT 1
Query plan
Limit  (cost=0.57..0.59 rows=1 width=4) (actual time=0.055..0.056 rows=1 loops=1)
   Buffers: shared hit=5
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using oauth_access_tokens_pkey on public.oauth_access_tokens  (cost=0.57..976591.52 rows=41166634 width=4) (actual time=0.054..0.055 rows=1 loops=1)
         Index Cond: (oauth_access_tokens.id > 697299999)
         Heap Fetches: 0
         Buffers: shared hit=5
         I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 2686818450913394052

Time: 1.460 ms
  - planning: 1.381 ms
  - execution: 0.079 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 5 (~40.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

2. Upper bound of the batch

SELECT: app/models/concerns/each_batch.rb:84.

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/69767/commands/175083

SELECT "oauth_access_tokens"."id" FROM "oauth_access_tokens" WHERE (id > 697299999) AND "oauth_access_tokens"."id" >= 697300000 ORDER BY "oauth_access_tokens"."id" ASC LIMIT 1 OFFSET 200
Query plan
Limit  (cost=5.81..5.84 rows=1 width=4) (actual time=12.321..12.322 rows=1 loops=1)
   Buffers: shared hit=107 read=16
   I/O Timings: read=11.984 write=0.000
   ->  Index Only Scan using oauth_access_tokens_pkey on public.oauth_access_tokens  (cost=0.57..1079508.08 rows=41166633 width=4) (actual time=1.360..12.307 rows=201 loops=1)
         Index Cond: ((oauth_access_tokens.id > 697299999) AND (oauth_access_tokens.id >= 697300000))
         Heap Fetches: 3
         Buffers: shared hit=107 read=16
         I/O Timings: read=11.984 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -4731174787827274611

Time: 14.031 ms
  - planning: 1.674 ms
  - execution: 12.357 ms
    - I/O read: 11.984 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 107 (~856.00 KiB) from the buffer pool
  - reads: 16 (~128.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

3. Load the batch

SELECT: app/workers/authn/iam_replication/reconciliation_worker.rb:46. It reads the 200 rows of the batch and keeps the 2 in scope.

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/69767/commands/175088

SELECT "oauth_access_tokens".* FROM "oauth_access_tokens" WHERE (id > 697299999) AND "oauth_access_tokens"."id" >= 697300000 AND "oauth_access_tokens"."id" < 697300200 AND "oauth_access_tokens"."refresh_token" IS NOT NULL AND "oauth_access_tokens"."application_id" IS NOT NULL AND ("oauth_access_tokens"."revoked_at" IS NULL OR "oauth_access_tokens"."revoked_at" >= '2026-10-21')
Query plan
Index Scan using oauth_access_tokens_pkey on public.oauth_access_tokens  (cost=0.57..432.00 rows=149 width=309) (actual time=0.142..0.356 rows=2 loops=1)
   Index Cond: ((oauth_access_tokens.id > 697299999) AND (oauth_access_tokens.id >= 697300000) AND (oauth_access_tokens.id < 697300200))
   Filter: ((oauth_access_tokens.refresh_token IS NOT NULL) AND (oauth_access_tokens.application_id IS NOT NULL) AND ((oauth_access_tokens.revoked_at IS NULL) OR (oauth_access_tokens.revoked_at >= '2026-10-21 00:00:00'::timestamp without time zone)))
   Rows Removed by Filter: 198
   Buffers: shared hit=205
   I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -8487790165908541548

Time: 2.304 ms
  - planning: 1.910 ms
  - execution: 0.394 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 205 (~1.60 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

4. Preload the applications

SELECT: the preload(:application) of reconciliation_scope (lib/authn/iam_replication/oauth_access_token_replicator.rb:9), run with the batch load. This query does not change. The 200 ids are examples spread across the table, because Joe returns plans and not rows, so this plan shows the index used, not the real timing.

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/67966/commands/172802

SELECT "oauth_applications".* FROM "oauth_applications" WHERE "oauth_applications"."id" IN (1, 28751, 57501, 86251, 115001, 143751, 172501, 201251, 230001, 258751, 287501, 316251, 345001, 373751, 402501, 431251, 460001, 488751, 517501, 546251, 575001, 603751, 632501, 661251, 690001, 718751, 747501, 776251, 805001, 833751, 862501, 891251, 920001, 948751, 977501, 1006251, 1035001, 1063751, 1092501, 1121251, 1150001, 1178751, 1207501, 1236251, 1265001, 1293751, 1322501, 1351251, 1380001, 1408751, 1437501, 1466251, 1495001, 1523751, 1552501, 1581251, 1610001, 1638751, 1667501, 1696251, 1725001, 1753751, 1782501, 1811251, 1840001, 1868751, 1897501, 1926251, 1955001, 1983751, 2012501, 2041251, 2070001, 2098751, 2127501, 2156251, 2185001, 2213751, 2242501, 2271251, 2300001, 2328751, 2357501, 2386251, 2415001, 2443751, 2472501, 2501251, 2530001, 2558751, 2587501, 2616251, 2645001, 2673751, 2702501, 2731251, 2760001, 2788751, 2817501, 2846251, 2875001, 2903751, 2932501, 2961251, 2990001, 3018751, 3047501, 3076251, 3105001, 3133751, 3162501, 3191251, 3220001, 3248751, 3277501, 3306251, 3335001, 3363751, 3392501, 3421251, 3450001, 3478751, 3507501, 3536251, 3565001, 3593751, 3622501, 3651251, 3680001, 3708751, 3737501, 3766251, 3795001, 3823751, 3852501, 3881251, 3910001, 3938751, 3967501, 3996251, 4025001, 4053751, 4082501, 4111251, 4140001, 4168751, 4197501, 4226251, 4255001, 4283751, 4312501, 4341251, 4370001, 4398751, 4427501, 4456251, 4485001, 4513751, 4542501, 4571251, 4600001, 4628751, 4657501, 4686251, 4715001, 4743751, 4772501, 4801251, 4830001, 4858751, 4887501, 4916251, 4945001, 4973751, 5002501, 5031251, 5060001, 5088751, 5117501, 5146251, 5175001, 5203751, 5232501, 5261251, 5290001, 5318751, 5347501, 5376251, 5405001, 5433751, 5462501, 5491251, 5520001, 5548751, 5577501, 5606251, 5635001, 5663751, 5692501, 5721251)
Query plan
 Index Scan using oauth_applications_pkey on public.oauth_applications  (cost=0.43..426.14 rows=200 width=351) (actual time=9.018..355.156 rows=91 loops=1)
   Index Cond: (oauth_applications.id = ANY ('{1,28751,57501,86251,115001,143751,172501,201251,230001,258751,287501,316251,345001,373751,402501,431251,460001,488751,517501,546251,575001,603751,632501,661251,690001,718751,747501,776251,805001,833751,862501,891251,920001,948751,977501,1006251,1035001,1063751,1092501,1121251,1150001,1178751,1207501,1236251,1265001,1293751,1322501,1351251,1380001,1408751,1437501,1466251,1495001,1523751,1552501,1581251,1610001,1638751,1667501,1696251,1725001,1753751,1782501,1811251,1840001,1868751,1897501,1926251,1955001,1983751,2012501,2041251,2070001,2098751,2127501,2156251,2185001,2213751,2242501,2271251,2300001,2328751,2357501,2386251,2415001,2443751,2472501,2501251,2530001,2558751,2587501,2616251,2645001,2673751,2702501,2731251,2760001,2788751,2817501,2846251,2875001,2903751,2932501,2961251,2990001,3018751,3047501,3076251,3105001,3133751,3162501,3191251,3220001,3248751,3277501,3306251,3335001,3363751,3392501,3421251,3450001,3478751,3507501,3536251,3565001,3593751,3622501,3651251,3680001,3708751,3737501,3766251,3795001,3823751,3852501,3881251,3910001,3938751,3967501,3996251,4025001,4053751,4082501,4111251,4140001,4168751,4197501,4226251,4255001,4283751,4312501,4341251,4370001,4398751,4427501,4456251,4485001,4513751,4542501,4571251,4600001,4628751,4657501,4686251,4715001,4743751,4772501,4801251,4830001,4858751,4887501,4916251,4945001,4973751,5002501,5031251,5060001,5088751,5117501,5146251,5175001,5203751,5232501,5261251,5290001,5318751,5347501,5376251,5405001,5433751,5462501,5491251,5520001,5548751,5577501,5606251,5635001,5663751,5692501,5721251}'::integer[]))
   Buffers: shared hit=165 read=217
   I/O Timings: read=353.438 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'
Query ID: 4217662650966920372

Time: 356.523 ms
  - planning: 1.271 ms
  - execution: 355.252 ms
    - I/O read: 353.438 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 165 (~1.30 MiB) from the buffer pool
  - reads: 217 (~1.70 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

5. Cursor of the batch

SELECT: app/workers/authn/iam_replication/reconciliation_worker.rb:47. The cursor moves to the last id of the batch, also when no token in the batch is in scope.

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/69767/commands/175087

SELECT MAX("oauth_access_tokens"."id") FROM "oauth_access_tokens" WHERE (id > 697299999) AND "oauth_access_tokens"."id" >= 697300000 AND "oauth_access_tokens"."id" < 697300200
Query plan
Result  (cost=0.60..0.61 rows=1 width=4) (actual time=0.066..0.067 rows=1 loops=1)
   Buffers: shared hit=5
   I/O Timings: read=0.000 write=0.000
   InitPlan 1
     ->  Limit  (cost=0.57..0.60 rows=1 width=4) (actual time=0.063..0.064 rows=1 loops=1)
           Buffers: shared hit=5
           I/O Timings: read=0.000 write=0.000
           ->  Index Only Scan Backward using oauth_access_tokens_pkey on public.oauth_access_tokens  (cost=0.57..9.92 rows=282 width=4) (actual time=0.062..0.063 rows=1 loops=1)
                 Index Cond: ((oauth_access_tokens.id > 697299999) AND (oauth_access_tokens.id >= 697300000) AND (oauth_access_tokens.id < 697300200))
                 Heap Fetches: 0
                 Buffers: shared hit=5
                 I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 7577627684726487397

Time: 2.119 ms
  - planning: 1.996 ms
  - execution: 0.123 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 5 (~40.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

6. Last batch

At the end of the table, the upper bound query returns no row, and the load and the cursor queries run without an upper bound. The plans use the last 111 ids on the clone.

SELECT "oauth_access_tokens"."id" FROM "oauth_access_tokens" WHERE (id > 729101850) AND "oauth_access_tokens"."id" >= 729101851 ORDER BY "oauth_access_tokens"."id" ASC LIMIT 1 OFFSET 200
SELECT "oauth_access_tokens".* FROM "oauth_access_tokens" WHERE (id > 729101850) AND "oauth_access_tokens"."id" >= 729101851 AND "oauth_access_tokens"."refresh_token" IS NOT NULL AND "oauth_access_tokens"."application_id" IS NOT NULL AND ("oauth_access_tokens"."revoked_at" IS NULL OR "oauth_access_tokens"."revoked_at" >= '2026-10-21')
SELECT MAX("oauth_access_tokens"."id") FROM "oauth_access_tokens" WHERE (id > 729101850) AND "oauth_access_tokens"."id" >= 729101851
Query plans
Limit  (cost=8.73..8.80 rows=1 width=4) (actual time=89.072..89.073 rows=0 loops=1)
   Buffers: shared hit=8 read=110 dirtied=109
   WAL: records=109 fpi=109 bytes=886713
   I/O Timings: read=86.626 write=0.000
   ->  Index Only Scan using oauth_access_tokens_pkey on public.oauth_access_tokens  (cost=0.57..8.73 rows=108 width=4) (actual time=0.865..89.038 rows=111 loops=1)
         Index Cond: ((oauth_access_tokens.id > 729101850) AND (oauth_access_tokens.id >= 729101851))
         Heap Fetches: 111
         Buffers: shared hit=8 read=110 dirtied=109
         WAL: records=109 fpi=109 bytes=886713
         I/O Timings: read=86.626 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -4731174787827274611

Time: 91.060 ms
  - planning: 1.949 ms
  - execution: 89.111 ms
    - I/O read: 86.626 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 8 (~64.00 KiB) from the buffer pool
  - reads: 110 (~880.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 109 (~872.00 KiB)
  - writes: 0
Index Scan using oauth_access_tokens_pkey on public.oauth_access_tokens  (cost=0.57..166.47 rows=57 width=309) (actual time=0.019..0.377 rows=102 loops=1)
   Index Cond: ((oauth_access_tokens.id > 729101850) AND (oauth_access_tokens.id >= 729101851))
   Filter: ((oauth_access_tokens.refresh_token IS NOT NULL) AND (oauth_access_tokens.application_id IS NOT NULL) AND ((oauth_access_tokens.revoked_at IS NULL) OR (oauth_access_tokens.revoked_at >= '2026-10-21 00:00:00'::timestamp without time zone)))
   Rows Removed by Filter: 9
   Buffers: shared hit=115
   I/O Timings: read=0.000 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: 2480669045449514627

Time: 18.066 ms
  - planning: 17.632 ms
  - execution: 0.434 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 115 (~920.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
Result  (cost=0.64..0.65 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1)
   Buffers: shared hit=6
   I/O Timings: read=0.000 write=0.000
   InitPlan 1
     ->  Limit  (cost=0.57..0.64 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)
           Buffers: shared hit=6
           I/O Timings: read=0.000 write=0.000
           ->  Index Only Scan Backward using oauth_access_tokens_pkey on public.oauth_access_tokens  (cost=0.57..8.73 rows=108 width=4) (actual time=0.012..0.012 rows=1 loops=1)
                 Index Cond: ((oauth_access_tokens.id > 729101850) AND (oauth_access_tokens.id >= 729101851))
                 Heap Fetches: 1
                 Buffers: shared hit=6
                 I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'
Query ID: 8249404974585827699

Time: 1.766 ms
  - planning: 1.721 ms
  - execution: 0.045 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 6 (~48.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Conclusion

  • All queries use oauth_access_tokens_pkey or oauth_applications_pkey. No sequential scan.
  • The bound queries are index-only scans: 12 ms on the range with the lowest share of tokens in scope, against 7.4 s with the previous shape.
  • The filters run on at most 200 rows per batch, so no new partial index is needed.
  • The batch load in query 3 read its pages from the cache. On a cold cache it reads up to one page per row, like the previous plan for this query, which took 300 ms for 200 rows.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

🤖 Drafted by Claude

Edited by Daniele Bracciani

Merge request reports

Loading
Loading