Add more attach mode options to profile attach operations

Gal Katzrequested to merge
gkatz_expand_attach_mode into master

What does this MR do and why?

Changes the mode argument of securityScanProfileAttach so a request can choose which projects receive the profile. SET is the new default and keeps what REPLACE did before, which is attaching to all target projects and removing other profiles of the same scan type.

Mode Projects that get the profile Other profiles of the same scan type
SET (default) All Removed
REPLACE Only projects that already have a profile of the same scan type Removed
APPEND All Kept
FILL_PROFILE_GAPS Only projects with no profile of the same scan type Nothing to remove
FILL_SCAN_GAPS Only projects with no profile of the same scan type and no scan coverage for it Nothing to remove

FILL_SCAN_GAPS returns an error for scan types without an analyzer status, such as triage_and_remediation. The check lives in ProjectAttachService, so group attaches report it through the background operation.

REPLACE changes meaning in this MR and SET takes over the old behavior. The mode is new in 19.6, so no release has shipped with the old meaning. Jobs queued with mode: replace during the deploy run the narrower REPLACE, which never removes more than before.

Query plans

Security::ScanProfileProject.project_ids_with_profile_type

Raw SQL 
SELECT DISTINCT
    "security_scan_profiles_projects"."project_id"
FROM
    "security_scan_profiles_projects"
    INNER JOIN "security_scan_profiles" ON "security_scan_profiles"."id" = "security_scan_profiles_projects"."security_scan_profile_id"
WHERE
    "security_scan_profiles_projects"."project_id" IN (86624825, 74581983, 86624706)
    AND "security_scan_profiles"."deleted_at" IS NULL
    AND "security_scan_profiles"."namespace_id" = 115756369
    AND "security_scan_profiles"."scan_type" = 0
LIMIT 3
Query plan

Full details here

 Limit  (cost=10.56..10.57 rows=1 width=8) (actual time=7.476..7.479 rows=1 loops=1)
   Buffers: shared hit=14 read=5 dirtied=1
   WAL: records=1 fpi=1 bytes=8045
   I/O Timings: read=7.095 write=0.000
   ->  HashAggregate  (cost=10.56..10.57 rows=1 width=8) (actual time=7.462..7.465 rows=1 loops=1)
         Group Key: security_scan_profiles_projects.project_id
         Batches: 1  Memory Usage: 32kB
         Buffers: shared hit=14 read=5 dirtied=1
         WAL: records=1 fpi=1 bytes=8045
         I/O Timings: read=7.095 write=0.000
         ->  Hash Join  (cost=5.11..10.56 rows=1 width=8) (actual time=7.439..7.456 rows=1 loops=1)
               Hash Cond: (security_scan_profiles_projects.security_scan_profile_id = security_scan_profiles.id)
               Buffers: shared hit=14 read=5 dirtied=1
               WAL: records=1 fpi=1 bytes=8045
               I/O Timings: read=7.095 write=0.000
               ->  Index Only Scan using index_security_scan_profiles_projects_on_unique_project_profile on public.security_scan_profiles_projects  (cost=0.42..5.85 rows=5 width=16) (actual time=0.025..0.041 rows=3 loops=1)
                     Index Cond: (security_scan_profiles_projects.project_id = ANY ('{86624825,74581983,86624706}'::bigint[]))
                     Heap Fetches: 0
                     Index Searches: 2
                     Buffers: shared hit=7
                     I/O Timings: read=0.000 write=0.000
               ->  Hash  (cost=4.67..4.67 rows=2 width=8) (actual time=7.396..7.396 rows=6 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     Buffers: shared hit=7 read=5 dirtied=1
                     WAL: records=1 fpi=1 bytes=8045
                     I/O Timings: read=7.095 write=0.000
                     ->  Index Scan using index_security_scan_profiles_unique_name_not_deleted on public.security_scan_profiles  (cost=0.28..4.67 rows=2 width=8) (actual time=1.878..7.380 rows=6 loops=1)
                           Index Cond: ((security_scan_profiles.namespace_id = 115756369) AND (security_scan_profiles.scan_type = 0))
                           Index Searches: 1
                           Buffers: shared hit=7 read=5 dirtied=1
                           WAL: records=1 fpi=1 bytes=8045
                           I/O Timings: read=7.095 write=0.000
Settings: jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB'
Query ID: 5107771254740696020

Security::AnalyzerProjectStatus.covered_project_ids

Raw SQL 
SELECT
    "analyzer_project_statuses"."project_id"
FROM
    "analyzer_project_statuses"
WHERE
    "analyzer_project_statuses"."project_id" IN (86624825, 74581983, 86624706)
    AND "analyzer_project_statuses"."analyzer_type" = 0
    AND "analyzer_project_statuses"."status" != 0
LIMIT 3
Query plan

Full details here

 Limit  (cost=0.56..7.74 rows=1 width=8) (actual time=8.592..11.222 rows=1 loops=1)
   Buffers: shared hit=7 read=9
   I/O Timings: read=11.070 write=0.000
   ->  Index Scan using index_analyzer_project_statuses_status on public.analyzer_project_statuses  (cost=0.56..7.74 rows=1 width=8) (actual time=8.589..11.218 rows=1 loops=1)
         Index Cond: ((analyzer_project_statuses.project_id = ANY ('{86624825,74581983,86624706}'::bigint[])) AND (analyzer_project_statuses.analyzer_type = 0))
         Index Searches: 3
         Filter: (analyzer_project_statuses.status <> 0)
         Buffers: shared hit=7 read=9
         I/O Timings: read=11.070 write=0.000
Settings: seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5'
Query ID: 7326063551803171691

How to set up and validate locally

  1. Pick three projects in one top level group. Project A has a custom SAST profile Old attached. Project B has SAST scan coverage (a sast analyzer status that is not not_configured) but no profile. Project C has neither.

  2. Run the mutation once per mode. Change mode to SET, REPLACE, APPEND, FILL_PROFILE_GAPS or FILL_SCAN_GAPS. The profile is the SAST preset:

    mutation {
  securityScanProfileAttach(input: {
    securityScanProfileId: "gid://gitlab/Security::ScanProfile/sast",
    projectIds: [
      "gid://gitlab/Project/<A>",
      "gid://gitlab/Project/<B>",
      "gid://gitlab/Project/<C>"
    ],
    mode: SET
  }) {
    errors
  }
}

  3. Check the profiles attached to A, B and C after each run:

    Mode A B C
    SET (also the result when mode is omitted) SAST preset only SAST preset SAST preset
    REPLACE SAST preset only no profile no profile
    APPEND Old and SAST preset SAST preset SAST preset
    FILL_PROFILE_GAPS Old only SAST preset SAST preset
    FILL_SCAN_GAPS Old only no profile SAST preset

  4. Reset between runs. Attach Old to A with mode: SET, then detach the SAST preset from B and C with securityScanProfileDetach.

  5. Run FILL_SCAN_GAPS with a profile of a scan type that has no analyzer status, for example a triage and remediation profile. The response has the error Scan type 'triage_and_remediation' does not support the FILL_SCAN_GAPS mode.

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.

Edited by Gal Katz

Merge request reports