[PATCH] Erase login IP addresses after seven days

[Lukas Fleischer]

Add a script to periodically remove old IP addresses from the users
database.

The login IP addresses are stored for spam protection and to prevent
from abuse. It is quite unlikely that we ever need the IP address of a
user whose last login is more than a week old. It makes sense to remove
such IP addresses to protect our users' privacy.

Signed-off-by: Lukas Fleischer <lfleischer at archlinux.org>
---
 aurweb/scripts/usermaint.py | 22 +++++++++++++++++
 setup.py                    |  1 +
 test/setup.sh               |  1 +
 test/t2700-usermaint.sh     | 49 +++++++++++++++++++++++++++++++++++++
 4 files changed, 73 insertions(+)
 create mode 100755 aurweb/scripts/usermaint.py
 create mode 100755 test/t2700-usermaint.sh

diff --git a/aurweb/scripts/usermaint.py b/aurweb/scripts/usermaint.py
new file mode 100755
index 0000000..1621d41
--- /dev/null
+++ b/aurweb/scripts/usermaint.py
@@ -0,0 +1,22 @@
+#!/usr/bin/env python3
+
+import time
+
+import aurweb.db
+
+
+def main():
+    conn = aurweb.db.Connection()
+
+    limit_to = int(time.time()) - 86400 * 7
+    conn.execute("UPDATE Users SET LastLoginIPAddress = NULL " +
+                 "WHERE LastLogin < ?", [limit_to])
+    conn.execute("UPDATE Users SET LastSSHLoginIPAddress = NULL " +
+                 "WHERE LastSSHLogin < ?", [limit_to])
+
+    conn.commit()
+    conn.close()
+
+
+if __name__ == '__main__':
+    main()
diff --git a/setup.py b/setup.py
index 9d10cc1..ca26f0d 100644
--- a/setup.py
+++ b/setup.py
@@ -29,6 +29,7 @@ setup(
             'aurweb-popupdate = aurweb.scripts.popupdate:main',
             'aurweb-rendercomment = aurweb.scripts.rendercomment:main',
             'aurweb-tuvotereminder = aurweb.scripts.tuvotereminder:main',
+            'aurweb-usermaint = aurweb.scripts.usermaint:main',
         ],
     },
 )
diff --git a/test/setup.sh b/test/setup.sh
index d98c49c..5e10fec 100644
--- a/test/setup.sh
+++ b/test/setup.sh
@@ -14,6 +14,7 @@ GIT_UPDATE="$TOPLEVEL/aurweb/git/update.py"
 MKPKGLISTS="$TOPLEVEL/aurweb/scripts/mkpkglists.py"
 TUVOTEREMINDER="$TOPLEVEL/aurweb/scripts/tuvotereminder.py"
 PKGMAINT="$TOPLEVEL/aurweb/scripts/pkgmaint.py"
+USERMAINT="$TOPLEVEL/aurweb/scripts/usermaint.py"
 AURBLUP="$TOPLEVEL/aurweb/scripts/aurblup.py"
 NOTIFY="$TOPLEVEL/aurweb/scripts/notify.py"
 RENDERCOMMENT="$TOPLEVEL/aurweb/scripts/rendercomment.py"
diff --git a/test/t2700-usermaint.sh b/test/t2700-usermaint.sh
new file mode 100755
index 0000000..4f62514
--- /dev/null
+++ b/test/t2700-usermaint.sh
@@ -0,0 +1,49 @@
+#!/bin/sh
+
+test_description='usermaint tests'
+
+. ./setup.sh
+
+test_expect_success 'Test removal of login IP addresses.' '
+	now=$(date -d now +%s) &&
+	threedaysago=$(date -d "3 days ago" +%s) &&
+	tendaysago=$(date -d "10 days ago" +%s) &&
+	cat <<-EOD | sqlite3 aur.db &&
+	UPDATE Users SET LastLogin = $threedaysago, LastLoginIPAddress = "1.2.3.4" WHERE ID = 1;
+	UPDATE Users SET LastLogin = $tendaysago, LastLoginIPAddress = "2.3.4.5" WHERE ID = 2;
+	UPDATE Users SET LastLogin = $now, LastLoginIPAddress = "3.4.5.6" WHERE ID = 3;
+	UPDATE Users SET LastLogin = 0, LastLoginIPAddress = "4.5.6.7" WHERE ID = 4;
+	UPDATE Users SET LastLogin = 0, LastLoginIPAddress = "5.6.7.8" WHERE ID = 5;
+	UPDATE Users SET LastLogin = $tendaysago, LastLoginIPAddress = "6.7.8.9" WHERE ID = 6;
+	EOD
+	"$USERMAINT" &&
+	cat <<-EOD >expected &&
+	1.2.3.4
+	3.4.5.6
+	EOD
+	echo "SELECT LastLoginIPAddress FROM Users WHERE LastLoginIPAddress IS NOT NULL;" | sqlite3 aur.db >actual &&
+	test_cmp actual expected
+'
+
+test_expect_success 'Test removal of SSH login IP addresses.' '
+	now=$(date -d now +%s) &&
+	threedaysago=$(date -d "3 days ago" +%s) &&
+	tendaysago=$(date -d "10 days ago" +%s) &&
+	cat <<-EOD | sqlite3 aur.db &&
+	UPDATE Users SET LastSSHLogin = $now, LastSSHLoginIPAddress = "1.2.3.4" WHERE ID = 1;
+	UPDATE Users SET LastSSHLogin = $threedaysago, LastSSHLoginIPAddress = "2.3.4.5" WHERE ID = 2;
+	UPDATE Users SET LastSSHLogin = $tendaysago, LastSSHLoginIPAddress = "3.4.5.6" WHERE ID = 3;
+	UPDATE Users SET LastSSHLogin = 0, LastSSHLoginIPAddress = "4.5.6.7" WHERE ID = 4;
+	UPDATE Users SET LastSSHLogin = 0, LastSSHLoginIPAddress = "5.6.7.8" WHERE ID = 5;
+	UPDATE Users SET LastSSHLogin = $tendaysago, LastSSHLoginIPAddress = "6.7.8.9" WHERE ID = 6;
+	EOD
+	"$USERMAINT" &&
+	cat <<-EOD >expected &&
+	1.2.3.4
+	2.3.4.5
+	EOD
+	echo "SELECT LastSSHLoginIPAddress FROM Users WHERE LastSSHLoginIPAddress IS NOT NULL;" | sqlite3 aur.db >actual &&
+	test_cmp actual expected
+'
+
+test_done
-- 
2.17.0