Fix duplicated policy approval rules in rule details (!141021) · Merge requests · GitLab.org / GitLab

Martin Čavoj requested to merge 425341-security-approvals-are-duplicated-in-repository-settings-branch-rules-details into master Jan 04, 2024

What does this MR do and why?

This MR fixes duplicated policy approval rules in rule details. An example of this issue can be seen in this project. It also hides any_merge_request rules from the list of user-defined approval rules, similarly to scan_finding and license_scanning rules.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Before	After
Approval rules stemming from policies were duplicated if they contained multiple policy rules
`any_merge_request` policy rules were shown under user-defined approval rules, whereas `scan_finding` and `license_scanning` were excluded

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

Example below:

Create a project
Go to Secure -> Policies

Create a new scan result policy. Go to .yaml mode and use the following YAML:

type: scan_result_policy
name: Policy rules
description: ''
enabled: true
rules:
  - type: scan_finding
    scanners: []
    vulnerabilities_allowed: 0
    severity_levels: []
    vulnerability_states: []
    branch_type: protected
  - type: license_finding
    match_on_inclusion: true
    license_types:
      - BSD 3-Clause "New" or "Revised" License
    license_states:
      - newly_detected
    branch_type: protected
  - type: any_merge_request
    branch_type: protected
    commits: unsigned
actions:
  - type: require_approval
    approvals_required: 1
    role_approvers:
      - developer
approval_settings:
  block_branch_modification: true
  prevent_pushing_and_force_pushing: true
  prevent_approval_by_author: true
  prevent_approval_by_commit_author: true
  remove_approvals_with_new_commit: true
  require_password_to_approve: false

Configure with merge request & merge
Go to Settings -> Repository -> Branch rules -> View details for main branch
Verify there is only one Policy rules approval rule visible
Additionally, go to Settings -> Merge requests and create a few approval settings under "Merge request approvals" section.
Verify that these rules are not grouped and are all visible

Database

`protected_branch.approval_project_rules_with_unique_policies`:

Previous query:

SELECT "approval_project_rules".* FROM "approval_project_rules" INNER JOIN "approval_project_rules_protected_branches" ON "approval_project_rules"."id" = "approval_project_rules_protected_branches"."approval_project_rule_id" WHERE "approval_project_rules_protected_branches"."protected_branch_id" = 84449947

Previous plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25093/commands/79698

New query:

SELECT "approval_project_rules".* FROM "approval_project_rules" INNER JOIN "approval_project_rules_protected_branches" ON "approval_project_rules"."id" = "approval_project_rules_protected_branches"."approval_project_rule_id" WHERE "approval_project_rules_protected_branches"."protected_branch_id" = 84449947 AND ("approval_project_rules"."security_orchestration_policy_configuration_id" IS NULL OR "approval_project_rules"."id" IN (SELECT MIN("approval_project_rules"."id") AS id FROM "approval_project_rules" INNER JOIN "approval_project_rules_protected_branches" ON "approval_project_rules_protected_branches"."approval_project_rule_id" = "approval_project_rules"."id" INNER JOIN "protected_branches" ON "protected_branches"."id" = "approval_project_rules_protected_branches"."protected_branch_id" WHERE "protected_branches"."id" = 84449947 GROUP BY "approval_project_rules"."security_orchestration_policy_configuration_id", "approval_project_rules"."orchestration_policy_idx"))

New plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25093/commands/79702

BranchRulesResolver preloading

Query:

SELECT "approval_project_rules_protected_branches"."approval_project_rule_id" AS t0_r0, "approval_project_rules_protected_branches"."protected_branch_id" AS t0_r1, "approval_project_rules"."id" AS t1_r0, "approval_project_rules"."created_at" AS t1_r1, "approval_project_rules"."updated_at" AS t1_r2, "approval_project_rules"."project_id" AS t1_r3, "approval_project_rules"."approvals_required" AS t1_r4, "approval_project_rules"."name" AS t1_r5, "approval_project_rules"."rule_type" AS t1_r6, "approval_project_rules"."scanners" AS t1_r7, "approval_project_rules"."vulnerabilities_allowed" AS t1_r8, "approval_project_rules"."severity_levels" AS t1_r9, "approval_project_rules"."report_type" AS t1_r10, "approval_project_rules"."vulnerability_states" AS t1_r11, "approval_project_rules"."orchestration_policy_idx" AS t1_r12, "approval_project_rules"."applies_to_all_protected_branches" AS t1_r13, "approval_project_rules"."security_orchestration_policy_configuration_id" AS t1_r14, "approval_project_rules"."scan_result_policy_id" AS t1_r15 FROM "approval_project_rules_protected_branches" LEFT OUTER JOIN "approval_project_rules" ON "approval_project_rules"."id" = "approval_project_rules_protected_branches"."approval_project_rule_id" WHERE ("approval_project_rules"."security_orchestration_policy_configuration_id" IS NULL OR "approval_project_rules"."id" IN (SELECT MIN("approval_project_rules"."id") AS id FROM "approval_project_rules" INNER JOIN "approval_project_rules_protected_branches" ON "approval_project_rules_protected_branches"."approval_project_rule_id" = "approval_project_rules"."id" INNER JOIN "protected_branches" ON "protected_branches"."id" = "approval_project_rules_protected_branches"."protected_branch_id" WHERE "protected_branches"."id" = 84449947 GROUP BY "approval_project_rules"."security_orchestration_policy_configuration_id", "approval_project_rules"."orchestration_policy_idx")) AND "approval_project_rules_protected_branches"."protected_branch_id" = 84449947

Plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25093/commands/79703

Related to #425341 (closed)

Edited Jan 05, 2024 by Martin Čavoj

Fix duplicated policy approval rules in rule details