Match rule schedules to scan execution policies by enabled position

What does this MR do and why?

Fixes the lookup between scan execution rule schedules and scan execution policies when a disabled policy comes first.

SyncPoliciesService builds OrchestrationPolicyRuleSchedule records from active_scan_execution_policies. That list holds only enabled policies, capped at the per-configuration limit. So a rule schedule's policy_index is the policy's position among enabled scan execution policies. OrchestrationPolicyRuleSchedule#policy already reads active_scan_execution_policies.at(policy_index), which matches.

security_policies.policy_index is different. It counts every scan execution policy in the YAML, enabled or not. Two lookups compared the two indexes directly:

• Security::Policy.for_rule_schedule. PipelineCreation#security_policy_for uses it to record the PolicySchedulePipeline row for each scheduled scan pipeline. RuleScheduleService#find_security_policy uses it in the self-heal that creates per-project schedules.
• OrchestrationPolicyRuleSchedule.for_scan_execution_policy. ScanExecutionPolicies::CreateProjectSchedulesService uses it.

When a disabled scan execution policy comes before an enabled one, both lookups return the wrong policy.

• With the per-project schedule flags off, scheduled scan pipelines are recorded against the wrong policy, for example the disabled one.
• With the WIP flags on, per-project schedules are created from the wrong rules, or not at all.

The fix:

• Add Security::Policy#rule_schedule_index. It returns the policy's position among enabled, undeleted scan execution policies. It returns nil for disabled policies, deleted policies, and other policy types.
• for_scan_execution_policy uses rule_schedule_index.
• for_rule_schedule selects enabled, undeleted scan execution policies ordered by index, then applies offset(rule_schedule.policy_index).limit(1).

What rule schedules store does not change.

Rollout: no migration and no feature flag. for_scan_execution_policy runs one extra COUNT query per call.

References

No linked issue.

Related feature issue: #592731 (closed)

Screenshots or screen recordings

Not applicable. No UI change. The before/after behavior is in the table below.

	Before	After
Rule schedule (cron 0 1 * * *) resolves to	Disabled policy	Enabled policy
for_scan_execution_policy(disabled)	["0 1 * * *"]	[]
for_scan_execution_policy(enabled)	[]	["0 1 * * *"]

How to set up and validate locally

1. Create a security policy project. In .gitlab/security-policies/policy.yml, define two scan execution policies with schedule rules.
   • The first has enabled: false and cadence 0 0 * * *.
   • The second has enabled: true and cadence 0 1 * * *.
2. Link the policy project to a project and let the policy sync run.
3. In the Rails console, with config set to the project's security orchestration policy configuration, run:

   config.rule_schedules.map { |rs| Security::Policy.for_rule_schedule(rs).first&.name }
   config.security_policies.type_scan_execution_policy.map { |p| [p.name, Security::OrchestrationPolicyRuleSchedule.for_scan_execution_policy(p).pluck(:cron)] }

4. The rule schedule with cron 0 1 * * * now resolves to the enabled policy. for_scan_execution_policy returns [] for the disabled policy and ["0 1 * * *"] for the enabled one.

Database

No migration. Plans are EXPLAIN (ANALYZE, BUFFERS) on the production-size gitlab-production-main thin clone, run through postgres.ai, using the configuration with the most scan execution policies (17 rows in security_policies). Each query is bounded by one policy configuration, which is capped per type.

Security::Policy#rule_schedule_index (new COUNT)

SELECT COUNT(*) FROM "security_policies" WHERE "security_policies"."type" = 1 AND "security_policies"."security_orchestration_policy_configuration_id" = 3183009 AND "security_policies"."enabled" = TRUE AND "security_policies"."policy_index" >= 0 AND "security_policies"."policy_index" < 12

Query plan

 Aggregate  (cost=3.45..3.46 rows=1 width=8) (actual time=0.311..0.312 rows=1 loops=1)
   Buffers: shared hit=20 read=1
   I/O Timings: read=0.137 write=0.000
   ->  Index Scan using index_security_policies_on_unique_config_type_policy_index on public.security_policies  (cost=0.42..3.44 rows=1 width=0) (actual time=0.301..0.307 rows=1 loops=1)
         Index Cond: ((security_policies.security_orchestration_policy_configuration_id = 3183009) AND (security_policies.type = 1) AND (security_policies.policy_index >= 0) AND (security_policies.policy_index < 12))
         Filter: security_policies.enabled
         Rows Removed by Filter: 11
         Buffers: shared hit=20 read=1
         I/O Timings: read=0.137 write=0.000

Security::Policy.for_rule_schedule (changed scope)

SELECT "security_policies".* FROM "security_policies" WHERE "security_policies"."type" = 1 AND "security_policies"."security_orchestration_policy_configuration_id" = 3183009 AND (policy_index >= 0) AND "security_policies"."enabled" = TRUE ORDER BY "security_policies"."policy_index" ASC LIMIT 1 OFFSET 8

Query plan

 Limit  (cost=3.44..6.46 rows=1 width=560) (actual time=0.105..0.106 rows=0 loops=1)
   Buffers: shared hit=26
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_security_policies_on_unique_config_type_policy_index on public.security_policies  (cost=0.42..3.44 rows=1 width=560) (actual time=0.079..0.104 rows=5 loops=1)
         Index Cond: ((security_policies.security_orchestration_policy_configuration_id = 3183009) AND (security_policies.type = 1) AND (security_policies.policy_index >= 0))
         Filter: security_policies.enabled
         Rows Removed by Filter: 12
         Buffers: shared hit=26
         I/O Timings: read=0.000 write=0.000

OrchestrationPolicyRuleSchedule.for_scan_execution_policy (unchanged SQL, policy_index value now comes from rule_schedule_index)

SELECT "security_orchestration_policy_rule_schedules".* FROM "security_orchestration_policy_rule_schedules" WHERE "security_orchestration_policy_rule_schedules"."security_orchestration_policy_configuration_id" = 3183009 AND "security_orchestration_policy_rule_schedules"."policy_index" = 8 AND "security_orchestration_policy_rule_schedules"."policy_type" = 0

Query plan

 Index Scan using index_sop_schedules_on_sop_configuration_id on public.security_orchestration_policy_rule_schedules  (cost=0.28..4.81 rows=1 width=84) (actual time=4.252..4.253 rows=0 loops=1)
   Index Cond: (security_orchestration_policy_rule_schedules.security_orchestration_policy_configuration_id = 3183009)
   Filter: ((security_orchestration_policy_rule_schedules.policy_index = 8) AND (security_orchestration_policy_rule_schedules.policy_type = 0))
   Rows Removed by Filter: 2
   Buffers: shared hit=3 read=4 dirtied=2
   WAL: records=4 fpi=2 bytes=15598
   I/O Timings: read=3.475 write=0.000

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.