3 SELECT evergreen.upgrade_deps_block_check('0961', :eg_version);
5 CREATE EXTENSION IF NOT EXISTS pgcrypto;
7 CREATE TABLE actor.passwd_type (
9 name TEXT UNIQUE NOT NULL,
10 login BOOLEAN NOT NULL DEFAULT FALSE,
11 regex TEXT, -- pending
12 crypt_algo TEXT, -- e.g. 'bf'
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)
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
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)
33 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
36 type_row actor.passwd_type%ROWTYPE;
38 /* Returns a new salt based on the passwd_type encryption settings.
39 * Returns NULL If the password type is not crypt()'ed.
42 SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
45 RETURN EXCEPTION 'No such password type: %', pw_type;
48 IF type_row.iter_count IS NULL THEN
49 -- This password type is unsalted. That's OK.
53 RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
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.
70 CREATE OR REPLACE FUNCTION actor.set_passwd(
71 pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
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
84 IF new_salt IS NOT NULL THEN
87 pw_salt := actor.get_salt(pw_usr, pw_type);
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
93 pw_salt := actor.create_salt(pw_type);
97 IF pw_salt IS NULL THEN
98 pw_text := new_pass; -- unsalted, use as-is.
100 pw_text := CRYPT(new_pass, pw_salt);
104 SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
105 WHERE usr = pw_usr AND passwd_type = pw_type;
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);
117 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
121 type_row actor.passwd_type%ROWTYPE;
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).
129 SELECT INTO pw_salt salt FROM actor.passwd
130 WHERE usr = pw_usr AND passwd_type = pw_type;
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);
142 -- We have no salt to return. actor.create_salt() needed.
147 CREATE OR REPLACE FUNCTION
148 actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
151 usr_row actor.usr%ROWTYPE;
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.
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';
165 -- Only migrate passwords that have not already been migrated.
170 SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
172 pw_salt := actor.create_salt('main');
174 PERFORM actor.set_passwd(
175 pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
177 -- clear the existing password
178 UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
184 CREATE OR REPLACE FUNCTION
185 actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
186 RETURNS BOOLEAN AS $$
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
196 SELECT INTO pw_salt salt FROM actor.passwd
197 WHERE usr = pw_usr AND passwd_type = pw_type;
204 IF pw_salt IS NULL THEN
205 -- Password is unsalted, compare the un-CRYPT'ed values.
207 SELECT TRUE FROM actor.passwd WHERE
209 passwd_type = pw_type AND
215 SELECT TRUE FROM actor.passwd WHERE
217 passwd_type = pw_type AND
218 passwd = CRYPT(test_passwd, pw_salt)
221 $$ STRICT LANGUAGE PLPGSQL;
223 --- DATA ----------------------
225 INSERT INTO actor.passwd_type
226 (code, name, login, crypt_algo, iter_count)
227 VALUES ('main', 'Main Login Password', TRUE, 'bf', 10);