diff options
-rw-r--r-- | lib/VNWeb/Auth.pm | 14 | ||||
-rw-r--r-- | lib/VNWeb/Discussions/Edit.pm | 3 | ||||
-rw-r--r-- | lib/VNWeb/User/Edit.pm | 14 | ||||
-rw-r--r-- | lib/VNWeb/User/Login.pm | 4 | ||||
-rw-r--r-- | sql/perms.sql | 1 | ||||
-rw-r--r-- | sql/schema.sql | 12 | ||||
-rw-r--r-- | util/updates/2020-04-27-audit-logging.sql | 11 |
7 files changed, 56 insertions, 3 deletions
diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm index 0837fdd9..5a7f4d46 100644 --- a/lib/VNWeb/Auth.pm +++ b/lib/VNWeb/Auth.pm @@ -290,4 +290,18 @@ sub prefSet { } +# Add an entry to the audit log. +sub audit { + my($self, $affected_uid, $action, $detail) = @_; + tuwf->dbExeci('INSERT INTO audit_log', { + by_uid => $self->uid(), + by_name => $self->{user}{user_name}, + by_ip => tuwf->reqIP(), + affected_uid => $affected_uid||undef, + affected_name => $affected_uid ? sql('(SELECT username FROM users WHERE id =', \$affected_uid, ')') : undef, + action => $action, + detail => $detail, + }); +} + 1; diff --git a/lib/VNWeb/Discussions/Edit.pm b/lib/VNWeb/Discussions/Edit.pm index 40f13a74..740eb9cb 100644 --- a/lib/VNWeb/Discussions/Edit.pm +++ b/lib/VNWeb/Discussions/Edit.pm @@ -51,7 +51,7 @@ elm_api DiscussionsEdit => $FORM_OUT, $FORM_IN, sub { return elm_Unauth if !can_edit t => $t; if($data->{delete} && auth->permBoardmod) { - warn "AUDIT: Delete t$tid.$num\n"; + auth->audit($t->{user_id}, 'post delete', "deleted t$tid.$num"); if($num == 1) { # (This could be a single query if there were proper ON DELETE CASCADE in the DB, though that's hard for notifications...) tuwf->dbExeci('DELETE FROM threads_posts WHERE tid =', \$tid); @@ -68,6 +68,7 @@ elm_api DiscussionsEdit => $FORM_OUT, $FORM_IN, sub { return elm_Redirect "/t$tid"; } } + auth->audit($t->{user_id}, 'post edit', "edited t$tid.$num") if $t->{user_id} != auth->uid; my $pollchanged = !$data->{tid} && $data->{poll}; if($num == 1) { diff --git a/lib/VNWeb/User/Edit.pm b/lib/VNWeb/User/Edit.pm index 3248b0de..8fb2f7ff 100644 --- a/lib/VNWeb/User/Edit.pm +++ b/lib/VNWeb/User/Edit.pm @@ -136,13 +136,16 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub { if($own && $data->{password}) { return elm_InsecurePass if is_insecurepass $data->{password}{new}; + my $ok = 1; if(auth->uid == $data->{id}) { - return elm_BadCurPass if !auth->setpass($data->{id}, undef, $data->{password}{old}, $data->{password}{new}); + $ok = 0 if !auth->setpass($data->{id}, undef, $data->{password}{old}, $data->{password}{new}); } else { tuwf->dbExeci(select => sql_func user_admin_setpass => \$data->{id}, \auth->uid, sql_fromhex(auth->token), sql_fromhex auth->_preparepass($data->{password}{new}) ); } + auth->audit($data->{id}, $ok ? 'password change' : 'bad password', 'at user edit form'); + return elm_BadCurPass if !$ok; } my $ret = \&elm_Success; @@ -151,6 +154,7 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub { my $oldmail = $own && _getmail $data->{id}; if($own && $newmail ne $oldmail) { return elm_DoubleEmail if tuwf->dbVali(select => sql_func user_emailexists => \$newmail, \$data->{id}); + auth->audit($data->{id}, 'email change', "old=$oldmail; new=$newmail"); if(auth->permUsermod) { tuwf->dbExeci(select => sql_func user_admin_setmail => \$data->{id}, \auth->uid, sql_fromhex(auth->token), \$newmail); } else { @@ -174,7 +178,15 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub { } } + my $old = tuwf->dbRowi('SELECT', sql_comma(keys %set), 'FROM users WHERE id =', \$data->{id}); tuwf->dbExeci('UPDATE users SET', \%set, 'WHERE id =', \$data->{id}); + my $new = tuwf->dbRowi('SELECT', sql_comma(keys %set), 'FROM users WHERE id =', \$data->{id}); + + $_ = JSON::XS->new->allow_nonref->encode($_) for values %$old, %$new; + my @diff = grep $old->{$_} ne $new->{$_}, keys %set; + auth->audit($data->{id}, 'user edit', join '; ', map "$_: $old->{$_} -> $new->{$_}", @diff) + if @diff && (auth->uid != $data->{id} || grep /^(perm_|ign_votes|username)/, @diff); + $ret->(); }; diff --git a/lib/VNWeb/User/Login.pm b/lib/VNWeb/User/Login.pm index 95295e05..2c10bca0 100644 --- a/lib/VNWeb/User/Login.pm +++ b/lib/VNWeb/User/Login.pm @@ -31,7 +31,8 @@ elm_api UserLogin => undef, { return $insecure ? elm_InsecurePass : elm_Success if auth->login($data->{username}, $data->{password}, $insecure); - # Failed login, update throttle. + # Failed login, log and update throttle. + auth->audit(tuwf->dbVali('SELECT id FROM users WHERE username =', \$data->{username}), 'bad password', 'failed login attempt'); my $upd = { ip => \$ip, timeout => sql_fromtime $tm + config->{login_throttle}[0] @@ -50,6 +51,7 @@ elm_api UserChangePass => undef, { my $uid = tuwf->dbVali('SELECT id FROM users WHERE username =', \$data->{username}); die if !$uid; return elm_InsecurePass if is_insecurepass $data->{newpass}; + auth->audit($uid, 'password change', 'after login with an insecure password'); die if !auth->setpass($uid, undef, $data->{oldpass}, $data->{newpass}); # oldpass should already have been verified. elm_Success }; diff --git a/sql/perms.sql b/sql/perms.sql index 103b3561..bf72873f 100644 --- a/sql/perms.sql +++ b/sql/perms.sql @@ -6,6 +6,7 @@ GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_site; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vndb_site; GRANT SELECT, INSERT ON anime TO vndb_site; +GRANT INSERT ON audit_log TO vndb_site; GRANT SELECT, INSERT ON changes TO vndb_site; GRANT SELECT, INSERT, UPDATE ON chars TO vndb_site; GRANT SELECT, INSERT ON chars_hist TO vndb_site; diff --git a/sql/schema.sql b/sql/schema.sql index 6275f816..e055aa51 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -87,6 +87,18 @@ CREATE TABLE anime ( lastfetch timestamptz ); +-- audit_log +CREATE TABLE audit_log ( + date timestamptz NOT NULL DEFAULT NOW(), + by_uid integer, + by_name text, + by_ip inet NOT NULL, + affected_uid integer, + affected_name text, + action text NOT NULL, + detail text +); + -- changes CREATE TABLE changes ( id SERIAL PRIMARY KEY, diff --git a/util/updates/2020-04-27-audit-logging.sql b/util/updates/2020-04-27-audit-logging.sql new file mode 100644 index 00000000..47a99ce9 --- /dev/null +++ b/util/updates/2020-04-27-audit-logging.sql @@ -0,0 +1,11 @@ +CREATE TABLE audit_log ( + date timestamptz NOT NULL DEFAULT NOW(), + by_uid integer, + by_name text, + by_ip inet NOT NULL, + affected_uid integer, + affected_name text, + action text NOT NULL, + detail text +); +\i sql/perms.sql |