Add remaining licenses without spdx to custom software license table

What does this MR do and why?

In MR !171528 (merged), we added a migration to copy the licenses without spdx identifier on the software_licenses table to the custom_software_license table. However we are not able to enable the feature flag custom_software_licenses without also updating the software_license_policies references.

This MR repeats the same post-migration from MR !171528 (merged) to copy the licenses without spdx created after the MR !171528 (merged) (merged)to the custom_software_license table. However it also updates our code to create a new custom_software_license record when a license without spdx is created, allowing us to enable and disable the feature flag custom_software_license as needed.

Query

INSERT INTO custom_software_licenses (name, project_id)
  SELECT
    name,
    project_id
FROM
    software_license_policies
    INNER JOIN software_licenses ON (software_licenses.id = software_license_policies.software_license_id)
WHERE
    software_licenses.spdx_identifier IS NULL
    AND software_license_policies.id IN (SELECT "software_license_policies"."id" FROM "software_license_policies" WHERE "software_license_policies"."id" BETWEEN 21 AND 38041 AND "software_license_policies"."id" >= 21 AND "software_license_policies"."id" < 25127)
ON CONFLICT DO NOTHING

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/34040/commands/104700

Migration sample

INSERT INTO custom_software_licenses (name, project_id)
  SELECT
    name,
    project_id
FROM
    software_license_policies
    INNER JOIN software_licenses ON (software_licenses.id = software_license_policies.software_license_id)
WHERE
    software_licenses.spdx_identifier IS NULL
    AND software_license_policies.id IN (SELECT "software_license_policies"."id" FROM "software_license_policies" WHERE "software_license_policies"."id" BETWEEN 21 AND 38041 AND "software_license_policies"."id" >= 21 AND "software_license_policies"."id" < 25127)
ON CONFLICT DO NOTHING

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

How to set up and validate locally

1. Create a new project
2. Go to Secure > Policies
3. Click in New policy
4. Select Merge request approval policy
5. Create a policy like:

type: approval_policy
name: test
description: ''
enabled: true
rules:
  - type: license_finding
    match_on_inclusion_license: true
    license_types:
      - Some Custom License
    license_states:
      - detected
    branch_type: default
actions:
  - type: require_approval
    approvals_required: 1
    role_approvers:
      - developer
  - type: send_bot_message
    enabled: true
approval_settings:
  block_branch_modification: false
  prevent_pushing_and_force_pushing: false
  prevent_approval_by_author: false
  prevent_approval_by_commit_author: false
  remove_approvals_with_new_commit: false
  require_password_to_approve: false
fallback_behavior:
  fail: closed

6. Merge the policy
7. Verify using the rails console that a new SoftwareLicense, and CustomSoftwareLicense were created

SoftwareLicense.last
Security::CustomSoftwareLicense.last

[2] pry(main)> SoftwareLicense.last
  SoftwareLicense Load (5.5ms)  SELECT "software_licenses".* FROM "software_licenses" ORDER BY "software_licenses"."id" DESC LIMIT 1 
=> #<SoftwareLicense:0x000000030acff6e0 id: 25673, name: "Some Custom License", spdx_identifier: nil>

[4] pry(main)> Security::CustomSoftwareLicense.last
  Security::CustomSoftwareLicense Load (2.7ms)  SELECT "custom_software_licenses".* FROM "custom_software_licenses" ORDER BY "custom_software_licenses"."id" DESC LIMIT 1 
=> #<Security::CustomSoftwareLicense:0x000000030ac1cfe8 id: 25102, project_id: 1089, name: "Some Custom License">

8. Verify that the SoftwareLicensePolicy is linked to the SoftwareLicense record.

pry(main)> SoftwareLicensePolicy.last
  SoftwareLicensePolicy Load (6.6ms)  SELECT "software_license_policies".* FROM "software_license_policies" ORDER BY "software_license_policies"."id" DESC LIMIT 1 
=> #<SoftwareLicensePolicy:0x000000030a6f9728
 id: 38041,
 project_id: 1089,
 software_license_id: 25673,
 classification: "denied",
 created_at: Mon, 25 Nov 2024 21:35:09.365347000 UTC +00:00,
 updated_at: Mon, 25 Nov 2024 21:35:09.365347000 UTC +00:00,
 scan_result_policy_id: 799,
 custom_software_license_id: nil,
 approval_policy_rule_id: 124>