summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorLukas Fleischer <lfleischer@archlinux.org>2018-05-10 21:38:25 +0200
committerLukas Fleischer <lfleischer@archlinux.org>2018-05-10 21:38:25 +0200
commitce93360257f3b4ef60035a75708148fc814811c1 (patch)
tree2d1cac47e524ba6e6164bb92e40bbbf78e2a265b
parent4381a0d7c23203d19d8c33afc6264fa584ba0a4f (diff)
downloadaur-ce93360257f3b4ef60035a75708148fc814811c1.tar.gz
aur-ce93360257f3b4ef60035a75708148fc814811c1.tar.xz
Erase login IP addresses after seven days
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@archlinux.org>
-rwxr-xr-xaurweb/scripts/usermaint.py22
-rw-r--r--setup.py1
-rw-r--r--test/setup.sh1
-rwxr-xr-xtest/t2700-usermaint.sh49
4 files changed, 73 insertions, 0 deletions
diff --git a/aurweb/scripts/usermaint.py b/aurweb/scripts/usermaint.py
new file mode 100755
index 00000000..1621d410
--- /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 9d10cc1c..ca26f0d8 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 d98c49c6..5e10fec8 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 00000000..4f625142
--- /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