summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-10-03 12:40:24 +0200
committerYorhel <git@yorhel.nl>2019-10-03 15:08:56 +0200
commit97bfe01c28df6df181aa3fa6e4e9cecd983efb11 (patch)
tree96ed89d2df7b8927c60e800ca12041d34bda4bfb /util
parent89483d60d5df8346473a50734df4825210c70d3b (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')
-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
4 files changed, 18 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)
);
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