SQL: Use separate role for the website + disallow access to user data

Previously the website was connected to the database with a "database
owner" user, which has far too many permissions. Now there's a special
vndb_site user with only the necessary permissions. The primary
reason to do this is to decrease the impact if the site process is
compromised. E.g. it's now no longer possible to delete or modify old
entry revisions. An attacker can still do a lot of damage, however.

Additionally (and this was the main reason to implement this change in
the first place), the user sessions, passwords and email data is now not
easily accessible anymore. Hopefully, the new user management
abstractions will prevent email and password dumps in case of an SQL
injection or RCE vulnerability in the site code. Of course, this only
works if my implementation is fully correct and there's no privilige
escalation vulnerability somewhere.

Furthermore, changing your password now invalidates any existing
sessions, and the password reset function is disabled for 'usermods'
(because usermods can list email addresses from the database, and the
password reset function could still allow an attacker to gain access to
anyone's account).

I also changed the format of the password reset tokens, as they totally
don't need to be salted.

1 files changed, 80 insertions, 0 deletions

diff --git a/util/sql/perms.sql b/util/sql/perms.sql
new file mode 100644
index 00000000..fcfe47a1
--- /dev/null
+++ b/util/sql/perms.sql
@@ -0,0 +1,80 @@
+-- vndb_site
+
+GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_site;
+GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_site;
+GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO vndb_site;
+
+GRANT SELECT, INSERT, UPDATE, DELETE ON affiliate_links          TO vndb_site;
+GRANT SELECT, INSERT                 ON anime                    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;
+GRANT SELECT, INSERT,         DELETE ON chars_traits             TO vndb_site;
+GRANT SELECT, INSERT                 ON chars_traits_hist        TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON chars_vns                TO vndb_site;
+GRANT SELECT, INSERT                 ON chars_vns_hist           TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle           TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON notifications            TO vndb_site;
+GRANT SELECT, INSERT, UPDATE         ON producers                TO vndb_site;
+GRANT SELECT, INSERT                 ON producers_hist           TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON producers_relations      TO vndb_site;
+GRANT SELECT, INSERT                 ON producers_relations_hist TO vndb_site;
+GRANT SELECT                         ON quotes                   TO vndb_site;
+GRANT SELECT, INSERT, UPDATE         ON releases                 TO vndb_site;
+GRANT SELECT, INSERT                 ON releases_hist            TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON releases_lang            TO vndb_site;
+GRANT SELECT, INSERT                 ON releases_lang_hist       TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON releases_media           TO vndb_site;
+GRANT SELECT, INSERT                 ON releases_media_hist      TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON releases_platforms       TO vndb_site;
+GRANT SELECT, INSERT                 ON releases_platforms_hist  TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON releases_producers       TO vndb_site;
+GRANT SELECT, INSERT                 ON releases_producers_hist  TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON releases_vn              TO vndb_site;
+GRANT SELECT, INSERT                 ON releases_vn_hist         TO vndb_site;
+GRANT SELECT                         ON relgraphs                TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON rlists                   TO vndb_site;
+GRANT SELECT, INSERT, UPDATE         ON screenshots              TO vndb_site;
+-- No access to the 'sessions' table, managed by the user_* functions.
+GRANT SELECT, INSERT, UPDATE         ON staff                    TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON staff_alias              TO vndb_site;
+GRANT SELECT, INSERT                 ON staff_alias_hist         TO vndb_site;
+GRANT SELECT, INSERT                 ON staff_hist               TO vndb_site;
+GRANT SELECT, UPDATE                 ON stats_cache              TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tags                     TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tags_aliases             TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tags_parents             TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tags_vn                  TO vndb_site;
+GRANT SELECT                         ON tags_vn_inherit          TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON threads                  TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON threads_boards           TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON threads_poll_options     TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON threads_poll_votes       TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON threads_posts            TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON traits                   TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON traits_chars             TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON traits_parents           TO vndb_site;
+
+-- users table is special; The 'perm', 'passwd' and 'mail' columns are
+-- protected and can only be accessed through the user_* functions.
+GRANT SELECT (id, username,       registered, perm, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed),
+      INSERT (id, username, mail, registered,       c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed),
+      UPDATE (id, username,       registered,       c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed) ON users TO vndb_site;
+GRANT DELETE ON users TO vndb_site;
+
+GRANT SELECT, INSERT, UPDATE, DELETE ON users_prefs              TO vndb_site;
+GRANT SELECT, INSERT, UPDATE         ON vn                       TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON vn_anime                 TO vndb_site;
+GRANT SELECT, INSERT                 ON vn_anime_hist            TO vndb_site;
+GRANT SELECT, INSERT                 ON vn_hist                  TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON vn_relations             TO vndb_site;
+GRANT SELECT, INSERT                 ON vn_relations_hist        TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON vn_screenshots           TO vndb_site;
+GRANT SELECT, INSERT                 ON vn_screenshots_hist      TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON vn_seiyuu                TO vndb_site;
+GRANT SELECT, INSERT                 ON vn_seiyuu_hist           TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON vn_staff                 TO vndb_site;
+GRANT SELECT, INSERT                 ON vn_staff_hist            TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON vnlists                  TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON votes                    TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON wlists                   TO vndb_site;