Add method to pull security finding related issues
requested to merge 384867-add-issuelinks-field-to-pipelinesecurityreportfinding-issues-method into master
What does this MR do and why?
This MR adds a method and scope to be used for the finder methods
Screenshots or screen recordings
Query Plan
This will pull feedback and related data (i.e., issues)
Feedback
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13769/commands/48325
Query Plan
Bitmap Heap Scan on public.vulnerability_feedback (cost=147.78..336.89 rows=101 width=147) (actual time=114.505..114.507 rows=0 loops=1)
Buffers: shared hit=6 read=143
I/O Timings: read=113.463 write=0.000
-> Bitmap Index Scan using index_vulnerability_feedback_finding_uuid (cost=0.00..147.75 rows=101 width=0) (actual time=114.497..114.499 rows=0 loops=1)
Index Cond: (vulnerability_feedback.finding_uuid = ANY ('{cb3c6229-3d3a-5245-b734-8b6b25d69337,a745cfcb-e813-5b61-87c3-5cd84c55cdcc,d1a5dd68-3019-5bc4-b307-3fc199a25638,fa4e2a00-c643-59d5-ae97-fb7e2e869948,7a650e89-190e-53a3-8277-9ad745bea9ab,6eb1e15f-a1ad-5752-81b9-5775f862cded,96775e29-4414-5a49-80d0-e242b6ce4351,3b13445a-c28b-5a59-90c7-f4050a5ac740,d6580495-6fd2-514a-9a01-1f8ee3ad2bed,209a93c3-ec73-5263-80a2-ef950d434fee,31c62457-2725-5012-9b3c-b5c5907fe3ed,ae0bf209-9ac3-5c32-9067-462aab0fe655,fa4e44d3-335b-5f9e-8829-a80750bbcf94,ccad8658-f203-55bb-be63-421c2346586f,af36dd02-5ef6-5938-84db-99bcfa1489ce,514685ab-51d5-58ca-b066-65fa51e3f274,d2e6946f-21fb-5457-b399-b0aa0abda9f9,8f299006-0d20-56af-8c4a-852790366973,40a7aa02-f9d6-5d1c-ae50-0684e847aff9,6fa08807-7107-5439-8747-74f12e1d16ae,16e82c1d-b8d2-552a-be0a-6dc5da614635,c86bd214-5b64-5aad-9a46-f9bf8668fa28,872c9bba-6570-54b9-913a-3c73efebc443,2f2a1b87-758a-5304-8f6e-57e187f8af8e,b0909678-6639-5053-9a8e-25928bb19c35,3901b6cb-1d57-5ce7-a272-1a28bab59501,90c300c2-378f-548c-b153-fd9fb57d3966,d93647c3-320a-5f3d-a1d8-f709d323802b,81e3ac95-2f4f-5d47-a2f7-1ae206bdd383,a7a5e2fa-a767-5887-bb59-1686d7a3986c,9eb6d8dd-b50e-5785-95ef-35b148d7e155,bc00ad41-ce52-5a2f-8d4d-5b8f65069460,5d726f14-7a69-5622-abf2-8b152d96c02f,3a2aafe2-0c86-505b-af46-a0d11c7de54f,fa84159a-5fc6-523d-b2dc-dd6c13503025,54527fc6-dede-59ac-939c-2081c72d8ac5,50d68539-8ea9-5f7f-b2d8-b435821cdd0b,d36d2b5c-3b7b-58af-beb4-9c5315446ef4,d89eadb0-7953-5b4e-aff8-29b2c04617f7,e5b1c497-7f6f-59ff-8ba6-abae287b09fe,e7a31bc5-6428-5da3-a931-1e781b232d8f,ce211771-1789-5db2-84de-5cf6bb7d7480,e33e8787-9f04-5728-bd0c-53d8223ede2a,3b11e405-6ccf-5a41-a470-3918f9ce4d08,849e9a36-52de-5830-aa1d-0e0be78f18a0,5f67d95d-80bf-534d-8958-59e4de97aca4,a9529778-3056-5cc4-8bb1-10adb81ce02b,7c03cc64-c790-5dec-9530-3cdd77b8f467,3cc933de-5f01-56be-8ce1-482fe072e9bf,e917f328-1799-595a-ba08-8e63df08753c,35798e9b-d844-5c9d-b99e-695e4f07ea4d,4611c2e6-73cb-5749-a440-48b4b0627797,d11781f6-f50a-5a2e-b330-f12049751c53,b2dcb49b-45c5-5f5f-8975-e3593d30a12f,85287363-f9df-5fef-a3b3-64e3718dae7a,3680f2b0-76c5-5b0f-8419-fa79c3a743a1,2ee6b14b-d8cf-5e14-9560-d7e192c6af88,7daf4968-04fe-5ff8-83df-96cf16a22968,c94f7d1e-1c22-5092-ab58-700521c5a37a,52338219-5f6e-58e7-b76a-604ef1421eef,952e2859-481b-5e0a-b055-bb35587b8882,fe518add-ad09-5b9b-b7c9-298ee2f6250d,17b812c9-918f-5d84-b1f2-db3a76cc2c76,ce3781fd-6938-58a6-b026-88351170fc74,69e93653-4b0a-588c-bae5-9cd9d9959ba7,ccf916e2-75c3-5793-8396-c3859e2662db,62bcbd49-7042-5764-978d-dc3a7df0990a,be0d495f-9259-5b0e-8541-cd6c87393e4b,e4b7116c-54a8-5bbf-b761-9ca65aaf3557,f92708e1-cb02-52f8-b301-36a61c17baa1,154ee906-64d5-58b1-8dbd-f76a487dcf9e,859e96aa-0a70-5262-8fcf-48f9d62ca94b,1757ee98-b25e-501b-9b70-fac8ab4f3d30,65e58f30-20d7-5c8d-9641-efffbb71dbc1,a0c3d5ae-973b-56f5-ac75-3d4609b25662,50301098-7b41-5f1e-acab-e1d665b5aeaa,3ea43023-c33c-579a-ac57-768aea3894c5,d073c2bc-65bc-5900-9f1b-93b3980ce5d3,ee9b00dc-8ecb-5053-8778-5e89c7621ec2,44b8f05a-b158-5cc7-9956-2bafe6e00709,f33dc45f-d2e9-5781-b2b8-ce356b3d22b3,2abbc704-c00f-5bd9-bd8a-bd5cfbc6cb00,944d7db1-d986-59bf-87c6-d13d53ffb486,4b258b3a-c23c-54ec-a254-ad6c654c019d,6eb9652f-807e-5ff4-888c-01951e501ded,366d43f7-6b3b-5b05-bd0d-29dea2fcd730,c3b36d02-39cc-5907-87f1-b03aa4244730,e6a386a5-6d68-55b5-ad92-b01f994f1a43,8e2ad96d-b57a-535b-a08a-a639125bcdb1,f31f4ba1-8b8d-51e1-9096-8bb6fdfbe949,442d76c5-be89-52c5-bee5-ff5692810718,20a2a3e1-1185-5918-b36f-125b12576a8f,46433dd4-0f16-5043-be02-f43b5cb5a0ae,48439bfd-3e12-52eb-969c-0ea729423e07,d20b913e-cbc9-5dd8-bcf5-a3b845ff49fb,401d7a0f-2832-5295-8a41-fd8b969636a3,74003bbf-1720-57ac-a266-07b583733992,5eaadccf-2478-5262-b607-02fde4c6871e,36063c71-0a6b-55ba-8081-345e39e6ec33,38c6e0af-b6ff-5460-be2e-f6ab8a38b5cf}'::uuid[]))
Buffers: shared hit=6 read=143
I/O Timings: read=113.463 write=0.000
Time: 116.907 ms
- planning: 2.322 ms
- execution: 114.585 ms
- I/O read: 113.463 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 6 (~48.00 KiB) from the buffer pool
- reads: 143 (~1.10 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Issues
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13769/commands/48327
Query Plan
Index Scan using index_issues_on_id_and_weight on public.issues (cost=0.57..3.58 rows=1 width=1463) (actual time=3.174..3.174 rows=0 loops=1)
Index Cond: (issues.id = 35730)
Buffers: shared read=4
I/O Timings: read=3.118 write=0.000
Time: 8.728 ms
- planning: 5.497 ms
- execution: 3.231 ms
- I/O read: 3.118 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 0 from the buffer pool
- reads: 4 (~32.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
How to set up and validate locally
- Create an issue from a security finding
- Open a rails console
- Get the pipeline
pipeline = Ci::Pipeline.find(<pipeline id)
- Get the security findings, preloaded with feedback
findings = pipeline.security_findings.preload_feedback
- Get issues, it should return the issues but not query the database for the issue data
findings.map(&:feedbacks).flatten.map(&:issue)
FindingsFinder:
- Create an issue from a security finding
- Open a rails console
- Get the pipeline
pipeline = Ci::Pipeline.find(<pipeline id)
- Get the finder data with preloaded feedback. This will bulk load all feedback data at one time to make this call more performant.
finder_data = Security::FindingsFinder.new(pipeline, params: {preload_feedback: true}).execute
- Pull the related issues from the finding data. There should not be any additional database calls to pull issue data, and it will show all of the related issues.
finder_data.security_findings.map(&:related_issues)
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #384867 (closed)
Edited by Jonathan Schafer