Consider child pipelines for MR approval policies

What does this MR do and why?

This change updates the MR approval policies to consider child pipelines.

Database

Query

WITH RECURSIVE "base_and_descendants" AS (
		SELECT
			"ci_pipelines"."id"
		FROM
			"ci_pipelines"
		WHERE
			"ci_pipelines"."project_id" = 278964
			AND "ci_pipelines"."id" IN (687215962, 685462259, 685325240, 687005950)
	UNION (
		SELECT
			"ci_pipelines"."id"
		FROM
			"ci_pipelines",
			"base_and_descendants",
			"ci_sources_pipelines"
		WHERE
			"ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id"
			AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
			AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id"))
SELECT
	"ci_pipelines"."id"
FROM
	"base_and_descendants" AS "ci_pipelines";

Query Plan

 CTE Scan on base_and_descendants ci_pipelines  (cost=1185.27..1185.49 rows=11 width=4) (actual time=0.055..5.679 rows=12 loops=1)
   Buffers: shared hit=117 read=9
   I/O Timings: read=5.228 write=0.000
   CTE base_and_descendants
     ->  Recursive Union  (cost=0.58..1185.27 rows=11 width=4) (actual time=0.053..5.671 rows=12 loops=1)
           Buffers: shared hit=117 read=9
           I/O Timings: read=5.228 write=0.000
           ->  Index Only Scan using index_ci_pipelines_on_project_id_and_id_desc on public.ci_pipelines ci_pipelines_1  (cost=0.58..9.86 rows=1 width=4) (actual time=0.051..0.234 rows=4 loops=1)
                 Index Cond: ((ci_pipelines_1.project_id = 278964) AND (ci_pipelines_1.id = ANY ('{687215962,685462259,685325240,687005950}'::integer[])))
                 Heap Fetches: 0
                 Buffers: shared hit=22 read=1
                 I/O Timings: read=0.136 write=0.000
           ->  Nested Loop  (cost=1.15..117.52 rows=1 width=4) (actual time=0.550..2.710 rows=4 loops=2)
                 Buffers: shared hit=95 read=8
                 I/O Timings: read=5.092 write=0.000
                 ->  Nested Loop  (cost=0.57..115.44 rows=1 width=8) (actual time=0.531..1.492 rows=4 loops=2)
                       Buffers: shared hit=58 read=4
                       I/O Timings: read=2.781 write=0.000
                       ->  WorkTable Scan on base_and_descendants  (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.002 rows=6 loops=2)
                             I/O Timings: read=0.000 write=0.000
                       ->  Index Scan using index_ci_sources_pipelines_on_source_pipeline_id on public.ci_sources_pipelines  (cost=0.57..11.51 rows=1 width=16) (actual time=0.247..0.247 rows=1 loops=12)
                             Index Cond: (ci_sources_pipelines.source_pipeline_id = base_and_descendants.id)
                             Filter: (ci_sources_pipelines.source_project_id = ci_sources_pipelines.project_id)
                             Rows Removed by Filter: 0
                             Buffers: shared hit=58 read=4
                             I/O Timings: read=2.781 write=0.000
                 ->  Index Only Scan using ci_pipelines_pkey on public.ci_pipelines ci_pipelines_2  (cost=0.58..2.08 rows=1 width=4) (actual time=0.303..0.303 rows=1 loops=8)
                       Index Cond: (ci_pipelines_2.id = ci_sources_pipelines.pipeline_id)
                       Heap Fetches: 0
                       Buffers: shared hit=37 read=4
                       I/O Timings: read=2.311 write=0.000

Time: 12.577 ms
  - planning: 6.785 ms
  - execution: 5.792 ms
    - I/O read: 5.228 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 117 (~936.00 KiB) from the buffer pool
  - reads: 9 (~72.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

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

Target branch's child pipeline

Source branch's child pipeline

Before (Feature disabled)	After (Feature enabled)

How to set up and validate locally

• Create a project with security scans in child pipeline. https://gitlab.com/gitlab-org/govern/security-policies/sashis-test-group/test-child-pipeline-2 can be used as reference
• Enable approval_policy_parent_child_pipeline feature flag for the project
• Create MR approval policy that requires approval when a new vulnerability is detected

name: Require approval
description: ''
enabled: true
actions:
- type: require_approval
  approvals_required: 1
  group_approvers_ids:
  - <ID>
rules:
- type: scan_finding
  scanners: []
  vulnerabilities_allowed: 0
  severity_levels: []
  vulnerability_states:
  - new_needs_triage
  branch_type: protected
approval_settings:
  prevent_pushing_and_force_pushing: false

• Create an MR that introduces a new vulnerability from the child pipeline's security job. Example MR: gitlab-org/govern/security-policies/sashis-test-group/test-child-pipeline-2!1
• Observe that the MR requires approval as a new vulnerability was detected in the child pipeline.

Addresses #428591