Add with_enrichment_filters scope to Security Policy

What does this MR do and why?

As proposed in #585514, we are adding a cron job to refresh MR approvals for updated package metadata automatically. One of the required steps is to retrieve all policies with enrichment filters for a given project.

This MR adds a new scope with_enrichment_filters to the Security::Policy model.

This draft MR illustrates how the new scope is intended to be used.

New query

SELECT DISTINCT
	"security_policies".*
FROM
	"security_policies"
	INNER JOIN "security_policy_project_links" ON "security_policies"."id" = "security_policy_project_links"."security_policy_id"
	INNER JOIN "approval_policy_rules" ON "approval_policy_rules"."security_policy_id" = "security_policies"."id"
WHERE
	"security_policy_project_links"."project_id" = 78097844
	AND (rule_index >= 0)
	AND (approval_policy_rules.content -> 'vulnerability_attributes' ->> 'known_exploited' IS NOT NULL
		OR approval_policy_rules.content -> 'vulnerability_attributes' -> 'epss_score' IS NOT NULL)
	AND "security_policies"."enabled" = TRUE

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/48156/commands/144960

References

Related to: #585514

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 Marcos Rocha

Merge request reports

Loading