Skip to content

Update index secure for API Fuzzing telemetry

Michael Eddington requested to merge 210345-update-index-adding-api-fuzzing into master

What does this MR do?

Update an existing index for secure telemetry to include api_fuzzing and api_fuzzing_dnd. These are job names associated with the new API Fuzzing analyzer.

The index is needed to optimize queries in MR !47451 (merged).

Related to #210345 (closed)

Migration up:

$ bundle exec rails db:migrate:up VERSION=20201113105000 
== 20201113105000 UpdateIndexSecureForApiFuzzingTelemetry: migrating ==========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, [:user_id, :name, :created_at], {:where=>"(((type)::text = 'Ci::Build'::text) AND ((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text, ('dast'::character varying)::text, ('dependency_scanning'::character varying)::text, ('license_management'::character varying)::text, ('license_scanning'::character varying)::text, ('sast'::character varying)::text, ('coverage_fuzzing'::character varying)::text, ('apifuzzer_fuzz'::character varying)::text, ('apifuzzer_fuzz_dnd'::character varying)::text, ('secret_detection'::character varying)::text])))", :name=>"index_secure_ci_builds_on_user_id_created_at", :algorithm=>:concurrently})
   -> 0.0075s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- add_index(:ci_builds, [:user_id, :name, :created_at], {:where=>"(((type)::text = 'Ci::Build'::text) AND ((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text, ('dast'::character varying)::text, ('dependency_scanning'::character varying)::text, ('license_management'::character varying)::text, ('license_scanning'::character varying)::text, ('sast'::character varying)::text, ('coverage_fuzzing'::character varying)::text, ('apifuzzer_fuzz'::character varying)::text, ('apifuzzer_fuzz_dnd'::character varying)::text, ('secret_detection'::character varying)::text])))", :name=>"index_secure_ci_builds_on_user_id_created_at", :algorithm=>:concurrently})
   -> 0.0110s
-- execute("RESET ALL")
   -> 0.0004s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0197s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_secure_ci_builds_on_user_id_created_at_parser_features"})
   -> 0.0028s
== 20201113105000 UpdateIndexSecureForApiFuzzingTelemetry: migrated (0.0426s) =

Migration down

$ bundle exec rails db:migrate:down VERSION=20201113105000 
== 20201113105000 UpdateIndexSecureForApiFuzzingTelemetry: reverting ==========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_builds, [:user_id, :created_at], {:where=>"(((type)::text = 'Ci::Build'::text) AND ((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text, ('dast'::character varying)::text, ('dependency_scanning'::character varying)::text, ('license_management'::character varying)::text, ('license_scanning'::character varying)::text, ('sast'::character varying)::text, ('secret_detection'::character varying)::text])))", :name=>"index_secure_ci_builds_on_user_id_created_at_parser_features", :algorithm=>:concurrently})
   -> 0.0077s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- add_index(:ci_builds, [:user_id, :created_at], {:where=>"(((type)::text = 'Ci::Build'::text) AND ((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text, ('dast'::character varying)::text, ('dependency_scanning'::character varying)::text, ('license_management'::character varying)::text, ('license_scanning'::character varying)::text, ('sast'::character varying)::text, ('secret_detection'::character varying)::text])))", :name=>"index_secure_ci_builds_on_user_id_created_at_parser_features", :algorithm=>:concurrently})
   -> 0.0210s
-- execute("RESET ALL")
   -> 0.0002s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_builds)
   -> 0.0063s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_secure_ci_builds_on_user_id_created_at"})
   -> 0.0017s
== 20201113105000 UpdateIndexSecureForApiFuzzingTelemetry: reverted (0.0377s) =

Database-lab

CREATE INDEX CONCURRENTLY "index_secure_ci_builds_on_user_id_name_created_at" 
ON "ci_builds" ("user_id", "name", "created_at") 
WHERE
   (
((type)::text = 'Ci::Build'::text) 
      AND 
      (
(name)::text = ANY (ARRAY[('container_scanning'::character varying)::text, 
         (
            'dast'::character varying
         )
         ::text, 
         (
            'dependency_scanning'::character varying
         )
         ::text, 
         (
            'license_management'::character varying
         )
         ::text, 
         (
            'license_scanning'::character varying
         )
         ::text, 
         (
            'sast'::character varying
         )
         ::text, 
         (
            'coverage_fuzzing'::character varying
         )
         ::text, 
         (
            'apifuzzer_fuzz'::character varying
         )
         ::text, 
         (
            'apifuzzer_fuzz_dnd'::character varying
         )
         ::text, 
         (
            'secret_detection'::character varying
         )
         ::text])
      )
   )

The query has been executed. Duration: 72.196 min

Query without index (database-labs)

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

SELECT
    COUNT(DISTINCT "ci_builds"."user_id")
FROM
    "ci_builds"
WHERE
    "ci_builds"."type" = 'Ci::Build'
    AND "ci_builds"."name" = 'apifuzzer_fuzz'
    AND "ci_builds"."created_at" BETWEEN '2020-10-16 02:28:58.590164'
    AND '2020-11-13 02:28:58.590362'
    AND "ci_builds"."user_id" >= 1
    AND "ci_builds"."user_id" < 10001
Aggregate  (cost=530130.02..530130.03 rows=1 width=8) (actual time=214996.461..214996.463 rows=1 loops=1)
   Buffers: shared hit=6622 read=167858 dirtied=3077
   I/O Timings: read=212696.632
   ->  Index Scan using index_ci_builds_on_user_id_and_created_at_and_type_eq_ci_build on public.ci_builds  (cost=0.57..530130.01 rows=2 width=4) (actual time=214996.326..214996.326 rows=0 loops=1)
         Index Cond: ((ci_builds.user_id >= 1) AND (ci_builds.user_id < 10001) AND (ci_builds.created_at >= '2020-10-16 02:28:58.590164'::timestamp without time zone) AND (ci_builds.created_at <= '2020-11-13 02:28:58.590362'::timestamp without time zone))
         Filter: ((ci_builds.name)::text = 'apifuzzer_fuzz'::text)
         Rows Removed by Filter: 146793
         Buffers: shared hit=6613 read=167858 dirtied=3077
         I/O Timings: read=212696.632

Query with value in index (database-labs)

https://explain.depesz.com/s/6CTW

SELECT
   COUNT(DISTINCT "ci_builds"."user_id")
FROM
   "ci_builds"
WHERE
   "ci_builds"."type" = 'Ci::Build'
   AND "ci_builds"."name" = 'apifuzzer_fuzz'
   AND "ci_builds"."created_at" BETWEEN '2020-10-16 02:28:58.590164' AND '2020-11-13 02:28:58.590362'
   AND "ci_builds"."user_id" >= 1
   AND "ci_builds"."user_id" < 10001
Aggregate  (cost=947.17..947.18 rows=1 width=8) (actual time=2.791..2.793 rows=1 loops=1)
   Buffers: shared hit=177
   ->  Index Only Scan using index_secure_ci_builds_on_user_id_name_created_at on public.ci_builds  (cost=0.56..947.16 rows=2 width=4) (actual time=2.770..2.771 rows=0 loops=1)
         Index Cond: ((ci_builds.user_id >= 1) AND (ci_builds.user_id < 10001) AND (ci_builds.name = 'apifuzzer_fuzz'::text) AND (ci_builds.created_at >= '2020-10-16 02:28:58.590164'::timestamp without time zone) AND (ci_builds.created_at <= '2020-11-13 02:28:58.590362'::timestamp without time zone))
         Heap Fetches: 0
         Buffers: shared hit=177

Screenshots (strongly suggested)

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 Eddington

Merge request reports