diff options
author | Yorhel <git@yorhel.nl> | 2019-10-03 17:33:33 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-10-03 17:33:35 +0200 |
commit | 3f7769d2ba4047e8766e511b7a42c7aa4721f6f8 (patch) | |
tree | fb3263931645793e280f30e1bce0271281b496b6 /util/sql | |
parent | 0965af5fd74cf53503bb7ae0262d31c25f894385 (diff) |
Require email confirmation when changing email address
This ensures that the email address linked to a user is always valid and
actually belong(s|ed) to that user.
Diffstat (limited to 'util/sql')
-rw-r--r-- | util/sql/func.sql | 30 | ||||
-rw-r--r-- | util/sql/schema.sql | 3 |
2 files changed, 26 insertions, 7 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) ); |