Add reconciliation worker for OAuth applications replication

What does this MR do and why?

Adds a basic, interim Layer 4 of the IAM data propagation design (ADR). It is mainly a backfill: a cron worker walks every oauth_applications row by id and upserts it to IAM with the new public OauthApplicationReplicator#upsert. It does not need an outbox row, so it also covers rows that exist from before replication, or that changed without an outbox write.

How it works:

  • Scheduling: the cron runs every 5 minutes. Each run works for up to 4 minutes and resumes from the last processed id, stored in Redis. At the end of the table the id resets to 0, so the sweep loops. deduplicate :until_executed keeps one run at a time.
  • Batches: 200 rows, ordered by id. The last processed id is saved after each batch, so a failed run repeats at most one batch.
  • Failure handling: rows that IAM rejects (invalid_request) are logged and skipped. Any other gRPC error stops the run, and the next run retries. Unexpected errors on the Rails side are tracked and the row is skipped.
  • Unsupported secrets: IAM accepts only a SHA-512 hex digest as hashed_client_secret (proto rule ^[0-9a-f]{128}$). Older applications can still store a PBKDF2 or plaintext secret (about 178K on the production clone). OauthApplicationReplicator#upsert skips them without an RPC and returns :unsupported_secret_digest. Layer 4 counts them in the run summary only. Layer 3 (DrainWorker) marks their outbox rows as delivered and logs result: :unsupported_secret_digest, because a retry cannot fix them. How to replicate them: https://gitlab.com/gitlab-org/gitlab/-/work_items/631544
  • dynamic: the upsert now sends the dynamic attribute of the application.
  • Logging: one warn line per row that IAM rejects, and one summary per run on the Sidekiq done line (counts, layer, cell_id, over_time). Unsupported secrets have no per-row line, because the sweep runs continuously.
  • Extensibility: the worker takes the entity type, so refresh tokens can reuse it through Authn::IamReplication.replicator_for.

The worker uses the existing iam_data_replication flag and Authn::IamAuthService.enabled?. The shared Authn::IamService::GrpcClient still reports every gRPC error to error tracking, including rejected rows. A follow-up will change this.

Differences from ADR-DATA-01 (interim)

  • Full scan on every pass, not only recently changed rows.
  • Upsert only: no read-diff-write, no orphan deletion, no divergence count. These need a new IAM ListClients RPC: #628424
  • Continuous loop, not a daily job.
  • The backfill is the first pass of the sweep, not a one-shot job.
  • L0 only.

References

How to set up and validate locally

Prerequisites:

  1. In the Rails console, create applications with replication off, so Layers 1-3 do not replicate them:

    Feature.disable(:iam_data_replication)
    
    org = Organizations::Organization.first
    apps = Array.new(3) do |i|
      Authn::OauthApplication.create!(name: "l4-test-#{i}", redirect_uri: 'http://gdk.test:3000/callback',
        scopes: 'api', owner: User.first, organization: org, confidential: true)
    end
    # Optional: IAM does not accept this secret format, to see the skip path.
    apps.last.update_column(:secret, 'not-a-sha512-digest')
    
    Feature.enable(:iam_data_replication)
    Authn::IamOutbox.where(entity_id: apps.map(&:id)).count # => 0
  2. Run the worker. Either wait for the next cron run (every 5 minutes), or run it inline:

    Authn::IamReplication::ReconciliationWorker.new.perform('oauth_application')
  3. Check the result: the Sidekiq done line shows extra.authn_iam_replication_reconciliation_worker.result with unsupported_secret_digest: 1. Then query the IAM database:

    SELECT name, owning_cell_id FROM oauth_clients WHERE name LIKE 'l4-test-%';

    Expected: l4-test-0 and l4-test-1 with owning_cell_id = 1. l4-test-2 is absent, because Rails skipped it before the RPC.

  4. Optional: check the last processed id. After a full pass, it is "0":

    Gitlab::Redis::SharedState.with { |r| r.get('authn:iam_replication:reconciliation:last_processed_id:oauth_application') }
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 example invalid_request when owning_cell_id is missing)
  • The Sidekiq fail line, in case IAM is unavailable

Screenshots or screen recordings

Not applicable. This MR has no UI change.

Query plan

The worker only reads from the database. The last processed id is stored in Redis, and the upserts go to IAM over gRPC.

For every batch of 200 rows, the worker runs these queries:

  • each_batch finds the start of the scan and the upper bound of the batch.
  • The worker loads the batch, ordered by id.
  • The organization and polymorphic owner preloads, declared in reconciliation_scope (lib/authn/iam_replication/oauth_application_replicator.rb:10), run with the batch load. There is one query for organizations, and one for each owner_type in the batch (User, Namespace).

Plans were captured on the gitlab-production-main clone. No new index is needed: all queries use the primary keys.

1. Start of the scan

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

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59017/commands/164198

SELECT "oauth_applications"."id" FROM "oauth_applications" WHERE (id > 3500000) ORDER BY "oauth_applications"."id" ASC LIMIT 1
Query plan
 Limit  (cost=0.43..0.45 rows=1 width=4) (actual time=0.070..0.071 rows=1 loops=1)
   Buffers: shared hit=4
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using oauth_applications_pkey on public.oauth_applications  (cost=0.43..53693.79 rows=2245541 width=4) (actual time=0.069..0.069 rows=1 loops=1)
         Index Cond: (oauth_applications.id > 3500000)
         Heap Fetches: 0
         Buffers: shared hit=4
         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: -8776149306060349523

Time: 0.812 ms
  - planning: 0.710 ms
  - execution: 0.102 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.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/59017/commands/164199

SELECT "oauth_applications"."id" FROM "oauth_applications" WHERE (id > 3500000) AND "oauth_applications"."id" >= 3500001 ORDER BY "oauth_applications"."id" ASC LIMIT 1 OFFSET 200
Query plan
 Limit  (cost=5.71..5.74 rows=1 width=4) (actual time=0.083..0.084 rows=1 loops=1)
   Buffers: shared hit=4
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using oauth_applications_pkey on public.oauth_applications  (cost=0.43..59307.64 rows=2245541 width=4) (actual time=0.063..0.075 rows=201 loops=1)
         Index Cond: ((oauth_applications.id > 3500000) AND (oauth_applications.id >= 3500001))
         Heap Fetches: 0
         Buffers: shared hit=4
         I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB'
Query ID: 3091335826974559672

Time: 0.778 ms
  - planning: 0.667 ms
  - execution: 0.111 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 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:40

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59017/commands/164200

SELECT "oauth_applications".* FROM "oauth_applications" WHERE (id > 3500000) AND "oauth_applications"."id" >= 3500001 AND "oauth_applications"."id" < 3500201 ORDER BY "oauth_applications"."id" ASC
Query plan
 Index Scan using oauth_applications_pkey on public.oauth_applications  (cost=0.43..51.63 rows=214 width=352) (actual time=0.080..42.816 rows=200 loops=1)
   Index Cond: ((oauth_applications.id > 3500000) AND (oauth_applications.id >= 3500001) AND (oauth_applications.id < 3500201))
   Buffers: shared hit=140 read=63
   I/O Timings: read=42.022 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB'
Query ID: -4068327295097922451

Time: 44.003 ms
  - planning: 1.122 ms
  - execution: 42.881 ms
    - I/O read: 42.022 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 140 (~1.10 MiB) from the buffer pool
  - reads: 63 (~504.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

On the last batch of the table, each_batch has no upper bound. The plan is the same:

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59017/commands/164201

SELECT "oauth_applications".* FROM "oauth_applications" WHERE (id > 5750000) AND "oauth_applications"."id" >= 5752400 ORDER BY "oauth_applications"."id" ASC
Query plan
 Index Scan using oauth_applications_pkey on public.oauth_applications  (cost=0.43..17.86 rows=68 width=352) (actual time=0.012..0.345 rows=114 loops=1)
   Index Cond: ((oauth_applications.id > 5750000) AND (oauth_applications.id >= 5752400))
   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: -2397267822665720454

Time: 1.551 ms
  - planning: 1.159 ms
  - execution: 0.392 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

4. Preload organizations and owners

SELECT: app/workers/authn/iam_replication/reconciliation_worker.rb:40

These queries use the organization_id and owner_id values of the loaded batch. Joe returns plans, not rows, so the real ids of a production batch are not known. The ids below are examples, so these plans show the index used, not the real timing. The User and Namespace lists are ids spread across each table, which is the case with the most disk reads.

Organizations

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59017/commands/164202

SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = 1
Query plan
 Index Scan using organizations_pkey on public.organizations  (cost=0.14..3.16 rows=1 width=78) (actual time=1.328..1.330 rows=1 loops=1)
   Index Cond: (organizations.id = 1)
   Buffers: shared hit=3 read=2
   I/O Timings: read=1.286 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 3393604169010413945

Time: 2.089 ms
  - planning: 0.711 ms
  - execution: 1.378 ms
    - I/O read: 1.286 ms
    - I/O write: 0.000 ms

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

User owners (200 ids)

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59017/commands/164203

SELECT "users"."id", "users"."email", "users"."encrypted_password", "users"."reset_password_token", "users"."reset_password_sent_at", "users"."remember_created_at", "users"."sign_in_count", "users"."current_sign_in_at", "users"."last_sign_in_at", "users"."current_sign_in_ip", "users"."last_sign_in_ip", "users"."created_at", "users"."updated_at", "users"."name", "users"."admin", "users"."projects_limit", "users"."failed_attempts", "users"."locked_at", "users"."username", "users"."can_create_group", "users"."can_create_team", "users"."state", "users"."color_scheme_id", "users"."password_expires_at", "users"."created_by_id", "users"."last_credential_check_at", "users"."avatar", "users"."confirmation_token", "users"."confirmed_at", "users"."confirmation_sent_at", "users"."unconfirmed_email", "users"."hide_no_ssh_key", "users"."admin_email_unsubscribed_at", "users"."notification_email", "users"."hide_no_password", "users"."password_automatically_set", "users"."encrypted_otp_secret", "users"."encrypted_otp_secret_iv", "users"."encrypted_otp_secret_salt", "users"."otp_required_for_login", "users"."otp_backup_codes", "users"."public_email", "users"."dashboard", "users"."project_view", "users"."consumed_timestep", "users"."layout", "users"."hide_project_limit", "users"."note", "users"."unlock_token", "users"."otp_grace_period_started_at", "users"."external", "users"."incoming_email_token", "users"."auditor", "users"."require_two_factor_authentication_from_group", "users"."two_factor_grace_period", "users"."last_activity_on", "users"."notified_of_own_activity", "users"."preferred_language", "users"."theme_id", "users"."accepted_term_id", "users"."feed_token", "users"."private_profile", "users"."roadmap_layout", "users"."include_private_contributions", "users"."commit_email", "users"."group_view", "users"."managing_group_id", "users"."first_name", "users"."last_name", "users"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret" FROM "users" WHERE "users"."id" IN (1, 40001, 80001, 120001, 160001, 200001, 240001, 280001, 320001, 360001, 400001, 440001, 480001, 520001, 560001, 600001, 640001, 680001, 720001, 760001, 800001, 840001, 880001, 920001, 960001, 1000000, 1040000, 1080000, 1120000, 1160000, 1200000, 1240000, 1280000, 1320000, 1360000, 1400000, 1440000, 1480000, 1520000, 1560000, 1600000, 1640000, 1680000, 1720000, 1760000, 1800000, 1840000, 1880000, 1920000, 1960000, 2000000, 2040000, 2080000, 2120000, 2160000, 2200000, 2240000, 2280000, 2320000, 2360000, 2400000, 2440000, 2480000, 2520000, 2560000, 2600000, 2640000, 2680000, 2720000, 2760000, 2800000, 2840000, 2880000, 2920000, 2960000, 3000000, 3040000, 3080000, 3120000, 3160000, 3200000, 3240000, 3280000, 3320000, 3360000, 3400000, 3440000, 3480000, 3520000, 3560000, 3600000, 3640000, 3680000, 3720000, 3760000, 3800000, 3840000, 3880000, 3920000, 3960000, 4000000, 4040000, 4080000, 4120000, 4160000, 4200000, 4240000, 4280000, 4320000, 4360000, 4400000, 4440000, 4480000, 4520000, 4560000, 4600000, 4640000, 4680000, 4720000, 4760000, 4800000, 4840000, 4880000, 4920000, 4960000, 5000000, 5040000, 5080000, 5120000, 5160000, 5200000, 5240000, 5280000, 5320000, 5360000, 5400000, 5440000, 5480000, 5520000, 5560000, 5600000, 5640000, 5680000, 5720000, 5760000, 5800000, 5840000, 5880000, 5920000, 5960000, 6000000, 6040000, 6080000, 6120000, 6160000, 6200000, 6240000, 6280000, 6320000, 6360000, 6400000, 6440000, 6480000, 6520000, 6560000, 6600000, 6640000, 6680000, 6720000, 6760000, 6800000, 6840000, 6880000, 6920000, 6960000, 7000000, 7040000, 7080000, 7120000, 7160000, 7200000, 7240000, 7280000, 7320000, 7360000, 7400000, 7440000, 7480000, 7520000, 7560000, 7600000, 7640000, 7680000, 7720000, 7760000, 7800000, 7840000, 7880000, 7920000, 7960000)
Query plan
 Index Scan using users_pkey on public.users  (cost=0.44..633.93 rows=200 width=1661) (actual time=3.252..433.467 rows=180 loops=1)
   Index Cond: (users.id = ANY ('{1,40001,80001,120001,160001,200001,240001,280001,320001,360001,400001,440001,480001,520001,560001,600001,640001,680001,720001,760001,800001,840001,880001,920001,960001,1000000,1040000,1080000,1120000,1160000,1200000,1240000,1280000,1320000,1360000,1400000,1440000,1480000,1520000,1560000,1600000,1640000,1680000,1720000,1760000,1800000,1840000,1880000,1920000,1960000,2000000,2040000,2080000,2120000,2160000,2200000,2240000,2280000,2320000,2360000,2400000,2440000,2480000,2520000,2560000,2600000,2640000,2680000,2720000,2760000,2800000,2840000,2880000,2920000,2960000,3000000,3040000,3080000,3120000,3160000,3200000,3240000,3280000,3320000,3360000,3400000,3440000,3480000,3520000,3560000,3600000,3640000,3680000,3720000,3760000,3800000,3840000,3880000,3920000,3960000,4000000,4040000,4080000,4120000,4160000,4200000,4240000,4280000,4320000,4360000,4400000,4440000,4480000,4520000,4560000,4600000,4640000,4680000,4720000,4760000,4800000,4840000,4880000,4920000,4960000,5000000,5040000,5080000,5120000,5160000,5200000,5240000,5280000,5320000,5360000,5400000,5440000,5480000,5520000,5560000,5600000,5640000,5680000,5720000,5760000,5800000,5840000,5880000,5920000,5960000,6000000,6040000,6080000,6120000,6160000,6200000,6240000,6280000,6320000,6360000,6400000,6440000,6480000,6520000,6560000,6600000,6640000,6680000,6720000,6760000,6800000,6840000,6880000,6920000,6960000,7000000,7040000,7080000,7120000,7160000,7200000,7240000,7280000,7320000,7360000,7400000,7440000,7480000,7520000,7560000,7600000,7640000,7680000,7720000,7760000,7800000,7840000,7880000,7920000,7960000}'::integer[]))
   Buffers: shared hit=323 read=451 dirtied=6
   WAL: records=9 fpi=6 bytes=47046
   I/O Timings: read=430.273 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: 4529897401118444130

Time: 438.530 ms
  - planning: 4.957 ms
  - execution: 433.573 ms
    - I/O read: 430.273 ms
    - I/O write: 0.000 ms

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

Namespace owners (50 ids)

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/59017/commands/164204

SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id", "namespaces"."state" FROM "namespaces" WHERE "namespaces"."id" IN (1, 50001, 100001, 150001, 200001, 250001, 300001, 350001, 400001, 450001, 500001, 550001, 600001, 650001, 700001, 750001, 800001, 850001, 900001, 950001, 1000000, 1050000, 1100000, 1150000, 1200000, 1250000, 1300000, 1350000, 1400000, 1450000, 1500000, 1550000, 1600000, 1650000, 1700000, 1750000, 1800000, 1850000, 1900000, 1950000, 2000000, 2050000, 2100000, 2150000, 2200000, 2250000, 2300000, 2350000, 2400000, 2450000)
Query plan
 Index Scan using namespaces_pkey on public.namespaces  (cost=0.57..140.26 rows=50 width=356) (actual time=4.184..111.922 rows=44 loops=1)
   Index Cond: (namespaces.id = ANY ('{1,50001,100001,150001,200001,250001,300001,350001,400001,450001,500001,550001,600001,650001,700001,750001,800001,850001,900001,950001,1000000,1050000,1100000,1150000,1200000,1250000,1300000,1350000,1400000,1450000,1500000,1550000,1600000,1650000,1700000,1750000,1800000,1850000,1900000,1950000,2000000,2050000,2100000,2150000,2200000,2250000,2300000,2350000,2400000,2450000}'::integer[]))
   Buffers: shared hit=123 read=117
   I/O Timings: read=111.416 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: 9184974384277696495

Time: 114.426 ms
  - planning: 2.449 ms
  - execution: 111.977 ms
    - I/O read: 111.416 ms
    - I/O write: 0.000 ms

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

Conclusion

  • All queries use oauth_applications_pkey, organizations_pkey, users_pkey or namespaces_pkey. No sequential scan, and no new index is needed.
  • The oauth_applications queries run on real id ranges. They read at most 203 buffers and take less than 45 ms to load.
  • The preload queries use example ids, so their timing is not representative. They are primary key lookups with at most 200 ids, once per batch.

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.

🤖 Drafted by Claude

Edited by Daniele Bracciani

Merge request reports

Loading
Loading