Skip to content

Drop unused advisory tables

Brian Williams requested to merge bwill/delete-vulnerability-advisories into master

What does this MR do and why?

Drop the vulnerability_advisories and sbom_vulnerable_component_versions tables. These were planned to store advisory information related to vulnerable software components, but the pm_advisories table in the PackageMetadata namespace is being used for this instead.

These tables are empty and have no usage in the GitLab Rails application, so they are safe to drop in a post-deployment migration: https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#dropping-tables

Relates to: #416357 (closed)

Migrations

Up

main: == [advisory_lock_connection] object_id: 220300, pg_backend_pid: 235
main: == 20230705141703 RollbackVulnerabilityAdvisoriesForeignKeyOnVulnerableComponentVersions: migrating 
main: == 20230705141703 RollbackVulnerabilityAdvisoriesForeignKeyOnVulnerableComponentVersions: migrated (0.0058s) 
main: == 20230705141733 RollbackComponentVersionForeignKeyOnVulnerableComponentVersions: migrating 
main: == 20230705141733 RollbackComponentVersionForeignKeyOnVulnerableComponentVersions: migrated (0.0040s) 
main: == 20230705142241 DropVulnerableComponentVersions: migrating ==================
main: -- drop_table(:sbom_vulnerable_component_versions)
main:    -> 0.0027s
main: == 20230705142241 DropVulnerableComponentVersions: migrated (0.0070s) =========
main: == 20230705142334 DropVulnerabilitiesAdvisories: migrating ====================
main: -- drop_table(:vulnerability_advisories)
main:    -> 0.0017s
main: == 20230705142334 DropVulnerabilitiesAdvisories: migrated (0.0060s) ===========
main: == [advisory_lock_connection] object_id: 220300, pg_backend_pid: 235

Down

$ scripts/db_tasks db:migrate:down VERSION=20230705142334
Running: `bundle exec rake db:migrate:down:main VERSION=20230705142334`
main: == [advisory_lock_connection] object_id: 220520, pg_backend_pid: 118
main: == 20230705142334 DropVulnerabilitiesAdvisories: reverting ====================
main: -- create_table(:vulnerability_advisories, {:id=>false})
main: -- quote_column_name(:description)
main:    -> 0.0000s
main: -- quote_column_name(:title)
main:    -> 0.0000s
main: -- quote_column_name(:component_name)
main:    -> 0.0000s
main: -- quote_column_name(:solution)
main:    -> 0.0000s
main: -- quote_column_name(:not_impacted)
main:    -> 0.0000s
main: -- quote_column_name(:cvss_v2)
main:    -> 0.0000s
main: -- quote_column_name(:cvss_v3)
main:    -> 0.0000s
main: -- quote_column_name(:affected_range)
main:    -> 0.0000s
main:    -> 0.0072s
main: == 20230705142334 DropVulnerabilitiesAdvisories: reverted (0.0702s) ===========
main: == [advisory_lock_connection] object_id: 220520, pg_backend_pid: 118
$ scripts/db_tasks db:migrate:down VERSION=20230705142241
Running: `bundle exec rake db:migrate:down:main VERSION=20230705142241`
main: == [advisory_lock_connection] object_id: 220020, pg_backend_pid: 146
main: == 20230705142241 DropVulnerableComponentVersions: reverting ==================
main: -- create_table(:sbom_vulnerable_component_versions)
main:    -> 0.0058s
main: == 20230705142241 DropVulnerableComponentVersions: reverted (0.0147s) =========
main: == [advisory_lock_connection] object_id: 220020, pg_backend_pid: 146
$ scripts/db_tasks db:migrate:down VERSION=20230705141733
Running: `bundle exec rake db:migrate:down:main VERSION=20230705141733`
main: == [advisory_lock_connection] object_id: 220020, pg_backend_pid: 174
main: == 20230705141733 RollbackComponentVersionForeignKeyOnVulnerableComponentVersions: reverting 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE sbom_vulnerable_component_versions ADD CONSTRAINT fk_8a2a1197f9 FOREIGN KEY (sbom_component_version_id) REFERENCES sbom_component_versions (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0012s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- execute("ALTER TABLE sbom_vulnerable_component_versions VALIDATE CONSTRAINT fk_8a2a1197f9;")
main:    -> 0.0011s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20230705141733 RollbackComponentVersionForeignKeyOnVulnerableComponentVersions: reverted (0.0644s) 
main: == [advisory_lock_connection] object_id: 220020, pg_backend_pid: 174
$ scripts/db_tasks db:migrate:down VERSION=20230705141703
Running: `bundle exec rake db:migrate:down:main VERSION=20230705141703`
main: == [advisory_lock_connection] object_id: 220020, pg_backend_pid: 202
main: == 20230705141703 RollbackVulnerabilityAdvisoriesForeignKeyOnVulnerableComponentVersions: reverting 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE sbom_vulnerable_component_versions ADD CONSTRAINT fk_d720a1959a FOREIGN KEY (vulnerability_advisory_id) REFERENCES vulnerability_advisories (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0012s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- execute("ALTER TABLE sbom_vulnerable_component_versions VALIDATE CONSTRAINT fk_d720a1959a;")
main:    -> 0.0011s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230705141703 RollbackVulnerabilityAdvisoriesForeignKeyOnVulnerableComponentVersions: reverted (0.0655s) 
main: == [advisory_lock_connection] object_id: 220020, pg_backend_pid: 202

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 Brian Williams

Merge request reports