Skip to content

Temporary index for backfill owasp_top_10 migration

Bala Kumar requested to merge 419092-store-owasp-top-10-migration-tmp-index into master

What does this MR do and why?

Temporary index to support backfill migration !139544 (diffs) for storing owasp_top_10 data in vulnerability_reads table.

Index to support the query that the backfill migration uses of the form:

SELECT vulnerability_reads.vulnerability_id,
       external_id
FROM vulnerability_reads
JOIN vulnerability_occurrences ON vulnerability_reads.vulnerability_id = vulnerability_occurrences.vulnerability_id
JOIN vulnerability_occurrence_identifiers ON vulnerability_occurrences.id = vulnerability_occurrence_identifiers.occurrence_id
JOIN vulnerability_identifiers ON vulnerability_occurrence_identifiers.identifier_id = vulnerability_identifiers.id
WHERE vulnerability_reads.id IN
    (SELECT vulnerability_reads.id
     FROM vulnerability_reads
     WHERE "vulnerability_reads"."vulnerability_id" BETWEEN 1 AND 11513
       AND "vulnerability_reads"."vulnerability_id" >= 700
       AND "vulnerability_reads"."vulnerability_id" < 1000 )
  AND lower(external_type) = 'owasp'
  AND vulnerability_identifiers.external_id IN ('A1:2017',
                                                'A2:2017',
                                                'A3:2017',
                                                'A4:2017',
                                                'A5:2017',
                                                'A6:2017',
                                                'A7:2017',
                                                'A8:2017',
                                                'A9:2017',
                                                'A10:2017',
                                                'A1:2021',
                                                'A2:2021',
                                                'A3:2021',
                                                'A4:2021',
                                                'A5:2021',
                                                'A6:2021',
                                                'A7:2021',
                                                'A8:2021',
                                                'A9:2021',
                                                'A10:2021',
                                                'A1:2017-Injection',
                                                'A2:2017-Broken Authentication',
                                                'A3:2017-Sensitive Data Exposure',
                                                'A4:2017-XML External Entities (XXE)',
                                                'A5:2017-Broken Access Control',
                                                'A6:2017-Security Misconfiguration',
                                                'A7:2017-Cross-Site Scripting (XSS)',
                                                'A8:2017-Insecure Deserialization',
                                                'A9:2017-Using Components with Known Vulnerabilities',
                                                'A10:2017-Insufficient Logging & Monitoring',
                                                'A1:2021-Broken Access Control',
                                                'A2:2021-Cryptographic Failures',
                                                'A3:2021-Injection',
                                                'A4:2021-Insecure Design',
                                                'A5:2021-Security Misconfiguration',
                                                'A6:2021-Vulnerable and Outdated Components',
                                                'A7:2021-Identification and Authentication Failures',
                                                'A8:2021-Software and Data Integrity Failures',
                                                'A9:2021-Security Logging and Monitoring Failures',
                                                'A10:2021-Server-Side Request Forgery')

Database

Output of the post-deployment migration

bundle exec rake db:migrate:up:main VERSION=20231214101617

main: == [advisory_lock_connection] object_id: 181920, pg_backend_pid: 85921
main: == 20231214101617 AddTemporaryIndexToVulnerabilityIdentifiers: migrating ======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1026s
main: -- index_exists?(:vulnerability_identifiers, [:external_type, :external_id], {:where=>"lower(external_type) = 'owasp' and external_id IN (\n        'A1:2017', 'A2:2017', 'A3:2017', 'A4:2017', 'A5:2017',\n        'A6:2017', 'A7:2017', 'A8:2017', 'A9:2017', 'A10:2017',\n        'A1:2021', 'A2:2021', 'A3:2021', 'A4:2021', 'A5:2021',\n        'A6:2021', 'A7:2021', 'A8:2021', 'A9:2021', 'A10:2021',\n        'A1:2017-Injection',\n        'A2:2017-Broken Authentication',\n        'A3:2017-Sensitive Data Exposure',\n        'A4:2017-XML External Entities (XXE)',\n        'A5:2017-Broken Access Control',\n        'A6:2017-Security Misconfiguration',\n        'A7:2017-Cross-Site Scripting (XSS)',\n        'A8:2017-Insecure Deserialization',\n        'A9:2017-Using Components with Known Vulnerabilities',\n        'A10:2017-Insufficient Logging & Monitoring',\n        'A1:2021-Broken Access Control',\n        'A2:2021-Cryptographic Failures',\n        'A3:2021-Injection',\n        'A4:2021-Insecure Design',\n        'A5:2021-Security Misconfiguration',\n        'A6:2021-Vulnerable and Outdated Components',\n        'A7:2021-Identification and Authentication Failures',\n        'A8:2021-Software and Data Integrity Failures',\n        'A9:2021-Security Logging and Monitoring Failures',\n        'A10:2021-Server-Side Request Forgery')", :name=>:tmp_index_vulnerability_identifiers_on_owasp_top_10, :algorithm=>:concurrently})
main:    -> 0.0020s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- add_index(:vulnerability_identifiers, [:external_type, :external_id], {:where=>"lower(external_type) = 'owasp' and external_id IN (\n        'A1:2017', 'A2:2017', 'A3:2017', 'A4:2017', 'A5:2017',\n        'A6:2017', 'A7:2017', 'A8:2017', 'A9:2017', 'A10:2017',\n        'A1:2021', 'A2:2021', 'A3:2021', 'A4:2021', 'A5:2021',\n        'A6:2021', 'A7:2021', 'A8:2021', 'A9:2021', 'A10:2021',\n        'A1:2017-Injection',\n        'A2:2017-Broken Authentication',\n        'A3:2017-Sensitive Data Exposure',\n        'A4:2017-XML External Entities (XXE)',\n        'A5:2017-Broken Access Control',\n        'A6:2017-Security Misconfiguration',\n        'A7:2017-Cross-Site Scripting (XSS)',\n        'A8:2017-Insecure Deserialization',\n        'A9:2017-Using Components with Known Vulnerabilities',\n        'A10:2017-Insufficient Logging & Monitoring',\n        'A1:2021-Broken Access Control',\n        'A2:2021-Cryptographic Failures',\n        'A3:2021-Injection',\n        'A4:2021-Insecure Design',\n        'A5:2021-Security Misconfiguration',\n        'A6:2021-Vulnerable and Outdated Components',\n        'A7:2021-Identification and Authentication Failures',\n        'A8:2021-Software and Data Integrity Failures',\n        'A9:2021-Security Logging and Monitoring Failures',\n        'A10:2021-Server-Side Request Forgery')", :name=>:tmp_index_vulnerability_identifiers_on_owasp_top_10, :algorithm=>:concurrently})
main:    -> 0.0030s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: == 20231214101617 AddTemporaryIndexToVulnerabilityIdentifiers: migrated (0.1276s)

main: == [advisory_lock_connection] object_id: 181920, pg_backend_pid: 85921

bundle exec rake db:migrate:down:main VERSION=20231214101617

main: == [advisory_lock_connection] object_id: 181980, pg_backend_pid: 81549
main: == 20231214101617 AddTemporaryIndexToVulnerabilityIdentifiers: reverting ======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0847s
main: -- indexes(:vulnerability_identifiers)
main:    -> 0.0032s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:vulnerability_identifiers, {:algorithm=>:concurrently, :name=>:tmp_index_vulnerability_identifiers_on_owasp_top_10})
main:    -> 0.0044s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: == 20231214101617 AddTemporaryIndexToVulnerabilityIdentifiers: reverted (0.1042s)

main: == [advisory_lock_connection] object_id: 181980, pg_backend_pid: 81549

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #419092 (closed)

Edited by Bala Kumar

Merge request reports