Skip to content

Change owner of security_findings before rename

Michał Zając requested to merge mzajac/incident-7769 into master

What does this MR do and why?

This MR makes sure to change the owner of security_findings table and security_findings_id_seq before renaming them

Related to gitlab-com/gl-infra/production#7769 (closed)

Database review

UP

gitlab on  mzajac/incident-7769 [$✘!] via ⬢ v16.15.0 via 💎 ruby ➜ dbup db/post_migrate/20220902204048_move_security_findings_table_to_gitlab_partitions_dynamic_schema.rb
main: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: migrating 
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("LOCK TABLE vulnerability_scanners, security_scans, security_findings IN ACCESS EXCLUSIVE MODE\n")
main:    -> 0.0004s
main: -- execute("SELECT tableowner FROM pg_tables WHERE tablename = 'security_findings';\n")
main:    -> 0.0123s
main: -- execute("SELECT\n  pg_get_constraintdef(pg_catalog.pg_constraint.oid)\nFROM\n  pg_catalog.pg_constraint\nINNER JOIN pg_class ON pg_class.oid = pg_catalog.pg_constraint.conrelid\nWHERE\n  conname = 'check_partition_number' AND\n  pg_class.relname = 'security_findings'\n")
main:    -> 0.0022s
main: -- execute("ALTER TABLE security_findings RENAME TO security_findings_1;\n")
main:    -> 0.0010s
main: -- execute("ALTER INDEX security_findings_pkey RENAME TO security_findings_1_pkey;\n")
main:    -> 0.0009s
main: -- execute("CREATE TABLE security_findings (\n  LIKE security_findings_1 INCLUDING ALL\n) PARTITION BY LIST (partition_number);\n")
main:    -> 0.0051s
main: -- execute("ALTER TABLE security_findings OWNER TO quintasan;\n")
main:    -> 0.0006s
main: -- execute("ALTER SEQUENCE security_findings_id_seq OWNER TO quintasan;\n")
main:    -> 0.0007s
main: -- execute("ALTER SEQUENCE security_findings_id_seq OWNED BY public.security_findings.id;\n")
main:    -> 0.0007s
main: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT fk_rails_729b763a54 FOREIGN KEY (scanner_id) REFERENCES vulnerability_scanners(id) ON DELETE CASCADE;\n")
main:    -> 0.0035s
main: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT fk_rails_bb63863cf1 FOREIGN KEY (scan_id) REFERENCES security_scans(id) ON DELETE CASCADE;\n")
main:    -> 0.0021s
main: -- execute("ALTER TABLE security_findings_1 SET SCHEMA gitlab_partitions_dynamic;\n")
main:    -> 0.0037s
main: -- execute("ALTER TABLE security_findings ATTACH PARTITION gitlab_partitions_dynamic.security_findings_1 FOR VALUES IN (1);\n")
main:    -> 0.0074s
main: -- execute("ALTER TABLE security_findings DROP CONSTRAINT check_partition_number;\n")
main:    -> 0.0017s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_unique_columns RENAME TO security_findings_1_uuid_scan_id_partition_number_idx;\n")
main:    -> 0.0013s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_confidence RENAME TO security_findings_1_confidence_idx;\n")
main:    -> 0.0013s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_project_fingerprint RENAME TO security_findings_1_project_fingerprint_idx;\n")
main:    -> 0.0013s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scan_id_and_deduplicated RENAME TO security_findings_1_scan_id_deduplicated_idx;\n")
main:    -> 0.0013s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scan_id_and_id RENAME TO security_findings_1_scan_id_id_idx;\n")
main:    -> 0.0012s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scanner_id RENAME TO security_findings_1_scanner_id_idx;\n")
main:    -> 0.0013s
main: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_severity RENAME TO security_findings_1_severity_idx;\n")
main:    -> 0.0013s
main: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: migrated (0.0635s) 

ci: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: migrating 
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE vulnerability_scanners, security_scans, security_findings IN ACCESS EXCLUSIVE MODE\n")
ci:    -> 0.0004s
ci: -- execute("SELECT tableowner FROM pg_tables WHERE tablename = 'security_findings';\n")
ci:    -> 0.0013s
ci: -- execute("SELECT\n  pg_get_constraintdef(pg_catalog.pg_constraint.oid)\nFROM\n  pg_catalog.pg_constraint\nINNER JOIN pg_class ON pg_class.oid = pg_catalog.pg_constraint.conrelid\nWHERE\n  conname = 'check_partition_number' AND\n  pg_class.relname = 'security_findings'\n")
ci:    -> 0.0008s
ci: -- execute("ALTER TABLE security_findings RENAME TO security_findings_1;\n")
ci:    -> 0.0004s
ci: -- execute("ALTER INDEX security_findings_pkey RENAME TO security_findings_1_pkey;\n")
ci:    -> 0.0005s
ci: -- execute("CREATE TABLE security_findings (\n  LIKE security_findings_1 INCLUDING ALL\n) PARTITION BY LIST (partition_number);\n")
ci:    -> 0.0022s
ci: -- execute("ALTER TABLE security_findings OWNER TO quintasan;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER SEQUENCE security_findings_id_seq OWNER TO quintasan;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER SEQUENCE security_findings_id_seq OWNED BY public.security_findings.id;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT fk_rails_729b763a54 FOREIGN KEY (scanner_id) REFERENCES vulnerability_scanners(id) ON DELETE CASCADE;\n")
ci:    -> 0.0022s
ci: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT fk_rails_bb63863cf1 FOREIGN KEY (scan_id) REFERENCES security_scans(id) ON DELETE CASCADE;\n")
ci:    -> 0.0007s
ci: -- execute("ALTER TABLE security_findings_1 SET SCHEMA gitlab_partitions_dynamic;\n")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE security_findings ATTACH PARTITION gitlab_partitions_dynamic.security_findings_1 FOR VALUES IN (1);\n")
ci:    -> 0.0034s
ci: -- execute("ALTER TABLE security_findings DROP CONSTRAINT check_partition_number;\n")
ci:    -> 0.0005s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_unique_columns RENAME TO security_findings_1_uuid_scan_id_partition_number_idx;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_confidence RENAME TO security_findings_1_confidence_idx;\n")
ci:    -> 0.0004s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_project_fingerprint RENAME TO security_findings_1_project_fingerprint_idx;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scan_id_and_deduplicated RENAME TO security_findings_1_scan_id_deduplicated_idx;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scan_id_and_id RENAME TO security_findings_1_scan_id_id_idx;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_scanner_id RENAME TO security_findings_1_scanner_id_idx;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER INDEX gitlab_partitions_dynamic.index_security_findings_on_severity RENAME TO security_findings_1_severity_idx;\n")
ci:    -> 0.0003s
ci: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: migrated (0.0184s)

DOWN

gitlab on  mzajac/incident-7769 [$!] via ⬢ v16.15.0 via 💎 ruby ➜ dbdown db/post_migrate/20220902204048_move_security_findings_table_to_gitlab_partitions_dynamic_schema.rb
main: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: reverting 
main: -- execute("SELECT\n  partitions.relname AS partition_name\nFROM pg_inherits\nJOIN pg_class parent ON pg_inherits.inhparent = parent.oid\nJOIN pg_class partitions ON pg_inherits.inhrelid = partitions.oid\nWHERE\n  parent.relname = 'security_findings'\nORDER BY (regexp_matches(partitions.relname, 'security_findings_(\\d+)'))[1]::int DESC\nLIMIT 1\n")
main:    -> 0.0044s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("LOCK TABLE vulnerability_scanners, security_scans, security_findings IN ACCESS EXCLUSIVE MODE\n")
main:    -> 0.0004s
main: -- execute("ALTER TABLE security_findings DETACH PARTITION gitlab_partitions_dynamic.security_findings_1;\n")
main:    -> 0.0022s
main: -- execute("ALTER TABLE gitlab_partitions_dynamic.security_findings_1 SET SCHEMA public;\n")
main:    -> 0.0011s
main: -- execute("ALTER SEQUENCE security_findings_id_seq OWNED BY security_findings_1.id;\n")
main:    -> 0.0005s
main: -- execute("DROP TABLE security_findings;\n")
main:    -> 0.0015s
main: -- execute("ALTER TABLE security_findings_1 RENAME TO security_findings;\n")
main:    -> 0.0005s
main: -- execute("ALTER INDEX public.security_findings_1_pkey RENAME TO security_findings_pkey;\n")
main:    -> 0.0008s
main: -- execute("ALTER INDEX public.security_findings_1_uuid_scan_id_partition_number_idx RENAME TO index_security_findings_on_unique_columns;\n")
main:    -> 0.0004s
main: -- execute("ALTER INDEX public.security_findings_1_confidence_idx RENAME TO index_security_findings_on_confidence;\n")
main:    -> 0.0006s
main: -- execute("ALTER INDEX public.security_findings_1_project_fingerprint_idx RENAME TO index_security_findings_on_project_fingerprint;\n")
main:    -> 0.0003s
main: -- execute("ALTER INDEX public.security_findings_1_scan_id_deduplicated_idx RENAME TO index_security_findings_on_scan_id_and_deduplicated;\n")
main:    -> 0.0008s
main: -- execute("ALTER INDEX public.security_findings_1_scan_id_id_idx RENAME TO index_security_findings_on_scan_id_and_id;\n")
main:    -> 0.0003s
main: -- execute("ALTER INDEX public.security_findings_1_scanner_id_idx RENAME TO index_security_findings_on_scanner_id;\n")
main:    -> 0.0003s
main: -- execute("ALTER INDEX public.security_findings_1_severity_idx RENAME TO index_security_findings_on_severity;\n")
main:    -> 0.0004s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- current_schema()
main:    -> 0.0002s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT check_partition_number\nCHECK ( (partition_number = 1) )\nNOT VALID;\n")
main:    -> 0.0004s
main: -- current_schema()
main:    -> 0.0005s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- execute("ALTER TABLE security_findings VALIDATE CONSTRAINT check_partition_number;")
main:    -> 0.0004s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: reverted (0.0377s) 

ci: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: reverting 
ci: -- execute("SELECT\n  partitions.relname AS partition_name\nFROM pg_inherits\nJOIN pg_class parent ON pg_inherits.inhparent = parent.oid\nJOIN pg_class partitions ON pg_inherits.inhrelid = partitions.oid\nWHERE\n  parent.relname = 'security_findings'\nORDER BY (regexp_matches(partitions.relname, 'security_findings_(\\d+)'))[1]::int DESC\nLIMIT 1\n")
ci:    -> 0.0011s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE vulnerability_scanners, security_scans, security_findings IN ACCESS EXCLUSIVE MODE\n")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE security_findings DETACH PARTITION gitlab_partitions_dynamic.security_findings_1;\n")
ci:    -> 0.0020s
ci: -- execute("ALTER TABLE gitlab_partitions_dynamic.security_findings_1 SET SCHEMA public;\n")
ci:    -> 0.0008s
ci: -- execute("ALTER SEQUENCE security_findings_id_seq OWNED BY security_findings_1.id;\n")
ci:    -> 0.0004s
ci: -- execute("DROP TABLE security_findings;\n")
ci:    -> 0.0013s
ci: -- execute("ALTER TABLE security_findings_1 RENAME TO security_findings;\n")
ci:    -> 0.0004s
ci: -- execute("ALTER INDEX public.security_findings_1_pkey RENAME TO security_findings_pkey;\n")
ci:    -> 0.0004s
ci: -- execute("ALTER INDEX public.security_findings_1_uuid_scan_id_partition_number_idx RENAME TO index_security_findings_on_unique_columns;\n")
ci:    -> 0.0004s
ci: -- execute("ALTER INDEX public.security_findings_1_confidence_idx RENAME TO index_security_findings_on_confidence;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER INDEX public.security_findings_1_project_fingerprint_idx RENAME TO index_security_findings_on_project_fingerprint;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER INDEX public.security_findings_1_scan_id_deduplicated_idx RENAME TO index_security_findings_on_scan_id_and_deduplicated;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER INDEX public.security_findings_1_scan_id_id_idx RENAME TO index_security_findings_on_scan_id_and_id;\n")
ci:    -> 0.0004s
ci: -- execute("ALTER INDEX public.security_findings_1_scanner_id_idx RENAME TO index_security_findings_on_scanner_id;\n")
ci:    -> 0.0003s
ci: -- execute("ALTER INDEX public.security_findings_1_severity_idx RENAME TO index_security_findings_on_severity;\n")
ci:    -> 0.0004s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- current_schema()
ci:    -> 0.0002s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE security_findings\nADD CONSTRAINT check_partition_number\nCHECK ( (partition_number = 1) )\nNOT VALID;\n")
ci:    -> 0.0005s
ci: -- current_schema()
ci:    -> 0.0002s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE security_findings VALIDATE CONSTRAINT check_partition_number;")
ci:    -> 0.0004s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20220902204048 MoveSecurityFindingsTableToGitlabPartitionsDynamicSchema: reverted (0.0175s)

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 Michał Zając

Merge request reports