Thread: password management

On 7/05/2010 2:31 AM, akp geek wrote:
> Dear all -
>
>                 I am writing function to handle the passwords. Currently
> the crypt is being used to store the password in the database. what I
> need to do is, when the user wants to change the password, I need to
> check if that password is not being used before up to 5 times, If not
> then then records should be inserted to the database.
>
>                The problem where i am running into, when I capture the
> password that user entered, I can't compare to the one in database ,
> because each time the function crypt gives different one. Is there any
> way that I can achieve this?

Extract the salt from each stored password and re-encrypt the new
password with the same salt when comparing it to the old one.

eg:


craig=> create table password_history ( password text not null );
CREATE TABLE
craig=> insert into password_history(password) values ( crypt('fred',
gen_salt('md5')) );
INSERT 0 1
craig=> insert into password_history(password) values ( crypt('bob',
gen_salt('md5')) );
INSERT 0 1
craig=> insert into password_history(password) values (
crypt('smeghead', gen_salt('md5')) );
INSERT 0 1
craig=> create or replace function extract_salt(text) returns text as $$
craig$> select (regexp_matches($1, E'^(\\$[^\\$]+\\$[^\\$]+)\\$'))[1];
craig$> $$ language sql immutable;
CREATE FUNCTION
craig=> select extract_salt(password), password from password_history;
  extract_salt |              password
--------------+------------------------------------
  $1$p3AMpr5s  | $1$p3AMpr5s$BtNTSXwIJbHrdnJEZ4NFg.
  $1$FKySMIXg  | $1$FKySMIXg$xFM5osjqclTuaJIUiGvU3.
  $1$MUwd2dGt  | $1$MUwd2dGt$w06IEIvJ1lROXw7WGb3dw.
(3 rows)

craig=> select exists (select 1 from password_history where
crypt('fred', extract_salt(password)) = password);
  ?column?
----------
  t
(1 row)

craig=> select exists (select 1 from password_history where crypt('bob',
extract_salt(password)) = password);
  ?column?
----------
  t
(1 row)

craig=> select exists (select 1 from password_history where
crypt('nosuch', extract_salt(password)) = password);
  ?column?
----------
  f
(1 row)



Make sure to generate a new salt value if you accept the password and
want to store it, though.


( Perhaps pgcrypto needs a function to extract the salt? )


--
Craig Ringer

On 7/05/2010 12:01 PM, Craig Ringer wrote:
>
> craig=> create or replace function extract_salt(text) returns text as $$
> craig$> select (regexp_matches($1, E'^(\\$[^\\$]+\\$[^\\$]+)\\$'))[1];
> craig$> $$ language sql immutable;

Upon re-reading the pgcrypto documentation I see that this is unnecessary.

Just pass the password hash as the salt. Pgcrypto will extract the salt
part of the hash its self. (otherwise, how could you check passwords?)

So - just as if you were testing authentication, crypt the user's new
password plaintext against each of the old password hashes using the old
password hash as salt, and see if the output hash is the same as the old
password hash. If it is, they've re-used the password.

--
Craig Ringer