encrypted passwords in database
in most applications you have some variant of this table:
CREATE TABLE users (
id serial PRIMARY KEY,
username TEXT NOT NULL,
passwd TEXT
);
and, usually, the passwd stores user password in clear text way.
this is usually not a problem, but in case you'd like to add password encryption in database, there are some ways to do it - and i'll show you which way i like most.
first solution is a no-brainer. make the app crypt the password and do whatever is neccessary.
now, this looks like a fine solution until you'll have more than 1 application that will be checking/setting passwords. and - usually - you will.
after all - even if you do not plan to put another website on the same database, odds are one day you'll want to change user password from psql. and what then?
so, it is better to leave the encryption job to postgres itself.
to make it so, we'll do some "magic".
first, let's make our users table in a way that it will automatically convert entered password to encrypted.
to do it - we will need pgcrypto module from contrib directory. if you dont know what i'm talking about - that's really bad, as contrib modules are extremly useful.
if you're using pre-packaged postgresql, there should be package named postgresql-contrib-your-version or similarly. just install it.
then, find pgcrypto.sql file. usually you can find it in places like /usr/share/postgresql/contrib/pgcrypto.sql, /usr/local/share/postgresql/contrib/pgcrypto.sql, /usr/local/pgsql/share/postgresql/contrib/pgcrypto.sql or similar.
when you have the file, just connect to your database of choice (using superuser account) and issue (from psql):
\i /home/pgdba/work/share/postgresql/contrib/pgcrypto.sql
which will load the pgcrypto module to your database.
now, for some more interesting fun.
for our users table, we'll add a simple trigger:
CREATE OR REPLACE FUNCTION trg_crypt_users_pass() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
IF substr(NEW.passwd, 1, 3) <> '$1$' THEN
NEW.passwd := crypt( NEW.passwd, gen_salt('md5') );
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_crypt_users_pass BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE trg_crypt_users_pass();
you might wander why there is this if-with-substr.
it's simple - we want to encrypt only the password that do not start with '$1$'. reason? crypted password will start with '$1$', and if we didn't put the "if" there, the first update to users table (even if it wouldn't touch passwd field) would scramble the password, thus rendering account unusable.
now, let's test if it works:
INSERT INTO users (username, passwd) VALUES ('depesz', 'depesz');
INSERT INTO users (username, passwd) VALUES ('NULL-user', NULL);
INSERT INTO users (username, passwd) VALUES ('test', ' ');
INSERT INTO users (username, passwd) VALUES ('foo', '$1$');
and what is in the table?
# SELECT * FROM users;
id | username | passwd
----+-----------+------------------------------------
1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0
2 | NULL-user | [null]
3 | test | $1$ik3jkoki$UO4MSNsHSb5SQdq7GeZRS/
4 | foo | $1$
(4 rows)
ok, works as expected. the case with passwd = '$1$' is dubious, and we could "fix" the issue with adding length-check to trigger, but it doesn't really bother
me, so i'll leave it as it is - after all, to make a full check i would have to use a regexp, which is not really nice.
so, now our table has encrypted passwords. and i can easily search for users:
# select * from users where username = 'depesz' and crypt('depesz', passwd) = passwd;
id | username | passwd
----+----------+------------------------------------
1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0
(1 row)
bad password check:
# select * from users where username = 'depesz' and crypt('bad-password', passwd) = passwd;
id | username | passwd
----+----------+--------
(0 rows)
now. it's not really "easily". i could definitely do better than that.
so, let's introduce another datatype: "password":
CREATE DOMAIN password as TEXT;
now, let's convert data:
alter table users alter column passwd type password;
ok, but having another datatype doesn't give me anything good. yet.
i'd like to be able to do things like:
select * from users where username = 'depesz' and passwd = 'depesz';
without all this "crypt()" mess. so, let's write some small, custom operators.
because passwords can only "match" or "not match" we will need only 2 operators: "=" and "<>". so, there goes the code:
CREATE FUNCTION password_leq(password, TEXT) RETURNS bool as $BODY$
SELECT crypt($2, $1) = $1::text;
$BODY$ language sql immutable;
CREATE OPERATOR = (
leftarg = password,
rightarg = text,
negator = <>,
procedure = password_leq
);
CREATE FUNCTION password_lne(password, TEXT) RETURNS bool as $BODY$
SELECT crypt($2, $1) <> $1::text;
$BODY$ language sql immutable;
CREATE OPERATOR <> (
leftarg = password,
rightarg = text,
negator = =,
procedure = password_lne
);
CREATE FUNCTION password_req(TEXT, password) RETURNS bool as $BODY$
SELECT crypt($1, $2) = $2::text;
$BODY$ language sql immutable;
CREATE OPERATOR = (
leftarg = text,
rightarg = password,
negator = <>,
procedure = password_req
);
CREATE FUNCTION password_rne(TEXT, password) RETURNS bool as $BODY$
SELECT crypt($1, $2) <> $2::text;
$BODY$ language sql immutable;
CREATE OPERATOR <> (
leftarg = text,
rightarg = password,
negator = =,
procedure = password_rne
);
now, thanks to this we can:
# select * from users where passwd = 'depesz'::text;
id | username | passwd
----+----------+------------------------------------
1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0
(1 row)
but, unfortunatelly, this will fail:
# select * from users where passwd = 'depesz';
id | username | passwd
----+----------+--------
(0 rows)
reason is very simple - postgresql, when running this query will implicitly cast 'depesz' to 'password', so the "=" operator will be called for (password = password) and not for (password = text)!
to make it working we'll need 2 more operators:
CREATE FUNCTION password_beq(left password, right password) RETURNS bool as $BODY$
DECLARE
left_crypted bool;
right_crypted bool;
BEGIN
left_crypted := ( substr(left, 1, 3) = '$1$' );
right_crypted := ( substr(right, 1, 3) = '$1$' );
IF (left_crypted) AND (NOT right_crypted) THEN
RETURN crypt(right, left)::TEXT = left::TEXT;
END IF;
IF (NOT left_crypted) AND (right_crypted) THEN
RETURN crypt(left, right)::TEXT = right::TEXT;
END IF;
RETURN left::TEXT = right::TEXT;
END;
$BODY$ language plpgsql immutable;
CREATE OPERATOR = (
leftarg = password,
rightarg = password,
negator = <>,
procedure = password_beq
);
CREATE FUNCTION password_bne(password, password) RETURNS bool as $BODY$
SELECT NOT password_beq($1, $2);
$BODY$ language sql immutable;
CREATE OPERATOR <> (
leftarg = password,
rightarg = password,
negator = =,
procedure = password_bne
);
now, the password_beq function is quite complex. what it does? it tries to guess which side of comparison is encrypted, and which is not.
when only one side of comparison has '$1$' at the beginning, it crypts the other argument, and then compares. if both, or none of arguments have '$1$' - it just compares them as simple strings.
now, i can:
# select * from users where passwd = 'depesz';
id | username | passwd
----+----------+------------------------------------
1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0
(1 row)
so, without modifying client code i modified storage of password to make them crypted. which is good, at the very least for me.
this solution has one slight "issue" which can be perceived both as a drawback, or as a bonus benefit:
# select * from users where passwd = '$1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0';
id | username | passwd
----+----------+------------------------------------
1 | depesz | $1$Im51jH1k$/9AOm/t.4BixxF7YzZ5hx0
(1 row)
that is - instead of using standard password i can also authenticate using its hash. whether it's good i leave for you to decide - for me it's definitely a benefit.
No comments:
Post a Comment