MR Approval Policies ignore vulnerability severity overrides when evaluating security findings

Summary

MR Approval Policies are not respecting vulnerability severity overrides when evaluating security findings, leading to inconsistent security policy enforcement. This occurs because approval policies query the Security::Finding table directly instead of considering overridden severities stored in vulnerability records.

Problem Description

The issue stems from architectural inconsistencies in how different GitLab security features handle vulnerability severity:

1. MR Approval Policies and Security Policy Bot Messgae: Use Security::Finding.severity directly without considering overrides
2. Security Reports/Vulnerability Management: Show overridden severity from vulnerability records

This creates a disconnect where:

• A vulnerability may be overridden from "High" to "Low" severity in the vulnerability management interface
• MR Approval Policies continue to treat it as "High" severity, potentially blocking merges inappropriately
• Security reports show the correct overridden "Low" severity

Technical Root Cause

As identified by @bwill, the core issue is in the ScanResultPolicies::FindingsFinder which uses only the Security::Finding's severity without accounting for vulnerability_severity_overrides.

The current logic:

• Queries security_findings table directly for severity filtering
• Ignores any severity overrides stored in vulnerability_severity_overrides table
• Results in policy decisions based on original, potentially outdated severity levels

Impact

• Inconsistent Security Posture: Different GitLab features show different severity levels for the same vulnerability
• Workflow Disruption: MRs may be unnecessarily blocked by approval policies based on outdated severity assessments
• User Confusion: Security teams see conflicting information across GitLab interfaces
• Compliance Issues: Override decisions made by security teams are not reflected in policy enforcement

Proposed Solution

As suggested by @bwill: we might need to implement a database-level solution using a LATERAL JOIN to incorporate severity overrides:

SELECT
  security_findings.id,
  COALESCE(severity_overrides.new_severity, security_findings.severity) AS severity,
  -- other fields
FROM security_findings
  JOIN vulnerability_occurrences ON vulnerability_occurrences.uuid = security_findings.uuid
  JOIN vulnerabilities ON vulnerability_occurrences.vulnerability_id = vulnerabilities.id
  LEFT JOIN LATERAL (
    SELECT new_severity, original_severity
    FROM vulnerability_severity_overrides
    WHERE vulnerability_id = vulnerabilities.id
    ORDER BY id DESC
    LIMIT 1
  ) severity_overrides ON true
WHERE COALESCE(severity_overrides.new_severity, security_findings.severity) IN (target_severities);

or with denormalizing UUID (adding uuid to severity_overrides table) to reduce joins or use application-level filtering instead (remove severity filtering from SQL and handle in Ruby code)

Additionally we need to do MR Resync: Implement mechanism to re-evaluate existing MRs when severity overrides are applied

Additional Considerations

• Performance: The proposed SQL solution involves multiple joins and may impact query performance
• Backward Compatibility: Existing approval policies should continue working with the enhanced logic
• Real-time Updates: Consider how to handle MRs that are already open when severity overrides are applied