From ce93360257f3b4ef60035a75708148fc814811c1 Mon Sep 17 00:00:00 2001 From: Lukas Fleischer Date: Thu, 10 May 2018 21:38:25 +0200 Subject: 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 --- 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 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 -- cgit v1.2.3-24-g4f1b