From ac14333a8ee2adc556019bec776aa14f61dec79b Mon Sep 17 00:00:00 2001
From: Bill Erickson <berickxx@gmail.com>
Date: Fri, 17 Jul 2015 16:00:17 -0400
Subject: [PATCH] LP#1468422 Password storage/migration proof of concept

* Backwards compatible salted password storage using pgcrypt
* Adds actor.passwd and actor.passwd_type tables
* Includes some inline tests along the bottom
* Completes with ROLLBACK (for now)

Signed-off-by: Bill Erickson <berickxx@gmail.com>
---
 .../Pg/upgrade/XXXX.schema.password-storage.sql    | 121 +++++++++++++++++++++
 1 file changed, 121 insertions(+)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.password-storage.sql

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
new file mode 100644
index 0000000000..296f535511
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.password-storage.sql
@@ -0,0 +1,121 @@
+
+BEGIN;
+
+CREATE EXTENSION IF NOT EXISTS pgcrypto;
+
+CREATE TABLE actor.passwd_type (
+    code        TEXT PRIMARY KEY,
+    name        TEXT UNIQUE NOT NULL,
+    login       BOOLEAN NOT NULL DEFAULT FALSE,
+    regex       TEXT,   -- pending
+    crypt_algo  TEXT    -- pending
+);
+
+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,
+    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)
+);
+
+-- 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)
+    RETURNS TEXT AS $$
+    SELECT salt FROM actor.passwd 
+        WHERE usr = $1 AND passwd_type = $2;
+$$ STRICT LANGUAGE SQL;
+
+-- 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 $$
+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;
+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)
+CREATE OR REPLACE FUNCTION 
+    actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, hashed_passwd TEXT) 
+    RETURNS BOOLEAN AS $$
+DECLARE
+    pw_salt TEXT;
+BEGIN
+
+    SELECT INTO pw_salt salt FROM actor.passwd 
+        WHERE usr = pw_usr AND passwd_type = pw_type;
+
+    IF NOT FOUND THEN
+        -- No such user or password-type
+        RETURN FALSE;
+    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)
+    );
+END;
+$$ STRICT LANGUAGE PLPGSQL;
+
+--- DATA ----------------------
+
+INSERT INTO actor.passwd_type (code, name, login) 
+    VALUES ('main', 'Main Login Password', TRUE);
+
+
+-- INLINE TESTS ---------------
+
+-- concerto user br1mclark
+-- confirm the pre-migration password is what we think it is.
+SELECT TRUE AS verify_old_pw
+    FROM actor.usr WHERE id = 187 AND passwd = MD5('montyc1234');
+
+-- migrate all passwords
+SELECT actor.migrate_all_passwd();
+
+-- see what the new password row looks like
+SELECT * FROM actor.passwd WHERE usr = 187;
+
+-- see if the new-style password verifies
+SELECT actor.verify_passwd(187, 'main', 
+    MD5(
+        MD5('montyc1234') || 
+        actor.passwd_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'))
+    ) AS verify_pw_bad;
+
+
+ROLLBACK;
+--COMMIT;
-- 
2.11.0