From 3cd1553d65c296dd22c39ea1b419158e87e3918a Mon Sep 17 00:00:00 2001
From: Bill Erickson <berickxx@gmail.com>
Date: Tue, 21 Jul 2015 12:45:51 -0400
Subject: [PATCH] LP#1468422 Real-time / per-user password migration

Don't rely on mass password migration, since it would take a very long
time.  Instead, migrate users on demand.

Raise work factor (iteration count) from 10 to 14.

Current flow:

1. Application requests a salt to use as the CHAP-style seed
2. If new-style password exists, salt is returned.
3. Else, old password is migrated and the new salt is returned.
4. App finalizes login by checking verify_passwd.

Signed-off-by: Bill Erickson <berickxx@gmail.com>
---
 .../Pg/upgrade/XXXX.schema.password-storage.sql    | 79 ++++++++++++++--------
 1 file changed, 50 insertions(+), 29 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 296f535511..c4d4f70a76 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
@@ -22,36 +22,56 @@ CREATE TABLE actor.passwd (
     CONSTRAINT passwd_type_once_per_user UNIQUE (usr, passwd_type)
 );
 
--- actor.passwd is not IDL-accessible, so the application
--- needs a way to retrieve the salt via cstore.
-CREATE OR REPLACE FUNCTION actor.passwd_salt(pw_user INTEGER, pw_type TEXT)
+-- 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)
     RETURNS TEXT AS $$
-    SELECT salt FROM actor.passwd 
-        WHERE usr = $1 AND passwd_type = $2;
-$$ STRICT LANGUAGE SQL;
+DECLARE
+    pw_salt TEXT;
+BEGIN
+
+    SELECT INTO pw_salt salt FROM actor.passwd 
+        WHERE usr = pw_usr AND passwd_type = pw_type;
+
+    IF FOUND THEN
+        RETURN pw_salt;
+    END IF;
+
+    IF pw_type = 'main' THEN
+        RETURN actor.migrate_passwd(pw_usr);
+    END IF;
+
+    -- Only 'main' passwords are auto-migrated.
+    RETURN NULL;
+
+END;
+$$ LANGUAGE PLPGSQL;
 
 -- Migrates actor.usr.passwd to actor.passwd with 
--- password type 'main' for every user.
--- Could drop this after the initial migration.
-CREATE OR REPLACE FUNCTION actor.migrate_all_passwd() RETURNS VOID AS $$
+-- password type 'main' for every user.  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;
 BEGIN
-    FOR usr_obj IN SELECT * FROM actor.usr LOOP
-        pw_salt := gen_salt('bf', 10); -- TODO: configurable?
-
-        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)
-            );
-
-        -- OR DROP passwd column after loop
-        UPDATE actor.usr SET passwd = '' WHERE id = usr_obj.id;
-    END LOOP;
+
+    SELECT INTO usr_obj * FROM actor.usr WHERE id = pw_usr;
+    pw_salt := gen_salt('bf', 14); -- TODO: configurable?
+
+    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)
+        );
+
+    -- OR DROP passwd column after loop
+    UPDATE actor.usr SET passwd = '' WHERE id = usr_obj.id;
+    RETURN pw_salt;
 END;
 $$ LANGUAGE PLPGSQL;
 
@@ -66,11 +86,10 @@ DECLARE
     pw_salt TEXT;
 BEGIN
 
-    SELECT INTO pw_salt salt FROM actor.passwd 
-        WHERE usr = pw_usr AND passwd_type = pw_type;
+    pw_salt := actor.passwd_salt(pw_usr, pw_type);
 
-    IF NOT FOUND THEN
-        -- No such user or password-type
+    IF pw_salt IS NULL THEN
+        -- Requested password does not exist in actor.passwd
         RETURN FALSE;
     END IF;
 
@@ -96,8 +115,10 @@ INSERT INTO actor.passwd_type (code, name, login)
 SELECT TRUE AS verify_old_pw
     FROM actor.usr WHERE id = 187 AND passwd = MD5('montyc1234');
 
--- migrate all passwords
-SELECT actor.migrate_all_passwd();
+-- This should result in a migration
+SELECT actor.passwd_salt(187, 'main') AS new_salt;
+-- Directly migrate
+-- SELECT actor.migrate_passwd(187);
 
 -- see what the new password row looks like
 SELECT * FROM actor.passwd WHERE usr = 187;
-- 
2.11.0