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.

5 files changed, 206 insertions, 4 deletions

diff --git a/util/sql/all.sql b/util/sql/all.sql
index 2c47f63b..12852221 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -45,6 +45,10 @@ CREATE TYPE vn_relation       AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid
 
 \i util/sql/triggers.sql
 
+-- permissions
+
+\i util/sql/perms.sql
+
 -- Sequences used for ID generation of items not in the DB
 CREATE SEQUENCE covers_seq;
 CREATE SEQUENCE charimg_seq;
diff --git a/util/sql/func.sql b/util/sql/func.sql
index ec109692..20fc71c4 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -2,6 +2,7 @@
 --   edit_*      -> revision insertion abstraction functions
 --   *_notify    -> functions issuing a PgSQL NOTIFY statement
 --   notify_*    -> functions creating entries in the notifications table
+--   user_*      -> functions to manage users and sessions
 --   update_*    -> functions to update a cache
 --   *_update    ^  (I should probably rename these to
 --   *_calc      ^   the update_* scheme for consistency)
@@ -687,3 +688,110 @@ END;
 $$ LANGUAGE plpgsql;
 
 
+
+
+----------------------------------------------------------
+--                    user management                   --
+----------------------------------------------------------
+-- XXX: These functions run with the permissions of the 'vndb' user.
+
+
+-- Returns the raw scrypt parameters (N, r, p and salt) for this user, in order
+-- to create an encrypted pass. Returns NULL if this user does not have a valid
+-- password.
+CREATE OR REPLACE FUNCTION user_getscryptargs(integer) RETURNS bytea AS $$
+  SELECT
+    CASE WHEN length(passwd) = 46 THEN substring(passwd from 1 for 14) ELSE NULL END
+  FROM users WHERE id = $1
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+-- Create a new session for this user (uid, scryptpass, token)
+CREATE OR REPLACE FUNCTION user_login(integer, bytea, bytea) RETURNS boolean AS $$
+  INSERT INTO sessions (uid, token) SELECT $1, $3 FROM users
+   WHERE length($2) = 46 AND length($3) = 20
+     AND id = $1 AND passwd = $2
+  RETURNING true
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+CREATE OR REPLACE FUNCTION user_logout(integer, bytea) RETURNS void AS $$
+  DELETE FROM sessions WHERE uid = $1 AND token = $2
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+CREATE OR REPLACE FUNCTION user_update_lastused(integer, bytea) RETURNS void AS $$
+  UPDATE sessions SET lastused = NOW() WHERE uid = $1 AND token = $2
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+CREATE OR REPLACE FUNCTION user_isloggedin(integer, bytea) RETURNS timestamptz AS $$
+  SELECT lastused FROM sessions WHERE uid = $1 AND token = $2
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+CREATE OR REPLACE FUNCTION user_emailexists(text) RETURNS boolean AS $$
+  SELECT true FROM users WHERE lower(mail) = lower($1) LIMIT 1
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+CREATE OR REPLACE FUNCTION user_isvalidtoken(integer, bytea) RETURNS boolean AS $$
+  SELECT true FROM users WHERE id = $1 AND passwd = $2 AND length($2) = 20
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+-- Replace password with a reset token. args: email, token. Returns: user id.
+-- Doesn't work for usermods, otherwise an attacker could use this function to
+-- gain access to all user's emails by obtaining a reset token of a usermod.
+-- Ideally Postgres itself would send the user an email so that the application
+-- calling this function doesn't even get the token, and thus can't get access
+-- to someone's account. But alas, that'd require a separate process.
+CREATE OR REPLACE FUNCTION user_resetpass(text, bytea) RETURNS integer AS $$
+  UPDATE users SET passwd = $2 WHERE lower(mail) = lower($1) AND length($2) = 20 AND perm & 128 = 0 RETURNING id;
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+-- Changes the user's password and invalidates all existing sessions. args: uid, old_pass_or_token, new_pass
+CREATE OR REPLACE FUNCTION user_setpass(integer, bytea, bytea) RETURNS boolean AS $$
+  WITH upd(id) AS (
+    UPDATE users SET passwd = $3 WHERE id = $1 AND passwd = $2 AND length($2) IN(20,46) AND length($3) = 46 RETURNING id
+  ), del AS( -- Not referenced, but still guaranteed to run
+    DELETE FROM sessions WHERE uid IN(SELECT id FROM upd)
+  )
+  SELECT true FROM upd
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+-- Internal function, used to verify whether user ($2 with session $3) is
+-- allowed to access data from user $1.
+CREATE OR REPLACE FUNCTION user_isauth(integer, integer, bytea) RETURNS boolean AS $$
+  SELECT true FROM users
+   WHERE id = $2
+     AND EXISTS(SELECT 1 FROM sessions WHERE uid = $2 AND token = $3)
+     AND ($2 = $1 OR perm & 128 = 128)
+$$ LANGUAGE SQL;
+
+
+-- uid of user email to get, uid currently logged in, session token of currently logged in.
+-- Ensures that only the user itself or a useradmin can get someone's email address.
+CREATE OR REPLACE FUNCTION user_getmail(integer, integer, bytea) RETURNS text AS $$
+  SELECT mail FROM users WHERE id = $1 AND user_isauth($1, $2, $3)
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+CREATE OR REPLACE FUNCTION user_setmail(integer, integer, bytea, text) RETURNS void AS $$
+  UPDATE users SET mail = $4 WHERE id = $1 AND user_isauth($1, $2, $3)
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+CREATE OR REPLACE FUNCTION user_setperm(integer, integer, bytea, integer) RETURNS void AS $$
+  UPDATE users SET perm = $4 WHERE id = $1 AND user_isauth(-1, $2, $3)
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+CREATE OR REPLACE FUNCTION user_admin_setpass(integer, integer, bytea, bytea) RETURNS void AS $$
+  WITH upd(id) AS (
+    UPDATE users SET passwd = $4 WHERE id = $1 AND user_isauth(-1, $2, $3) AND length($4) = 46 RETURNING id
+  )
+  DELETE FROM sessions WHERE uid IN(SELECT id FROM upd)
+$$ LANGUAGE SQL SECURITY DEFINER;
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;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index b641c556..ca234d49 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -569,10 +569,7 @@ CREATE TABLE users (
   mail varchar(100) NOT NULL,
   perm smallint NOT NULL DEFAULT 1+4+16,
   -- Interpretation of the passwd column depends on its length:
-  -- * 29 bytes: Password reset token
-  --   First 9 bytes: salt (ASCII)
-  --   Latter 20 bytes: sha1(hex(token) + salt)
-  --   'token' is a sha1 digest obtained from random data.
+  -- * 20 bytes: Password reset token (sha1(lower_hex(20 bytes of random data)))
   -- * 46 bytes: scrypt password
   --   4 bytes: N (big endian)
   --   1 byte: r
diff --git a/util/sql/superuser_init.sql b/util/sql/superuser_init.sql
new file mode 100644
index 00000000..145ff30e
--- /dev/null
+++ b/util/sql/superuser_init.sql
@@ -0,0 +1,13 @@
+-- This script should be run before all other scripts and as a PostgreSQL
+-- superuser It will create the VNDB database and required users.
+-- All other SQL scripts should be run by the 'vndb' user.
+
+-- In order to "activate" a user, i.e. to allow login, you need to manually run
+-- the following for each user you want to activate:
+--   ALTER ROLE rolename LOGIN UNENCRYPTED PASSWORD 'password';
+
+CREATE ROLE vndb;
+CREATE DATABASE vndb OWNER vndb;
+
+-- The website
+CREATE ROLE vndb_site;