Add support for vulnerability age filtering

What does this MR do and why?

Add support for vulnerability age filtering

As part of the epic &6826 (closed), we are adding the option to filter vulnerabilities by age.

With this new option, policy creators will be able to create separate policies that force approvals on previously detected vulnerabilities that may have been ignored.

1. 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)
2. Age of vulnerability can only be selected when creating a policy based on previously detected vulnerabilities.

This MR adds the backend support for the vulnerability age filtering.

In MR !122481 (merged), we introduced a new query and new indexes. The query used the detected_at attribute to filter vulnerabilities. However, detected_at could be nil, as discussed here. As suggested in this comment, we updated our query to use the created_at attribute instead.

Updated query

SELECT
    COUNT(*)
FROM
    "vulnerabilities"
WHERE (created_at >= '2023-01-09 19:56:51.902449')
    AND "vulnerabilities"."id" IN (
        SELECT
            "vulnerability_reads"."vulnerability_id"
        FROM
            "vulnerability_reads"
        WHERE
            "vulnerability_reads"."project_id" = 43831199
            AND "vulnerability_reads"."uuid" IN ('03008860-d99c-5db5-9d7e-696e281a666e', '16da8d31-cc9a-53b3-ba80-ce5c9a2d014c', '29887843-b0c3-51eb-ae32-6541ce126d8c', '7eec5d87-b1a2-530e-b63c-84ea61d2fe3c', 'b7ee0340-1677-5e1f-9d67-bbb39d361671', 'c6a62a41-8ae2-5ce8-b2c1-a9a3d01bd870', 'cb5d498a-c216-5278-85fd-364793805495', 'd09bc1f6-1c75-5016-89b5-3b5e43851117', 'ef7da7cd-095b-56d6-964e-da65da71bb7f', '1ffb3985-a983-5162-af9b-e6dbeb81deeb', '7514bafd-d628-53cb-ad32-85e77844f5d2', '85651ee3-01cd-5b0a-bc23-00c494b45b6a', '04ebf666-c4ef-50b4-b62e-313b4e325d48', '0bf4f16d-8aea-5e4c-b4df-1681baf405e8', '10ae5b79-a964-5bb2-a867-6b3a59236939', '11b31277-fce6-5bb3-874a-1bf3d5c25d3c', '12a53708-2711-5788-8922-5bf2c0de6eaa', '2b2e1d00-1c3c-5185-be54-1c49f69ad166', '2bfa606c-9962-5877-9857-ba6b25f0a914', '2c6944e3-4335-50c9-856c-4b818ee714c2', '2ea4f566-10a8-5d30-95af-ded9b566d801', '3b3c81d4-dd31-5b52-8b57-5fb6e68bfedf', '3fd16cbf-73b2-54d5-8fdc-c6ee775e4498', '41289776-b162-56d8-9054-7db0e7d12146', '46f624da-37f1-500c-a434-b3c65af91fa4', '47174b58-041b-51da-b9f0-99bd9a434f37', '5037e0bd-12d4-523e-91f0-412c93989974', '50d634ab-4ee1-53c5-a4c9-fc05c766be18', '51f1c4c3-23ea-5d98-9a7d-ce279b14f10b', '5b68c2e0-7451-5950-93b7-60ba04235b48', '5c5738c2-193f-5677-a635-ed75c6e5518b', '64fb668c-971f-57c1-8df3-877277e706b7', '6b737a49-7c0f-5391-9185-9a570ab83156', '70104250-aafa-540f-ae36-6ed6d68e3306', '7012f101-97fb-5aa5-ab3e-09b470265864', '79a83d10-0c09-58da-b5d0-9362d2b05837', '82e25ab8-1705-53e2-8fb7-9305e157a84b', '8c07ce7a-ed01-58d4-82f3-981f4954c16e', '9343b6dd-ec32-5cca-bc0b-8bfb2d2bfde1', '97e49d98-a8d4-580a-aaf2-4da860a650c7', '9c701f87-96d3-54d9-a406-aa91d0e61611', 'a2abac6f-c509-5465-8053-aaf5a8213e18', 'a80bc127-b1a5-551c-8126-99235c91c956', 'b334d494-9175-50dd-add3-886df3094986', 'b953f9f0-6dcd-542a-a22f-c8f8b85b1d1c', 'b98df0db-81e8-53ef-84e6-6dd99e05ee3b', 'bcd56f27-2096-59e8-b1ac-7321ddd16aa1', 'c501814e-f79a-5f77-bab5-d1cd70ae3eb7', 'd109ce06-5c79-5118-a48a-4bbb1be24ae3', 'd6a14526-ee08-5d46-ad4a-e51d5d9f6234')
            AND "vulnerability_reads"."state" IN (1, 4, 2, 3))

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/20197/commands/65827

The previous index, index_vulnerabilities_on_detected_at_and_id, will be used as discussed here.

Related issue: #399116 (closed)

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

1. Create a new project
2. Add a .gitlab-ci.yml file with the content:

include:
- template: Jobs/SAST.gitlab-ci.yml

3. The vulnerability age is used to filter previously detected vulnerabilities. We need to add a vulnerability to test this MR. To add a new vulnerability, using the Web IDE, add a new file with the content:

class RunScript
    def run_script
        system("cat #{params[:path]}") 
    end
end 

4. Commit the changes to a new branch and open a MR.

5. Wait for the pipeline to finish.

6. Click on the pipeline link

7. Click on the security tab and verify that a new vulnerability was detected.

8. Merge the MR.

9. Go to Secure > Vulnerability Report

10. Confirm the Vulnerability

11. Go to Manage > Members and add another user to the project with developer access to prevent the policy from being auto-approved

12. Go to Secure > Policies.

13. Click on New Policy.

14. Select Scan result policy.

15. Change to yaml mode and copy the yaml content below, adjusting the user_approvers_ids as needed:

type: scan_result_policy
name: Test vulnerability age
description: ''
enabled: true
rules:
  - type: scan_finding
    branches: []
    vulnerabilities_allowed: 0
    severity_levels:
      - high
    vulnerability_states:
      - confirmed
    scanners: []
    vulnerability_age:
      interval: day
      operator: less_than
      value: 1
actions:
  - type: require_approval
    approvals_required: 1
    user_approvers_ids:
      - 1
      - 49

16. Click on Configure with a merge request
17. Merge the new MR to add the security policy.
18. Check if vulnerability_age fields were persisted correctly

[1] pry(main)> Security::ScanResultPolicyRead.last
  Security::ScanResultPolicyRead Load (1.6ms)  SELECT "scan_result_policies".* FROM "scan_result_policies" ORDER BY "scan_result_policies"."id" DESC LIMIT 1
=> #<Security::ScanResultPolicyRead:0x0000000134cc4338
 id: 102,
 security_orchestration_policy_configuration_id: 24,
 created_at: Mon, 26 Jun 2023 13:43:37.156489000 UTC +00:00,
 updated_at: Mon, 26 Jun 2023 13:43:37.156489000 UTC +00:00,
 orchestration_policy_idx: 0,
 license_states: nil,
 match_on_inclusion: false,
 role_approvers: [],
 age_value: 1,
 age_operator: "less_than",
 age_interval: "day",
 vulnerability_attributes: nil>

19. Create a new MR with no new vulnerability
20. Check if approval is required
21. Update the security policies to use greater_than operator:

name: Test vulnerability age
description: ''
enabled: true
actions:
- type: require_approval
  approvals_required: 1
  user_approvers_ids:
  - 1
  - 49
rules:
- type: scan_finding
  branches: []
  vulnerabilities_allowed: 0
  severity_levels:
  - high
  vulnerability_states:
  - confirmed
  scanners: []
  vulnerability_age:
    interval: day
    operator: greater_than
    value: 1

22. Check if vulnerability_age fields were updated correctly

[1] pry(main)> Security::ScanResultPolicyRead.last
  Security::ScanResultPolicyRead Load (1.6ms)  SELECT "scan_result_policies".* FROM "scan_result_policies" ORDER BY "scan_result_policies"."id" DESC LIMIT 1
=> #<Security::ScanResultPolicyRead:0x000000014fe55808
 id: 105,
 security_orchestration_policy_configuration_id: 24,
 created_at: Mon, 26 Jun 2023 14:53:01.685460000 UTC +00:00,
 updated_at: Mon, 26 Jun 2023 14:53:01.685460000 UTC +00:00,
 orchestration_policy_idx: 0,
 license_states: nil,
 match_on_inclusion: false,
 role_approvers: [],
 age_value: 1,
 age_operator: "greater_than",
 age_interval: "day",
 vulnerability_attributes: nil>

23. Check that approval is not required since the vulnerability was detected outside the age range.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.