diff options
author | Yorhel <git@yorhel.nl> | 2019-10-03 12:40:24 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-10-03 15:08:56 +0200 |
commit | 97bfe01c28df6df181aa3fa6e4e9cecd983efb11 (patch) | |
tree | 96ed89d2df7b8927c60e800ca12041d34bda4bfb /util/sql | |
parent | 89483d60d5df8346473a50734df4825210c70d3b (diff) |
SQL: Change sessions.lastused into sessions.expires
This will be helpful when adding other types of sessions with different
expiration.
Diffstat (limited to 'util/sql')
-rw-r--r-- | util/sql/func.sql | 13 | ||||
-rw-r--r-- | util/sql/perms.sql | 2 | ||||
-rw-r--r-- | util/sql/schema.sql | 8 |
3 files changed, 10 insertions, 13 deletions
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) ); |