BE: Support new Age filtering options

Why are we doing this work

Policy creators will be able to create separate policies that force approvals on previously detected vulnerabilities that may have been ignored. Users can choose to require approvals if the age of the vulnerability is more than or less than the selected age (X Days, weeks, months, or years)

This issue focusses on updating backend to support filtering the age filter

Relevant links

• Epic: Additional Filtering for Scan Result Policies (&6826 - closed)

Non-functional requirements

• Documentation: Update scan_finding rule type to include age attribute
• Feature flag:
• Performance:
• Testing:

Implementation plan

• Update security_orchestration_policy.json JSON schema to add definition for age attribute: MR !123956 (merged)

                "vulnerability_age": {
                  "type": "object",
                  "properties": {
                    "operator": {
                      "enum": [
                        "greater_than",
                        "less_than"
                      ],
                      "type": "string",
                      "description": "Specify the operator to which the age value is compared to"
                    },
                    "value": {
                      "description": "Specifies a age number",
                      "type": "integer"    
                    },
                    "interval": {
                      "enum": [
                        "day",
                        "week",
                        "month",
                        "year"
                      ],
                      "type": "string",
                      "description": "Specify the interval to which the age value is compared to"
                    }
                  }
                }

• Add vulnerability_age columns(operator, value & interval)to scan_result_policies table MR !122239 (merged)
• Update Security::SecurityOrchestrationPolicies::ProcessScanResultPolicyService to persist the age related columns in ScanResultPolicyRead. MR !123956 (merged)
• Create index on detected_at and id: MR !122481 (merged)

CREATE INDEX index_vulnerabilities_on_detected_at_and_id ON vulnerabilities USING btree (id, detected_at);

• Create index on uuid, project_id and state: MR !122481 (merged)

CREATE INDEX index_vulnerability_reads_on_uuid_project_id_and_state ON vulnerability_reads USING btree (uudi, project_id, state);

• Update Security::ScanResultPolicies::VulnerabilitiesCountService to consider age attributes to calculate count. MR !122481 (merged)

diff --git a/ee/app/services/security/scan_result_policies/vulnerabilities_count_service.rb b/ee/app/services/security/scan_result_policies/vulnerabilities_count_service.rb
index d1132490390a..c77090535282 100644
--- a/ee/app/services/security/scan_result_policies/vulnerabilities_count_service.rb
+++ b/ee/app/services/security/scan_result_policies/vulnerabilities_count_service.rb
@@ -5,15 +5,15 @@ module ScanResultPolicies
     class VulnerabilitiesCountService
       COUNT_BATCH_SIZE = 50
 
-      def initialize(pipeline:, uuids:, states:, age: ,allowed_count:)
+      def initialize(pipeline:, uuids:, states:, vulnerability_age:, allowed_count:)
         @pipeline = pipeline
         @uuids = uuids
         @states = states
-        @age = age
+        @vulnerability_age = vulnerability_age
         @allowed_count = allowed_count
       end
 
-      attr_reader :pipeline, :uuids, :states, :age, :allowed_count
+      attr_reader :pipeline, :uuids, :states, :vulnerability_age, :allowed_count
 
       def execute
         result_count = 0
@@ -36,13 +36,26 @@ def execute
       def count_vulnerabilities_by_uuid_and_state(uuids_batch)
         reads = pipeline.project.vulnerability_reads.by_uuid(uuids_batch).with_states(states)
 
-        if age
+        if vulnerability_age
+          operator = case vulnerability_age[:operator]
+                     when :greater_tha then '>='
+                     when :less_than then '<='
+                     end
+          interval_in_days = case vulnerability_age[:interval]
+                             when :days then 1
+                             when :weeks then 7
+                             when :months then 30
+                             when :years then 365
+                             end
+
+          age_in_days = vulnerability_age[:value] * interval_in_days
+
           return Vulnerability
-            .where(detected_at >= age)
+            .where("detected_at #{operator} ?", age_in_days.days.ago)
             .where(id: reads.select(:vulnerability_id))
             .count
         end
-        
+
         reads.count
       end
     end

Verification steps

• Follow the test cases mentioned in Product Acceptance Test Cases
• YAML format:

type: scan_result_policy
name: Test Policy
description: ''
enabled: true
rules:
  - type: scan_finding
    branches: []
    scanners: []
    vulnerabilities_allowed: 0
    severity_levels:
      - critical
      - high
    vulnerability_states:
      - detected
+   vulnerability_age:
+     operator: greater_than
+     value: 10
+     interval: days
actions:
  - type: require_approval
    approvals_required: 1
    role_approvers:
      - maintainer