Add dastSiteTokenCreate mutation (!44825) · Merge requests · GitLab.org / GitLab

Philip Cunningham requested to merge add-dast-site-token-create-mutation-245211 into master Oct 09, 2020

What does this MR do?

adds a new mutation for creating dast_site_tokens which will subsequently be used to create dast_site_validations.

Related Issue(s)

Database

Summary

adds new finder for dast_site_validations.

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
=> 1000
[14] DastSiteValidation.count
=> 100000
[15] Project.count
=> 100

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

Schema

dast_site_validations.url_base is indexed
dast_site_tokens.project_id is indexed

gitlabhq_development=# \d dast_site_validations
                                               Table "public.dast_site_validations"
           Column           |           Type           | Collation | Nullable |                      Default
----------------------------+--------------------------+-----------+----------+---------------------------------------------------
 id                         | bigint                   |           | not null | nextval('dast_site_validations_id_seq'::regclass)
 dast_site_token_id         | bigint                   |           | not null |
 created_at                 | timestamp with time zone |           | not null |
 updated_at                 | timestamp with time zone |           | not null |
 validation_started_at      | timestamp with time zone |           |          |
 validation_passed_at       | timestamp with time zone |           |          |
 validation_failed_at       | timestamp with time zone |           |          |
 validation_last_retried_at | timestamp with time zone |           |          |
 validation_strategy        | smallint                 |           | not null |
 url_base                   | text                     |           | not null |
 url_path                   | text                     |           | not null |
 state                      | text                     |           | not null | 'pending'::text
Indexes:
    "dast_site_validations_pkey" PRIMARY KEY, btree (id)
    "index_dast_site_validations_on_dast_site_token_id" btree (dast_site_token_id)
    "index_dast_site_validations_on_url_base" btree (url_base)
Check constraints:
    "check_13b34efe4b" CHECK (char_length(url_path) <= 255)
    "check_283be72e9b" CHECK (char_length(state) <= 255)
    "check_cd3b538210" CHECK (char_length(url_base) <= 255)
Foreign-key constraints:
    "fk_rails_285c617324" FOREIGN KEY (dast_site_token_id) REFERENCES dast_site_tokens(id) ON DELETE CASCADE
Referenced by:
    TABLE "dast_sites" CONSTRAINT "fk_0a57f2271b" FOREIGN KEY (dast_site_validation_id) REFERENCES dast_site_validations(id) ON DELETE SET NULL

gitlabhq_development=# \d dast_site_tokens
                                       Table "public.dast_site_tokens"
   Column   |           Type           | Collation | Nullable |                   Default
------------+--------------------------+-----------+----------+----------------------------------------------
 id         | bigint                   |           | not null | nextval('dast_site_tokens_id_seq'::regclass)
 project_id | bigint                   |           | not null |
 created_at | timestamp with time zone |           | not null |
 updated_at | timestamp with time zone |           | not null |
 expired_at | timestamp with time zone |           |          |
 token      | text                     |           | not null |
 url        | text                     |           | not null |
Indexes:
    "dast_site_tokens_pkey" PRIMARY KEY, btree (id)
    "index_dast_site_tokens_on_project_id" btree (project_id)
Check constraints:
    "check_02a6bf20a7" CHECK (char_length(token) <= 255)
    "check_69ab8622a6" CHECK (char_length(url) <= 255)
Foreign-key constraints:
    "fk_rails_e84f721a8e" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
Referenced by:
    TABLE "dast_site_validations" CONSTRAINT "fk_rails_285c617324" FOREIGN KEY (dast_site_token_id) REFERENCES dast_site_tokens(id) ON DELETE CASCADE

Performance

Filtering by `project_id`

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" = 55;

Nested Loop  (cost=0.57..1323.35 rows=391 width=162) (actual time=0.022..1.676 rows=1810 loops=1)
  ->  Index Scan using index_dast_site_tokens_on_project_id on dast_site_tokens  (cost=0.28..6.36 rows=5 width=8) (actual time=0.012..0.021 rows=18 loops=1)
        Index Cond: (project_id = 55)
  ->  Index Scan using index_dast_site_validations_on_dast_site_token_id on dast_site_validations  (cost=0.29..259.58 rows=382 width=162) (actual time=0.005..0.066 rows=101 loops=18)
        Index Cond: (dast_site_token_id = dast_site_tokens.id)

Planning Time: 0.135 ms
Execution Time: 1.822 ms

https://explain.depesz.com/s/DH8L

Filtering by `url_base`

SELECT "dast_site_validations".* FROM "dast_site_validations" WHERE "dast_site_validations"."url_base" = 'http://example622.test:80' ORDER BY "dast_site_validations"."id" DESC;

Sort  (cost=373.49..374.44 rows=382 width=162) (actual time=0.215..0.229 rows=208 loops=1)
  Sort Key: id DESC
  Sort Method: quicksort  Memory: 80kB
  ->  Index Scan using index_dast_site_validations_on_url_base on dast_site_validations  (cost=0.42..357.10 rows=382 width=162) (actual time=0.016..0.169 rows=208 loops=1)
        Index Cond: (url_base = 'http://example574.test:80'::text)

Planning Time: 0.066 ms
Execution Time: 0.255 ms

https://explain.depesz.com/s/Wsqo

Filerting by both

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" = 55 AND "dast_site_validations"."url_base" = 'http://example574.test:80' ORDER BY "dast_site_validations"."id" DESC;

Sort  (cost=80.55..80.56 rows=2 width=162) (actual time=0.557..0.558 rows=5 loops=1)
  Sort Key: dast_site_validations.id DESC
  Sort Method: quicksort  Memory: 26kB
  ->  Nested Loop  (cost=13.06..80.54 rows=2 width=162) (actual time=0.078..0.549 rows=5 loops=1)
        ->  Index Scan using index_dast_site_tokens_on_project_id on dast_site_tokens  (cost=0.28..6.36 rows=5 width=8) (actual time=0.018..0.028 rows=13 loops=1)
              Index Cond: (project_id = 55)
        ->  Bitmap Heap Scan on dast_site_validations  (cost=12.79..14.82 rows=2 width=162) (actual time=0.038..0.038 rows=0 loops=13)
              Recheck Cond: ((dast_site_token_id = dast_site_tokens.id) AND (url_base = 'http://example574.test:80'::text))
              Heap Blocks: exact=5
              ->  BitmapAnd  (cost=12.79..12.79 rows=2 width=0) (actual time=0.037..0.037 rows=0 loops=13)
                    ->  Bitmap Index Scan on index_dast_site_validations_on_dast_site_token_id  (cost=0.00..5.16 rows=382 width=0) (actual time=0.009..0.009 rows=101 loops=13)
                          Index Cond: (dast_site_token_id = dast_site_tokens.id)
                    ->  Bitmap Index Scan on index_dast_site_validations_on_url_base  (cost=0.00..7.28 rows=382 width=0) (actual time=0.021..0.021 rows=208 loops=13)
                          Index Cond: (url_base = 'http://example574.test:80'::text)

Planning Time: 0.201 ms
Execution Time: 0.589 ms

https://explain.depesz.com/s/tZsQ

Does this MR meet the acceptance criteria?

Conformity

Changelog entry behind feature flag, not added
Documentation (if required)
Code review guidelines
Merge request performance guidelines
Style guides
Database guides
Separation of EE specific content

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

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 Oct 13, 2020 by Philip Cunningham

Add dastSiteTokenCreate mutation