Skip to content

Update security dashboard when no vulnerabilities

Cameron Swords requested to merge update-security-dashboard-when-no-vulnerabilities-found into master

What does this MR do?

The Security Dashboard finds the latest successful Pipelines that contain vulnerabilities. These vulnerabilities are displayed on the dashboard. This causes an issue that when all vulnerabilities are fixed, the dashboard shows the vulnerabilities as if they are still present.

This MR attempts to fix this issue by finding the latest successful Pipelines that expose Secure Reports. If the latest pipelines have reports and no vulnerabilities, then the Security Dashboard will show zero vulnerabilities as expected.

This MR resolves issue #35569 (closed).

Effect to the Database / Queries

The following queries are what is generated when viewing the Group Security Dashboard viewing the group security-products.

The SQL query prior to this change An explanation of the query plan can be found at https://explain.depesz.com/s/P2Nn. 
SELECT vulnerability_occurrences.*, ci_pipelines.sha
FROM "vulnerability_occurrences"
INNER JOIN "vulnerability_occurrence_pipelines" ON "vulnerability_occurrence_pipelines"."occurrence_id" = "vulnerability_occurrences"."id"
INNER JOIN "ci_pipelines" ON "ci_pipelines"."id" = "vulnerability_occurrence_pipelines"."pipeline_id"
WHERE 
	"ci_pipelines"."id" IN (
		SELECT MAX(id) AS id
		FROM "ci_pipelines"
		WHERE "ci_pipelines"."project_id" IN (
			SELECT "projects"."id"
			FROM "projects"
			INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project'
			WHERE (rs.path LIKE 'security-products/%'))
		AND ( EXISTS (
					SELECT 1
					FROM "vulnerability_occurrence_pipelines"
					WHERE (ci_pipelines.id = vulnerability_occurrence_pipelines.pipeline_id)) )
		AND ("ci_pipelines"."status" IN ('success'))
		GROUP BY "ci_pipelines"."project_id")
	AND ( NOT EXISTS (
				SELECT 1
				FROM "vulnerability_feedback"
				WHERE
					(vulnerability_occurrences.report_type = vulnerability_feedback.category)
					AND (vulnerability_occurrences.project_id = vulnerability_feedback.project_id)
					AND (ENCODE(vulnerability_occurrences.project_fingerprint, 'HEX') = vulnerability_feedback.project_fingerprint)
					AND "vulnerability_feedback"."feedback_type" = 0) )
ORDER BY
	"vulnerability_occurrences"."severity" DESC,
	"vulnerability_occurrences"."confidence" DESC,
	"vulnerability_occurrences"."id" ASC
LIMIT 20 OFFSET 0

The SQL query after this change I could not determine an explanation of the query plan as the chatops-test job timed out. 
SELECT vulnerability_occurrences.*, ci_pipelines.sha
FROM "vulnerability_occurrences"
INNER JOIN "vulnerability_occurrence_pipelines" ON "vulnerability_occurrence_pipelines"."occurrence_id" = "vulnerability_occurrences"."id"
INNER JOIN "ci_pipelines" ON "ci_pipelines"."id" = "vulnerability_occurrence_pipelines"."pipeline_id"
WHERE 
	"ci_pipelines"."id" IN (
		SELECT MAX(id) AS id
		FROM "ci_pipelines"
		WHERE "ci_pipelines"."project_id" IN (
			SELECT "projects"."id"
			FROM "projects"
			INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project'
			WHERE (rs.path LIKE 'security-products/%'))
		AND ( EXISTS (
					SELECT 1
					FROM "ci_builds"
					WHERE
						"ci_builds"."type" IN ('Ci::Build')
						AND (ci_pipelines.id = ci_builds.commit_id)
						AND ((((OPTIONS LIKE '%:artifacts:%:reports:%:sast:%')
						OR (OPTIONS LIKE '%:artifacts:%:reports:%:dependency_scanning:%'))
						OR (OPTIONS LIKE '%:artifacts:%:reports:%:container_scanning:%'))
						OR (OPTIONS LIKE '%:artifacts:%:reports:%:dast:%'))
						AND ("ci_builds"."retried" = FALSE
						OR "ci_builds"."retried" IS NULL)))
		AND ("ci_pipelines"."status" IN ('success'))
		GROUP BY "ci_pipelines"."project_id")
	AND ( NOT EXISTS (
		SELECT 1
		FROM "vulnerability_feedback"
		WHERE
			(vulnerability_occurrences.report_type = vulnerability_feedback.category)
			AND (vulnerability_occurrences.project_id = vulnerability_feedback.project_id)
			AND (ENCODE(vulnerability_occurrences.project_fingerprint, 'HEX') = vulnerability_feedback.project_fingerprint)
			AND "vulnerability_feedback"."feedback_type" = 0))
ORDER BY
	"vulnerability_occurrences"."severity" DESC,
	"vulnerability_occurrences"."confidence" DESC,
	"vulnerability_occurrences"."id" ASC
LIMIT 20 OFFSET 0

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 Cameron Swords

Merge request reports