BBM: Clear stale group 2FA requirement from users

Related to !248677 (merged)

Summary

!248677 (merged)

What does this MR do and why?

Queues a batched background migration that clears the cached users.require_two_factor_authentication_from_group value for users where no group in any hierarchy they belong to enforces 2FA anymore.

The cached value is only recalculated by membership and group-setting events. The group-side recalculation has always excluded members with the Minimal Access role, which had no effect until that role started counting toward the value. From then on, a group withdrawing its 2FA enforcement left those members carrying a stale true, blocking them with the 2FA setup page even though nothing requires it.

The affected population cannot be selected by membership, since roles can change after the value goes stale (how an RFH was triggered), so the migration selects by the symptom. It batches over users currently holding the requirement (users.require_two_factor_authentication_from_group: true), through a temporary partial index.

The migration is deliberately one-directional:

• It only clears, never sets. The check reads the raw namespaces.require_two_factor_authentication column across all of the user's memberships, with no plan or license gating, so it counts strictly more groups as enforcing than the runtime calculation does. When even this looser check finds nothing, the runtime value can only be false, so clearing is safe.
• Users are left untouched when any group in a hierarchy they belong to still enforces 2FA, even where it would not count at runtime. Users with no remaining group membership are cleared.

!248677 (merged) closed the recalculation gap going forward, preventing new stale values and support escalations like https://gitlab.com/gitlab-com/request-for-help/-/work_items/5178. This migration clears the users whose cached value went stale before that fix shipped.

Database Review

Query plans and the population census are in the comments.

db:check-migrations

https://gitlab.com/gitlab-org/gitlab/-/jobs/15981499302

Temporary partial index

CREATE INDEX tmp_idx_users_on_id_where_two_factor_required_from_group
ON users USING btree (id)
WHERE require_two_factor_authentication_from_group = true AND user_type = 0;

Time Estimation

Per the docs, it would take:

287,795 flagged human users

287,795 rows / 1,000 per batch = ~288 batches

288 batches * 2 minutes (default interval) = ~9.6 hours => ~10 hours

The db:gitlabcom-database-testing estimate of ~1 month extrapolates from the full users tuple count (25,290,000 rows / 1,000 per batch = 25,290 batches).

Post Deployment Tracking

Steps

1. Verify deployment with chatops: /chatops run batched_background_migrations list --job-class-name ClearStaleTwoFactorRequirementFromUsers
2. Use Kibana with json.message: Stale user group 2FA enforcement cleared. on the pubsub-sidekiq-inf-gprd* data view to watch progress. Expected total on GitLab.com: ~553 cleared users.
3. If something goes wrong, pause with /chatops run batched_background_migrations pause MIGRATION_ID.

How to set up and validate locally

1. Create a group enforcing 2FA with a member, withdraw the enforcement, and plant the stale value:

   group = Group.find_by_full_path('<group>')
   user  = User.find_by_username('<user>')
   
   group.add_member(user, Gitlab::Access::MINIMAL_ACCESS)
   User.where(id: user.id).update_all(require_two_factor_authentication_from_group: true, two_factor_grace_period: 0)
   group.update_column(:require_two_factor_authentication, false)
   
   user.reload.require_two_factor_authentication_from_group # => true

2. Optionally change the member role or remove the membership entirely. The migration clears the value in every case.

3. Run the migration and confirm the value is cleared:

   Gitlab::BackgroundMigration::ClearStaleTwoFactorRequirementFromUsers.new(
     batch_table: :users, batch_column: :id, sub_batch_size: 100, pause_ms: 0,
     connection: ApplicationRecord.connection
   ).perform
   
   user.reload.require_two_factor_authentication_from_group # => false

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.