Delete policy approval rules of configurations that no longer apply

What does this MR do and why?

Resolves #637674

A project can keep merge request approval policy rules from a security policy configuration that no longer applies to it. Those rules are copied onto every new merge request and require approvals from unrelated groups, but they don't show in Secure > Policies or the approval rules API.

Nothing removes them today:

  • On project transfer, Security::Policies::ProjectTransferWorker deletes rules only for the configurations of the new namespace and the old one, resolved when the worker runs. Rules from any other configuration (left by an earlier transfer, or by an old namespace that has since moved) survive.
  • An inherited resync runs Security::Policies::GroupProjectTransferWorker, which only unlinks configurations the project still has policy links to. Rules whose links are already gone are never reached.

Behind the cleanup_inapplicable_policy_approval_rules flag, both workers now also delete the rules, reads, violations and license policies of any configuration that still has project approval rules or reads on the project but no longer applies to it. The deletion goes through one shared method, delete_scan_result_policy_rules_for_project, and works per configuration, so it doesn't depend on the rows matching a current approval policy rule.

That shared method deletes merge request rules before the reads. If a job fails partway, the reads and project rules are still there for the retry to find the configuration again.

It also batches the merge request rule cleanup over the project's rules only (it used to walk every rule of the configuration), so the cost follows the project, not the configuration's size.

Reviewable LOC: 55 (plus 119 in tests)

Database review

The change adds no migration, index or new table. New SQL comes from the with_scan_result_policy_rules_for_project scope (one query per worker run, behind cleanup_inapplicable_policy_approval_rules) and from delete_scan_result_policy_rules_for_project, which now also runs for configurations that no longer apply to the project. That method only wraps the existing per-configuration delete helpers, so their statements are unchanged. The DELETEs run in batches through EachBatch (delete_in_batches, each_batch) with the same predicates as the SELECTs below.

Plans are from the production-sized gitlab-production-main Database Lab clone, for one project that has policy rules from one configuration. <project_id> and <configuration_id> stand for that project and configuration. Cold is the first run after a clone reset, warm is a repeat of the same query. Each timing links to its Database Lab console entry.

Query Cold Warm
Find configurations, with NOT IN 81 ms, 910 buffers (907 read) 6.9 ms, 910 hit
Find configurations, without NOT IN 9.7 ms, 910 hit (pages already loaded by the run above) 6.0 ms, 910 hit
Merge request rules 7.4 ms, 7 buffers 0.10 ms, 7 hit
License policies 6.6 ms, 38 buffers 0.11 ms, 38 hit
Policy violations 2.3 ms, 38 buffers 0.10 ms, 38 hit
Policy reads 0.13 ms, 14 hit 0.11 ms, 14 hit
Project approval rules 4.4 ms, 14 buffers 0.13 ms, 14 hit

Find configurations with leftover rules (new scope)

Used by ProjectTransferWorker (without the NOT IN) and GroupProjectTransferWorker (with NOT IN, the applicable configuration ids). Runs once per worker run, behind the feature flag.

SELECT "security_orchestration_policy_configurations".*
FROM "security_orchestration_policy_configurations"
WHERE ("security_orchestration_policy_configurations"."id" IN (
          SELECT "scan_result_policies"."security_orchestration_policy_configuration_id"
          FROM "scan_result_policies" WHERE "scan_result_policies"."project_id" = <project_id>)
       OR "security_orchestration_policy_configurations"."id" IN (
          SELECT "approval_project_rules"."security_orchestration_policy_configuration_id"
          FROM "approval_project_rules" WHERE "approval_project_rules"."project_id" = <project_id>))
  AND "security_orchestration_policy_configurations"."id" NOT IN (<applicable_configuration_ids>)
Seq Scan on security_orchestration_policy_configurations  (~48k rows removed by filter)
  Filter: (id <> ALL (...)) AND ((id = ANY (hashed SubPlan 1)) OR (id = ANY (hashed SubPlan 2)))
  SubPlan 1: Index Scan using index_scan_result_policies_on_project_id (project_id = <project_id>)
  SubPlan 2: Index Scan using index_approval_project_rules_on_project_id_and_rule_type (project_id = <project_id>)
Cold: 81 ms, 907 buffers read. Warm: 6.9 ms, 910 buffers hit.

The sequential scan covers the whole (small, ~48k rows) configurations table. Both subqueries use the project_id indexes. Without the NOT IN the plan is the same.

Deleting the rules of one inapplicable configuration

Runs once per configuration found above. Each statement below is shown as the SELECT form of its DELETE.

Merge request rules (open and draft merge requests only; state_id != 3 excludes merged ones):

SELECT "approval_merge_request_rules".* FROM "approval_merge_request_rules"
INNER JOIN "merge_requests" ON "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id"
WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = <configuration_id>
  AND "merge_requests"."state_id" != 3
  AND "merge_requests"."target_project_id" = <project_id>
Nested Loop
  -> Index Only Scan using idx_mrs_on_target_id_and_created_at_and_state_id on merge_requests
       (target_project_id = <project_id>), Filter: state_id <> 3
  -> Index Scan using idx_approval_merge_request_rules_on_mr_id_config_id_and_id on approval_merge_request_rules
       (merge_request_id = merge_requests.id AND security_orchestration_policy_configuration_id = <configuration_id>)
Cold: 7.4 ms, 7 buffers. Warm: 0.10 ms, 7 hit. The sample run matched no rows, so the inner lookup shows as never executed.

The inner lookup is one index probe per open merge request of the project.

License policies:

SELECT "software_license_policies".* FROM "software_license_policies"
WHERE "software_license_policies"."project_id" = <project_id>
  AND "software_license_policies"."scan_result_policy_id" IN (
    SELECT "scan_result_policies"."id" FROM "scan_result_policies"
    WHERE "scan_result_policies"."security_orchestration_policy_configuration_id" = <configuration_id>
      AND "scan_result_policies"."project_id" = <project_id>)
Nested Loop
  -> Index Scan using index_scan_result_policies_on_configuration_action_and_rule_idx (configuration_id, project_id)
  -> Index Scan using idx_software_license_policies_unique_on_custom_license_project (project_id, scan_result_policy_id)
Cold: 6.6 ms, 38 buffers. Warm: 0.11 ms, 38 hit.

Policy violations:

SELECT "scan_result_policy_violations".* FROM "scan_result_policy_violations"
WHERE "scan_result_policy_violations"."scan_result_policy_id" IN (
  SELECT "scan_result_policies"."id" FROM "scan_result_policies"
  WHERE "scan_result_policies"."security_orchestration_policy_configuration_id" = <configuration_id>
    AND "scan_result_policies"."project_id" = <project_id>)
Nested Loop
  -> Index Scan using index_scan_result_policies_on_configuration_action_and_rule_idx (configuration_id, project_id)
  -> Index Scan using index_scan_result_policy_violations_on_policy_and_merge_request (scan_result_policy_id)
Cold: 2.3 ms, 38 buffers. Warm: 0.10 ms, 38 hit.

Policy reads (delete_all; the foreign key cascade removes the linked project rules):

SELECT "scan_result_policies".* FROM "scan_result_policies"
WHERE "scan_result_policies"."security_orchestration_policy_configuration_id" = <configuration_id>
  AND "scan_result_policies"."project_id" = <project_id>
Index Scan using index_scan_result_policies_on_configuration_action_and_rule_idx (configuration_id, project_id)
Cold: 0.13 ms, 14 buffers hit. Warm: 0.11 ms, 14 hit.

Project approval rules (anything the cascade did not cover):

SELECT "approval_project_rules".* FROM "approval_project_rules"
WHERE "approval_project_rules"."security_orchestration_policy_configuration_id" = <configuration_id>
  AND "approval_project_rules"."project_id" = <project_id>
Index Scan using index_approval_project_rules_on_project_id_config_id_and_id (configuration_id, project_id)
Cold: 4.4 ms, 14 buffers. Warm: 0.13 ms, 14 hit.

Batching of the merge request rules

delete_merge_request_rules_for_project used to run each_batch over all merge request rules of the configuration (5,000 per batch) and apply the project predicate inside each batch DELETE. A configuration can have millions of rules across projects, so the number of batches, and one batch-bounds query each, followed the configuration's size, not the project's. This MR now batches over the project's rules only: the join on merge_requests.target_project_id is part of the batched relation, so the cost follows the project's merge requests. The rows deleted are the same.

New batch queries, shown with the bound columns Rails adds (<start_id> is the previous batch's end). Statements 1 and 2 find the batch bounds; statement 3 is the SELECT form of the batch DELETE.

-- 1. first batch start
SELECT "approval_merge_request_rules"."id" FROM "approval_merge_request_rules"
INNER JOIN "merge_requests" ON "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id"
WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = <configuration_id>
  AND "merge_requests"."state_id" != 3
  AND "merge_requests"."target_project_id" = <project_id>
ORDER BY "approval_merge_request_rules"."id" ASC LIMIT 1

-- 2. next batch end (one per further batch)
SELECT "approval_merge_request_rules"."id" FROM "approval_merge_request_rules"
INNER JOIN "merge_requests" ON "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id"
WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = <configuration_id>
  AND "merge_requests"."state_id" != 3
  AND "merge_requests"."target_project_id" = <project_id>
  AND "approval_merge_request_rules"."id" >= <start_id>
ORDER BY "approval_merge_request_rules"."id" ASC LIMIT 1 OFFSET 5000

-- 3. batch rows (the DELETE removes these ids)
SELECT "approval_merge_request_rules"."id" FROM "approval_merge_request_rules"
INNER JOIN "merge_requests" ON "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id"
WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = <configuration_id>
  AND "merge_requests"."state_id" != 3
  AND "merge_requests"."target_project_id" = <project_id>
  AND "approval_merge_request_rules"."id" >= <start_id>
Statements 1 and 2 (3 has the same plan without the Sort and Limit):
Limit
  -> Sort (id)
       -> Nested Loop
            -> Index Only Scan using idx_mrs_on_target_id_and_created_at_and_state_id on merge_requests
                 (target_project_id = <project_id>), Filter: state_id <> 3
            -> Index Only Scan using idx_approval_merge_request_rules_on_mr_id_config_id_and_id on approval_merge_request_rules
                 (merge_request_id = merge_requests.id AND security_orchestration_policy_configuration_id = <configuration_id> [AND id >= <start_id>])

Plans are for a project with open merge requests and matching rules, on a configuration that has millions of merge request rules in total. Both indexes already exist.

Statement Cold Warm
1. first batch start 1.3 s, ~2.4k buffers (1.3k read) 2.4 ms, ~2.4k hit
2. next batch end 2.5 ms, ~2.4k hit (pages loaded by statement 1) 2.5 ms, ~2.4k hit
3. batch rows 2.3 ms, ~2.4k hit (pages loaded by statement 1) 2.4 ms, ~2.4k hit

The cold first run is the I/O of reading the project's merge request index entries and rule entries once. For a project without matching rules, statement 1 returns no row and the batching stops after that one query.

For comparison, the previous batching over the whole configuration ran this bounds query once per 5,000 rules of the configuration, about 107 ms each on a large configuration with partly cached pages (console link); that query is no longer issued.

All other queries are index-backed and bounded by one project and one configuration. Database review will be requested after the backend review.

References

  • Rollout issue: #637675
  • Follow-up to !257724 (merged), which made the inherited resync reconcile stale policy links but only reaches configurations that still have links.

Screenshots or screen recordings

Not applicable, backend-only change.

How to set up and validate locally

  1. Pick a project with policy approval rules and a group configuration that does not apply to it, then in a Rails console:
    project = Project.find(<project_id>)
    stale = Security::OrchestrationPolicyConfiguration.find(<unrelated_group_configuration_id>)
    read = Security::ScanResultPolicyRead.create!(project: project, security_orchestration_policy_configuration: stale,
      orchestration_policy_idx: 99, rule_idx: 0, match_on_inclusion_license: false)
    ApprovalProjectRule.create!(project: project, name: 'stale', report_type: :any_merge_request, rule_type: :report_approver,
      approvals_required: 1, security_orchestration_policy_configuration: stale, scan_result_policy_read: read)
  2. Run Security::Policies::GroupProjectTransferWorker.new.perform(project.id, User.admins.first.id), or resync the project's inherited policies.
  3. The stale rule and read are gone; rules of configurations that still apply are kept.
  4. With Feature.disable(:cleanup_inapplicable_policy_approval_rules), the stale rows stay.

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.

Context for LLM agents

Rejected alternatives

  • Adding approval_merge_request_rules to the lookup scope. Those rows aren't filtered by merge state and can number in the hundreds of thousands per project. Instead, merge request rules are deleted first, so a configuration stays discoverable through its reads or project rules until its merge request rules are gone.
  • Unlinking through LinkProjectService for configurations found through rule rows. That path deletes by approval_policy_rule_id, so legacy rows that don't match a current approval policy rule would survive.
  • A data migration. A correct cleanup needs the per-configuration application logic, and self-managed instances wouldn't get it.

Invariants a follow-up must keep

  • Never delete rows of a configuration in project.all_security_orchestration_policy_configurations(include_invalid: true). That set includes CSP and configurations with invalid YAML.
  • delete_scan_result_policy_rules_for_project order: merge request rules, then license policies and violations (through the reads), then reads, then project rules.
Edited by Alan (Maciej) Paruszewski

Merge request reports

Loading
Loading