Add minimal audit logging for user-related changes

Includes failed logins (but not through the API...), password changes,
email changes, any user-related changes performed by a moderator and
post edits/deletions performed by a moderator.

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