Update software license policies with custom licenses

What does this MR do and why?

In MR !173790 (merged), we added a migration to copy the licenses without the spdx identifier on the software_licenses table to the custom_software_license table. In this MR, we update the software_license_policies records to use the migrated custom_software_license. We are not removing the references to the software_license table yet, allowing us to turn on and off the custom_software_license feature flag as needed.

Database batch query

UPDATE software_license_policies
  SET custom_software_license_id = custom_software_licenses.id
FROM
    custom_software_licenses
    JOIN software_licenses ON custom_software_licenses.name = software_licenses.name
WHERE
    software_licenses.spdx_identifier IS NULL
    AND custom_software_licenses.project_id = software_license_policies.project_id
    AND software_licenses.id = software_license_policies.software_license_id
    AND software_license_policies.id IN (SELECT "software_license_policies"."id" FROM "software_license_policies" WHERE "software_license_policies"."id" BETWEEN 21 AND 38043 AND "software_license_policies"."id" >= 37527 AND "software_license_policies"."id" < 37829)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/34257/commands/105492

References

Please include cross links to any resources that are relevant to this MR This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

• Related to #478520 (closed)

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

How to set up and validate locally

1. Disable the custom_software_license feature flag using the rails console

Feature.disable(:custom_software_license)

2. Enable the license_scanning_with_sbom_licenses feature flag using the rails console

Feature.enable(:license_scanning_with_sbom_licenses)

2. Create a new project
3. Add an empty Gemfile.lock file
4. Add a file called gl-sbom-gem-bundler.cdx.json with the content. This file defines a component with a license that is not on the SPDX list.

{
    "bomFormat": "CycloneDX",
    "specVersion": "1.4",
    "serialNumber": "urn:uuid:a15e529c-2113-4a11-a694-6bc3ea4e2b53",
    "version": 1,
    "metadata": {
        "timestamp": "2022-02-23T08:02:39Z",
        "tools": [
            {
                "vendor": "GitLab",
                "name": "Gemnasium",
                "version": "2.34.0"
            }
        ],
        "authors": [
            {
                "name": "GitLab",
                "email": "support@gitlab.com"
            }
        ],
        "properties": [
            {
                "name": "gitlab:dependency_scanning:input_file:path",
                "value": "Gemfile.lock"
            },
            {
                "name": "gitlab:dependency_scanning:package_manager:name",
                "value": "bundler"
            },
            {
                "name": "gitlab:meta:schema_version",
                "value": "1"
            }
        ]
    },
    "components": [
        {
            "name": "sidekiq",
            "version": "4.2.10",
            "purl": "pkg:gem/sidekiq@4.2.10",
            "type": "library",
            "bom-ref": "pkg:gem/sidekiq@4.2.10",
            "licenses": [
                {
                    "license": {
                        "name": "New-Custom-License"
                    }
                }
            ]
        }
    ]
}

5. Add a .gitlab-ci.yml with the content

include:
  - template: Jobs/Dependency-Scanning.gitlab-ci.yml

gemnasium-dependency_scanning:
  stage: test
  script: 'pwd'
  artifacts:
    reports:
      cyclonedx: gl-sbom-gem-bundler.cdx.json

6. Go to Build > Pipelines and wait for the pipeline to finish
7. Click on the licenses tab and verify the component has the license defined in the report
8. Go to Secure > Policies
9. Click in New policy
10. Select Merge request approval policy
11. Create a policy to block the same license defined in the report:

type: approval_policy
name: policy
description: ''
enabled: true
rules:
  - type: license_finding
    match_on_inclusion_license: true
    license_types:
      - New-Custom-License
    license_states:
      - detected
    branches: []
actions:
  - type: require_approval
    approvals_required: 1
    role_approvers:
      - developer
  - type: send_bot_message
    enabled: true
approval_settings:
  block_branch_modification: true
  prevent_pushing_and_force_pushing: true
fallback_behavior:
  fail: closed

12. Verify using the rails console

• That a new custom_software_license was created

Security::CustomSoftwareLicense.last
 Security::CustomSoftwareLicense Load (2.1ms)  SELECT "custom_software_licenses".* FROM "custom_software_licenses" ORDER BY "custom_software_licenses"."id" DESC LIMIT 1
=> #<Security::CustomSoftwareLicense:0x000000016e781238 id: 25105, project_id: 1108, name: "New-Custom-License">

• And also that a new software_license without spdx_identifier was created

SoftwareLicense.last
SoftwareLicense Load (4.8ms)  SELECT "software_licenses".* FROM "software_licenses" ORDER BY "software_licenses"."id" DESC LIMIT 1 
=> #<SoftwareLicense:0x000000016e0e9f38 id: 25674, name: "New-Custom-License", spdx_identifier: nil>

• The software_license_policy is linked to both a custom_software_license and software_license

SoftwareLicensePolicy.last
  SoftwareLicensePolicy Load (3.0ms)  SELECT "software_license_policies".* FROM "software_license_policies" ORDER BY "software_license_policies"."id" DESC LIMIT 1
=> #<SoftwareLicensePolicy:0x0000000168f138a8
 id: 38042,
 project_id: 1108,
 software_license_id: 25674,
 classification: "denied",
 created_at: Fri, 06 Dec 2024 18:14:09.105232000 UTC +00:00,
 updated_at: Fri, 06 Dec 2024 18:14:09.105232000 UTC +00:00,
 scan_result_policy_id: 801,
 custom_software_license_id: 25105,
 approval_policy_rule_id: 126,
 software_license_spdx_identifier: nil>

13. Create an MR editing the README file

14. Verify the MR is blocked

15. Enable the FF custom_software_license feature flag using the rails console

Feature.enable(:custom_software_license)

16. Push another commit to the MR
17. Verify the MR is still blocked
18. Repeat steps 8-12, changing the name of the custom_software_license to ensure the expected behavior happens when the ff is enabled.