Persist approval_policy_rule_id for scan_result_policy_violations

What does this MR do and why?

This MR sets approval_policy_rule_id to scan_result_policy_violations table. This is done to replace scan_result_policy_read_id with the new Use database read model for merge request appr... (&9971).

Currently, we do not store the real policy index from YAML in scan_result_policies table. We take the active policies, check policy scope and store the index from 0. Whereas in security_policies we store both disabled and unscoped policies too, so we need to query security_policies using the real index. This MR adds real_policy_index to Security::ScanResultPolicyRead to get the real policy index from security_policies table.

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.

How to set up and validate locally

• Enable security_policies_sync and security_policies_sync_group feature flags
• Create a project and create 2 approval policies for the project from Secure -> Policies:

name: Pre-Existing security findings
description: ''
enabled: true
actions:
- type: require_approval
  approvals_required: 1
  role_approvers:
  - maintainer
- type: send_bot_message
  enabled: true
rules:
- type: scan_finding
  scanners: []
  vulnerabilities_allowed: 0
  severity_levels: []
  vulnerability_states:
  - detected
  - confirmed
  - dismissed
  - resolved
  branch_type: protected
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
fallback_behavior:
  fail: closed

name: Newly Detected findings
description: ''
enabled: true
actions:
- type: require_approval
  approvals_required: 1
  role_approvers:
  - maintainer
- type: send_bot_message
  enabled: true
rules:
- type: scan_finding
  scanners: []
  vulnerabilities_allowed: 0
  severity_levels: []
  vulnerability_states: []
  branch_type: protected
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
fallback_behavior:
  fail: closed

• Update the project's .gitlab-ci.yml to run secret detection and dependency scanning jobs:

stages:
  - test

include:
  - template: Jobs/Secret-Detection.gitlab-ci.yml


dependency_scanning:
  image: "busybox:latest"
  stage: test
  allow_failure: true
  artifacts:
    reports:
      dependency_scanning: gl-dependency-scanning-report.json
    paths: [gl-dependency-scanning-report.json]
  dependencies: []
  script:
    - wget -O gl-dependency-scanning-report.json https://gitlab.com/gitlab-org/govern/security-policies/sashis-test-group/test-multi-pipeline-simple/-/raw/main/gl-dependency-scanning-report.json

• Create an MR with a leaked secret in .env

AWS_TOKEN=AKIAZYONPI3G4JNCCWGQ

• After the pipeline succeeds, verify the policy violation bot comment with violations. Also, verify that the violations have approval_policy_rule_id from rails c:

mr = MergeRequest.find(<id>)
mr.scan_result_policy_violaitons.map(&:approval_policy_rule_id)