Open-ILS/src/sql/Pg/upgrade/XXXX.schema.password-storage.sql

   1 BEGIN;
   2
   3 -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
   4
   5 CREATE EXTENSION IF NOT EXISTS pgcrypto;
   6
   7 CREATE TABLE actor.passwd_type (
   8     code        TEXT PRIMARY KEY,
   9     name        TEXT UNIQUE NOT NULL,
  10     login       BOOLEAN NOT NULL DEFAULT FALSE,
  11     regex       TEXT,   -- pending
  12     crypt_algo  TEXT,   -- e.g. 'bf'
  13
  14     -- gen_salt() iter count used with each new salt.
  15     -- A non-NULL value for iter_count is our indication the
  16     -- password is salted and encrypted via crypt()
  17     iter_count  INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
  18 );
  19
  20 CREATE TABLE actor.passwd (
  21     id          SERIAL PRIMARY KEY,
  22     usr         INTEGER NOT NULL REFERENCES actor.usr(id)
  23                 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  24     salt        TEXT, -- will be NULL for non-crypt'ed passwords
  25     passwd      TEXT NOT NULL,
  26     passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
  27                 DEFERRABLE INITIALLY DEFERRED,
  28     create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  29     edit_date   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  30     CONSTRAINT  passwd_type_once_per_user UNIQUE (usr, passwd_type)
  31 );
  32
  33 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
  34     RETURNS TEXT AS $$
  35 DECLARE
  36     type_row actor.passwd_type%ROWTYPE;
  37 BEGIN
  38     /* Returns a new salt based on the passwd_type encryption settings.
  39      * Returns NULL If the password type is not crypt()'ed.
  40      */
  41
  42     SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
  43
  44     IF NOT FOUND THEN
  45         RETURN EXCEPTION 'No such password type: %', pw_type;
  46     END IF;
  47
  48     IF type_row.iter_count IS NULL THEN
  49         -- This password type is unsalted.  That's OK.
  50         RETURN NULL;
  51     END IF;
  52
  53     RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
  54 END;
  55 $$ LANGUAGE PLPGSQL;
  56
  57
  58 /*
  59     TODO: when a user changes their password in the application, the
  60     app layer has access to the bare password.  At that point, we have
  61     the opportunity to store the new password without the MD5(MD5())
  62     intermediate hashing.  Do we care?  We would need a way to indicate
  63     which passwords have the legacy intermediate hashing and which don't
  64     so the app layer would know whether it should perform the intermediate
  65     hashing.  In either event, with the exception of migrate_passwd(), the
  66     DB functions know or care nothing about intermediate hashing.  Every
  67     password is just a value that may or may not be internally crypt'ed.
  68 */
  69
  70 CREATE OR REPLACE FUNCTION actor.set_passwd(
  71     pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
  72     RETURNS BOOLEAN AS $$
  73 DECLARE
  74     pw_salt TEXT;
  75     pw_text TEXT;
  76 BEGIN
  77     /* Sets the password value, creating a new actor.passwd row if needed.
  78      * If the password type supports it, the new_pass value is crypt()'ed.
  79      * For crypt'ed passwords, the salt comes from one of 3 places in order:
  80      * new_salt (if present), existing salt (if present), newly created
  81      * salt.
  82      */
  83
  84     IF new_salt IS NOT NULL THEN
  85         pw_salt := new_salt;
  86     ELSE
  87         pw_salt := actor.get_salt(pw_usr, pw_type);
  88
  89         IF pw_salt IS NULL THEN
  90             /* We have no salt for this user + type.  Assume they want a
  91              * new salt.  If this type is unsalted, create_salt() will
  92              * return NULL. */
  93             pw_salt := actor.create_salt(pw_type);
  94         END IF;
  95     END IF;
  96
  97     IF pw_salt IS NULL THEN
  98         pw_text := new_pass; -- unsalted, use as-is.
  99     ELSE
 100         pw_text := CRYPT(new_pass, pw_salt);
 101     END IF;
 102
 103     UPDATE actor.passwd
 104         SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
 105         WHERE usr = pw_usr AND passwd_type = pw_type;
 106
 107     IF NOT FOUND THEN
 108         -- no password row exists for this user + type.  Create one.
 109         INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
 110             VALUES (pw_usr, pw_type, pw_salt, pw_text);
 111     END IF;
 112
 113     RETURN TRUE;
 114 END;
 115 $$ LANGUAGE PLPGSQL;
 116
 117 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
 118     RETURNS TEXT AS $$
 119 DECLARE
 120     pw_salt TEXT;
 121     type_row actor.passwd_type%ROWTYPE;
 122 BEGIN
 123     /* Returns the salt for the requested user + type.  If the password
 124      * type of "main" is requested and no password exists in actor.passwd,
 125      * the user's existing password is migrated and the new salt is returned.
 126      * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
 127      */
 128
 129     SELECT INTO pw_salt salt FROM actor.passwd
 130         WHERE usr = pw_usr AND passwd_type = pw_type;
 131
 132     IF FOUND THEN
 133         RETURN pw_salt;
 134     END IF;
 135
 136     IF pw_type = 'main' THEN
 137         -- Main password has not yet been migrated.
 138         -- Do it now and return the newly created salt.
 139         RETURN actor.migrate_passwd(pw_usr);
 140     END IF;
 141
 142     -- We have no salt to return.  actor.create_salt() needed.
 143     RETURN NULL;
 144 END;
 145 $$ LANGUAGE PLPGSQL;
 146
 147 CREATE OR REPLACE FUNCTION
 148     actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
 149 DECLARE
 150     pw_salt TEXT;
 151     usr_row actor.usr%ROWTYPE;
 152 BEGIN
 153     /* Migrates legacy actor.usr.passwd value to actor.passwd with
 154      * a password type 'main' and returns the new salt.  For backwards
 155      * compatibility with existing CHAP-style API's, we perform a
 156      * layer of intermediate MD5(MD5()) hashing.  This is intermediate
 157      * hashing is not required of other passwords.
 158      */
 159
 160     SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
 161
 162     pw_salt := actor.create_salt('main');
 163
 164     PERFORM actor.set_passwd(
 165         pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
 166
 167     -- clear the existing password
 168     UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
 169
 170     RETURN pw_salt;
 171 END;
 172 $$ LANGUAGE PLPGSQL;
 173
 174 CREATE OR REPLACE FUNCTION
 175     actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
 176     RETURNS BOOLEAN AS $$
 177 DECLARE
 178     pw_salt TEXT;
 179 BEGIN
 180     /* Returns TRUE if the password provided matches the in-db password.
 181      * If the password type is salted, we compare the output of CRYPT().
 182      * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
 183      * 'main' passwords.
 184      */
 185
 186     SELECT INTO pw_salt salt FROM actor.passwd
 187         WHERE usr = pw_usr AND passwd_type = pw_type;
 188
 189     IF NOT FOUND THEN
 190         -- no such password
 191         RETURN FALSE;
 192     END IF;
 193
 194     IF pw_salt IS NULL THEN
 195         -- Password is unsalted, compare the un-CRYPT'ed values.
 196         RETURN EXISTS (
 197             SELECT TRUE FROM actor.passwd WHERE
 198                 usr = pw_usr AND
 199                 passwd_type = pw_type AND
 200                 passwd = test_passwd
 201         );
 202     END IF;
 203
 204     RETURN EXISTS (
 205         SELECT TRUE FROM actor.passwd WHERE
 206             usr = pw_usr AND
 207             passwd_type = pw_type AND
 208             passwd = CRYPT(test_passwd, pw_salt)
 209     );
 210 END;
 211 $$ STRICT LANGUAGE PLPGSQL;
 212
 213 --- DATA ----------------------
 214
 215 INSERT INTO actor.passwd_type
 216     (code, name, login, crypt_algo, iter_count)
 217     VALUES ('main', 'Main Login Password', TRUE, 'bf', 14);
 218
 219 COMMIT;