Annotate compliance framework IDs in policy annotations

What does this MR do and why?

This MR adds support for annotating compliance framework IDs in policy.yml.

When annotating policy.yml, some fields may contain references to compliance frameworks by IDs. To handle this, we introduce a new service: ComplianceManagement::FrameworksFinder, which queries compliance frameworks by their IDs.

Currently, the finder supports querying only via the ids param, as that is the only requirement for our current use case. However, the service can be easily extended for future needs.

This MR is a split from !190355 (merged). Please refer to the parent MR for additional context and background.

References

!190355 (diffs, comment 2511749189)

Screenshots

SQL

The query plan is generated using namespace_id = 9970 and compliance framework ids from GitLab.org namespace.

Query Plan

EXPLAIN
SELECT
  "compliance_management_frameworks"."id" AS t0_r0,
  "compliance_management_frameworks"."name" AS t0_r1,
  "compliance_management_frameworks"."description" AS t0_r2,
  "compliance_management_frameworks"."color" AS t0_r3,
  "compliance_management_frameworks"."namespace_id" AS t0_r4,
  "compliance_management_frameworks"."pipeline_configuration_full_path" AS t0_r5,
  "compliance_management_frameworks"."created_at" AS t0_r6,
  "compliance_management_frameworks"."updated_at" AS t0_r7,
  "namespaces"."id" AS t1_r0,
  "namespaces"."name" AS t1_r1,
  "namespaces"."path" AS t1_r2,
  "namespaces"."owner_id" AS t1_r3,
  "namespaces"."created_at" AS t1_r4,
  "namespaces"."updated_at" AS t1_r5,
  "namespaces"."type" AS t1_r6,
  "namespaces"."description" AS t1_r7,
  "namespaces"."avatar" AS t1_r8,
  "namespaces"."membership_lock" AS t1_r9,
  "namespaces"."share_with_group_lock" AS t1_r10,
  "namespaces"."visibility_level" AS t1_r11,
  "namespaces"."request_access_enabled" AS t1_r12,
  "namespaces"."ldap_sync_status" AS t1_r13,
  "namespaces"."ldap_sync_error" AS t1_r14,
  "namespaces"."ldap_sync_last_update_at" AS t1_r15,
  "namespaces"."ldap_sync_last_successful_update_at" AS t1_r16,
  "namespaces"."ldap_sync_last_sync_at" AS t1_r17,
  "namespaces"."description_html" AS t1_r18,
  "namespaces"."lfs_enabled" AS t1_r19,
  "namespaces"."parent_id" AS t1_r20,
  "namespaces"."shared_runners_minutes_limit" AS t1_r21,
  "namespaces"."repository_size_limit" AS t1_r22,
  "namespaces"."require_two_factor_authentication" AS t1_r23,
  "namespaces"."two_factor_grace_period" AS t1_r24,
  "namespaces"."cached_markdown_version" AS t1_r25,
  "namespaces"."project_creation_level" AS t1_r26,
  "namespaces"."runners_token" AS t1_r27,
  "namespaces"."file_template_project_id" AS t1_r28,
  "namespaces"."saml_discovery_token" AS t1_r29,
  "namespaces"."runners_token_encrypted" AS t1_r30,
  "namespaces"."custom_project_templates_group_id" AS t1_r31,
  "namespaces"."auto_devops_enabled" AS t1_r32,
  "namespaces"."extra_shared_runners_minutes_limit" AS t1_r33,
  "namespaces"."last_ci_minutes_notification_at" AS t1_r34,
  "namespaces"."last_ci_minutes_usage_notification_level" AS t1_r35,
  "namespaces"."subgroup_creation_level" AS t1_r36,
  "namespaces"."max_pages_size" AS t1_r37,
  "namespaces"."max_artifacts_size" AS t1_r38,
  "namespaces"."mentions_disabled" AS t1_r39,
  "namespaces"."default_branch_protection" AS t1_r40,
  "namespaces"."max_personal_access_token_lifetime" AS t1_r41,
  "namespaces"."push_rule_id" AS t1_r42,
  "namespaces"."shared_runners_enabled" AS t1_r43,
  "namespaces"."allow_descendants_override_disabled_shared_runners" AS t1_r44,
  "namespaces"."traversal_ids" AS t1_r45,
  "namespaces"."organization_id" AS t1_r46
FROM
  "compliance_management_frameworks"
LEFT OUTER JOIN
  "namespaces"
ON
  "namespaces"."id" = "compliance_management_frameworks"."namespace_id"
WHERE
  "compliance_management_frameworks"."id" IN (1020425, 1019873, 1019512, 1019907, 1019906)
  AND "namespaces"."id" = 9970;

Explain: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/39474/commands/121406

How to set up and validate locally

1. Enable the feature flag locally from console

Feature.enable(:annotate_security_orchestration_policy_yaml)

2. Navigate to "Secure => Policies" for a group
3. Create a MR approval policy with policy scope referencing compliance framework ID fields
   • If there's no compliance frameowork available, create one using the widget

Example: MR approval policy

Policy.yml

approval_policy:
 - name: MR Approval Policy
   description: ''
   enabled: true
   policy_scope:
      compliance_frameworks:
          including:
            - id: 1
   rules:
     - type: scan_finding
       scanners: []
       vulnerabilities_allowed: 0
       severity_levels: []
       vulnerability_states: []
       branch_type: protected
   actions:
     - type: require_approval
       approvals_required: 1
       user_approvers_ids:
         - 1
         - 5
       role_approvers: []
     - type: send_bot_message
       enabled: true
   approval_settings:
     block_branch_modification: false
     block_group_branch_modification: false
     prevent_pushing_and_force_pushing: false
     prevent_approval_by_author: false
     prevent_approval_by_commit_author: false
     remove_approvals_with_new_commit: false
     require_password_to_approve: false
   fallback_behavior:
     fail: closed

4. Create Merge request with the changes
5. The changes in the merge request should Include comments next IDs (including the compliance framework IDs)

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.

Related to #497774 (closed)