diff options
-rw-r--r-- | lib/Multi/Maintenance.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Auth.pm | 17 | ||||
-rw-r--r-- | util/sql/func.sql | 13 | ||||
-rw-r--r-- | util/sql/perms.sql | 2 | ||||
-rw-r--r-- | util/sql/schema.sql | 8 | ||||
-rw-r--r-- | util/updates/update_20191003b.sql | 8 |
6 files changed, 24 insertions, 26 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 4281ff67..1ef68dbf 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -90,7 +90,7 @@ my %dailies = ( WHERE NOT EXISTS(SELECT 1 FROM vn WHERE rgraph = vg.id) AND NOT EXISTS(SELECT 1 FROM producers WHERE rgraph = vg.id)|, - cleansessions => q|DELETE FROM sessions WHERE lastused < NOW()-'1 month'::interval|, + cleansessions => q|DELETE FROM sessions WHERE expires < NOW()|, cleannotifications => q|DELETE FROM notifications WHERE read < NOW()-'1 month'::interval|, cleannotifications2=> q|DELETE FROM notifications WHERE id IN ( SELECT id FROM (SELECT id, row_number() OVER (PARTITION BY uid ORDER BY id DESC) > 500 from notifications) AS x(id,del) WHERE x.del)|, diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm index 306bb64c..50ad6bc6 100644 --- a/lib/VNWeb/Auth.pm +++ b/lib/VNWeb/Auth.pm @@ -149,18 +149,11 @@ sub _create_session { sub _load_session { my($self, $uid, $token_db) = @_; - my $user = {}; - if($uid) { - my $loggedin = sql_func(user_isloggedin => 'id', sql_fromhex($token_db)); - $user = tuwf->dbRowi( - 'SELECT id, username, perm, ', sql_totime($loggedin), ' AS lastused', - 'FROM users WHERE id = ', \$uid, 'AND', $loggedin, 'IS NOT NULL' - ); - - # update the sessions.lastused column if lastused < now()-'6 hours' - tuwf->dbExeci(SELECT => sql_func user_update_lastused => \$user->{id}, sql_fromhex $token_db) - if $user->{id} && $user->{lastused} < time()-6*3600; - } + my $user = $uid ? tuwf->dbRowi( + 'SELECT id, username, perm FROM users + WHERE id = ', \$uid, + 'AND', sql_func(user_isloggedin => 'id', sql_fromhex($token_db)) + ) : {}; # Drop the cookie if it's not valid tuwf->resCookie(auth => undef) if !$user->{id} && tuwf->reqCookie('auth'); diff --git a/util/sql/func.sql b/util/sql/func.sql index b06cf5e5..1749f09e 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -747,7 +747,7 @@ $$ 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 + INSERT INTO sessions (uid, token, expires) SELECT $1, $3, NOW() + '1 month' FROM users WHERE length($2) = 46 AND length($3) = 20 AND id = $1 AND passwd = $2 RETURNING true @@ -759,13 +759,10 @@ CREATE OR REPLACE FUNCTION user_logout(integer, bytea) RETURNS void AS $$ $$ 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 +-- 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(); $$ LANGUAGE SQL SECURITY DEFINER; diff --git a/util/sql/perms.sql b/util/sql/perms.sql index f49686fc..e3e0e3b8 100644 --- a/util/sql/perms.sql +++ b/util/sql/perms.sql @@ -121,7 +121,7 @@ GRANT SELECT ON releases_vn TO vndb_multi; GRANT SELECT, INSERT, UPDATE, DELETE ON relgraphs TO vndb_multi; GRANT SELECT, INSERT, UPDATE, DELETE ON rlists TO vndb_multi; GRANT SELECT ON screenshots TO vndb_multi; -GRANT SELECT (lastused) ON sessions TO vndb_multi; +GRANT SELECT (expires) ON sessions TO vndb_multi; GRANT DELETE ON sessions TO vndb_multi; GRANT SELECT, INSERT, UPDATE, DELETE ON shop_denpa TO vndb_multi; GRANT SELECT, INSERT, UPDATE, DELETE ON shop_dlsite TO vndb_multi; diff --git a/util/sql/schema.sql b/util/sql/schema.sql index aa64289a..fa43a5c0 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -450,10 +450,10 @@ CREATE TABLE screenshots ( -- sessions CREATE TABLE sessions ( - uid integer NOT NULL, - token bytea NOT NULL, - added timestamptz NOT NULL DEFAULT NOW(), - lastused timestamptz NOT NULL DEFAULT NOW(), + uid integer NOT NULL, + token bytea NOT NULL, + added timestamptz NOT NULL DEFAULT NOW(), + expires timestamptz NOT NULL, PRIMARY KEY (uid, token) ); diff --git a/util/updates/update_20191003b.sql b/util/updates/update_20191003b.sql new file mode 100644 index 00000000..c70ef9b8 --- /dev/null +++ b/util/updates/update_20191003b.sql @@ -0,0 +1,8 @@ +ALTER TABLE sessions RENAME COLUMN lastused TO expires; +UPDATE sessions SET expires = expires + '1 month'::interval; +ALTER TABLE sessions ALTER COLUMN expires DROP DEFAULT; + +DROP FUNCTION user_isloggedin(integer, bytea); +DROP FUNCTION user_update_lastused(integer, bytea); + +\i util/sql/func.sql |