Show project-level audit events in group (!27785) · Merge requests · GitLab.org / GitLab

Tan Le requested to merge expose-project-audit-events-on-group into master Mar 23, 2020

What does this MR do?

Surface project-level audit events on group-level audit events. These new project-level audit events are now visible via both UI (https://docs.gitlab.com/ee/administration/audit_events.html#group-events-starter) and API (https://docs.gitlab.com/ee/api/audit_events.html#group-audit-events-starter)

Query Plans

Using OR

SELECT
    "audit_events".*
FROM
    "audit_events"
WHERE ("audit_events"."entity_type" = 'Group'
    AND "audit_events"."entity_id" = 9970
    OR "audit_events"."entity_type" = 'Project'
    AND "audit_events"."entity_id" IN (
        SELECT
            "projects"."id"
        FROM
            "projects"
        WHERE
            "projects"."namespace_id" = 9970))
ORDER BY
    "audit_events"."id" DESC
LIMIT 20 OFFSET 0

 Limit  (cost=12.04..38.92 rows=20 width=216) (actual time=97.020..450.182 rows=20 loops=1)
   Buffers: shared hit=8601 read=609
   I/O Timings: read=434.420
   ->  Index Scan using audit_events_pkey on public.audit_events  (cost=12.04..42434611.68 rows=31572128 width=216) (actual time=97.018..450.164 rows=20 loops=1)
         Filter: ((((audit_events.entity_type)::text = 'Group'::text) AND (audit_events.entity_id = 9970)) OR (((audit_events.entity_type)::text = 'Project'::text) AND (hashed SubPlan 1)))
         Rows Removed by Filter: 9552
         Buffers: shared hit=8601 read=609
         I/O Timings: read=434.420
         SubPlan 1
           ->  Index Only Scan using index_projects_on_namespace_id_and_id on public.projects  (cost=0.43..10.83 rows=255 width=4) (actual time=6.462..23.818 rows=214 loops=1)
                 Index Cond: (projects.namespace_id = 9970)
                 Heap Fetches: 3
                 Buffers: shared hit=175 read=20
                 I/O Timings: read=23.535

Time: 423.441 ms
  - planning: 0.480 ms
  - execution: 422.961 ms
    - I/O read: 381.440 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 10260 (~80.20 MiB) from the buffer pool
  - reads: 888 (~6.90 MiB) from the OS file cache, including disk I/O
  - dirtied: 832 (~6.50 MiB)
  - writes: 0

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

Using UNION

SELECT
    "audit_events".*
FROM ((
        SELECT
            "audit_events".*
        FROM
            "audit_events"
        WHERE
            "audit_events"."entity_type" = 'Group'
            AND "audit_events"."entity_id" = 9970)
    UNION (
        SELECT
            "audit_events".*
        FROM
            "audit_events"
        WHERE
            "audit_events"."entity_type" = 'Project'
            AND "audit_events"."entity_id" IN (
                SELECT
                    "projects"."id"
                FROM
                    "projects"
                WHERE
                    "projects"."namespace_id" = 9970))) audit_events
ORDER BY
    "audit_events"."id" DESC
LIMIT 20 OFFSET 0

 Limit  (cost=7900.68..7900.73 rows=20 width=1092) (actual time=382914.921..382914.930 rows=20 loops=1)
   Buffers: shared hit=4717845 read=467798 dirtied=4276 written=551
   I/O Timings: read=347297.992 write=24.493
   ->  Sort  (cost=7900.68..7910.80 rows=4046 width=1092) (actual time=382914.919..382914.926 rows=20 loops=1)
         Sort Key: audit_events.id DESC
         Sort Method: top-N heapsort  Memory: 35kB
         Buffers: shared hit=4717845 read=467798 dirtied=4276 written=551
         I/O Timings: read=347297.992 write=24.493
         ->  HashAggregate  (cost=7712.10..7752.56 rows=4046 width=1092) (actual time=375953.963..380952.098 rows=5205769 loops=1)
               Group Key: audit_events.id, audit_events.author_id, audit_events.type, audit_events.entity_id, audit_events.entity_type, audit_events.details, audit_events.created_at, audit_events.updated_at
               Buffers: shared hit=4717842 read=467798 dirtied=4276 written=551
               I/O Timings: read=347297.992 write=24.493
               ->  Append  (cost=0.57..7631.18 rows=4046 width=1092) (actual time=12.905..360383.890 rows=5205769 loops=1)
                     Buffers: shared hit=4717842 read=467798 dirtied=4276 written=551
                     I/O Timings: read=347297.992 write=24.493
                     ->  Index Scan using index_audit_events_on_entity_id_and_entity_type_and_id_desc on public.audit_events  (cost=0.57..30.35 rows=15 width=217) (actual time=12.905..2912.145 rows=2045 loops=1)
                           Index Cond: ((audit_events.entity_id = 9970) AND ((audit_events.entity_type)::text = 'Group'::text))
                           Buffers: shared hit=102 read=1884 dirtied=11
                           I/O Timings: read=2882.167
                     ->  Nested Loop  (cost=1.01..7560.37 rows=4031 width=217) (actual time=8.955..356633.972 rows=5203724 loops=1)
                           Buffers: shared hit=4717740 read=465914 dirtied=4265 written=551
                           I/O Timings: read=344415.825 write=24.493
                           ->  Index Only Scan using index_projects_on_namespace_id_and_id on public.projects  (cost=0.43..4.71 rows=16 width=4) (actual time=4.628..63.523 rows=216 loops=1)
                                 Index Cond: (projects.namespace_id = 9970)
                                 Heap Fetches: 18
                                 Buffers: shared hit=174 read=45 dirtied=7
                                 I/O Timings: read=58.308
                           ->  Index Scan using index_audit_events_on_entity_id_and_entity_type_and_id_desc on public.audit_events audit_events_1  (cost=0.57..469.71 rows=252 width=217) (actual time=3.844..1641.687 rows=24091 loops=216)
                                 Index Cond: ((audit_events_1.entity_id = projects.id) AND ((audit_events_1.entity_type)::text = 'Project'::text))
                                 Buffers: shared hit=4717566 read=465869 dirtied=4258 written=551
                                 I/O Timings: read=344357.517 write=24.493

Time: 6.386 min
  - planning: 0.993 ms
  - execution: 6.386 min
    - I/O read: 5.788 min
    - I/O write: 24.493 ms

Shared buffers:
  - hits: 4717845 (~36.00 GiB) from the buffer pool
  - reads: 467798 (~3.60 GiB) from the OS file cache, including disk I/O
  - dirtied: 4276 (~33.40 MiB)
  - writes: 551 (~4.30 MiB)

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

Feature flag

Due to performance concern, this feature is under audit_log_group_level feature flag.

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
[-] Tested in all supported browsers
[-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done

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

Related to #39139

Edited Apr 29, 2020 by Tan Le

Show project-level audit events in group