Skip to content

Add DastSiteValidationCreate GraphQL mutation

Philip Cunningham requested to merge dast-site-validation-create-mutation-233020 into master

What does this MR do?

adds a new mutation for creating dast_site_validations and extends the existing dast_site_validations finder to also search by state.

Related Issue(s)

Database

Summary

  • extends existing finder.
  • adds index to dast_site_validations.state

Notes

there is 1 dast_site_validation on production which is insufficient to do any meaningful analysis, so i have seeded my local development environment with sufficient data in order to give a projection of performance.

[13] DastSiteToken.count
=> 1002
[14] DastSiteValidation.count
=> 100003
[15] Project.count
=> 12

Projections

currently we see few records in these tables (e.g. DastSiteValidation.count = 1 on gitlab.com), when we have 15M projects for gitlab.com, it means a fraction of those projects will have multiple dast_site_profiles

Performance

By State

SELECT "dast_site_validations".* FROM "dast_site_validations" WHERE ("dast_site_validations"."state" IN ('passed')) ORDER BY "dast_site_validations"."id" DESC
Sort  (cost=1510.80..1511.70 rows=362 width=162) (actual time=14.525..16.383 rows=24961 loops=1)
  Sort Key: id DESC
  Sort Method: quicksort  Memory: 4279kB
  ->  Index Scan using index_dast_site_validations_on_url_base_and_state on dast_site_validations  (cost=0.42..1495.41 rows=362 width=162) (actual time=2.540..9.652 rows=24961 loops=1)
        Index Cond: (state = 'passed'::text)

Planning Time: 0.074 ms
Execution Time: 18.176 ms

Compound Query

SELECT "dast_site_validations".* FROM "dast_site_validations" INNER JOIN "dast_site_tokens" ON "dast_site_tokens"."id" = "dast_site_validations"."dast_site_token_id" WHERE "dast_site_tokens"."project_id" = 10 AND "dast_site_validations"."url_base" = 'http://filib.io' AND ("dast_site_validations"."state" IN ('passed')) ORDER BY "dast_site_validations"."id" DESC
Sort  (cost=8.73..8.74 rows=1 width=162) (actual time=42.754..42.929 rows=2300 loops=1)
  Sort Key: dast_site_validations.id DESC
  Sort Method: quicksort  Memory: 420kB
  ->  Nested Loop  (cost=0.69..8.72 rows=1 width=162) (actual time=0.051..41.853 rows=2300 loops=1)
        ->  Index Scan using index_dast_site_validations_on_url_base_and_state on dast_site_validations  (cost=0.42..3.46 rows=2 width=162) (actual time=0.023..7.347 rows=24961 loops=1)
              Index Cond: ((url_base = 'http://filib.io'::text) AND (state = 'passed'::text))
        ->  Index Scan using dast_site_tokens_pkey on dast_site_tokens  (cost=0.28..2.29 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=24961)
              Index Cond: (id = dast_site_validations.dast_site_token_id)
              Filter: (project_id = 10)
              Rows Removed by Filter: 1

Planning Time: 0.162 ms
Execution Time: 43.094 ms

Migration

% rake db:migrate:up VERSION=20201015194852 && rake db:migrate:down VERSION=20201015194852                                                                                                                             
== 20201015194852 AddIndexOnStateForDastSiteValidations: migrating ============
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:dast_site_validations, [:url_base, :state], {:name=>"index_dast_site_validations_on_url_base_and_state", :algorithm=>:concurrently})
   -> 0.0029s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- add_index(:dast_site_validations, [:url_base, :state], {:name=>"index_dast_site_validations_on_url_base_and_state", :algorithm=>:concurrently})
   -> 0.0037s
-- execute("RESET ALL")
   -> 0.0002s
-- transaction_open?()
   -> 0.0000s
-- indexes(:dast_site_validations)
   -> 0.0017s
-- remove_index(:dast_site_validations, {:algorithm=>:concurrently, :name=>"index_dast_site_validations_on_url_base"})
   -> 0.0017s
== 20201015194852 AddIndexOnStateForDastSiteValidations: migrated (0.0112s) ===

== 20201015194852 AddIndexOnStateForDastSiteValidations: reverting ============
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:dast_site_validations, :url_base, {:name=>"index_dast_site_validations_on_url_base", :algorithm=>:concurrently})
   -> 0.0027s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:dast_site_validations, :url_base, {:name=>"index_dast_site_validations_on_url_base", :algorithm=>:concurrently})
   -> 0.0034s
-- execute("RESET ALL")
   -> 0.0003s
-- transaction_open?()
   -> 0.0000s
-- indexes(:dast_site_validations)
   -> 0.0016s
-- remove_index(:dast_site_validations, {:algorithm=>:concurrently, :name=>"index_dast_site_validations_on_url_base_and_state"})
   -> 0.0027s
== 20201015194852 AddIndexOnStateForDastSiteValidations: reverted (0.0116s) ===

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 Philip Cunningham

Merge request reports