[aur-dev] [PATCH 1/4] git-serve: Save last SSH login date and IP address
In addition to logging the last login date and IP address on the web interface, store the time stamp and IP address of the last SSH login in the database. This simplifies user banning if one of the new SSH interface features, such as the voting mechanism implemented in 7ee2fdd (git-serve: Add support for (un-)voting, 2017-01-23), is abused. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org> --- aurweb/git/serve.py | 13 +++++++++++++ schema/aur-schema.sql | 2 ++ upgrading/4.5.0.txt | 10 +++++++++- 3 files changed, 24 insertions(+), 1 deletion(-) diff --git a/aurweb/git/serve.py b/aurweb/git/serve.py index 4c03e3b..cfd4910 100755 --- a/aurweb/git/serve.py +++ b/aurweb/git/serve.py @@ -410,6 +410,18 @@ def pkgbase_has_full_access(pkgbase, user): return cur.fetchone()[0] > 0 +def log_ssh_login(user, remote_addr): + conn = aurweb.db.Connection() + + now = int(time.time()) + conn.execute("UPDATE Users SET LastSSHLogin = ?, " + + "LastSSHLoginIPAddress = ? WHERE Username = ?", + [now, remote_addr, user]) + + conn.commit() + conn.close() + + def die(msg): sys.stderr.write("{:s}\n".format(msg)) exit(1) @@ -451,6 +463,7 @@ def serve(action, cmdargv, user, privileged, remote_addr): if enable_maintenance: if remote_addr not in maintenance_exc: raise aurweb.exceptions.MaintenanceException + log_ssh_login(user, remote_addr) if action == 'git' and cmdargv[1] in ('upload-pack', 'receive-pack'): action = action + '-' + cmdargv[1] diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql index 13e3fd9..b0663eb 100644 --- a/schema/aur-schema.sql +++ b/schema/aur-schema.sql @@ -38,6 +38,8 @@ CREATE TABLE Users ( PGPKey VARCHAR(40) NULL DEFAULT NULL, LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0, LastLoginIPAddress VARCHAR(45) NULL DEFAULT NULL, + LastSSHLogin BIGINT UNSIGNED NOT NULL DEFAULT 0, + LastSSHLoginIPAddress VARCHAR(45) NULL DEFAULT NULL, InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0, RegistrationTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CommentNotify TINYINT(1) NOT NULL DEFAULT 1, diff --git a/upgrading/4.5.0.txt b/upgrading/4.5.0.txt index 6c4ce80..5cf0888 100644 --- a/upgrading/4.5.0.txt +++ b/upgrading/4.5.0.txt @@ -2,4 +2,12 @@ --- ALTER TABLE Users ADD COLUMN Timezone VARCHAR(32) NOT NULL DEFAULT 'UTC'; ---- \ No newline at end of file +--- + +2. Add LastSSHLogin and LastSSHLoginIPAddress columns to the Users table: + +--- +ALTER TABLE Users + ADD COLUMN LastSSHLogin BIGINT UNSIGNED NOT NULL DEFAULT 0, + ADD COLUMN LastSSHLoginIPAddress VARCHAR(45) NULL DEFAULT NULL; +--- -- 2.11.0
Inspired by commit 32c8d0c (Store last login address as plain text, 2016-03-13). Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org> --- schema/aur-schema.sql | 2 +- upgrading/4.5.0.txt | 7 +++++++ web/lib/acctfuncs.inc.php | 2 +- 3 files changed, 9 insertions(+), 2 deletions(-) diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql index b0663eb..99f9083 100644 --- a/schema/aur-schema.sql +++ b/schema/aur-schema.sql @@ -376,7 +376,7 @@ CREATE TABLE IF NOT EXISTS TU_Votes ( -- Malicious user banning -- CREATE TABLE Bans ( - IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + IPAddress VARCHAR(45) NULL DEFAULT NULL, BanTS TIMESTAMP NOT NULL, PRIMARY KEY (IPAddress) ) ENGINE = InnoDB; diff --git a/upgrading/4.5.0.txt b/upgrading/4.5.0.txt index 5cf0888..fb0a299 100644 --- a/upgrading/4.5.0.txt +++ b/upgrading/4.5.0.txt @@ -11,3 +11,10 @@ ALTER TABLE Users ADD COLUMN LastSSHLogin BIGINT UNSIGNED NOT NULL DEFAULT 0, ADD COLUMN LastSSHLoginIPAddress VARCHAR(45) NULL DEFAULT NULL; --- + +3. Convert the IPAddress column of the Bans table to VARCHAR(45). If the table + contains any active bans, convert them accordingly: + +---- +ALTER TABLE Bans MODIFY IPAddress VARCHAR(45) NULL DEFAULT NULL; +---- diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php index 08dbc67..b3cf612 100644 --- a/web/lib/acctfuncs.inc.php +++ b/web/lib/acctfuncs.inc.php @@ -621,7 +621,7 @@ function try_login() { function is_ipbanned() { $dbh = DB::connect(); - $q = "SELECT * FROM Bans WHERE IPAddress = " . $dbh->quote(ip2long($_SERVER['REMOTE_ADDR'])); + $q = "SELECT * FROM Bans WHERE IPAddress = " . $dbh->quote($_SERVER['REMOTE_ADDR']); $result = $dbh->query($q); return ($result->fetchColumn() ? true : false); -- 2.11.0
Currently, IP address bans affect the web interface only. Make sure they are honored in the SSH interface as well. Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org> --- aurweb/exceptions.py | 4 ++++ aurweb/git/serve.py | 12 ++++++++++++ 2 files changed, 16 insertions(+) diff --git a/aurweb/exceptions.py b/aurweb/exceptions.py index 639f9e0..664db68 100644 --- a/aurweb/exceptions.py +++ b/aurweb/exceptions.py @@ -6,6 +6,10 @@ class MaintenanceException(AurwebException): pass +class BannedException(AurwebException): + pass + + class PermissionDeniedException(AurwebException): def __init__(self, user): msg = 'permission denied: {:s}'.format(user) diff --git a/aurweb/git/serve.py b/aurweb/git/serve.py index cfd4910..44cce75 100755 --- a/aurweb/git/serve.py +++ b/aurweb/git/serve.py @@ -422,6 +422,14 @@ def log_ssh_login(user, remote_addr): conn.close() +def bans_match(remote_addr): + conn = aurweb.db.Connection() + + cur = conn.execute("SELECT COUNT(*) FROM Bans WHERE IPAddress = ?", + [remote_addr]) + return cur.fetchone()[0] > 0 + + def die(msg): sys.stderr.write("{:s}\n".format(msg)) exit(1) @@ -463,6 +471,8 @@ def serve(action, cmdargv, user, privileged, remote_addr): if enable_maintenance: if remote_addr not in maintenance_exc: raise aurweb.exceptions.MaintenanceException + if bans_match(remote_addr): + raise aurweb.exceptions.BannedException log_ssh_login(user, remote_addr) if action == 'git' and cmdargv[1] in ('upload-pack', 'receive-pack'): @@ -586,6 +596,8 @@ def main(): serve(action, cmdargv, user, privileged, remote_addr) except aurweb.exceptions.MaintenanceException: die("The AUR is down due to maintenance. We will be back soon.") + except aurweb.exceptions.BannedException: + die("The SSH interface is disabled for your IP address.") except aurweb.exceptions.InvalidArgumentsException as e: die_with_help('{:s}: {}'.format(action, e)) except aurweb.exceptions.AurwebException as e: -- 2.11.0
Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org> --- test/setup.sh | 8 ++++++++ test/t1200-git-serve.sh | 21 +++++++++++++++++++++ 2 files changed, 29 insertions(+) diff --git a/test/setup.sh b/test/setup.sh index 26873e8..2959a4e 100644 --- a/test/setup.sh +++ b/test/setup.sh @@ -98,6 +98,12 @@ AUTH_KEYTYPE_MISSING=sha-rsa AUTH_KEYTEXT_MISSING=AAAAB3NzaC1yc2EAAAADAQABAAABAQC9UTpssBunuTBCT3KFtv+yb+cN0VmI2C9O9U7wHlkEZWxNBK8is6tnDHXBxRuvRk0LHILkTidLLFX22ZF0+TFgSz7uuEvGZVNpa2Fn2+vKJJYMvZEvb/f8VHF5/Jddt21VOyu23royTN/duiT7WIZdCtEmq5C9Y43NPfsB8FbUc+FVSYT2Lq7g1/bzvFF+CZxwCrGjC3qC7p3pshICfFR8bbWgRN33ClxIQ7MvkcDtfNu38dLotJqdfEa7NdQgba5/S586f1A4OWKc/mQJFyTaGhRBxw/cBSjqonvO0442VYLHFxlrTHoUunKyOJ8+BJfKgjWmfENC9ESY3mL/IEn5 AUTH_FINGERPRINT_MISSING=SHA256:uB0B+30r2WA1TDMUmFcaEBjosjnFGzn33XFhiyvTL9w +# Setup fake SSH environment. +SSH_CLIENT='1.2.3.4 1234 22' +SSH_CONNECTION='1.2.3.4 1234 4.3.2.1 22' +SSH_TTY=/dev/pts/0 +export SSH_CLIENT SSH_CONNECTION SSH_TTY + # Initialize the test database. rm -f aur.db sed \ @@ -122,6 +128,8 @@ echo "INSERT INTO Users (ID, UserName, Passwd, Email, AccountTypeID) VALUES (9, echo "INSERT INTO SSHPubKeys (UserID, Fingerprint, PubKey) VALUES (1, '$AUTH_FINGERPRINT_USER', '$AUTH_KEYTYPE_USER $AUTH_KEYTEXT_USER');" | sqlite3 aur.db echo "INSERT INTO SSHPubKeys (UserID, Fingerprint, PubKey) VALUES (2, '$AUTH_FINGERPRINT_TU', '$AUTH_KEYTYPE_TU $AUTH_KEYTEXT_TU');" | sqlite3 aur.db +echo "INSERT INTO Bans (IPAddress, BanTS) VALUES ('1.3.3.7', 0);" | sqlite3 aur.db + echo "INSERT INTO PackageBlacklist (Name) VALUES ('forbidden');" | sqlite3 aur.db echo "INSERT INTO OfficialProviders (Name, Repo, Provides) VALUES ('official', 'core', 'official');" | sqlite3 aur.db diff --git a/test/t1200-git-serve.sh b/test/t1200-git-serve.sh index f986b62..07383af 100755 --- a/test/t1200-git-serve.sh +++ b/test/t1200-git-serve.sh @@ -31,6 +31,27 @@ test_expect_success 'Test maintenance mode.' ' mv config.old config ' +test_expect_success 'Test IP address logging.' ' + SSH_ORIGINAL_COMMAND=help AUR_USER=user "$GIT_SERVE" 2>actual && + cat >expected <<-EOF && + 1.2.3.4 + EOF + echo "SELECT LastSSHLoginIPAddress FROM Users WHERE UserName = \"user\";" | \ + sqlite3 aur.db >actual && + test_cmp expected actual +' + +test_expect_success 'Test IP address bans.' ' + SSH_CLIENT_ORIG="$SSH_CLIENT" && + SSH_CLIENT="1.3.3.7 1337 22" && + SSH_ORIGINAL_COMMAND=help test_must_fail "$GIT_SERVE" 2>actual && + cat >expected <<-EOF && + The SSH interface is disabled for your IP address. + EOF + test_cmp expected actual && + SSH_CLIENT="$SSH_CLIENT_ORIG" +' + test_expect_success 'Test setup-repo and list-repos.' ' SSH_ORIGINAL_COMMAND="setup-repo foobar" AUR_USER=user \ "$GIT_SERVE" 2>&1 && -- 2.11.0
participants (1)
-
Lukas Fleischer