Skip to content

Database changes for license approval using scan result policies

Sashi Kumar Kumaresan requested to merge sk/385605-db-changes into sk/385605-db-changes-1

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:

1. Add unique index to software_license_policies

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)

console.postgres.ai

2. Remove existing unique index in software_license_policies

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)

console.postgre.ai

3. Add match_on_inclusion boolean column to scan_result_policies

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) ===

console.postgres.ai

Schema Relation

Untitled

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.

Edited by Sashi Kumar Kumaresan

Merge request reports