Part 2 of #224102 (closed)
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.
: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}
},
}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.
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)== 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) ====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:
@gitlab-com/gl-security/appsec