Database changes for license approval using scan result policies
What does this MR do and why?
Addresses #385605 (closed)
Migration MR for approval rules: !111682 (merged) Service changes MR: !110967 (diffs)
This MR introduces database changes required for License Approval Policies (&8092 - closed) to work with security policies. The motivation of the epic is to deprecate License-check and License compliance tab and replace it with license_finding
rules in scan result policies.
There are 3 migrations in this MR:
software_license_policies
1. Add unique index to Motivation:
We added scan_result_policy_id
to software_license_policies
as part of !109624 (merged). Before introduction the relationship with scan_result_policy, the unique index was scoped to only project and software license. With this feature, we will have software licenses from individual policies and to achieve this, we need unique index scoped to scan_result_policy_id
to.
Migration Output:
AddUniqueSoftwareLicensePoliciesIndexOnProjectAndScanResultPolicy
bundle exec rake db:migrate:up:main VERSION=20230202102928
main: == 20230202102928 AddUniqueSoftwareLicensePoliciesIndexOnProjectAndScanResultPolicy: migrating
main: -- transaction_open?()
main: -> 0.0001s
main: -- view_exists?(:postgres_partitions)
main: -> 0.1189s
main: -- index_exists?(:software_license_policies, [:project_id, :software_license_id, :scan_result_policy_id], {:unique=>true, :name=>"idx_software_license_policies_unique_on_project_and_scan_policy", :algorithm=>:concurrently})
main: -> 0.0029s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:software_license_policies, [:project_id, :software_license_id, :scan_result_policy_id], {:unique=>true, :name=>"idx_software_license_policies_unique_on_project_and_scan_policy", :algorithm=>:concurrently})
main: -> 0.0035s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20230202102928 AddUniqueSoftwareLicensePoliciesIndexOnProjectAndScanResultPolicy: migrated (0.1357s)
bundle exec rake db:migrate:down:main VERSION=20230202102928
main: == 20230202102928 AddUniqueSoftwareLicensePoliciesIndexOnProjectAndScanResultPolicy: reverting
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0797s
main: -- indexes(:software_license_policies)
main: -> 0.0027s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0001s
main: -- remove_index(:software_license_policies, {:algorithm=>:concurrently, :name=>"idx_software_license_policies_unique_on_project_and_scan_policy"})
main: -> 0.0047s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20230202102928 AddUniqueSoftwareLicensePoliciesIndexOnProjectAndScanResultPolicy: reverted (0.1007s)
software_license_policies
2. Remove existing unique index in Motivation:
As mentioned in previous migration, the old unique index has to be removed to scope it to scan_result_policy_id
Migration Output:
RemoveUniqueSoftwareLicensePoliciesIndexOnProject
bundle exec rake db:migrate:up:main VERSION=20230202103006
main: == 20230202103006 RemoveUniqueSoftwareLicensePoliciesIndexOnProject: migrating
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.1053s
main: -- indexes(:software_license_policies)
main: -> 0.0034s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0004s
main: -- remove_index(:software_license_policies, {:algorithm=>:concurrently, :name=>"index_software_license_policies_unique_per_project"})
main: -> 0.0021s
main: -- execute("RESET statement_timeout")
main: -> 0.0005s
main: == 20230202103006 RemoveUniqueSoftwareLicensePoliciesIndexOnProject: migrated (0.1219s)
bundle exec rake db:migrate:down:main VERSION=20230202103006
main: == 20230202103006 RemoveUniqueSoftwareLicensePoliciesIndexOnProject: reverting
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0787s
main: -- index_exists?(:software_license_policies, [:project_id, :software_license_id], {:unique=>true, :name=>"index_software_license_policies_unique_per_project", :algorithm=>:concurrently})
main: -> 0.0032s
main: -- Index being recreated because the existing version was INVALID: table_name: software_license_policies, column_name: [:project_id, :software_license_id]
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0005s
main: -- indexes(:software_license_policies)
main: -> 0.0019s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- remove_index(:software_license_policies, {:algorithm=>:concurrently, :name=>"index_software_license_policies_unique_per_project"})
main: -> 0.0018s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: -- add_index(:software_license_policies, [:project_id, :software_license_id], {:unique=>true, :name=>"index_software_license_policies_unique_per_project", :algorithm=>:concurrently})
main: -> 0.0020s
main: == 20230202103006 RemoveUniqueSoftwareLicensePoliciesIndexOnProject: reverted (0.1024s)
match_on_inclusion
boolean column to scan_result_policies
3. Add Motivation:
When match_on_inclusion
is set to false, only the licenses mentioned in the policy should be allowed and if the license scanning job detects any other license, it needs approval. For this to work, we also need to store this in scan_result_policies
table along with license_states
Migration Output:
AddMatchOnInclusionToScanResultPolicy
bundle exec rake db:migrate:up:main VERSION=20230206172702
main: == 20230206172702 AddMatchOnInclusionToScanResultPolicy: migrating ============
main: -- add_column(:scan_result_policies, :match_on_inclusion, :boolean, {:default=>true, :null=>false})
main: -> 0.0029s
main: == 20230206172702 AddMatchOnInclusionToScanResultPolicy: migrated (0.0043s) ===
bundle exec rake db:migrate:down:main VERSION=20230206172702
main: == 20230206172702 AddMatchOnInclusionToScanResultPolicy: reverting ============
main: -- remove_column(:scan_result_policies, :match_on_inclusion)
main: -> 0.0025s
main: == 20230206172702 AddMatchOnInclusionToScanResultPolicy: reverted (0.0035s) ===
Schema Relation
Source: dbdiagram.io
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.