Skip to content

Migrate links for vulnerability findings from

Zamir Martins requested to merge migrate_links_for_vulnerability_findings into master

What does this MR do and why?

Migrate links from vulnerability_occurences text column (raw_data) into its own table vulnerability_finding_links.

Related issue: #362661 (closed)

DB migrate/rollback

$ bundle exec rails db:migrate
main: == 20230208125736 ScheduleMigrationForLinks: migrating ========================
main: == 20230208125736 ScheduleMigrationForLinks: migrated (0.0493s) ===============
$ bundle exec rails db:migrate:down:main VERSION=20230208125736
main: == 20230208125736 ScheduleMigrationForLinks: reverting ========================
main: == 20230208125736 ScheduleMigrationForLinks: reverted (0.0231s) ===============

Query plan

Link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15613/commands/54027

Bulk insert queries through upsert_all:

INSERT INTO "vulnerability_finding_links" ("vulnerability_occurrence_id","name","url","created_at","updated_at") VALUES (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2019-09/msg00080.html', '2023-02-17 12:41:14.456385', '2023-02-17 12:41:14.456417'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2019-09/msg00081.html', '2023-02-17 12:41:14.456425', '2023-02-17 12:41:14.456428'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2019-11/msg00000.html', '2023-02-17 12:41:14.456434', '2023-02-17 12:41:14.456437'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2019-11/msg00002.html', '2023-02-17 12:41:14.456444', '2023-02-17 12:41:14.456446'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2019-11/msg00003.html', '2023-02-17 12:41:14.456452', '2023-02-17 12:41:14.456455'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2019-11/msg00013.html', '2023-02-17 12:41:14.456462', '2023-02-17 12:41:14.456464'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2019-11/msg00016.html', '2023-02-17 12:41:14.456470', '2023-02-17 12:41:14.456472'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2019-11/msg00017.html', '2023-02-17 12:41:14.456479', '2023-02-17 12:41:14.456481'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2019-11/msg00018.html', '2023-02-17 12:41:14.456487', '2023-02-17 12:41:14.456489'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2019-11/msg00019.html', '2023-02-17 12:41:14.456494', '2023-02-17 12:41:14.456496'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2020-01/msg00008.html', '2023-02-17 12:41:14.456503', '2023-02-17 12:41:14.456506'), (543, NULL, 'http://lists.opensuse.org/opensuse-security-announce/2020-01/msg00040.html', '2023-02-17 12:41:14.456512', '2023-02-17 12:41:14.456514'), (543, NULL, 'http://packetstormsecurity.com/files/154503/Slackware-Security-Advisory-expat-Updates.html', '2023-02-17 12:41:14.456520', '2023-02-17 12:41:14.456522'), (543, NULL, 'http://packetstormsecurity.com/files/154927/Slackware-Security-Advisory-python-Updates.html', '2023-02-17 12:41:14.456529', '2023-02-17 12:41:14.456531'), (543, NULL, 'http://packetstormsecurity.com/files/154947/Slackware-Security-Advisory-mozilla-firefox-Updates.html', '2023-02-17 12:41:14.456536', '2023-02-17 12:41:14.456538'), (543, NULL, 'http://seclists.org/fulldisclosure/2019/Dec/23', '2023-02-17 12:41:14.456544', '2023-02-17 12:41:14.456546'), (543, NULL, 'http://seclists.org/fulldisclosure/2019/Dec/26', '2023-02-17 12:41:14.456551', '2023-02-17 12:41:14.456553'), (543, NULL, 'http://seclists.org/fulldisclosure/2019/Dec/27', '2023-02-17 12:41:14.456559', '2023-02-17 12:41:14.456561'), (543, NULL, 'http://seclists.org/fulldisclosure/2019/Dec/30', '2023-02-17 12:41:14.456566', '2023-02-17 12:41:14.456568'), (543, NULL, 'https://access.redhat.com/errata/RHSA-2019:3210', '2023-02-17 12:41:14.456574', '2023-02-17 12:41:14.456576'), (543, NULL, 'https://access.redhat.com/errata/RHSA-2019:3237', '2023-02-17 12:41:14.456582', '2023-02-17 12:41:14.456584'), (543, NULL, 'https://access.redhat.com/errata/RHSA-2019:3756', '2023-02-17 12:41:14.456590', '2023-02-17 12:41:14.456593'), (543, NULL, 'https://access.redhat.com/security/cve/CVE-2019-15903', '2023-02-17 12:41:14.456599', '2023-02-17 12:41:14.456601'), (543, NULL, 'https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-15903', '2023-02-17 12:41:14.456606', '2023-02-17 12:41:14.456608'), (543, NULL, 'https://github.com/libexpat/libexpat/commit/c20b758c332d9a13afbbb276d30db1d183a85d43', '2023-02-17 12:41:14.456614', '2023-02-17 12:41:14.456616'), (543, NULL, 'https://github.com/libexpat/libexpat/issues/317', '2023-02-17 12:41:14.456621', '2023-02-17 12:41:14.456623'), (543, NULL, 'https://github.com/libexpat/libexpat/issues/342', '2023-02-17 12:41:14.456628', '2023-02-17 12:41:14.456631'), (543, NULL, 'https://github.com/libexpat/libexpat/pull/318', '2023-02-17 12:41:14.456636', '2023-02-17 12:41:14.456638'), (543, NULL, 'https://lists.debian.org/debian-lts-announce/2019/11/msg00006.html', '2023-02-17 12:41:14.456644', '2023-02-17 12:41:14.456646'), (543, NULL, 'https://lists.debian.org/debian-lts-announce/2019/11/msg00017.html', '2023-02-17 12:41:14.456651', '2023-02-17 12:41:14.456653'), (543, NULL, 'https://lists.fedoraproject.org/archives/list/package-announce@lists.fedoraproject.org/message/A4TZKPJFTURRLXIGLB34WVKQ5HGY6JJA/', '2023-02-17 12:41:14.456659', '2023-02-17 12:41:14.456661'), (543, NULL, 'https://lists.fedoraproject.org/archives/list/package-announce@lists.fedoraproject.org/message/BDUTI5TVQWIGGQXPEVI4T2ENHFSBMIBP/', '2023-02-17 12:41:14.456666', '2023-02-17 12:41:14.456669'), (543, NULL, 'https://lists.fedoraproject.org/archives/list/package-announce@lists.fedoraproject.org/message/S26LGXXQ7YF2BP3RGOWELBFKM6BHF6UG/', '2023-02-17 12:41:14.456675', '2023-02-17 12:41:14.456677'), (543, NULL, 'https://nvd.nist.gov/vuln/detail/CVE-2019-15903', '2023-02-17 12:41:14.456684', '2023-02-17 12:41:14.456686'), (543, NULL, 'https://seclists.org/bugtraq/2019/Dec/17', '2023-02-17 12:41:14.456691', '2023-02-17 12:41:14.456694'), (543, NULL, 'https://seclists.org/bugtraq/2019/Dec/21', '2023-02-17 12:41:14.456700', '2023-02-17 12:41:14.456703'), (543, NULL, 'https://seclists.org/bugtraq/2019/Dec/23', '2023-02-17 12:41:14.456708', '2023-02-17 12:41:14.456710'), (543, NULL, 'https://seclists.org/bugtraq/2019/Nov/1', '2023-02-17 12:41:14.456717', '2023-02-17 12:41:14.456719'), (543, NULL, 'https://seclists.org/bugtraq/2019/Nov/24', '2023-02-17 12:41:14.456725', '2023-02-17 12:41:14.456728'), (543, NULL, 'https://seclists.org/bugtraq/2019/Oct/29', '2023-02-17 12:41:14.456733', '2023-02-17 12:41:14.456735'), (543, NULL, 'https://seclists.org/bugtraq/2019/Sep/30', '2023-02-17 12:41:14.456741', '2023-02-17 12:41:14.456748'), (543, NULL, 'https://seclists.org/bugtraq/2019/Sep/37', '2023-02-17 12:41:14.456754', '2023-02-17 12:41:14.456756'), (543, NULL, 'https://security.gentoo.org/glsa/201911-08', '2023-02-17 12:41:14.456763', '2023-02-17 12:41:14.456765'), (543, NULL, 'https://security.netapp.com/advisory/ntap-20190926-0004/', '2023-02-17 12:41:14.456771', '2023-02-17 12:41:14.456774'), (543, NULL, 'https://support.apple.com/kb/HT210785', '2023-02-17 12:41:14.456780', '2023-02-17 12:41:14.456787'), (543, NULL, 'https://support.apple.com/kb/HT210788', '2023-02-17 12:41:14.456793', '2023-02-17 12:41:14.456795'), (543, NULL, 'https://support.apple.com/kb/HT210789', '2023-02-17 12:41:14.456801', '2023-02-17 12:41:14.456803'), (543, NULL, 'https://support.apple.com/kb/HT210790', '2023-02-17 12:41:14.456809', '2023-02-17 12:41:14.456811'), (543, NULL, 'https://support.apple.com/kb/HT210793', '2023-02-17 12:41:14.456817', '2023-02-17 12:41:14.456819'), (543, NULL, 'https://support.apple.com/kb/HT210794', '2023-02-17 12:41:14.456825', '2023-02-17 12:41:14.456827'), (543, NULL, 'https://support.apple.com/kb/HT210795', '2023-02-17 12:41:14.456833', '2023-02-17 12:41:14.456835'), (543, NULL, 'https://ubuntu.com/security/notices/USN-4132-1', '2023-02-17 12:41:14.456842', '2023-02-17 12:41:14.456844'), (543, NULL, 'https://ubuntu.com/security/notices/USN-4132-2', '2023-02-17 12:41:14.456850', '2023-02-17 12:41:14.456852'), (543, NULL, 'https://ubuntu.com/security/notices/USN-4165-1', '2023-02-17 12:41:14.456858', '2023-02-17 12:41:14.456860'), (543, NULL, 'https://ubuntu.com/security/notices/USN-4202-1', '2023-02-17 12:41:14.456865', '2023-02-17 12:41:14.456867'), (543, NULL, 'https://ubuntu.com/security/notices/USN-4335-1', '2023-02-17 12:41:14.456873', '2023-02-17 12:41:14.456875'), (543, NULL, 'https://ubuntu.com/security/notices/USN-4852-1', '2023-02-17 12:41:14.456881', '2023-02-17 12:41:14.456883'), (543, NULL, 'https://ubuntu.com/security/notices/USN-5455-1', '2023-02-17 12:41:14.456889', '2023-02-17 12:41:14.456891'), (543, NULL, 'https://usn.ubuntu.com/4132-1/', '2023-02-17 12:41:14.456897', '2023-02-17 12:41:14.456899'), (543, NULL, 'https://usn.ubuntu.com/4132-2/', '2023-02-17 12:41:14.456905', '2023-02-17 12:41:14.456907'), (543, NULL, 'https://usn.ubuntu.com/4165-1/', '2023-02-17 12:41:14.456912', '2023-02-17 12:41:14.456915'), (543, NULL, 'https://usn.ubuntu.com/4202-1/', '2023-02-17 12:41:14.456921', '2023-02-17 12:41:14.456923'), (543, NULL, 'https://usn.ubuntu.com/4335-1/', '2023-02-17 12:41:14.456930', '2023-02-17 12:41:14.456932'), (543, NULL, 'https://www.debian.org/security/2019/dsa-4530', '2023-02-17 12:41:14.456937', '2023-02-17 12:41:14.456939'), (543, NULL, 'https://www.debian.org/security/2019/dsa-4549', '2023-02-17 12:41:14.456946', '2023-02-17 12:41:14.456948'), (543, NULL, 'https://www.debian.org/security/2019/dsa-4571', '2023-02-17 12:41:14.456953', '2023-02-17 12:41:14.456955'), (543, NULL, 'https://www.mozilla.org/en-US/security/advisories/mfsa2019-34/#CVE-2019-15903', '2023-02-17 12:41:14.456960', '2023-02-17 12:41:14.456963'), (543, NULL, 'https://www.oracle.com/security-alerts/cpuapr2020.html', '2023-02-17 12:41:14.456968', '2023-02-17 12:41:14.456970'), (543, NULL, 'https://www.oracle.com/security-alerts/cpuoct2020.html', '2023-02-17 12:41:14.456975', '2023-02-17 12:41:14.456977'), (543, NULL, 'https://www.tenable.com/security/tns-2021-11', '2023-02-17 12:41:14.456983', '2023-02-17 12:41:14.456985') ON CONFLICT ("id") DO UPDATE SET "vulnerability_occurrence_id"=excluded."vulnerability_occurrence_id","name"=excluded."name","url"=excluded."url","created_at"=excluded."created_at","updated_at"=excluded."updated_at"

DB test job

Link: !111454 (comment 1270848930)

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 Zamir Martins

Merge request reports