diff options
-rw-r--r-- | lib/VNWeb/Auth.pm | 9 | ||||
-rw-r--r-- | util/sql/func.sql | 43 | ||||
-rw-r--r-- | util/sql/schema.sql | 8 | ||||
-rw-r--r-- | util/updates/update_20191003b.sql | 12 |
4 files changed, 45 insertions, 27 deletions
diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm index 50ad6bc6..0b43074a 100644 --- a/lib/VNWeb/Auth.pm +++ b/lib/VNWeb/Auth.pm @@ -152,7 +152,7 @@ sub _load_session { my $user = $uid ? tuwf->dbRowi( 'SELECT id, username, perm FROM users WHERE id = ', \$uid, - 'AND', sql_func(user_isloggedin => 'id', sql_fromhex($token_db)) + 'AND', sql_func(user_isvalidsession => 'id', sql_fromhex($token_db), \'web') ) : {}; # Drop the cookie if it's not valid @@ -198,9 +198,8 @@ sub logout { } -# Replaces the user's password with a random token that can be used to reset -# the password. Returns ($uid, $token) if the email address is found in the DB, -# () otherwise. +# Create a random token that can be used to reset the password. +# Returns ($uid, $token) if the email address is found in the DB, () otherwise. sub resetpass { my(undef, $mail) = @_; my $token = unpack 'H*', urandom(20); @@ -215,7 +214,7 @@ sub resetpass { sub isvalidtoken { my(undef, $uid, $token) = @_; tuwf->dbVali( - select => sql_func(user_isvalidtoken => \$uid, sql_fromhex sha1_hex lc $token) + select => sql_func(user_isvalidsession => \$uid, sql_fromhex(sha1_hex lc $token), \'pass') ); } diff --git a/util/sql/func.sql b/util/sql/func.sql index 1749f09e..51dc2aac 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -745,9 +745,9 @@ CREATE OR REPLACE FUNCTION user_getscryptargs(integer) RETURNS bytea AS $$ $$ LANGUAGE SQL SECURITY DEFINER; --- Create a new session for this user (uid, scryptpass, token) +-- Create a new web session for this user (uid, scryptpass, token) CREATE OR REPLACE FUNCTION user_login(integer, bytea, bytea) RETURNS boolean AS $$ - INSERT INTO sessions (uid, token, expires) SELECT $1, $3, NOW() + '1 month' FROM users + INSERT INTO sessions (uid, token, expires, type) SELECT $1, $3, NOW() + '1 month', 'web' FROM users WHERE length($2) = 46 AND length($3) = 20 AND id = $1 AND passwd = $2 RETURNING true @@ -755,14 +755,17 @@ $$ LANGUAGE SQL SECURITY DEFINER; CREATE OR REPLACE FUNCTION user_logout(integer, bytea) RETURNS void AS $$ - DELETE FROM sessions WHERE uid = $1 AND token = $2 + DELETE FROM sessions WHERE uid = $1 AND token = $2 AND type = 'web' $$ LANGUAGE SQL SECURITY DEFINER; --- Returns true if the given session token is valid and extends the session expiration. -CREATE OR REPLACE FUNCTION user_isloggedin(integer, bytea) RETURNS bool AS $$ - UPDATE sessions SET expires = NOW() + '1 month' WHERE uid = $1 AND token = $2 AND expires < NOW() + '1 month'::interval - '6 hours'::interval; - SELECT true FROM sessions WHERE uid = $1 AND token = $2 AND expires > NOW(); +-- Returns true if the given session token is valid. +-- As a side effect, this also extends the expiration time of web sessions. +CREATE OR REPLACE FUNCTION user_isvalidsession(integer, bytea, session_type) RETURNS bool AS $$ + UPDATE sessions SET expires = NOW() + '1 month' + WHERE uid = $1 AND token = $2 AND type = $3 AND $3 = 'web' + AND expires < NOW() + '1 month'::interval - '6 hours'::interval; + SELECT true FROM sessions WHERE uid = $1 AND token = $2 AND type = $3 AND expires > NOW(); $$ LANGUAGE SQL SECURITY DEFINER; @@ -771,26 +774,30 @@ CREATE OR REPLACE FUNCTION user_emailexists(text) RETURNS boolean AS $$ $$ 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. +-- Create a password 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; + WITH uid(uid) AS ( + SELECT id FROM users WHERE lower(mail) = lower($1) AND length($2) = 20 AND perm & 128 = 0 + ) + INSERT INTO sessions (uid, token, expires, type) SELECT uid, $2, NOW()+'1 week', 'pass' FROM uid RETURNING uid $$ LANGUAGE SQL SECURITY DEFINER; --- Changes the user's password and invalidates all existing sessions. args: uid, old_pass_or_token, new_pass +-- Changes the user's password and invalidates all existing sessions. args: uid, old_pass_or_reset_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 + UPDATE users SET passwd = $3 + WHERE id = $1 + AND length($3) = 46 + AND ( (passwd = $2 AND length($2) = 46) + OR EXISTS(SELECT 1 FROM sessions WHERE uid = $1 AND token = $2 AND type = 'pass' AND expires > NOW()) + ) + RETURNING id ), del AS( -- Not referenced, but still guaranteed to run DELETE FROM sessions WHERE uid IN(SELECT id FROM upd) ) @@ -799,11 +806,11 @@ $$ LANGUAGE SQL SECURITY DEFINER; -- Internal function, used to verify whether user ($2 with session $3) is --- allowed to access data from user $1. +-- allowed to access sensitive 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 EXISTS(SELECT 1 FROM sessions WHERE uid = $2 AND token = $3 AND type = 'web') AND ($2 = $1 OR perm & 128 = 128) $$ LANGUAGE SQL; diff --git a/util/sql/schema.sql b/util/sql/schema.sql index fa43a5c0..57136b0f 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -65,6 +65,7 @@ CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); CREATE TYPE tag_category AS ENUM('cont', 'ero', 'tech'); CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); CREATE TYPE resolution AS ENUM ('unknown', 'nonstandard', '640x480', '800x600', '1024x768', '1280x960', '1600x1200', '640x400', '960x600', '960x640', '1024x576', '1024x600', '1024x640', '1280x720', '1280x800', '1366x768', '1600x900', '1920x1080'); +CREATE TYPE session_type AS ENUM ('web', 'pass'); -- Sequences used for ID generation of items not in the DB CREATE SEQUENCE covers_seq; @@ -454,6 +455,7 @@ CREATE TABLE sessions ( token bytea NOT NULL, added timestamptz NOT NULL DEFAULT NOW(), expires timestamptz NOT NULL, + type session_type NOT NULL, PRIMARY KEY (uid, token) ); @@ -703,15 +705,13 @@ CREATE TABLE users ( username varchar(20) NOT NULL UNIQUE, -- [pub] mail varchar(100) NOT NULL, perm smallint NOT NULL DEFAULT 1+4+16, - -- Interpretation of the passwd column depends on its length: - -- * 20 bytes: Password reset token (sha1(lower_hex(20 bytes of random data))) - -- * 46 bytes: scrypt password + -- A valid passwd column is 46 bytes: -- 4 bytes: N (big endian) -- 1 byte: r -- 1 byte: p -- 8 bytes: salt -- 32 bytes: scrypt(passwd, global_salt + salt, N, r, p, 32) - -- * Anything else: Invalid, account disabled. + -- Anything else is invalid, account disabled. passwd bytea NOT NULL DEFAULT '', registered timestamptz NOT NULL DEFAULT NOW(), c_votes integer NOT NULL DEFAULT 0, diff --git a/util/updates/update_20191003b.sql b/util/updates/update_20191003b.sql index c70ef9b8..11b1a5d9 100644 --- a/util/updates/update_20191003b.sql +++ b/util/updates/update_20191003b.sql @@ -1,8 +1,20 @@ +-- lastused -> expires ALTER TABLE sessions RENAME COLUMN lastused TO expires; UPDATE sessions SET expires = expires + '1 month'::interval; ALTER TABLE sessions ALTER COLUMN expires DROP DEFAULT; +-- Support different session types +CREATE TYPE session_type AS ENUM ('web', 'pass'); +ALTER TABLE sessions ADD COLUMN type session_type NOT NULL DEFAULT 'web'; +ALTER TABLE sessions ALTER COLUMN type DROP DEFAULT; + DROP FUNCTION user_isloggedin(integer, bytea); DROP FUNCTION user_update_lastused(integer, bytea); +DROP FUNCTION user_isvalidtoken(integer, bytea); + +-- Convert old password reset tokens to the new session format +INSERT INTO sessions (uid, token, expires, type) + SELECT id, passwd, NOW() + '1 week', 'pass' FROM users WHERE length(passwd) = 20; +UPDATE users SET passwd = '' WHERE length(passwd) = 20; \i util/sql/func.sql |