From cedf213b704fa2c49b034da39333966aac9dc1cb Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 21 Jul 2015 18:05:36 -0400 Subject: [PATCH] LP#1468422 storing more algo bits; big nod toward non-main passwords Store the iter_count and start using the crypt_algo column. Make it possible to change the salt, and potentially strengthen the salt, when changing passwords. Make is possible to start salt-less passwords, for pw's that are managed outside of the DB. Signed-off-by: Bill Erickson --- .../Pg/upgrade/XXXX.schema.password-storage.sql | 170 ++++++++++++++++----- 1 file changed, 134 insertions(+), 36 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.password-storage.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.password-storage.sql index c4d4f70a76..844fa59391 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.password-storage.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.password-storage.sql @@ -8,28 +8,101 @@ CREATE TABLE actor.passwd_type ( name TEXT UNIQUE NOT NULL, login BOOLEAN NOT NULL DEFAULT FALSE, regex TEXT, -- pending - crypt_algo TEXT -- pending + crypt_algo TEXT, -- pending + -- gen_salt iter count; used with each new salt. + -- existing salts encode their iter count directly. + -- A value for iter_count tells us the password is + -- salted and it's our job to generate/manage the salt. + iter_count INTEGER CHECK (iter_count IS NULL OR iter_count > 0) ); CREATE TABLE actor.passwd ( id SERIAL PRIMARY KEY, usr INTEGER NOT NULL REFERENCES actor.usr(id) - ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - salt TEXT NOT NULL, + ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + salt TEXT, passwd TEXT NOT NULL, passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code) - ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - CONSTRAINT passwd_type_once_per_user UNIQUE (usr, passwd_type) + ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + CONSTRAINT passwd_type_once_per_user UNIQUE (usr, passwd_type) ); +-- Returns a new salt based on the current passwd_type encryption settings +CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT) + RETURNS TEXT AS $$ +DECLARE + type_row actor.passwd_type%ROWTYPE; +BEGIN + SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type; + + IF NOT FOUND THEN + RETURN EXCEPTION 'No such password type: %', pw_type; + END IF; + + IF type_row.iter_count IS NULL THEN + -- This password type is unsalted. That's OK. + RETURN NULL; + END IF; + + RETURN gen_salt(type_row.crypt_algo, type_row.iter_count); +END; +$$ LANGUAGE PLPGSQL; + + +-- Sets the password value, creating the password row if needed. +-- new_pass is MD5(MD5(real-password) || salt) for 'main' passwords. +CREATE OR REPLACE FUNCTION actor.set_passwd( + pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL) + RETURNS BOOLEAN AS $$ +DECLARE + pw_salt TEXT; + pw_text TEXT; +BEGIN + + IF new_salt IS NOT NULL THEN + pw_salt := new_salt; + ELSE + pw_salt := actor.get_salt(pw_usr, pw_type); + + IF pw_salt IS NULL THEN + -- We have no salt for this user + type. + -- Assume they want a new salt. If this type + -- is unsalted, create_salt() will return NULL. + pw_salt := actor.create_salt(pw_type); + END IF; + END IF; + + IF pw_salt IS NULL THEN + pw_text := new_pass; -- unsalted, use as-is. + ELSE + pw_text := CRYPT(new_pass, pw_salt); + END IF; + + UPDATE actor.passwd + SET passwd = pw_text, salt = pw_salt, edit_date = NOW() + WHERE usr = pw_usr AND passwd_type = pw_type; + + IF NOT FOUND THEN + -- no password row exists for this user + type. Create one. + INSERT INTO actor.passwd (usr, passwd_type, salt, passwd) + VALUES (pw_usr, pw_type, pw_salt, pw_text); + END IF; + + RETURN TRUE; +END; +$$ LANGUAGE PLPGSQL; + -- actor.passwd is not IDL-accessible. This function gives the -- application access to the salt. If the password type of "main" -- is requested and no password exists in actor.passwd, the user's -- existing password is migrated and the new salt is returned. -CREATE OR REPLACE FUNCTION actor.passwd_salt(pw_usr INTEGER, pw_type TEXT) +CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT) RETURNS TEXT AS $$ DECLARE pw_salt TEXT; + type_row actor.passwd_type%ROWTYPE; BEGIN SELECT INTO pw_salt salt FROM actor.passwd @@ -40,72 +113,80 @@ BEGIN END IF; IF pw_type = 'main' THEN + -- Main password has not yet been migrated. + -- Do it now and return the newly created salt. RETURN actor.migrate_passwd(pw_usr); END IF; - -- Only 'main' passwords are auto-migrated. + -- We have no salt to return. actor.create_salt() needed. RETURN NULL; - END; $$ LANGUAGE PLPGSQL; -- Migrates actor.usr.passwd to actor.passwd with --- password type 'main' for every user. Returns the salt. +-- password type 'main' and returns the salt. CREATE OR REPLACE FUNCTION actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$ DECLARE pw_salt TEXT; - usr_obj actor.usr%ROWTYPE; + usr_row actor.usr%ROWTYPE; BEGIN + SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr; - SELECT INTO usr_obj * FROM actor.usr WHERE id = pw_usr; - pw_salt := gen_salt('bf', 14); -- TODO: configurable? + pw_salt := actor.create_salt('main'); - INSERT INTO actor.passwd (usr, passwd_type, salt, passwd) - VALUES ( - usr_obj.id, - 'main', -- only 'main' is migrated - pw_salt, - CRYPT(MD5(usr_obj.passwd || pw_salt), pw_salt) - ); + PERFORM actor.set_passwd( + pw_usr, 'main', MD5(usr_row.passwd || pw_salt), pw_salt); + + -- clear the existing password + UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id; - -- OR DROP passwd column after loop - UPDATE actor.usr SET passwd = '' WHERE id = usr_obj.id; RETURN pw_salt; END; $$ LANGUAGE PLPGSQL; - --- Returns TRUE if the hashed password provided matches the --- in-db hashed password once run through CRYPT(). --- hashed_passwd is MD5(MD5(password), salt) +-- Returns TRUE if the password provided matches the in-db password. +-- If the password type is salted, we compare the output of CRYPT(). +-- test_passwd is MD5(MD5(password) || salt) for 'main' passwords. CREATE OR REPLACE FUNCTION - actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, hashed_passwd TEXT) + actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT) RETURNS BOOLEAN AS $$ DECLARE pw_salt TEXT; BEGIN - pw_salt := actor.passwd_salt(pw_usr, pw_type); + SELECT INTO pw_salt salt FROM actor.passwd + WHERE usr = pw_usr AND passwd_type = pw_type; + + IF NOT FOUND THEN + -- no such password + RETURN FALSE; + END IF; IF pw_salt IS NULL THEN - -- Requested password does not exist in actor.passwd - RETURN FALSE; + -- Password is unsalted, compare the un-CRYPT'ed values. + RETURN EXISTS ( + SELECT TRUE FROM actor.passwd WHERE + usr = pw_usr AND + passwd_type = pw_type AND + passwd = test_passwd + ); END IF; RETURN EXISTS ( SELECT TRUE FROM actor.passwd WHERE usr = pw_usr AND passwd_type = pw_type AND - passwd = CRYPT(hashed_passwd, pw_salt) + passwd = CRYPT(test_passwd, pw_salt) ); END; $$ STRICT LANGUAGE PLPGSQL; --- DATA ---------------------- -INSERT INTO actor.passwd_type (code, name, login) - VALUES ('main', 'Main Login Password', TRUE); +INSERT INTO actor.passwd_type + (code, name, login, crypt_algo, iter_count) + VALUES ('main', 'Main Login Password', TRUE, 'bf', 14); -- INLINE TESTS --------------- @@ -116,8 +197,8 @@ SELECT TRUE AS verify_old_pw FROM actor.usr WHERE id = 187 AND passwd = MD5('montyc1234'); -- This should result in a migration -SELECT actor.passwd_salt(187, 'main') AS new_salt; --- Directly migrate +SELECT actor.get_salt(187, 'main') AS new_salt; +-- Or directly migrate -- SELECT actor.migrate_passwd(187); -- see what the new password row looks like @@ -127,16 +208,33 @@ SELECT * FROM actor.passwd WHERE usr = 187; SELECT actor.verify_passwd(187, 'main', MD5( MD5('montyc1234') || - actor.passwd_salt(187, 'main')) + actor.get_salt(187, 'main')) ) AS verify_pw_good; -- make sure a bad password fails SELECT actor.verify_passwd(187, 'main', MD5( MD5('montyc1234XXX') || - actor.passwd_salt(187, 'main')) + actor.get_salt(187, 'main')) ) AS verify_pw_bad; +DO $$ + DECLARE new_salt TEXT; + DECLARE ok BOOLEAN; +BEGIN + -- modify password and use a new salt + + SELECT INTO new_salt actor.create_salt('main'); + + PERFORM actor.set_passwd( + 187, 'main', MD5(MD5('bobblehead') || new_salt), new_salt); + + SELECT INTO ok actor.verify_passwd( + 187, 'main', MD5(MD5('bobblehead') || actor.get_salt(187, 'main'))); + + RAISE NOTICE 'new password check resulted in %', ok; +END $$; + ROLLBACK; --COMMIT; -- 2.11.0