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

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0961', :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     -- Avoid calling get_salt() here, because it may result in a
 161     -- migrate_passwd() call, creating a loop.
 162     SELECT INTO pw_salt salt FROM actor.passwd
 163         WHERE usr = pw_usr AND passwd_type = 'main';
 164
 165     -- Only migrate passwords that have not already been migrated.
 166     IF FOUND THEN
 167         RETURN pw_salt;
 168     END IF;
 169
 170     SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
 171
 172     pw_salt := actor.create_salt('main');
 173
 174     PERFORM actor.set_passwd(
 175         pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
 176
 177     -- clear the existing password
 178     UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
 179
 180     RETURN pw_salt;
 181 END;
 182 $$ LANGUAGE PLPGSQL;
 183
 184 CREATE OR REPLACE FUNCTION
 185     actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
 186     RETURNS BOOLEAN AS $$
 187 DECLARE
 188     pw_salt TEXT;
 189 BEGIN
 190     /* Returns TRUE if the password provided matches the in-db password.
 191      * If the password type is salted, we compare the output of CRYPT().
 192      * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
 193      * 'main' passwords.
 194      */
 195
 196     SELECT INTO pw_salt salt FROM actor.passwd
 197         WHERE usr = pw_usr AND passwd_type = pw_type;
 198
 199     IF NOT FOUND THEN
 200         -- no such password
 201         RETURN FALSE;
 202     END IF;
 203
 204     IF pw_salt IS NULL THEN
 205         -- Password is unsalted, compare the un-CRYPT'ed values.
 206         RETURN EXISTS (
 207             SELECT TRUE FROM actor.passwd WHERE
 208                 usr = pw_usr AND
 209                 passwd_type = pw_type AND
 210                 passwd = test_passwd
 211         );
 212     END IF;
 213
 214     RETURN EXISTS (
 215         SELECT TRUE FROM actor.passwd WHERE
 216             usr = pw_usr AND
 217             passwd_type = pw_type AND
 218             passwd = CRYPT(test_passwd, pw_salt)
 219     );
 220 END;
 221 $$ STRICT LANGUAGE PLPGSQL;
 222
 223 --- DATA ----------------------
 224
 225 INSERT INTO actor.passwd_type
 226     (code, name, login, crypt_algo, iter_count)
 227     VALUES ('main', 'Main Login Password', TRUE, 'bf', 10);
 228
 229 COMMIT;