summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/VNWeb/Auth.pm9
-rw-r--r--util/sql/func.sql43
-rw-r--r--util/sql/schema.sql8
-rw-r--r--util/updates/update_20191003b.sql12
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