Skip approval rule cleanup for never-linked projects

What does this MR do and why?

SyncProjectService#should_unlink_policy? now skips the unlink for approval policies that are not linked to the project.

A policy scope or status change enqueues one Security::SyncProjectPolicyWorker per project of the policy configuration (Security::SyncPolicyWorker#sync_policy_for_projects over all_project_ids). Out-of-scope projects went through unlink_policy even when the policy was never linked to them. Each unlink calls Security::Policy#delete_approval_merge_request_rules_for_project. That method iterates over all approval_merge_request_rules of the whole configuration in batches of 5000 and only filters by project inside each DELETE. The unlink also triggers sync_merge_request_rules and policy violation comment updates for nothing.

Approval rules are only created after the project is linked (link_policy, sync_approval_policy_diff in BaseProjectPolicyService). A never-linked project has nothing to clean up.

Impact on GitLab.com

Adding one project to an approval policy's project-list scope in a group with about 99k projects:

  • enqueued about 98k sync jobs
  • at the 300 concurrency limit, jobs ran about 35s median each (p99 about 100s)
  • about 160k DB-seconds per 15 minutes
  • high CPU pressure on the main database primary for hours
  • hundreds of thousands of downstream jobs (violation comments, audit events)
Scope and known gaps
  • Approval policies only. Scan execution project schedules can be created without a project link (Security::SecurityOrchestrationPolicies::RuleScheduleService), so their cleanup still runs for every out-of-scope project. It is cheap (project-scoped delete).
  • The unlink deletes the link before the rules (to avoid a rule re-sync race). If a job is interrupted in between, the retry still cleans up: the skip also requires that no approval project rule or open merge request approval rule of the policy remains in the project.
  • Security::DeleteApprovalPolicyRulesWorker is enqueued less often. The end of unlink_policy is the only place that enqueues it, once no project link remains for the policy's rules. Never-linked projects used to reach that point on every scope or status change. Now a policy linked to no projects keeps its soft-deleted approval_policy_rules rows until a linked project is unlinked. The leftover rows are inert, so there is no code change.
  • No feature flag.

References

  • Scopes the merge request rule delete to the project (independent): !261748 (merged)
  • Longer term, only fan out to projects whose scope status changed: #607604

Changelog: performance (EE).

How to set up and validate locally

New specs fail on master and pass with this change:

  • scope change on a never-linked project: Security::ScanResultPolicies::ApprovalRules::DeleteService is not instantiated
  • policy disabled on a never-linked project: DeleteService is not instantiated
  • interrupted unlink (link already deleted, rules remain): the retry deletes the rules
  • interrupted unlink in the merge request rule loop (link, read and project rules already deleted, merge request rule remains): the retry deletes the merge request rules

The scope change on a linked project example now creates an approval project rule and asserts it is removed. It passes on master too, but it guards the deletion and not only the DeleteService.new call.

Also fixed a stub in the "policy gets enabled / unscoped" context that left scope_applicable? true.

bundle exec rspec \
  ee/spec/services/security/security_orchestration_policies/sync_project_service_spec.rb \
  ee/spec/workers/security/sync_project_policy_worker_spec.rb \
  ee/spec/workers/security/sync_policy_worker_spec.rb

134 examples across the service and both sync worker specs pass.

Database

New query, run once per out-of-scope project of an approval policy that is not linked to it (SyncProjectService#approval_rules_remain_in_project?):

SELECT 1 FROM "approval_project_rules"
WHERE "approval_project_rules"."project_id" = $1
  AND "approval_project_rules"."approval_policy_rule_id" IN (
    SELECT "approval_policy_rules"."id" FROM "approval_policy_rules"
    WHERE "approval_policy_rules"."security_policy_id" = $2)
LIMIT 1

Plan on production-size data (gitlab-production-main thin clone, EXPLAIN (ANALYZE, BUFFERS), project and policy values are internal ids and omitted). Never-linked project (no rows, the common case): 1.5 ms execution, 16 buffers (14 hit, 2 read).

Limit  (actual time=1.503..1.506 rows=0 loops=1)
  ->  Nested Loop  (actual time=1.502..1.502 rows=0 loops=1)
        Join Filter: (r.approval_policy_rule_id = approval_policy_rules.id)
        ->  Index Only Scan using idx_approval_policy_rules_security_policy_id_id on approval_policy_rules
              Index Cond: (security_policy_id = $2)   (rows=2)
        ->  Index Scan using index_approval_project_rules_on_project_id_and_rule_type on approval_project_rules r
              Index Cond: (project_id = $1)           (rows=0 loops=2)

Project with leftover rules (match): same plan shape, 8 ms with a cold cache (17 buffers). It uses project_id and security_policy_id indexes, so no new index is needed.

A second query runs only if the project rule check above finds nothing. It covers an unlink that died in the merge request rule loop, after the project rules were already removed by the cascade (SyncProjectService#approval_rules_remain_in_project?). It reuses the scopes of delete_approval_merge_request_rules_for_project, so it matches exactly what that delete would remove:

SELECT 1 AS one FROM "approval_merge_request_rules"
INNER JOIN "merge_requests" ON "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id"
WHERE "merge_requests"."state_id" != 3
  AND "merge_requests"."target_project_id" = $1
  AND "approval_merge_request_rules"."approval_policy_rule_id" IN (
    SELECT "approval_policy_rules"."id" FROM "approval_policy_rules"
    WHERE "approval_policy_rules"."security_policy_id" = $2)
LIMIT 1

Plans on production-size data (gitlab-production-main thin clone, EXPLAIN (ANALYZE, BUFFERS), internal ids omitted). The policy has 2 rules and about 8.6k approval merge request rules in total.

Typical never-linked project (no rows): planner starts from the project's merge requests (idx_mrs_on_target_id_and_created_at_and_state_id), 10 ms execution, 15 buffers. No rule lookup runs because the project has no open merge request.

Limit  (actual time=10.444..10.446 rows=0 loops=1)
  ->  Nested Loop  (rows=0)
        Join Filter: (r.approval_policy_rule_id = approval_policy_rules.id)
        ->  Index Only Scan using idx_approval_policy_rules_security_policy_id_id on approval_policy_rules  (rows=2)
        ->  Nested Loop  (rows=0 loops=2)
              ->  Index Only Scan using idx_mrs_on_target_id_and_created_at_and_state_id on merge_requests
                    Index Cond: (target_project_id = $1)   Filter: (state_id <> 3)   (rows=0)
              ->  Index Scan using idx_approval_merge_request_rules_on_mr_id_config_id_and_id on approval_merge_request_rules  (never executed)

Project with leftover rules (match): same plan, 30 ms cold (22 buffers), the first matching row ends the scan.

Worst case, a very large project with many open merge requests (a no-match run): the planner flips to the policy side. It reads the policy's merge request rules through index_approval_merge_request_rules_on_approval_policy_rule_id and filters by project via idx_merge_requests_on_unmerged_state_id: 38 ms with a warm cache (about 46k buffer hits, 8.6k rule rows visited). The first, cold run took 14 s, nearly all of it disk reads on the clone. The cost is bounded by the number of merge request rules of this one policy, not by the number of projects. No new index is added.

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.

Edited by Alan (Maciej) Paruszewski

Merge request reports

Loading
Loading