Skip to content

Geo: Add vulnerability export replication

What does this MR do?

Issue: #220959 (closed)

This MR:

  • adds replication for vulnerability exports behind a feature flag
  • adds migrations for a registry table, a states table and for indices
  • puts the replication behind a feature flag disabled by default

DB review information

Migrations in db/migrate

rake db:migrate

== 20200720154007 CreateVulnerabilitiesExportVerificationStatus: migrating ====
-- table_exists?(:vulnerability_export_verification_status)
   -> 0.0005s
-- create_table(:vulnerability_export_verification_status, {:id=>false})
   -> 0.0183s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE vulnerability_export_verification_status\nADD CONSTRAINT check_48fdf48546\nCHECK ( char_length(verification_failure) <= 255 )\nNOT VALID;\n")
   -> 0.0011s
-- execute("ALTER TABLE vulnerability_export_verification_status VALIDATE CONSTRAINT check_48fdf48546;")
   -> 0.0004s
== 20200720154007 CreateVulnerabilitiesExportVerificationStatus: migrated (0.0323s)

== 20200720154244 AddVerificationFailureIndexToVulnerabilityExports: migrating
-- add_index(:vulnerability_export_verification_status, :verification_failure, {:where=>"(verification_failure IS NOT NULL)", :name=>"vulnerability_exports_verification_failure_partial"})
   -> 0.0019s
-- add_index(:vulnerability_export_verification_status, :verification_checksum, {:where=>"(verification_checksum IS NOT NULL)", :name=>"vulnerability_exports_verification_checksum_partial"})
   -> 0.0014s
== 20200720154244 AddVerificationFailureIndexToVulnerabilityExports: migrated (0.0035s)

rake db:rollback

== 20200720154244 AddVerificationFailureIndexToVulnerabilityExports: reverting
-- remove_index(:vulnerability_export_verification_status, {:name=>"vulnerability_exports_verification_failure_partial"})
   -> 0.0066s
-- remove_index(:vulnerability_export_verification_status, {:name=>"vulnerability_exports_verification_checksum_partial"})
   -> 0.0005s
== 20200720154244 AddVerificationFailureIndexToVulnerabilityExports: reverted (0.0072s)

== 20200720154007 CreateVulnerabilitiesExportVerificationStatus: reverting ====
-- drop_table(:vulnerability_export_verification_status)
   -> 0.0112s
== 20200720154007 CreateVulnerabilitiesExportVerificationStatus: reverted (0.0113s)

Migration in ee/db/geo/migrate

rake geo:db:migrate

== 20200710194046 CreateVulnerabilityExportRegistry: migrating ================
-- table_exists?(:vulnerability_export_registry)
   -> 0.0004s
-- create_table(:vulnerability_export_registry, {:id=>:bigserial, :force=>:cascade})
   -> 0.0288s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE vulnerability_export_registry\nADD CONSTRAINT check_6edacda197\nCHECK ( char_length(last_sync_failure) <= 255 )\nNOT VALID;\n")
   -> 0.0025s
-- execute("ALTER TABLE vulnerability_export_registry VALIDATE CONSTRAINT check_6edacda197;")
   -> 0.0009s
== 20200710194046 CreateVulnerabilityExportRegistry: migrated (0.0417s) =======

rake geo:db:rollback

== 20200710194046 CreateVulnerabilityExportRegistry: reverting ================
-- drop_table(:vulnerability_export_registry)
   -> 0.0863s
== 20200710194046 CreateVulnerabilityExportRegistry: reverted (0.0863s) =======

A note on ordering columns in vulnerability_export_verification_status table

Here is a summary of column sizes and how they are ordered by their type lengths.

gitlabhq_development=#
gitlabhq_development=# SELECT pg_column_size(vulnerability_export_id),
gitlabhq_development-#        pg_column_size(verification_retry_at),
gitlabhq_development-#        pg_column_size(verified_at),
gitlabhq_development-#        pg_column_size(verification_retry_count),
gitlabhq_development-#        pg_column_size(verification_checksum),
gitlabhq_development-#        pg_column_size(verification_failure) FROM vulnerability_export_verification_status LIMIT 1;

 pg_column_size | pg_column_size | pg_column_size | pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------+----------------+----------------+----------------
              8 |              8 |              8 |              2 |              5 |             16
(1 row)

gitlabhq_development=# SELECT a.attname, t.typname, t.typalign, t.typlen
gitlabhq_development-#   FROM pg_class c
gitlabhq_development-#   JOIN pg_attribute a ON (a.attrelid = c.oid)
gitlabhq_development-#   JOIN pg_type t ON (t.oid = a.atttypid)
gitlabhq_development-#  WHERE c.relname = 'vulnerability_export_verification_status'
gitlabhq_development-#    AND a.attnum >= 0
gitlabhq_development-#  ORDER BY t.typlen DESC;

         attname          |   typname   | typalign | typlen
--------------------------+-------------+----------+--------
 vulnerability_export_id  | int8        | d        |      8
 verification_retry_at    | timestamptz | d        |      8
 verified_at              | timestamptz | d        |      8
 verification_retry_count | int2        | s        |      2
 verification_checksum    | bytea       | i        |     -1
 verification_failure     | text        | i        |     -1
(6 rows)

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Michael Kozono

Merge request reports