Report auth events in manage stage usage ping (!39747) · Merge requests · GitLab.org / GitLab

What does this MR do?

Provide aggregate auth event details in usage ping to help identify how often particular authentication methods are being used. This will help inform decision making about improvements and fixes.

Telemetry example

:usage_activity_by_stage=> {
  :manage => {
    ...
    :user_auth_by_provider=>{"ldap"=>10, "smartcard"=>20, "group_saml"=>10}
  },
},
:usage_activity_by_stage_monthly=> {
  :manage => {
    ...
    :user_auth_by_provider=>{"ldap"=>1, "smartcard"=>2, "group_saml"=>1}
  },
}

Database

Query

https://paste.depesz.com/s/Ehy

SELECT COUNT(DISTINCT "authentication_events"."user_id") FROM "authentication_events" WHERE "authentication_events"."success" = true AND "authentication_events"."provider" = 'standard' AND "authentication_events"."created_at" BETWEEN '2020-08-12 19:05:45.606188' AND '2020-09-09 19:05:45.606328' AND "authentication_events"."user_id" BETWEEN 0 AND 1000;

The above will happen once per provider configured on the system. For any given instance this is likely just one or two distinct providers, but it can be any of dozens we support. Of course, the last query will depend on how many entries there are. If there are thousands/millions it will be batched accordingly.

Query Plan

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

 Aggregate  (cost=2.17..2.18 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=1)
   ->  Index Only Scan using index_authentication_events_on_provider_user_id_created_at on authentication_events  (cost=0.14..2.17 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1)
         Index Cond: ((provider = 'standard'::text) AND (user_id >= 0) AND (user_id <= 1000) AND (created_at >= '2020-08-16 19:05:45.606188-05'::timestamp with time zone) AND (created_at <= '2020-09-16 19:05:45.606328-05'::timestamp with time zone))
         Heap Fetches: 0
 Planning Time: 0.220 ms
 Execution Time: 0.039 ms
(6 rows)

Migration up

== 20200916151442 AddResultIndexToAuthenticationEvents: migrating =============
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:authentication_events, [:provider, :user_id, :created_at], {:where=>"result = 1", :name=>"index_authentication_events_on_provider_user_id_created_at", :algorithm=>:concurrently})
   -> 0.0030s
-- add_index(:authentication_events, [:provider, :user_id, :created_at], {:where=>"result = 1", :name=>"index_authentication_events_on_provider_user_id_created_at", :algorithm=>:concurrently})
   -> 0.0093s
== 20200916151442 AddResultIndexToAuthenticationEvents: migrated (0.0127s) ====

Migration down

Screenshots

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

Report auth events in manage stage usage ping