Skip to content

Add security report ingestion information to dashboard seed data

What does this MR do and why?

This MR introduces 2 new attributes for the security dashboard initialization payload which will be used by the client application to show alert to users.

Related to Show information about security report schema v... (#357104 - closed).

Migration outputs

migrate up
main: == 20220706132238 AddIndicesOnSecurityScansInfoColumn: migrating ==============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:security_scans, "pipeline_id, jsonb_array_length(COALESCE((security_scans.info -> 'errors'::text), '[]'::jsonb))", {:name=>:index_security_scans_on_length_of_errors, :algorithm=>:concurrently})
main:    -> 0.0112s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- add_index(:security_scans, "pipeline_id, jsonb_array_length(COALESCE((security_scans.info -> 'errors'::text), '[]'::jsonb))", {:name=>:index_security_scans_on_length_of_errors, :algorithm=>:concurrently})
main:    -> 0.0136s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:security_scans, "pipeline_id, jsonb_array_length(COALESCE((security_scans.info -> 'warnings'::text), '[]'::jsonb))", {:name=>:index_security_scans_on_length_of_warnings, :algorithm=>:concurrently})
main:    -> 0.0043s
main: -- add_index(:security_scans, "pipeline_id, jsonb_array_length(COALESCE((security_scans.info -> 'warnings'::text), '[]'::jsonb))", {:name=>:index_security_scans_on_length_of_warnings, :algorithm=>:concurrently})
main:    -> 0.0034s
main: == 20220706132238 AddIndicesOnSecurityScansInfoColumn: migrated (0.0497s) =====
migrate down
main: == 20220706132238 AddIndicesOnSecurityScansInfoColumn: reverting ==============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:security_scans)
main:    -> 0.0098s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- remove_index(:security_scans, {:algorithm=>:concurrently, :name=>:index_security_scans_on_length_of_errors})
main:    -> 0.0036s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:security_scans)
main:    -> 0.0038s
main: -- remove_index(:security_scans, {:algorithm=>:concurrently, :name=>:index_security_scans_on_length_of_warnings})
main:    -> 0.0015s
main: == 20220706132238 AddIndicesOnSecurityScansInfoColumn: reverted (0.0322s) =====

Query plans for the introduced queries;

I used a specific pipeline with 31 security scans which is quite a lot to check if the queries perform well.

Check if there are security scans with errors
SELECT
    1 AS one
FROM
    "security_scans"
WHERE
    "security_scans"."pipeline_id" = 305762779
    AND (jsonb_array_length(COALESCE(info -> 'errors', '[]'::jsonb)) > 0)
LIMIT 1
Limit  (cost=0.56..2.71 rows=1 width=4) (actual time=0.076..0.077 rows=1 loops=1)
   Buffers: shared hit=8
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_security_scans_on_length_of_errors on public.security_scans  (cost=0.56..4.85 rows=2 width=4) (actual time=0.074..0.075 rows=1 loops=1)
         Index Cond: ((security_scans.pipeline_id = 305762779) AND (jsonb_array_length(COALESCE((security_scans.info -> 'errors'::text), '[]'::jsonb)) > 0))
         Buffers: shared hit=8
         I/O Timings: read=0.000 write=0.000

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/10950/commands/39366

Check if there are security scans with warnings
SELECT
    1 AS one
FROM
    "security_scans"
WHERE
    "security_scans"."pipeline_id" = 305762779
    AND (jsonb_array_length(COALESCE(info -> 'warnings', '[]'::jsonb)) > 0)
LIMIT 1
 Limit  (cost=0.56..2.71 rows=1 width=4) (actual time=0.228..0.229 rows=0 loops=1)
   Buffers: shared hit=3 read=4
   I/O Timings: read=0.153 write=0.000
   ->  Index Scan using index_security_scans_on_length_of_warnings on public.security_scans  (cost=0.56..4.85 rows=2 width=4) (actual time=0.226..0.226 rows=0 loops=1)
         Index Cond: ((security_scans.pipeline_id = 305762779) AND (jsonb_array_length(COALESCE((security_scans.info -> 'warnings'::text), '[]'::jsonb)) > 0))
         Buffers: shared hit=3 read=4
         I/O Timings: read=0.153 write=0.000

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/10950/commands/39368

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Mehmet Emin INAC

Merge request reports

Loading