Add dastSiteTokenCreate mutation
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
project_id
Filtering by 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
url_base
Filtering by 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
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
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