summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/Multi/Maintenance.pm2
-rw-r--r--lib/VNWeb/Auth.pm17
-rw-r--r--util/sql/func.sql13
-rw-r--r--util/sql/perms.sql2
-rw-r--r--util/sql/schema.sql8
-rw-r--r--util/updates/update_20191003b.sql8
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