Skip to content

Add AuthenticationEvent to store sign-in events

Drew Blessing requested to merge dblessing-auth-events into master

What does this MR do?

Part 1 of #224102 (closed).

AuthenticationEvent will track sign-ins via providers such as LDAP, SAML, and other OmniAuth/OAuth providers. The end goal is to report usage of each authentication method via Usage Ping. However, this MR only deals with adding the model and database as a first step.

The plan is to also use this as an extension of AuditEvent in the future. Currently we store auth events in audit events but it's not possible to query for authentication events specifically because the information is embedded in the details column. That's why we need to recreate it in order to use in usage ping. I can see its use in Audit Events making filtering easier, too. Some of this is discussed in the issue starting at #224102 (comment 398939484).

I believe the columns here will represent the data necessary to replicate the behavior of audit events specific to authentication. Later on we can make AuthenticationEvent a subclass and implement all of the expected methods to return data in the format audit events expects.

With this data we can easily create audit events such as:

  • 'Successfully signed-in with Google OAuth2 from IP address 123.123.123.123'
  • 'Failed sign-in with Twitter from IP Address 234.234.234.234'

Database

The indexes created should be sufficient for the currently planned queries for usage ping. The planned query is something like:

AuthenticationEvent.group(:provider).distinct.count(:user_id)
SELECT COUNT(DISTINCT "authentication_events"."user_id") AS count_user_id, "authentication_events"."provider" AS authentication_events_provider FROM "authentication_events" GROUP BY "authentication_events"."provider"

Example query plan for above query

                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.14..0.74 rows=2 width=12) (actual time=0.025..0.030 rows=2 loops=1)
   ->  GroupAggregate  (cost=0.14..3.46 rows=11 width=12) (actual time=0.025..0.029 rows=2 loops=1)
         Group Key: provider
         ->  Index Scan using provider_index on authentication_events  (cost=0.14..3.30 rows=11 width=12) (actual time=0.013..0.016 rows=11 loops=1)
 Planning Time: 0.074 ms
 Execution Time: 0.061 ms
(6 rows)

Migration Output

== 20200908100053 CreateAuthenticationEvents: migrating =======================
-- table_exists?(:authentication_events)
   -> 0.0004s
-- create_table(:authentication_events)
   -> 0.0103s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE authentication_events\nADD CONSTRAINT check_c64f424630\nCHECK ( char_length(provider) <= 64 )\nNOT VALID;\n")
   -> 0.0005s
-- execute("ALTER TABLE authentication_events VALIDATE CONSTRAINT check_c64f424630;")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE authentication_events\nADD CONSTRAINT check_45a6cc4e80\nCHECK ( char_length(user_name) <= 255 )\nNOT VALID;\n")
   -> 0.0006s
-- execute("ALTER TABLE authentication_events VALIDATE CONSTRAINT check_45a6cc4e80;")
   -> 0.0005s
== 20200908100053 CreateAuthenticationEvents: migrated (0.0234s) ==============

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 Drew Blessing

Merge request reports