Skip to content

Use security_report_time_window logic for pipeline comparison

Sashi Kumar Kumaresanrequested to merge
525509-policy-time-window-merge-request into master

What does this MR do and why?

With Merge Request Approval Policy Time Window (#525509) we want to add a new option in MR approval policy to set a time window for selecting the target branch comparison pipeline for MR. This will enable comparisons against the latest pipeline with security reports created within a specified time range, rather than always waiting for the most recent baseline run to complete.

This MR updates the pipeline comparison logic to use the time_window to select the target branch pipeline. This feature is split into 3 MRs:

Target branch pipeline selection

The current logic of selecting a target branch pipeline is to look for pipelines with the required security reports in preferred SHA order:

  • diff_head_pipeline.target_sha
  • diff_base_sha
  • diff_start_sha

But if no pipeline is found for those SHAs with security report, the approval policy comparison fails due to missing baseline to compare.

The new logic updates it by using the time window configuration to query the pipelines in a time range so that we are guaranteed to get a pipeline with security report for older SHA.

Flow Diagram 
flowchart TD
    A[target_pipeline_for_approval_policy] --> B[Query comparison_pipeline for target branch sha]
    B --> F{Found comparison_pipeline?}
    
    F -->|Yes| G[Return comparison_pipeline]
    F -->|No| H{Feature flag enabled AND time_window present?}
    
    H -->|Yes| I[Find pipeline within time window]
    I --> J{Found pipeline with time window?}
    J -->|Yes| K[Return time window pipeline]
    J -->|No| L[Return latest_pipeline_for_target_branch]
    
    H -->|No| L[Return latest_pipeline_for_target_branch]
    
    G --> N[End]
    K --> N
    L --> N

Pipeline selection within time window

graph TB
    subgraph "Time Window: 12:00 - 14:00 (2 hours ago → pipeline created_at)"
        direction LR
        TW1[12:00<br/>Window Start]
        TW2[14:00<br/>Reference pipeline created_at]
    end
    
    subgraph "Pipeline Analysis"
        direction TB
        PA[Pipeline A<br/>Created: 11:00<br/>Status: ❌ Outside Window]
        PB[Pipeline B<br/>Created: 12:30<br/>Status: ✅ Valid]
        PC[Pipeline C<br/>Created: 13:00<br/>Status: ✅ Valid]
        PD[Pipeline D<br/>Created: 13:30<br/>Status: ✅ Valid]
        PE[Pipeline E<br/>Created: 14:00<br/>Status: 🔄 Comparison Pipeline<br/>Used in created_before_id filter]
    end
    
    subgraph "Selection Logic"
        SL1[Filter by time window<br/>Valid candidates: B, C, D]
        SL2[Check report availability<br/>B, C, D have required reports]
        SL3[Filter created_before_id: E.id<br/>Only B, C, D remain]
        SL4[Order by ID descending<br/>D → C → B]
        SL5[Return Pipeline D]
    end
    
    TW1 -.-> PB
    TW2 -.-> PE
    
    PB --> SL1
    PC --> SL1
    PD --> SL1
    PE -.->|"created_before_id filter"| SL3
    
    style TW1 fill:#e3f2fd
    style TW2 fill:#e3f2fd
    style PB fill:#c8e6c9
    style PC fill:#c8e6c9
    style PD fill:#c8e6c9
    style PA fill:#ffebee
    style PE fill:#fff3e0

Database query

SELECT
    "p_ci_pipelines".* 
FROM
    "p_ci_pipelines" 
WHERE
    "p_ci_pipelines"."project_id" = 278964 
    AND "p_ci_pipelines"."ref" = 'master' 
    AND "p_ci_pipelines"."id" < 2024397354  
ORDER BY
    "p_ci_pipelines"."id" DESC LIMIT 100
Query Plan 
 Limit  (cost=3.17..143.40 rows=100 width=392) (actual time=78.461..83.056 rows=100 loops=1)
   Buffers: shared hit=7 read=123 dirtied=3
   WAL: records=3 fpi=3 bytes=23851
   ->  Merge Append  (cost=3.17..1261402.33 rows=899475 width=392) (actual time=78.459..83.043 rows=100 loops=1)
         Sort Key: p_ci_pipelines.id DESC
         Buffers: shared hit=7 read=123 dirtied=3
         WAL: records=3 fpi=3 bytes=23851
         ->  Index Scan using index_ci_pipelines_on_project_idandrefandiddesc on gitlab_partitions_dynamic.ci_pipelines p_ci_pipelines_1  (cost=0.70..1140725.42 rows=830738 width=393) (actual time=24.273..24.274 rows=1 loops=1)
               Index Cond: ((p_ci_pipelines_1.project_id = 278964) AND ((p_ci_pipelines_1.ref)::text = 'master'::text) AND (p_ci_pipelines_1.id < 2024397354))
               Buffers: shared hit=3 read=6
         ->  Index Scan using ci_pipelines_103_project_id_ref_id_idx on gitlab_partitions_dynamic.ci_pipelines_103 p_ci_pipelines_2  (cost=0.56..34303.44 rows=22528 width=389) (actual time=24.256..24.256 rows=1 loops=1)
               Index Cond: ((p_ci_pipelines_2.project_id = 278964) AND ((p_ci_pipelines_2.ref)::text = 'master'::text) AND (p_ci_pipelines_2.id < 2024397354))
               Buffers: shared read=5
         ->  Index Scan using ci_pipelines_104_project_id_ref_id_idx on gitlab_partitions_dynamic.ci_pipelines_104 p_ci_pipelines_3  (cost=0.57..35379.00 rows=23344 width=385) (actual time=18.000..18.000 rows=1 loops=1)
               Index Cond: ((p_ci_pipelines_3.project_id = 278964) AND ((p_ci_pipelines_3.ref)::text = 'master'::text) AND (p_ci_pipelines_3.id < 2024397354))
               Buffers: shared read=5
         ->  Index Scan using ci_pipelines_105_project_id_ref_id_idx on gitlab_partitions_dynamic.ci_pipelines_105 p_ci_pipelines_4  (cost=0.57..29280.14 rows=19213 width=382) (actual time=4.812..4.812 rows=1 loops=1)
               Index Cond: ((p_ci_pipelines_4.project_id = 278964) AND ((p_ci_pipelines_4.ref)::text = 'master'::text) AND (p_ci_pipelines_4.id < 2024397354))
               Buffers: shared read=5
         ->  Index Scan using ci_pipelines_106_project_id_ref_id_idx on gitlab_partitions_dynamic.ci_pipelines_106 p_ci_pipelines_5  (cost=0.56..5588.18 rows=3651 width=410) (actual time=7.073..11.622 rows=100 loops=1)
               Index Cond: ((p_ci_pipelines_5.project_id = 278964) AND ((p_ci_pipelines_5.ref)::text = 'master'::text) AND (p_ci_pipelines_5.id < 2024397354))
               Buffers: shared hit=2 read=102 dirtied=3
               WAL: records=3 fpi=3 bytes=23851
         ->  Index Scan Backward using ci_pipelines_107_pkey on gitlab_partitions_dynamic.ci_pipelines_107 p_ci_pipelines_6  (cost=0.12..3.15 rows=1 width=1816) (actual time=0.028..0.028 rows=0 loops=1)
               Index Cond: (p_ci_pipelines_6.id < 2024397354)
               Filter: ((p_ci_pipelines_6.project_id = 278964) AND ((p_ci_pipelines_6.ref)::text = 'master'::text))
               Rows Removed by Filter: 0
               Buffers: shared hit=2
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'

Time: 98.776 ms
  - planning: 15.572 ms
  - execution: 83.204 ms
    - I/O read: N/A
    - I/O write: N/A

Shared buffers:
  - hits: 7 (~56.00 KiB) from the buffer pool
  - reads: 123 (~984.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 3 (~24.00 KiB)
  - writes: 0
SELECT
    "p_ci_pipelines".* 
FROM
    "p_ci_pipelines" 
WHERE
    "p_ci_pipelines"."project_id" = 278964 
    AND "p_ci_pipelines"."id" < 2024397354 
    AND "p_ci_pipelines"."ref" = 'master' 
    AND "p_ci_pipelines"."id" IN (
        2024395971, 2024378653, 2024378569, 2024378527, 2024372326, 2024372255, 2024372253, 2024372232, 2024372202, 2024372189, 2024372177, 2024372162, 2024372155, 2024372149, 2024372148, 2024372141, 2024372129, 2024372123, 2024372119, 2024372106, 2024372103, 2024372101, 2024372095, 2024372074, 2024372073, 2024372068, 2024372064, 2024372061, 2024372047, 2024372036, 2024367052, 2024312833, 2024304421, 2024303228, 2024298758, 2024265022, 2024258846, 2024256636, 2024231271, 2024206534, 2024202740, 2024202697, 2024202639, 2024196496, 2024188487, 2024188435, 2024186573, 2024185683, 2024164189, 2024156077, 2024145786, 2024145712, 2024145687, 2024145684, 2024145681, 2024145671, 2024145668, 2024145663, 2024145656, 2024145645, 2024145644, 2024145643, 2024145642, 2024145640, 2024145638, 2024145634, 2024145631, 2024145629, 2024145628, 2024145626, 2024145624, 2024145623, 2024145616, 2024145606, 2024145602, 2024145588, 2024141530, 2024118982, 2024095344, 2024033240, 2024010392, 2023984272, 2023973038, 2023972917, 2023958809, 2023958805, 2023958803, 2023954367, 2023948985, 2023909608, 2023909586, 2023909584, 2023909538, 2023909508, 2023909495, 2023909494, 2023909461, 2023909455, 2023909452, 2023909449
    ) 
    AND (
        "p_ci_pipelines"."source" IN (
            1, 2, 3, 4, 5, 6, 7, 8, 10, 11
        ) 
        OR "p_ci_pipelines"."source" IS NULL
    ) 
    AND "p_ci_pipelines"."created_at" > '2025-09-03 21:10:15.244080' 
    AND (
        EXISTS (
            SELECT
                1 
            FROM
                "p_ci_builds" 
            WHERE
                "p_ci_builds"."type" = 'Ci::Build' 
                AND (
                    "p_ci_builds"."retried" = FALSE 
                    OR "p_ci_builds"."retried" IS NULL
                ) 
                AND "p_ci_builds"."commit_id" = "p_ci_pipelines"."id" 
                AND "p_ci_builds"."partition_id" = "p_ci_pipelines"."partition_id" 
                AND (
                    EXISTS (
                        SELECT
                            1 
                        FROM
                            "p_ci_job_artifacts" 
                        WHERE
                            "p_ci_job_artifacts"."job_id" = "p_ci_builds"."id" 
                            AND "p_ci_job_artifacts"."partition_id" = "p_ci_builds"."partition_id" 
                            AND "p_ci_job_artifacts"."file_type" IN (
                                5, 6, 7, 8, 21, 23, 26, 27, 28
                            )
                    )
                )
            )
    ) 
ORDER BY
    "p_ci_pipelines"."id" ASC,
    "p_ci_pipelines"."partition_id" ASC LIMIT 1
Query Plan 
 Limit  (cost=4.48..130446.90 rows=1 width=629) (actual time=6.667..6.674 rows=1 loops=1)
   Buffers: shared hit=160 read=84 dirtied=1
   WAL: records=1 fpi=1 bytes=8061
   ->  Nested Loop Semi Join  (cost=4.48..782658.99 rows=6 width=629) (actual time=6.666..6.671 rows=1 loops=1)
         Buffers: shared hit=160 read=84 dirtied=1
         WAL: records=1 fpi=1 bytes=8061
         ->  Merge Append  (cost=3.19..1452.79 rows=6 width=629) (actual time=1.041..1.044 rows=1 loops=1)
               Sort Key: p_ci_pipelines.id, p_ci_pipelines.partition_id
               Buffers: shared hit=30 read=23
               ->  Index Scan using ci_pipelines_pkey on gitlab_partitions_dynamic.ci_pipelines p_ci_pipelines_1  (cost=0.60..322.10 rows=1 width=393) (actual time=0.035..0.035 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_1.id < 2024397354) AND (p_ci_pipelines_1.id = ANY ('{2024395971,2024378653,2024378569,2024378527,2024372326,2024372255,2024372253,2024372232,2024372202,2024372189,2024372177,2024372162,2024372155,2024372149,2024372148,2024372141,2024372129,2024372123,2024372119,2024372106,2024372103,2024372101,2024372095,2024372074,2024372073,2024372068,2024372064,2024372061,2024372047,2024372036,2024367052,2024312833,2024304421,2024303228,2024298758,2024265022,2024258846,2024256636,2024231271,2024206534,2024202740,2024202697,2024202639,2024196496,2024188487,2024188435,2024186573,2024185683,2024164189,2024156077,2024145786,2024145712,2024145687,2024145684,2024145681,2024145671,2024145668,2024145663,2024145656,2024145645,2024145644,2024145643,2024145642,2024145640,2024145638,2024145634,2024145631,2024145629,2024145628,2024145626,2024145624,2024145623,2024145616,2024145606,2024145602,2024145588,2024141530,2024118982,2024095344,2024033240,2024010392,2023984272,2023973038,2023972917,2023958809,2023958805,2023958803,2023954367,2023948985,2023909608,2023909586,2023909584,2023909538,2023909508,2023909495,2023909494,2023909461,2023909455,2023909452,2023909449}'::bigint[])))
                     Filter: ((p_ci_pipelines_1.created_at > '2025-09-03 21:10:15.24408'::timestamp without time zone) AND (p_ci_pipelines_1.project_id = 278964) AND ((p_ci_pipelines_1.ref)::text = 'master'::text) AND ((p_ci_pipelines_1.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_1.source IS NULL)))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=7
               ->  Index Scan using ci_pipelines_103_pkey on gitlab_partitions_dynamic.ci_pipelines_103 p_ci_pipelines_2  (cost=0.59..292.76 rows=1 width=389) (actual time=0.017..0.017 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_2.id < 2024397354) AND (p_ci_pipelines_2.id = ANY ('{2024395971,2024378653,2024378569,2024378527,2024372326,2024372255,2024372253,2024372232,2024372202,2024372189,2024372177,2024372162,2024372155,2024372149,2024372148,2024372141,2024372129,2024372123,2024372119,2024372106,2024372103,2024372101,2024372095,2024372074,2024372073,2024372068,2024372064,2024372061,2024372047,2024372036,2024367052,2024312833,2024304421,2024303228,2024298758,2024265022,2024258846,2024256636,2024231271,2024206534,2024202740,2024202697,2024202639,2024196496,2024188487,2024188435,2024186573,2024185683,2024164189,2024156077,2024145786,2024145712,2024145687,2024145684,2024145681,2024145671,2024145668,2024145663,2024145656,2024145645,2024145644,2024145643,2024145642,2024145640,2024145638,2024145634,2024145631,2024145629,2024145628,2024145626,2024145624,2024145623,2024145616,2024145606,2024145602,2024145588,2024141530,2024118982,2024095344,2024033240,2024010392,2023984272,2023973038,2023972917,2023958809,2023958805,2023958803,2023954367,2023948985,2023909608,2023909586,2023909584,2023909538,2023909508,2023909495,2023909494,2023909461,2023909455,2023909452,2023909449}'::bigint[])))
                     Filter: ((p_ci_pipelines_2.created_at > '2025-09-03 21:10:15.24408'::timestamp without time zone) AND (p_ci_pipelines_2.project_id = 278964) AND ((p_ci_pipelines_2.ref)::text = 'master'::text) AND ((p_ci_pipelines_2.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_2.source IS NULL)))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4
               ->  Index Scan using ci_pipelines_104_pkey on gitlab_partitions_dynamic.ci_pipelines_104 p_ci_pipelines_3  (cost=0.59..291.57 rows=1 width=385) (actual time=0.113..0.113 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_3.id < 2024397354) AND (p_ci_pipelines_3.id = ANY ('{2024395971,2024378653,2024378569,2024378527,2024372326,2024372255,2024372253,2024372232,2024372202,2024372189,2024372177,2024372162,2024372155,2024372149,2024372148,2024372141,2024372129,2024372123,2024372119,2024372106,2024372103,2024372101,2024372095,2024372074,2024372073,2024372068,2024372064,2024372061,2024372047,2024372036,2024367052,2024312833,2024304421,2024303228,2024298758,2024265022,2024258846,2024256636,2024231271,2024206534,2024202740,2024202697,2024202639,2024196496,2024188487,2024188435,2024186573,2024185683,2024164189,2024156077,2024145786,2024145712,2024145687,2024145684,2024145681,2024145671,2024145668,2024145663,2024145656,2024145645,2024145644,2024145643,2024145642,2024145640,2024145638,2024145634,2024145631,2024145629,2024145628,2024145626,2024145624,2024145623,2024145616,2024145606,2024145602,2024145588,2024141530,2024118982,2024095344,2024033240,2024010392,2023984272,2023973038,2023972917,2023958809,2023958805,2023958803,2023954367,2023948985,2023909608,2023909586,2023909584,2023909538,2023909508,2023909495,2023909494,2023909461,2023909455,2023909452,2023909449}'::bigint[])))
                     Filter: ((p_ci_pipelines_3.created_at > '2025-09-03 21:10:15.24408'::timestamp without time zone) AND (p_ci_pipelines_3.project_id = 278964) AND ((p_ci_pipelines_3.ref)::text = 'master'::text) AND ((p_ci_pipelines_3.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_3.source IS NULL)))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=3 read=1
               ->  Index Scan using ci_pipelines_105_pkey on gitlab_partitions_dynamic.ci_pipelines_105 p_ci_pipelines_4  (cost=0.59..292.19 rows=1 width=382) (actual time=0.071..0.071 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_4.id < 2024397354) AND (p_ci_pipelines_4.id = ANY ('{2024395971,2024378653,2024378569,2024378527,2024372326,2024372255,2024372253,2024372232,2024372202,2024372189,2024372177,2024372162,2024372155,2024372149,2024372148,2024372141,2024372129,2024372123,2024372119,2024372106,2024372103,2024372101,2024372095,2024372074,2024372073,2024372068,2024372064,2024372061,2024372047,2024372036,2024367052,2024312833,2024304421,2024303228,2024298758,2024265022,2024258846,2024256636,2024231271,2024206534,2024202740,2024202697,2024202639,2024196496,2024188487,2024188435,2024186573,2024185683,2024164189,2024156077,2024145786,2024145712,2024145687,2024145684,2024145681,2024145671,2024145668,2024145663,2024145656,2024145645,2024145644,2024145643,2024145642,2024145640,2024145638,2024145634,2024145631,2024145629,2024145628,2024145626,2024145624,2024145623,2024145616,2024145606,2024145602,2024145588,2024141530,2024118982,2024095344,2024033240,2024010392,2023984272,2023973038,2023972917,2023958809,2023958805,2023958803,2023954367,2023948985,2023909608,2023909586,2023909584,2023909538,2023909508,2023909495,2023909494,2023909461,2023909455,2023909452,2023909449}'::bigint[])))
                     Filter: ((p_ci_pipelines_4.created_at > '2025-09-03 21:10:15.24408'::timestamp without time zone) AND (p_ci_pipelines_4.project_id = 278964) AND ((p_ci_pipelines_4.ref)::text = 'master'::text) AND ((p_ci_pipelines_4.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_4.source IS NULL)))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=6 read=2
               ->  Index Scan using ci_pipelines_106_pkey on gitlab_partitions_dynamic.ci_pipelines_106 p_ci_pipelines_5  (cost=0.59..250.80 rows=1 width=410) (actual time=0.785..0.785 rows=1 loops=1)
                     Index Cond: ((p_ci_pipelines_5.id < 2024397354) AND (p_ci_pipelines_5.id = ANY ('{2024395971,2024378653,2024378569,2024378527,2024372326,2024372255,2024372253,2024372232,2024372202,2024372189,2024372177,2024372162,2024372155,2024372149,2024372148,2024372141,2024372129,2024372123,2024372119,2024372106,2024372103,2024372101,2024372095,2024372074,2024372073,2024372068,2024372064,2024372061,2024372047,2024372036,2024367052,2024312833,2024304421,2024303228,2024298758,2024265022,2024258846,2024256636,2024231271,2024206534,2024202740,2024202697,2024202639,2024196496,2024188487,2024188435,2024186573,2024185683,2024164189,2024156077,2024145786,2024145712,2024145687,2024145684,2024145681,2024145671,2024145668,2024145663,2024145656,2024145645,2024145644,2024145643,2024145642,2024145640,2024145638,2024145634,2024145631,2024145629,2024145628,2024145626,2024145624,2024145623,2024145616,2024145606,2024145602,2024145588,2024141530,2024118982,2024095344,2024033240,2024010392,2023984272,2023973038,2023972917,2023958809,2023958805,2023958803,2023954367,2023948985,2023909608,2023909586,2023909584,2023909538,2023909508,2023909495,2023909494,2023909461,2023909455,2023909452,2023909449}'::bigint[])))
                     Filter: ((p_ci_pipelines_5.created_at > '2025-09-03 21:10:15.24408'::timestamp without time zone) AND (p_ci_pipelines_5.project_id = 278964) AND ((p_ci_pipelines_5.ref)::text = 'master'::text) AND ((p_ci_pipelines_5.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_5.source IS NULL)))
                     Rows Removed by Filter: 11
                     Buffers: shared hit=8 read=20
               ->  Index Scan using ci_pipelines_107_pkey on gitlab_partitions_dynamic.ci_pipelines_107 p_ci_pipelines_6  (cost=0.15..3.18 rows=1 width=1816) (actual time=0.018..0.018 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_6.id < 2024397354) AND (p_ci_pipelines_6.id = ANY ('{2024395971,2024378653,2024378569,2024378527,2024372326,2024372255,2024372253,2024372232,2024372202,2024372189,2024372177,2024372162,2024372155,2024372149,2024372148,2024372141,2024372129,2024372123,2024372119,2024372106,2024372103,2024372101,2024372095,2024372074,2024372073,2024372068,2024372064,2024372061,2024372047,2024372036,2024367052,2024312833,2024304421,2024303228,2024298758,2024265022,2024258846,2024256636,2024231271,2024206534,2024202740,2024202697,2024202639,2024196496,2024188487,2024188435,2024186573,2024185683,2024164189,2024156077,2024145786,2024145712,2024145687,2024145684,2024145681,2024145671,2024145668,2024145663,2024145656,2024145645,2024145644,2024145643,2024145642,2024145640,2024145638,2024145634,2024145631,2024145629,2024145628,2024145626,2024145624,2024145623,2024145616,2024145606,2024145602,2024145588,2024141530,2024118982,2024095344,2024033240,2024010392,2023984272,2023973038,2023972917,2023958809,2023958805,2023958803,2023954367,2023948985,2023909608,2023909586,2023909584,2023909538,2023909508,2023909495,2023909494,2023909461,2023909455,2023909452,2023909449}'::bigint[])))
                     Filter: ((p_ci_pipelines_6.created_at > '2025-09-03 21:10:15.24408'::timestamp without time zone) AND (p_ci_pipelines_6.project_id = 278964) AND ((p_ci_pipelines_6.ref)::text = 'master'::text) AND ((p_ci_pipelines_6.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_6.source IS NULL)))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=2
         ->  Nested Loop Semi Join  (cost=1.29..263588.79 rows=15 width=24) (actual time=5.621..5.623 rows=1 loops=1)
               Buffers: shared hit=130 read=61 dirtied=1
               WAL: records=1 fpi=1 bytes=8061
               ->  Append  (cost=0.71..2838.64 rows=2400 width=24) (actual time=0.399..4.513 rows=38 loops=1)
                     Buffers: shared read=38 dirtied=1
                     WAL: records=1 fpi=1 bytes=8061
                     ->  Index Scan using index_ci_builds_on_commit_id_and_type_and_ref on gitlab_partitions_dynamic.ci_builds p_ci_builds_1  (cost=0.71..1721.51 rows=1236 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_builds_1.commit_id = p_ci_pipelines.id) AND ((p_ci_builds_1.type)::text = 'Ci::Build'::text))
                           Filter: (((NOT p_ci_builds_1.retried) OR (p_ci_builds_1.retried IS NULL)) AND (p_ci_pipelines.partition_id = p_ci_builds_1.partition_id))
                           Rows Removed by Filter: 0
                     ->  Index Scan using ci_builds_101_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_101 p_ci_builds_2  (cost=0.58..275.72 rows=264 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_builds_2.commit_id = p_ci_pipelines.id) AND ((p_ci_builds_2.type)::text = 'Ci::Build'::text))
                           Filter: (((NOT p_ci_builds_2.retried) OR (p_ci_builds_2.retried IS NULL)) AND (p_ci_builds_2.partition_id = p_ci_pipelines.partition_id))
                           Rows Removed by Filter: 0
                     ->  Index Scan using ci_builds_102_commit_id_convert_to_bigint_type_ref_idx on gitlab_partitions_dynamic.ci_builds_102 p_ci_builds_3  (cost=0.58..505.89 rows=522 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_builds_3.commit_id = p_ci_pipelines.id) AND ((p_ci_builds_3.type)::text = 'Ci::Build'::text))
                           Filter: (((NOT p_ci_builds_3.retried) OR (p_ci_builds_3.retried IS NULL)) AND (p_ci_pipelines.partition_id = p_ci_builds_3.partition_id))
                           Rows Removed by Filter: 0
                     ->  Index Scan using ci_builds_103_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_103 p_ci_builds_4  (cost=0.57..95.74 rows=108 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_builds_4.commit_id = p_ci_pipelines.id) AND ((p_ci_builds_4.type)::text = 'Ci::Build'::text))
                           Filter: (((NOT p_ci_builds_4.retried) OR (p_ci_builds_4.retried IS NULL)) AND (p_ci_pipelines.partition_id = p_ci_builds_4.partition_id))
                           Rows Removed by Filter: 0
                     ->  Index Scan using ci_builds_104_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_104 p_ci_builds_5  (cost=0.57..95.78 rows=111 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_builds_5.commit_id = p_ci_pipelines.id) AND ((p_ci_builds_5.type)::text = 'Ci::Build'::text))
                           Filter: (((NOT p_ci_builds_5.retried) OR (p_ci_builds_5.retried IS NULL)) AND (p_ci_pipelines.partition_id = p_ci_builds_5.partition_id))
                           Rows Removed by Filter: 0
                     ->  Index Scan using ci_builds_105_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_105 p_ci_builds_6  (cost=0.57..83.98 rows=99 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_builds_6.commit_id = p_ci_pipelines.id) AND ((p_ci_builds_6.type)::text = 'Ci::Build'::text))
                           Filter: (((NOT p_ci_builds_6.retried) OR (p_ci_builds_6.retried IS NULL)) AND (p_ci_pipelines.partition_id = p_ci_builds_6.partition_id))
                           Rows Removed by Filter: 0
                     ->  Index Scan using ci_builds_106_commit_id_type_ref_idx on gitlab_partitions_dynamic.ci_builds_106 p_ci_builds_7  (cost=0.57..48.03 rows=59 width=24) (actual time=0.394..4.503 rows=38 loops=1)
                           Index Cond: ((p_ci_builds_7.commit_id = p_ci_pipelines.id) AND ((p_ci_builds_7.type)::text = 'Ci::Build'::text))
                           Filter: (((NOT p_ci_builds_7.retried) OR (p_ci_builds_7.retried IS NULL)) AND (p_ci_pipelines.partition_id = p_ci_builds_7.partition_id))
                           Rows Removed by Filter: 0
                           Buffers: shared read=38 dirtied=1
                           WAL: records=1 fpi=1 bytes=8061
                     ->  Seq Scan on gitlab_partitions_dynamic.ci_builds_107 p_ci_builds_8  (cost=0.00..0.00 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=0)
                           Filter: (((NOT p_ci_builds_8.retried) OR (p_ci_builds_8.retried IS NULL)) AND ((p_ci_builds_8.type)::text = 'Ci::Build'::text) AND (p_ci_pipelines.partition_id = p_ci_builds_8.partition_id) AND (p_ci_pipelines.id = p_ci_builds_8.commit_id))
                           Rows Removed by Filter: 0
               ->  Append  (cost=0.58..108.56 rows=7 width=16) (actual time=0.029..0.029 rows=0 loops=38)
                     Buffers: shared hit=130 read=23
                     ->  Index Only Scan using idx_ci_job_artifacts_on_job_id_file_type_and_partition_id_uniq on gitlab_partitions_dynamic.ci_job_artifacts p_ci_job_artifacts_1  (cost=0.58..18.13 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_job_artifacts_1.job_id = p_ci_builds.id) AND (p_ci_job_artifacts_1.file_type = ANY ('{5,6,7,8,21,23,26,27,28}'::integer[])) AND (p_ci_job_artifacts_1.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                     ->  Index Only Scan using ci_job_artifacts_102_job_id_file_type_partition_id_idx on gitlab_partitions_dynamic.ci_job_artifacts_102 p_ci_job_artifacts_2  (cost=0.58..18.09 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_job_artifacts_2.job_id = p_ci_builds.id) AND (p_ci_job_artifacts_2.file_type = ANY ('{5,6,7,8,21,23,26,27,28}'::integer[])) AND (p_ci_job_artifacts_2.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                     ->  Index Only Scan using ci_job_artifacts_103_job_id_file_type_partition_id_idx on gitlab_partitions_dynamic.ci_job_artifacts_103 p_ci_job_artifacts_3  (cost=0.57..18.06 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_job_artifacts_3.job_id = p_ci_builds.id) AND (p_ci_job_artifacts_3.file_type = ANY ('{5,6,7,8,21,23,26,27,28}'::integer[])) AND (p_ci_job_artifacts_3.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                     ->  Index Only Scan using ci_job_artifacts_104_job_id_file_type_partition_id_idx on gitlab_partitions_dynamic.ci_job_artifacts_104 p_ci_job_artifacts_4  (cost=0.57..18.05 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_job_artifacts_4.job_id = p_ci_builds.id) AND (p_ci_job_artifacts_4.file_type = ANY ('{5,6,7,8,21,23,26,27,28}'::integer[])) AND (p_ci_job_artifacts_4.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                     ->  Index Only Scan using ci_job_artifacts_105_job_id_file_type_partition_id_idx on gitlab_partitions_dynamic.ci_job_artifacts_105 p_ci_job_artifacts_5  (cost=0.57..18.04 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: ((p_ci_job_artifacts_5.job_id = p_ci_builds.id) AND (p_ci_job_artifacts_5.file_type = ANY ('{5,6,7,8,21,23,26,27,28}'::integer[])) AND (p_ci_job_artifacts_5.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                     ->  Index Only Scan using ci_job_artifacts_106_job_id_file_type_partition_id_idx on gitlab_partitions_dynamic.ci_job_artifacts_106 p_ci_job_artifacts_6  (cost=0.57..18.13 rows=1 width=16) (actual time=0.028..0.028 rows=0 loops=38)
                           Index Cond: ((p_ci_job_artifacts_6.job_id = p_ci_builds.id) AND (p_ci_job_artifacts_6.file_type = ANY ('{5,6,7,8,21,23,26,27,28}'::integer[])) AND (p_ci_job_artifacts_6.partition_id = p_ci_builds.partition_id))
                           Heap Fetches: 0
                           Buffers: shared hit=130 read=23
                     ->  Seq Scan on gitlab_partitions_dynamic.ci_job_artifacts_107 p_ci_job_artifacts_7  (cost=0.02..0.02 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=0)
                           Filter: ((p_ci_builds.id = p_ci_job_artifacts_7.job_id) AND (p_ci_builds.partition_id = p_ci_job_artifacts_7.partition_id) AND (p_ci_job_artifacts_7.file_type = ANY ('{5,6,7,8,21,23,26,27,28}'::integer[])))
                           Rows Removed by Filter: 0
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'

Time: 83.755 ms
  - planning: 76.687 ms
  - execution: 7.068 ms
    - I/O read: N/A
    - I/O write: N/A

Shared buffers:
  - hits: 160 (~1.30 MiB) from the buffer pool
  - reads: 84 (~672.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 1 (~8.00 KiB)
  - writes: 0

References

Screenshots or screen recordings

FF disabled

Screenshot_2025-09-10_at_11.57.48_PM

Screenshot_2025-09-10_at_11.59.49_PM

FF enabled

Screenshot_2025-09-10_at_11.59.55_PM

How to set up and validate locally

  • Create a project/group
  • Create .gitlab-ci.yml with a security scan that is set to manual for default branch:
stages:
  - test
  - scan
include:
  - template: Security/Container-Scanning.gitlab-ci.yml
test:
  stage: test
  script:
    - echo "Running tests..."
container_scanning:
  stage: scan
  rules:
    - if: $CI_PIPELINE_SOURCE == "merge_request_event"
      when: never
    - if: $CI_COMMIT_BRANCH == $CI_DEFAULT_BRANCH
      when: manual
    - if: $CI_COMMIT_BRANCH
      when: on_success
  variables:
    CS_IMAGE: nginx:latest
  • Create 2 commits to the default branch and run the manual job for the first commit (latest commit will not have security scan)
  • Create MR approval policy with security_report_time_window:
approval_policy:
  - name: Time window comparison
    description: ''
    enabled: true
    policy_scope:
      projects:
        excluding: []
    rules:
      - type: scan_finding
        scanners:
          - container_scanning
        vulnerabilities_allowed: 0
        severity_levels: []
        vulnerability_states: []
        branch_type: protected
    actions:
      - type: require_approval
        approvals_required: 1
        role_approvers:
          - maintainer
      - type: send_bot_message
        enabled: true
    approval_settings:
      block_branch_modification: true
      block_group_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
    policy_tuning:
      unblock_rules_using_execution_policies: false
      security_report_time_window: 45
  • Create an MR that updates the README in the project and after the pipeline completes, verify that MR requires approval eventhough no new vulnerabilities are introduced (due to the pipeline that is selected without security scan)
  • Enable :approval_policy_time_window feature
  • Create a new MR and verify that the approval is not required
  • Merge the MR and run the manual scan job
  • Wait 45 minutes and create another commit in the master branch
  • Create a new MR and verify that the approval is required

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 #525509

Edited by Sashi Kumar Kumaresan

Merge request reports