summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
Diffstat (limited to 'util')
-rw-r--r--util/sql/func.sql30
-rw-r--r--util/sql/schema.sql3
-rw-r--r--util/updates/update_20191003b.sql4
3 files changed, 29 insertions, 8 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 51dc2aac..0ffe902d 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -781,10 +781,10 @@ $$ LANGUAGE SQL SECURITY DEFINER;
-- 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 $$
- 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
+ INSERT INTO sessions (uid, token, expires, type)
+ SELECT id, $2, NOW()+'1 week', 'pass' FROM users
+ WHERE lower(mail) = lower($1) AND length($2) = 20 AND perm & 128 = 0
+ RETURNING uid
$$ LANGUAGE SQL SECURITY DEFINER;
@@ -822,8 +822,21 @@ CREATE OR REPLACE FUNCTION user_getmail(integer, integer, bytea) RETURNS text AS
$$ LANGUAGE SQL SECURITY DEFINER;
-CREATE OR REPLACE FUNCTION user_setmail(integer, integer, bytea, text) RETURNS void AS $$
- UPDATE users SET mail = $4 WHERE id = $1 AND user_isauth($1, $2, $3)
+-- Set a token to change a user's email address.
+-- Args: uid, web-token, new-email-token, email
+CREATE OR REPLACE FUNCTION user_setmail_token(integer, bytea, bytea, text) RETURNS void AS $$
+ INSERT INTO sessions (uid, token, expires, type, mail)
+ SELECT id, $3, NOW()+'1 week', 'mail', $4 FROM users
+ WHERE id = $1 AND user_isauth($1, $1, $2) AND length($3) = 20
+$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+-- Actually change a user's email address, given a valid token.
+CREATE OR REPLACE FUNCTION user_setmail_confirm(integer, bytea) RETURNS boolean AS $$
+ WITH u(mail) AS (
+ DELETE FROM sessions WHERE uid = $1 AND token = $2 AND type = 'mail' AND expires > NOW() RETURNING mail
+ )
+ UPDATE users SET mail = (SELECT mail FROM u) WHERE id = $1 AND EXISTS(SELECT 1 FROM u) RETURNING true;
$$ LANGUAGE SQL SECURITY DEFINER;
@@ -838,3 +851,8 @@ CREATE OR REPLACE FUNCTION user_admin_setpass(integer, integer, bytea, bytea) RE
)
DELETE FROM sessions WHERE uid IN(SELECT id FROM upd)
$$ LANGUAGE SQL SECURITY DEFINER;
+
+
+CREATE OR REPLACE FUNCTION user_admin_setmail(integer, integer, bytea, text) RETURNS void AS $$
+ UPDATE users SET mail = $4 WHERE id = $1 AND user_isauth(-1, $2, $3)
+$$ LANGUAGE SQL SECURITY DEFINER;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 57136b0f..46bf3d27 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -65,7 +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');
+CREATE TYPE session_type AS ENUM ('web', 'pass', 'mail');
-- Sequences used for ID generation of items not in the DB
CREATE SEQUENCE covers_seq;
@@ -456,6 +456,7 @@ CREATE TABLE sessions (
added timestamptz NOT NULL DEFAULT NOW(),
expires timestamptz NOT NULL,
type session_type NOT NULL,
+ mail text,
PRIMARY KEY (uid, token)
);
diff --git a/util/updates/update_20191003b.sql b/util/updates/update_20191003b.sql
index 11b1a5d9..a7342463 100644
--- a/util/updates/update_20191003b.sql
+++ b/util/updates/update_20191003b.sql
@@ -4,13 +4,15 @@ 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');
+CREATE TYPE session_type AS ENUM ('web', 'pass', 'mail');
ALTER TABLE sessions ADD COLUMN type session_type NOT NULL DEFAULT 'web';
ALTER TABLE sessions ALTER COLUMN type DROP DEFAULT;
+ALTER TABLE sessions ADD COLUMN mail text;
DROP FUNCTION user_isloggedin(integer, bytea);
DROP FUNCTION user_update_lastused(integer, bytea);
DROP FUNCTION user_isvalidtoken(integer, bytea);
+DROP FUNCTION user_setmail(integer, integer, bytea, text);
-- Convert old password reset tokens to the new session format
INSERT INTO sessions (uid, token, expires, type)