Add filter by gitlab_recommended to group security scan profiles

What does this MR do and why?

Adds filtering by gitlab_recommended to the availableSecurityScanProfiles field of Group type.

Changelog: added
EE: true

How to set up and validate locally

1. Select or create a root group g without any existing scan profiles.
2. Sign in to your GDK as a developer (at least).
3. Create a custom SAST profile:

       Security::ScanProfile.create!(
         namespace_id: g.id,
         scan_type: :sast,
         name: 'Custom SAST Profile',
         description: 'Custom SAST configuration',
         gitlab_recommended: false
       )

4. Filter by gitlabRecommended using this query:

       {
         group(fullPath: "<g.full_path>") {
           availableSecurityScanProfiles(gitlabRecommended: true) {
             name
             scanType
             gitlabRecommended
           }
         }
       }

5. Verify only the secret_detection profile is returned.
6. Repeat with false and verify only the sast profile is returned.
7. Combine with the existing filter by type:

       {
         group(fullPath: "<g.full_path>") {
           availableSecurityScanProfiles(gitlabRecommended: true, type: SECRET_DETECTION) {
             name
             scanType
             gitlabRecommended
           }
         }
       }

8. Verify only the secret_detection profile is returned.

Query plans

Note: The tables are new and empty, so expect zero returned values.

fetch_existing_profiles

SQL

SELECT
    "security_scan_profiles".*
FROM
    "security_scan_profiles"
WHERE
    "security_scan_profiles"."namespace_id" = 9970
    AND "security_scan_profiles"."scan_type" = 1
    AND "security_scan_profiles"."gitlab_recommended" = TRUE

Query plan

See plan here

 Seq Scan on public.security_scan_profiles  (cost=0.00..0.00 rows=1 width=99) (actual time=0.004..0.004 rows=0 loops=1)
   Filter: (security_scan_profiles.gitlab_recommended AND (security_scan_profiles.namespace_id = 9970) AND (security_scan_profiles.scan_type = 1))
   Rows Removed by Filter: 0
Settings: random_page_cost = '1.5', work_mem = '100MB', jit = 'off', seq_page_cost = '4', effective_cache_size = '338688MB'

• Note: The plan looks like it is using Seq Scan because the table is empty.
  Locally on a table with records this is the result (when also adding SET enable_seqscan = OFF;):

Index Scan using index_security_scan_profiles_namespace_scan_type_name on security_scan_profiles  (cost=0.15..2.17 rows=1 width=99)
  Index Cond: ((namespace_id = 9970) AND (scan_type = 1))
  Filter: gitlab_recommended

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 [Backend] Add filter by GitLab recommended to g... (#582868 - closed) • Gal Katz • 18.7