[GENERAL] password management

[akp geek]

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?

  Appreciate your help

Regards

Re: [GENERAL] password management

[Craig Ringer]

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password management

[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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password management

[Christophe Dore]

Hi

 

IMHO, you should never store password in clear

 

If you store the last 5 crypted passwords, then you can make it  comparing the 
new password, crypted, to those 5 strings.

 

Regards

 

-- 

Christophe Doré 
Implementation Product Manager 

3 rue Marcel Allegot 
92190 Meudon, France 
+33 1 46 90 21 00 office 
+33 6 1379 2910 mobile 
CAST, Leader in Automated Application Intelligence 
Achieve Insight. Deliver Excellence. 

www.castsoftware.com   | Gain visibility into 
application quality to proactively manage risk and improve team performance.

From: akp geek [mailto:akpg...@gmail.com] 
Sent: jeudi 6 mai 2010 20:31
To: pgsql-general
Subject: password management

 

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?

 

  Appreciate your help

 

Regards