Add security scan profile attach mutation

Gal Katzrequested to merge
gkatz_security_profiles_attach_mutation into master

What does this MR do and why?

Introduces bulk mutations to attach security scan profiles to projects. This MR includes:

  • securityScanProfileAttach GraphQL mutation for attaching profiles to multiple projects and groups
  • Security::ScanProfiles::AttachService for handling attachment logic
  • Security::ScanProfiles::FindOrCreateService for creating GitLab-recommended profiles
  • apply_security_scan_profiles permission for authorization

The mutation supports both template based profile ids (e.g., gid://gitlab/Security::ScanProfile/secret_detection) and persisted profile ids for attaching existing custom profiles.

Changelog: added
EE: true

How to set up and validate locally

Setup

  1. Enable the security_scan_profiles feature flag: 
    Feature.enable(:security_scan_profiles_feature)
  2. Select a root group rg where you have at least maintainer permissions.
  3. Select (or create) two projects p1 and p2 under rg.

Test 1: Attach template-based profile and verify creation

  1. Use this GraphQL mutation to attach a template-based secret detection profile to p1: 
    mutation {
  securityScanProfileAttach(
    input: {
      securityScanProfileId: "gid://gitlab/Security::ScanProfile/secret_detection"
      projectIds: ["gid://gitlab/Project/<P1_ID>"]
    }
  ) {
    errors
  }
}
  2. Query p1 to verify the GitLab-recommended profile was created and attached: 
    {
  project(fullPath: "<p1_full_path>") {
    securityScanProfiles {
      id
      name
      description
      scanType
      gitlabRecommended
    }
  }
}
  3. Verify the response includes a profile with:
    • gitlabRecommended: true

Test 2: Attach persisted profile to another project

  1. Copy the profile id from Test 1, Step 2.
  2. Use this GraphQL mutation to attach the same profile to p2: 
    mutation {
  securityScanProfileAttach(
    input: {
      securityScanProfileId: "<PROFILE_ID_FROM_TEST_1>"
      projectIds: ["gid://gitlab/Project/<P2_ID>"]
    }
  ) {
    errors
  }
}
  3. Query p2 to verify the profile was attached.

Test 3: Bulk attachment to multiple projects

  1. Use this GraphQL mutation to attach the profile to both projects in a single request: 
    mutation {
  securityScanProfileAttach(
    input: {
      securityScanProfileId: "<PROFILE_ID>"
      projectIds: ["gid://gitlab/Project/<P1_ID>", "gid://gitlab/Project/<P2_ID>"]
    }
  ) {
    errors
  }
}
  2. Verify the mutation creates no duplicates and returns with empty errors.

Query plans

Project.root_ids_for:

This method fetches root namespace ids in two queries for better performance:

  1. Get distinct project namespace_ids
  2. Extract root namespace ids from those namespaces only Now using existing scope.

This approach avoids nested loop joins and should scale linearly with project count.

First part:

SQL 
SELECT DISTINCT
    "projects"."namespace_id"
FROM
    "projects"
WHERE
    "projects"."id" IN (70472537, 69782606, 71329491, 68794259, 66101996)
LIMIT 1000
Query plan

See details here

 Limit  (cost=16.54..16.56 rows=5 width=4) (actual time=0.069..0.072 rows=2 loops=1)
   Buffers: shared hit=31
   I/O Timings: read=0.000 write=0.000
   ->  Unique  (cost=16.54..16.56 rows=5 width=4) (actual time=0.068..0.071 rows=2 loops=1)
         Buffers: shared hit=31
         I/O Timings: read=0.000 write=0.000
         ->  Sort  (cost=16.54..16.55 rows=5 width=4) (actual time=0.067..0.068 rows=5 loops=1)
               Sort Key: projects.namespace_id
               Sort Method: quicksort  Memory: 25kB
               Buffers: shared hit=31
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using projects_pkey on public.projects  (cost=0.56..16.48 rows=5 width=4) (actual time=0.022..0.050 rows=5 loops=1)
                     Index Cond: (projects.id = ANY ('{70472537,69782606,71329491,68794259,66101996}'::integer[]))
                     Buffers: shared hit=28
                     I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'

insert_under_limit:

SQL 
INSERT INTO security_scan_profiles_projects (project_id, security_scan_profile_id, created_at, updated_at)
SELECT
    candidate_project_id,
    1,
    NOW(),
    NOW()
FROM
    UNNEST(ARRAY[66101996, 69782606, 69782162, 68794259, 68571417]) AS candidate_project_id
WHERE (
    SELECT
        COUNT(*)
    FROM
        security_scan_profiles_projects
    WHERE
        project_id = candidate_project_id) < 10
ON CONFLICT (project_id,
    security_scan_profile_id)
    DO NOTHING
RETURNING
    project_id;
Query plan

See details here

 ModifyTable on public.security_scan_profiles_projects  (cost=0.00..15.95 rows=2 width=40) (actual time=0.245..0.336 rows=5 loops=1)
   Buffers: shared hit=71
   WAL: records=26 fpi=4 bytes=4052
   ->  Function Scan on unnest candidate_project_id  (cost=0.00..15.95 rows=2 width=40) (actual time=0.166..0.179 rows=5 loops=1)
         Filter: ((SubPlan 1) < 10)
         Rows Removed by Filter: 0
         Buffers: shared hit=34
         WAL: records=1 fpi=0 bytes=99
         SubPlan 1
           ->  Aggregate  (cost=3.16..3.17 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=5)
                 Buffers: shared hit=13
                 ->  Index Only Scan using index_security_scan_profiles_projects_on_unique_project_profile on public.security_scan_profiles_projects security_scan_profiles_projects_1  (cost=0.14..3.16 rows=1 width=0) (actual time=0.013..0.013 rows=0 loops=5)
                       Index Cond: (security_scan_profiles_projects_1.project_id = candidate_project_id.candidate_project_id)
                       Heap Fetches: 5
                       Buffers: shared hit=13
Trigger RI_ConstraintTrigger_c_4226359321 for constraint fk_rails_36ece30d24: time=0.519 calls=5
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB'

upsert_profile:

SQL 
INSERT INTO "security_scan_profiles" ("namespace_id", "scan_type", "gitlab_recommended", "name", "description", "created_at", "updated_at")
    VALUES (1, 1, TRUE, 'Secret Push Protection (default)', 'GitLab''s recommended', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON CONFLICT (namespace_id, scan_type, lower(name))
    DO UPDATE SET
        updated_at = (
            CASE WHEN ("security_scan_profiles"."namespace_id" IS NOT DISTINCT FROM excluded."namespace_id"
                AND "security_scan_profiles"."scan_type" IS NOT DISTINCT FROM excluded."scan_type"
                AND "security_scan_profiles"."gitlab_recommended" IS NOT DISTINCT FROM excluded."gitlab_recommended"
                AND "security_scan_profiles"."name" IS NOT DISTINCT FROM excluded."name"
                AND "security_scan_profiles"."description" IS NOT DISTINCT FROM excluded."description") THEN
                "security_scan_profiles".updated_at
            ELSE
                CURRENT_TIMESTAMP
            END),
        "namespace_id" = excluded."namespace_id",
        "scan_type" = excluded."scan_type",
        "gitlab_recommended" = excluded."gitlab_recommended",
        "name" = excluded."name",
        "description" = excluded."description"
    RETURNING
        "id"
Query plan

See details here

 ModifyTable on public.security_scan_profiles  (cost=0.00..0.02 rows=1 width=99) (actual time=0.289..0.290 rows=1 loops=1)
   Buffers: shared hit=38 dirtied=4
   WAL: records=5 fpi=2 bytes=838
   ->  Result  (cost=0.00..0.02 rows=1 width=99) (actual time=0.096..0.096 rows=1 loops=1)
         Buffers: shared hit=15 dirtied=1
         WAL: records=1 fpi=0 bytes=99
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off'

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 mutation to bulk apply and remove... (#582824 - closed) • Gal Katz • 18.9

Edited by Gal Katz

Merge request reports