Skip to content

Chain of custody report filter by merge commit sha

Aishwarya Subramanian requested to merge filter-commit-sha into master

What does this MR do?

The Chain of Custody report in the Compliance dashboard provides a report of the latest Merge commits belonging to a group.

As part &4608 (closed), group administrators will be able to filter the report by Merge Commit SHA. The filter will be further extended to include squash_commit_sha and diff_head_sha in a future iteration.

This MR adds backend support for the user filter to be implemented in #267629 (closed).

Query
SELECT
  "merge_requests".*
FROM
  "merge_requests"
  INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
  LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE
  "projects"."namespace_id" = 9970
  AND (EXISTS (
      SELECT
        1
      FROM
        "project_authorizations"
      WHERE
        "project_authorizations"."user_id" = 4014344
        AND (project_authorizations.project_id = projects.id))
      OR projects.visibility_level IN (0, 10, 20))
  AND ("project_features"."merge_requests_access_level" > 0
    OR "project_features"."merge_requests_access_level" IS NULL)
  AND ("merge_requests"."state_id" IN (3))
  AND "merge_requests"."merge_commit_sha" = '894004bf7ac5abdefc87fc32d29e93b0b4256f55'
ORDER BY
  "merge_requests"."id" DESC

Query plan: https://explain.depesz.com/s/xK82

Execution time (cold cache):

Time: 1.716 s
  - planning: 1.968 ms
  - execution: 1.714 s
    - I/O read: 1.695 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1543 (~12.10 MiB) from the buffer pool
  - reads: 859 (~6.70 MiB) from the OS file cache, including disk I/O
  - dirtied: 28 (~224.00 KiB)
  - writes: 0

Execution time (warm cache):

Time: 4.567 ms
  - planning: 1.736 ms
  - execution: 2.831 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2395 (~18.70 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Mentions #267628 (closed)

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 Aishwarya Subramanian

Merge request reports