SQL: Separate sensitive columns out of the users table

This simplifies permissions management somewhat.

4 files changed, 7 insertions, 9 deletions

diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm
index 08ec4dad..a6c4d852 100644
--- a/lib/VNWeb/Auth.pm
+++ b/lib/VNWeb/Auth.pm
@@ -146,8 +146,9 @@ sub _load_session {
     my $user = $uid ? tuwf->dbRowi(
         'SELECT ', sql_user(), ',', sql_comma(@pref_columns, map "perm_$_", @perms), '
            FROM users u
-          WHERE id = ', \$uid,
-           'AND', sql_func(user_isvalidsession => 'id', sql_fromhex($token_db), \'web')
+           JOIN users_shadow us ON us.id = u.id
+          WHERE u.id = ', \$uid,
+           'AND', sql_func(user_isvalidsession => 'u.id', sql_fromhex($token_db), \'web')
     ) : {};
 
     # Drop the cookie if it's not valid
diff --git a/lib/VNWeb/Docs/Lib.pm b/lib/VNWeb/Docs/Lib.pm
index 2f2b273c..bea36241 100644
--- a/lib/VNWeb/Docs/Lib.pm
+++ b/lib/VNWeb/Docs/Lib.pm
@@ -11,7 +11,7 @@ my @special_perms = qw/boardmod dbmod usermod imgmod tagmod/;
 sub _moderators {
     my $cols = sql_comma map "perm_$_", @special_perms;
     my $where = sql_or map "perm_$_", @special_perms;
-    my $l = tuwf->dbAlli("SELECT id, username, $cols FROM users WHERE $where ORDER BY id LIMIT 100");
+    state $l //= tuwf->dbAlli("SELECT u.id, username, $cols FROM users u JOIN users_shadow us ON us.id = u.id WHERE $where ORDER BY u.id LIMIT 100");
 
     xml_string sub {
         dl_ sub {
diff --git a/lib/VNWeb/User/Edit.pm b/lib/VNWeb/User/Edit.pm
index 54bce482..7406b8af 100644
--- a/lib/VNWeb/User/Edit.pm
+++ b/lib/VNWeb/User/Edit.pm
@@ -89,7 +89,7 @@ TUWF::get qr{/$RE{uid}/edit}, sub {
     $u->{prefs}{skin} ||= config->{skin_default} if $u->{prefs};
 
     $u->{admin} = auth->permDbmod || auth->permUsermod || auth->permTagmod || auth->permBoardmod || auth->permImgmod ?
-        tuwf->dbRowi('SELECT ign_votes, ', sql_comma(map "perm_$_", auth->listPerms), 'FROM users WHERE id =', \$u->{id}) : undef;
+        tuwf->dbRowi('SELECT ign_votes, ', sql_comma(map "perm_$_", auth->listPerms), 'FROM users u JOIN users_shadow us ON us.id = u.id WHERE u.id =', \$u->{id}) : undef;
 
     $u->{password} = undef;
 
diff --git a/lib/VNWeb/User/Register.pm b/lib/VNWeb/User/Register.pm
index 89e34846..5cd28924 100644
--- a/lib/VNWeb/User/Register.pm
+++ b/lib/VNWeb/User/Register.pm
@@ -29,11 +29,8 @@ elm_api UserRegister => undef, {
         $ip =~ /:/ ? \"$ip/48" : \"$ip/30"
     );
 
-    my $id = tuwf->dbVali('INSERT INTO users', {
-        username => $data->{username},
-        mail     => $data->{email},
-        ip       => $ip,
-    }, 'RETURNING id');
+    my $id = tuwf->dbVali('INSERT INTO users', {username => $data->{username}, ip => $ip}, 'RETURNING id');
+    tuwf->dbExeci('INSERT INTO users_shadow', {id => $id, mail => $data->{email}});
     my(undef, $token) = auth->resetpass($data->{email});
 
     my $body = sprintf