Filter group_approvers in scan result policies by full_path

What does this MR do and why?

Addresses #382348 (closed)

This MR fixes the bug in Scan Result Policy. When group_approvers in policy yaml is set with full-path of a group. Currently we are querying for group only by it's path, so when full_path of a group is given, it will not be included in the approvers list of merge request approval rule.

Database Queries

    SELECT
        "namespaces"."id",
        "namespaces"."name",
        "namespaces"."path",
        "namespaces"."owner_id",
        "namespaces"."created_at",
        "namespaces"."updated_at",
        "namespaces"."type",
        "namespaces"."description",
        "namespaces"."avatar",
        "namespaces"."membership_lock",
        "namespaces"."share_with_group_lock",
        "namespaces"."visibility_level",
        "namespaces"."request_access_enabled",
        "namespaces"."ldap_sync_status",
        "namespaces"."ldap_sync_error",
        "namespaces"."ldap_sync_last_update_at",
        "namespaces"."ldap_sync_last_successful_update_at",
        "namespaces"."ldap_sync_last_sync_at",
        "namespaces"."description_html",
        "namespaces"."lfs_enabled",
        "namespaces"."parent_id",
        "namespaces"."shared_runners_minutes_limit",
        "namespaces"."repository_size_limit",
        "namespaces"."require_two_factor_authentication",
        "namespaces"."two_factor_grace_period",
        "namespaces"."cached_markdown_version",
        "namespaces"."project_creation_level",
        "namespaces"."runners_token",
        "namespaces"."file_template_project_id",
        "namespaces"."saml_discovery_token",
        "namespaces"."runners_token_encrypted",
        "namespaces"."custom_project_templates_group_id",
        "namespaces"."auto_devops_enabled",
        "namespaces"."extra_shared_runners_minutes_limit",
        "namespaces"."last_ci_minutes_notification_at",
        "namespaces"."last_ci_minutes_usage_notification_level",
        "namespaces"."subgroup_creation_level",
        "namespaces"."emails_disabled",
        "namespaces"."max_pages_size",
        "namespaces"."max_artifacts_size",
        "namespaces"."mentions_disabled",
        "namespaces"."default_branch_protection",
        "namespaces"."unlock_membership_to_ldap",
        "namespaces"."max_personal_access_token_lifetime",
        "namespaces"."push_rule_id",
        "namespaces"."shared_runners_enabled",
        "namespaces"."allow_descendants_override_disabled_shared_runners",
        "namespaces"."traversal_ids" 
    FROM
        ((SELECT
            "namespaces"."id",
            "namespaces"."name",
            "namespaces"."path",
            "namespaces"."owner_id",
            "namespaces"."created_at",
            "namespaces"."updated_at",
            "namespaces"."type",
            "namespaces"."description",
            "namespaces"."avatar",
            "namespaces"."membership_lock",
            "namespaces"."share_with_group_lock",
            "namespaces"."visibility_level",
            "namespaces"."request_access_enabled",
            "namespaces"."ldap_sync_status",
            "namespaces"."ldap_sync_error",
            "namespaces"."ldap_sync_last_update_at",
            "namespaces"."ldap_sync_last_successful_update_at",
            "namespaces"."ldap_sync_last_sync_at",
            "namespaces"."description_html",
            "namespaces"."lfs_enabled",
            "namespaces"."parent_id",
            "namespaces"."shared_runners_minutes_limit",
            "namespaces"."repository_size_limit",
            "namespaces"."require_two_factor_authentication",
            "namespaces"."two_factor_grace_period",
            "namespaces"."cached_markdown_version",
            "namespaces"."project_creation_level",
            "namespaces"."runners_token",
            "namespaces"."file_template_project_id",
            "namespaces"."saml_discovery_token",
            "namespaces"."runners_token_encrypted",
            "namespaces"."custom_project_templates_group_id",
            "namespaces"."auto_devops_enabled",
            "namespaces"."extra_shared_runners_minutes_limit",
            "namespaces"."last_ci_minutes_notification_at",
            "namespaces"."last_ci_minutes_usage_notification_level",
            "namespaces"."subgroup_creation_level",
            "namespaces"."emails_disabled",
            "namespaces"."max_pages_size",
            "namespaces"."max_artifacts_size",
            "namespaces"."mentions_disabled",
            "namespaces"."default_branch_protection",
            "namespaces"."unlock_membership_to_ldap",
            "namespaces"."max_personal_access_token_lifetime",
            "namespaces"."push_rule_id",
            "namespaces"."shared_runners_enabled",
            "namespaces"."allow_descendants_override_disabled_shared_runners",
            "namespaces"."traversal_ids" 
        FROM
            "namespaces" 
        WHERE
            "namespaces"."type" = 'Group' 
            AND "namespaces"."id" = 14491403) 
    UNION
    (
        SELECT
            "namespaces"."id",
            "namespaces"."name",
            "namespaces"."path",
            "namespaces"."owner_id",
            "namespaces"."created_at",
            "namespaces"."updated_at",
            "namespaces"."type",
            "namespaces"."description",
            "namespaces"."avatar",
            "namespaces"."membership_lock",
            "namespaces"."share_with_group_lock",
            "namespaces"."visibility_level",
            "namespaces"."request_access_enabled",
            "namespaces"."ldap_sync_status",
            "namespaces"."ldap_sync_error",
            "namespaces"."ldap_sync_last_update_at",
            "namespaces"."ldap_sync_last_successful_update_at",
            "namespaces"."ldap_sync_last_sync_at",
            "namespaces"."description_html",
            "namespaces"."lfs_enabled",
            "namespaces"."parent_id",
            "namespaces"."shared_runners_minutes_limit",
            "namespaces"."repository_size_limit",
            "namespaces"."require_two_factor_authentication",
            "namespaces"."two_factor_grace_period",
            "namespaces"."cached_markdown_version",
            "namespaces"."project_creation_level",
            "namespaces"."runners_token",
            "namespaces"."file_template_project_id",
            "namespaces"."saml_discovery_token",
            "namespaces"."runners_token_encrypted",
            "namespaces"."custom_project_templates_group_id",
            "namespaces"."auto_devops_enabled",
            "namespaces"."extra_shared_runners_minutes_limit",
            "namespaces"."last_ci_minutes_notification_at",
            "namespaces"."last_ci_minutes_usage_notification_level",
            "namespaces"."subgroup_creation_level",
            "namespaces"."emails_disabled",
            "namespaces"."max_pages_size",
            "namespaces"."max_artifacts_size",
            "namespaces"."mentions_disabled",
            "namespaces"."default_branch_protection",
            "namespaces"."unlock_membership_to_ldap",
            "namespaces"."max_personal_access_token_lifetime",
            "namespaces"."push_rule_id",
            "namespaces"."shared_runners_enabled",
            "namespaces"."allow_descendants_override_disabled_shared_runners",
            "namespaces"."traversal_ids" 
        FROM
            "namespaces" 
        WHERE
            "namespaces"."type" = 'Group' 
            AND "namespaces"."id" IN (
                SELECT
                    "routes"."namespace_id" 
                FROM
                    "routes" 
                WHERE
                    "routes"."source_type" = 'Namespace' 
                    AND (
                        LOWER(routes.path) IN (
                            'sandbox','gitlab-org/govern/demos/sandbox/group-policies-test'
                        )
                    )
            )
        ) 
    UNION
    (
        SELECT
            "namespaces"."id",
            "namespaces"."name",
            "namespaces"."path",
            "namespaces"."owner_id",
            "namespaces"."created_at",
            "namespaces"."updated_at",
            "namespaces"."type",
            "namespaces"."description",
            "namespaces"."avatar",
            "namespaces"."membership_lock",
            "namespaces"."share_with_group_lock",
            "namespaces"."visibility_level",
            "namespaces"."request_access_enabled",
            "namespaces"."ldap_sync_status",
            "namespaces"."ldap_sync_error",
            "namespaces"."ldap_sync_last_update_at",
            "namespaces"."ldap_sync_last_successful_update_at",
            "namespaces"."ldap_sync_last_sync_at",
            "namespaces"."description_html",
            "namespaces"."lfs_enabled",
            "namespaces"."parent_id",
            "namespaces"."shared_runners_minutes_limit",
            "namespaces"."repository_size_limit",
            "namespaces"."require_two_factor_authentication",
            "namespaces"."two_factor_grace_period",
            "namespaces"."cached_markdown_version",
            "namespaces"."project_creation_level",
            "namespaces"."runners_token",
            "namespaces"."file_template_project_id",
            "namespaces"."saml_discovery_token",
            "namespaces"."runners_token_encrypted",
            "namespaces"."custom_project_templates_group_id",
            "namespaces"."auto_devops_enabled",
            "namespaces"."extra_shared_runners_minutes_limit",
            "namespaces"."last_ci_minutes_notification_at",
            "namespaces"."last_ci_minutes_usage_notification_level",
            "namespaces"."subgroup_creation_level",
            "namespaces"."emails_disabled",
            "namespaces"."max_pages_size",
            "namespaces"."max_artifacts_size",
            "namespaces"."mentions_disabled",
            "namespaces"."default_branch_protection",
            "namespaces"."unlock_membership_to_ldap",
            "namespaces"."max_personal_access_token_lifetime",
            "namespaces"."push_rule_id",
            "namespaces"."shared_runners_enabled",
            "namespaces"."allow_descendants_override_disabled_shared_runners",
            "namespaces"."traversal_ids" 
        FROM
            "namespaces" 
        WHERE
            "namespaces"."type" = 'Group' 
            AND (
                LOWER(path) IN (
                    'sandbox','gitlab-org/govern/demos/sandbox/group-policies-test'
                )
            )
    )
) namespaces 
WHERE
"namespaces"."type" = 'Group'

EXPLAIN output:

 HashAggregate  (cost=13207.35..13214.95 rows=760 width=2945) (actual time=20.628..23.028 rows=1996 loops=1)
   Group Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, namespaces.description, namespaces.avatar, namespaces.membership_lock, namespaces.share_with_group_lock, namespaces.visibility_level, namespaces.request_access_enabled, namespaces.ldap_sync_status, namespaces.ldap_sync_error, namespaces.ldap_sync_last_update_at, namespaces.ldap_sync_last_successful_update_at, namespaces.ldap_sync_last_sync_at, namespaces.description_html, namespaces.lfs_enabled, namespaces.parent_id, namespaces.shared_runners_minutes_limit, namespaces.repository_size_limit, namespaces.require_two_factor_authentication, namespaces.two_factor_grace_period, namespaces.cached_markdown_version, namespaces.project_creation_level, namespaces.runners_token, namespaces.file_template_project_id, namespaces.saml_discovery_token, namespaces.runners_token_encrypted, namespaces.custom_project_templates_group_id, namespaces.auto_devops_enabled, namespaces.extra_shared_runners_minutes_limit, namespaces.last_ci_minutes_notification_at, namespaces.last_ci_minutes_usage_notification_level, namespaces.subgroup_creation_level, namespaces.emails_disabled, namespaces.max_pages_size, namespaces.max_artifacts_size, namespaces.mentions_disabled, namespaces.default_branch_protection, namespaces.unlock_membership_to_ldap, namespaces.max_personal_access_token_lifetime, namespaces.push_rule_id, namespaces.shared_runners_enabled, namespaces.allow_descendants_override_disabled_shared_runners, namespaces.traversal_ids
   Buffers: shared hit=8402
   I/O Timings: read=0.000 write=0.000
   ->  Append  (cost=0.56..13116.15 rows=760 width=2945) (actual time=0.032..16.300 rows=1998 loops=1)
         Buffers: shared hit=8402
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using namespaces_pkey on public.namespaces  (cost=0.56..3.58 rows=1 width=365) (actual time=0.031..0.032 rows=1 loops=1)
               Index Cond: (namespaces.id = 14491403)
               Filter: ((namespaces.type)::text = 'Group'::text)
               Rows Removed by Filter: 0
               Buffers: shared hit=5
               I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=1.13..10.75 rows=1 width=365) (actual time=0.087..0.116 rows=2 loops=1)
               Buffers: shared hit=26
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_on_routes_lower_path on public.routes  (cost=0.56..7.17 rows=1 width=8) (actual time=0.055..0.069 rows=2 loops=1)
                     Index Cond: (lower((routes.path)::text) = ANY ('{sandbox,gitlab-org/govern/demos/sandbox/group-policies-test}'::text[]))
                     Filter: ((routes.source_type)::text = 'Namespace'::text)
                     Rows Removed by Filter: 0
                     Buffers: shared hit=13
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using namespaces_pkey on public.namespaces namespaces_1  (cost=0.56..3.58 rows=1 width=365) (actual time=0.020..0.021 rows=1 loops=2)
                     Index Cond: (namespaces_1.id = routes.namespace_id)
                     Filter: ((namespaces_1.type)::text = 'Group'::text)
                     Rows Removed by Filter: 0
                     Buffers: shared hit=13
                     I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_on_namespaces_lower_path on public.namespaces namespaces_2  (cost=0.56..13090.41 rows=758 width=365) (actual time=0.037..15.943 rows=1995 loops=1)
               Index Cond: (lower((namespaces_2.path)::text) = ANY ('{sandbox,gitlab-org/govern/demos/sandbox/group-policies-test}'::text[]))
               Filter: ((namespaces_2.type)::text = 'Group'::text)
               Rows Removed by Filter: 6356
               Buffers: shared hit=8371
               I/O Timings: read=0.000 write=0.000

Time: 25.260 ms
  - planning: 1.777 ms
  - execution: 23.483 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 8402 (~65.60 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Screenshots or screen recordings

Page	Before	After
Policy Page
MR Approval

How to set up and validate locally

1. Create a group and a subgroup inside it and add members to the subgroup
2. Create a new scan result policy under Security & Compliance -> Policies and use the full-path of the subgroup in group_approvers YAML

scan_result_policy:
- name: critical vulnerability CS approvals
  description: 
  enabled: true
  rules:
  - type: scan_finding
    branches:
    - main
    scanners:
    - container_scanning
    vulnerabilities_allowed: 0
    severity_levels:
    - critical
    vulnerability_states:
    - newly_detected
  actions:
  - type: require_approval
    approvals_required: 1
    group_approvers:
    - 'root-group/sub-group'

3. Check the policy show page

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.