Add mode to security scan profile attach

Gal Katzrequested to merge
gkatz_mode_to_scan_profile_attach into master

What does this MR do and why?

Attaching a scan profile with securityScanProfileAttach now replaces the project's existing profiles of the same scan type instead of adding another one.

A project could collect several profiles of one type, (e.g all three triage and remediation presets). The new mode argument keeps the old behavior available: REPLACE is the default and APPEND adds the profile alongside the existing ones, to match the description in multiple places in the UI.

  • ProjectAttachService deletes the same type links in the same transaction as the insert, resets the replaced statuses to not_configured and records one security_scan_profile_replaced_on_project audit event per project.
  • AttachService and AttachWorker carry replace_existing for the group path. Jobs queued before the deploy have no such argument and run as replace.
  • ScanProfileStatus::UpdateService upserts a status row when a pipeline finishes without checking that the profile is still linked. A pipeline finishing during a replace or detach could leave a status row for a profile that is no longer attached. The link read and the upsert now share one transaction and the links are read with FOR SHARE. A concurrent replace waits for the upsert and then resets that row. If the replace commits first the link is gone and no row is written, so the stale row cannot happen.
  • The link read is ordered by profile id so rows are locked in a consistent order, which makes a deadlock with a concurrent replace less likely.

Changelog: changed
EE: true

Related issue

#631608 (closed)

Query plans

mark_statuses_unconfigured - update_all

Raw SQL 
UPDATE "security_scan_profile_project_statuses"
SET "status" = 0,
    "consecutive_failure_count" = 0,
    "consecutive_success_count" = 0,
    "updated_at" = '2026-10-01 23:27:23.614066'
WHERE "security_scan_profile_project_statuses"."project_id" IN (80134063, 8236)
  AND "security_scan_profile_project_statuses"."security_scan_profile_id" = 1050
Query plan

Full details - https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/66569/commands/171323

 Update on public.security_scan_profile_project_statuses  (cost=0.42..5.37 rows=0 width=0) (actual time=8.042..8.043 rows=0 loops=1)
   Buffers: shared hit=20 read=28 dirtied=7
   WAL: records=7 fpi=7 bytes=53289
   I/O Timings: read=7.644 write=0.000
   ->  Index Scan using idx_security_scan_profile_proj_statuses_on_project_and_profile on public.security_scan_profile_project_statuses  (cost=0.42..5.37 rows=1 width=20) (actual time=1.664..1.666 rows=1 loops=1)
         Index Cond: ((security_scan_profile_project_statuses.project_id = ANY ('{80134063,8236}'::bigint[])) AND (security_scan_profile_project_statuses.security_scan_profile_id = 1050))
         Index Searches: 2
         Buffers: shared hit=4 read=6
         I/O Timings: read=1.595 write=0.000
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off'
Query ID: -4950123411186125244

Security::ScanProfileProject.lock_linked_profile_ids, locking read in the status update

Raw SQL 
SELECT "security_scan_profiles_projects"."security_scan_profile_id"
FROM "security_scan_profiles_projects"
WHERE "security_scan_profiles_projects"."project_id" = 8235
  AND "security_scan_profiles_projects"."security_scan_profile_id" IN (3170, 3171, 3172)
ORDER BY "security_scan_profiles_projects"."security_scan_profile_id" ASC
LIMIT 1000
FOR SHARE
Query plan

Full details - https://console.postgres.ai/gitlab/projects/gitlab-production-sec/sessions/60059/commands/165406

 Limit  (cost=0.42..7.30 rows=1 width=14) (actual time=2.389..2.396 rows=1 loops=1)
   Buffers: shared hit=7 read=2 dirtied=2
   WAL: records=1 fpi=1 bytes=8243
   I/O Timings: read=2.148 write=0.000
   ->  LockRows  (cost=0.42..7.30 rows=1 width=14) (actual time=2.387..2.392 rows=1 loops=1)
         Buffers: shared hit=7 read=2 dirtied=2
         WAL: records=1 fpi=1 bytes=8243
         I/O Timings: read=2.148 write=0.000
         ->  Index Scan using index_security_scan_profiles_projects_on_unique_project_profile on public.security_scan_profiles_projects  (cost=0.42..7.29 rows=1 width=14) (actual time=1.792..1.795 rows=1 loops=1)
               Index Cond: ((security_scan_profiles_projects.project_id = 80134063) AND (security_scan_profiles_projects.security_scan_profile_id = ANY ('{1004782,1004782,1050}'::bigint[])))
               Index Searches: 1
               Buffers: shared hit=6 read=1
               I/O Timings: read=1.685 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 1321432754695595510

UpdateService - upsert_all

Raw SQL 
INSERT INTO "security_scan_profile_project_statuses"
  ("project_id","security_scan_profile_id","status","consecutive_failure_count",
   "consecutive_success_count","last_scan_at","build_id","created_at","updated_at")
VALUES
  (8235, 3170, 1, 0, 4, '2026-10-01 23:27:23.711303', 123, '2026-10-01 23:27:23.711303', '2026-10-01 23:27:23.711303'),
  (8236, 3171, 1, 0, 4, '2026-10-01 23:27:23.711303', 123, '2026-10-01 23:27:23.711303', '2026-10-01 23:27:23.711303')
ON CONFLICT ("project_id","security_scan_profile_id") DO UPDATE SET
  "status"=excluded."status",
  "consecutive_failure_count"=excluded."consecutive_failure_count",
  "consecutive_success_count"=excluded."consecutive_success_count",
  "last_scan_at"=excluded."last_scan_at",
  "build_id"=excluded."build_id",
  "created_at"=excluded."created_at",
  "updated_at"=excluded."updated_at"
RETURNING "id"
Query plan

Full details - https://console.postgres.ai/gitlab/projects/gitlab-production-sec/sessions/60059/commands/165404

 Insert on public.security_scan_profile_project_statuses  (cost=0.00..0.03 rows=2 width=62) (actual time=0.610..0.900 rows=2 loops=1)
   Buffers: shared hit=66 read=2 dirtied=3
   WAL: records=12 fpi=3 bytes=23261
   I/O Timings: read=0.427 write=0.000
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=62) (actual time=0.108..0.115 rows=2 loops=1)
         Buffers: shared hit=19
         I/O Timings: read=0.000 write=0.000
Trigger RI_ConstraintTrigger_c_64917 for constraint fk_rails_840795ce98: time=2.913 calls=2
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off'
Query ID: -3325503715101427597

How to set up and validate locally

  1. Attach two different triage and remediation presets with APPEND: 
    mutation {
  conservative: securityScanProfileAttach(input: {
    securityScanProfileId: "gid://gitlab/Security::ScanProfile/triage_and_remediation_conservative",
    projectIds: ["gid://gitlab/Project/<id>"],
    mode: APPEND
  }) {
    errors
  }
  standard: securityScanProfileAttach(input: {
    securityScanProfileId: "gid://gitlab/Security::ScanProfile/triage_and_remediation_standard",
    projectIds: ["gid://gitlab/Project/<id>"],
    mode: APPEND
  }) {
    errors
  }
}
  2. Check that both presets are attached: 
    query {
  project(fullPath: "<group>/<project>") {
    securityScanProfiles {
      name
    }
  }
}
  3. Attach the third preset without mode, which defaults to REPLACE: 
    mutation {
  securityScanProfileAttach(input: {
    securityScanProfileId: "gid://gitlab/Security::ScanProfile/triage_and_remediation_proactive",
    projectIds: ["gid://gitlab/Project/<id>"]
  }) {
    errors
  }
}
  4. Run the check query again. Only the Proactive preset is returned.
  5. Validate the audit event. In the project, go to Secure > Audit events. There is one security_scan_profile_replaced_on_project event whose message names both replaced presets and the Proactive preset. From the Rails console: 
    AuditEventReader.where(entity_type: 'Project', entity_id: <id>).map { |e| e.details.slice(:event_name, :custom_message) }

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