Track when an account last logged into a community auth site

mhagander · mhagander · commit 9e70a4e0c32f · 2020-02-25T10:58:05.000+01:00
This information can be useful when trying to debug issues with the
community auth and the wonders of distributed data...

No actual django model is created because django still doesn't support
multi-column primary keys. Thus no tool to use the data yet other than
psql.
diff --git a/pgweb/account/migrations/0004_cauth_last_login.py b/pgweb/account/migrations/0004_cauth_last_login.py
@@ -0,0 +1,22 @@
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.27 on 2020-02-25 09:40
+from __future__ import unicode_literals
+
+from django.db import migrations
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('account', '0003_cauth_consent'),
+    ]
+
+    operations = [
+        migrations.RunSQL("""CREATE TABLE account_communityauthlastlogin (
+   user_id int NOT NULL REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED,
+   site_id int NOT NULL REFERENCES account_communityauthsite (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+   lastlogin timestamptz NOT NULL,
+   logincount bigint NOT NULL,
+   CONSTRAINT account_communityauthlastlogin_pkey PRIMARY KEY (user_id, site_id)
+)"""),
+    ]
diff --git a/pgweb/account/views.py b/pgweb/account/views.py
@@ -9,7 +9,7 @@
 from django.contrib.auth.tokens import default_token_generator
 from django.contrib.auth import logout as django_logout
 from django.conf import settings
-from django.db import transaction
+from django.db import transaction, connection
 from django.db.models import Q
 
 import base64
@@ -528,6 +528,14 @@ def communityauth(request, siteid):
             return HttpResponseRedirect('/account/auth/{0}/consent/?{1}'.format(siteid,
                                                                                 urllib.parse.urlencode({'next': '/account/auth/{0}/{1}'.format(siteid, urldata)})))
 
+    # Record the login as the last login to this site. Django doesn't support tables with
+    # multi-column PK, so we have to do this in a raw query.
+    with connection.cursor() as curs:
+        curs.execute("INSERT INTO account_communityauthlastlogin (user_id, site_id, lastlogin, logincount) VALUES (%(userid)s, %(siteid)s, CURRENT_TIMESTAMP, 1) ON CONFLICT (user_id, site_id) DO UPDATE SET lastlogin=CURRENT_TIMESTAMP, logincount=EXCLUDED.logincount+1", {
+            'userid': request.user.id,
+            'siteid': site.id,
+        })
+
     info = {
         'u': request.user.username.encode('utf-8'),
         'f': request.user.first_name.encode('utf-8'),
