Обсуждение: field with Password
Hi. I would like to create a new table where one of the field would be a user password. Is there any data type for supporting this functionality? Something like Password DataType. I've taken a look of the available data types in PgAdmin Application and there is nothing similar to this. Thanks in advance. Best,
what do you expect from such type? try to use a text field for plain passwords or better store only hashvalues. see md5() regards thomas Iñigo Barandiaran schrieb: > Hi. > > > I would like to create a new table where one of the field would be a > user password. Is there any data type for supporting this > functionality? Something like Password DataType. I've taken a look of > the available data types in PgAdmin Application and there is nothing > similar to this. > > Thanks in advance. > > Best, >
Iñigo Barandiaran wrote: > Hi. > > > I would like to create a new table where one of the field would be a > user password. Is there any data type for supporting this > functionality? Something like Password DataType. I've taken a look of > the available data types in PgAdmin Application and there is nothing > similar to this. most commonly, passwords are stored as hashes, such as md5, rather than plaintext. 'text' would be as suitable for this as anything, or bytea, if you want to store the hashes in binary.
In response to Iñigo Barandiaran : > Hi. > > > I would like to create a new table where one of the field would be a > user password. Is there any data type for supporting this functionality? > Something like Password DataType. I've taken a look of the available > data types in PgAdmin Application and there is nothing similar to this. You can store the md5-Hash instead the plaintext password. (char(32) and a length-check) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Thanks for your answers. Sorry for the questions but I'm new to Postgre :) The problem with a plain text password is that a user can see it by looking at the user table. Both suggest to use MD5. How can i use it? Any link, example about this would be very appreciated. Thanks in advance! > Iñigo Barandiaran wrote: >> Hi. >> >> >> I would like to create a new table where one of the field would be a >> user password. Is there any data type for supporting this >> functionality? Something like Password DataType. I've taken a look of >> the available data types in PgAdmin Application and there is nothing >> similar to this. > > most commonly, passwords are stored as hashes, such as md5, rather > than plaintext. 'text' would be as suitable for this as anything, > or bytea, if you want to store the hashes in binary. > > > >
Iñigo Barandiaran wrote: > Thanks for your answers. Sorry for the questions but I'm new to > Postgre :) > > The problem with a plain text password is that a user can see it by > looking at the user table. > Both suggest to use MD5. How can i use it? Any link, example about > this would be very appreciated. md5 is a library function that converts a string to a 'hash', typically 32 bytes. so, when the user enters a password, you encode it with md5() and compare it with the stored hash. the md5 hash is not readily reversible (although brute force techniques can theoretically come up with strings that will generate the same hash) otoh, any table used to store security information probably should not be directly viewable by the end user. application programming techniques for ensuring application security go far beyond the charter of this email list, however.
> >> I would like to create a new table where one of the field would be a > >> user password. Is there any data type for supporting this > >> functionality? Something like Password DataType. I've taken a look > of > >> the available data types in PgAdmin Application and there is nothing > >> similar to this. > > > > most commonly, passwords are stored as hashes, such as md5, rather > > than plaintext. 'text' would be as suitable for this as anything, > > or bytea, if you want to store the hashes in binary. > > > Thanks for your answers. Sorry for the questions but I'm new to Postgre > :) > > The problem with a plain text password is that a user can see it by > looking at the user table. > Both suggest to use MD5. How can i use it? Any link, example about this > would be very appreciated. Insert new users like this: insert into myusers (usernm, passwd) values ($user, MD5($pass)); So the paintext password is not stored. But you should still restrict access to this table. Revoke rights to regular users. When a user logs in, check for their access like this: select * from myusers where usernm=$user and passwd=MD5($pass); The hash of a particular password is always the same. To make this scheme more secure, you should add a salt before hashing. (You can find how to do this via google).
Thanks! Ok. I've found http://256.com/sources/md5/ library. So the idea is to define in the dataBase a Field of PlainText type. When I want to insert a new user, I define a password, convert to MD5 hash with the library and store it in the DataBase. Afterwards, any user check should get the content of the DataBase of do the inverse process with the library. Is it correct? Thanks so much!!!!!! Best, > Iñigo Barandiaran wrote: >> Thanks for your answers. Sorry for the questions but I'm new to >> Postgre :) >> >> The problem with a plain text password is that a user can see it by >> looking at the user table. >> Both suggest to use MD5. How can i use it? Any link, example about >> this would be very appreciated. > > md5 is a library function that converts a string to a 'hash', > typically 32 bytes. so, when the user enters a password, you encode > it with md5() and compare it with the stored hash. the md5 hash is > not readily reversible (although brute force techniques can > theoretically come up with strings that will generate the same hash) > > otoh, any table used to store security information probably should not > be directly viewable by the end user. application programming > techniques for ensuring application security go far beyond the charter > of this email list, however. > > > >
On Wed, Feb 04, 2009 at 11:09:51AM +0100, Iñigo Barandiaran wrote: > I would like to create a new table where one of the field would be a > user password. Is there any data type for supporting this functionality? > Something like Password DataType. I've taken a look of the available > data types in PgAdmin Application and there is nothing similar to this. you might find this post useful: http://www.depesz.com/index.php/2007/11/05/encrypted-passwords-in-database/ depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Iñigo Barandiaran wrote:
And compare the supplied password with something like:
You don't need to depend on an external library for this functionality; it's built right into Postgres. Personally, in my own apps I write in PHP, I use a combination of sha1 and md5 to hash user passwords, without depending on Postgres to do the hashing, but the effect is basically the same.
Raymond
Thanks!Well, you can use the built-in md5 function for this purpose. For instance, you could insert a password into the table with a statement like:
Ok. I've found http://256.com/sources/md5/ library. So the idea is to define in the dataBase a Field of PlainText type. When I want to insert a new user, I define a password, convert to MD5 hash with the library and store it in the DataBase. Afterwards, any user check should get the content of the DataBase of do the inverse process with the library. Is it correct?
Thanks so much!!!!!!
Best,
insert into auth_data (user_id, password) values (1, md5('test'));
And compare the supplied password with something like:
select true from auth_data where user_id = 1 and password = md5('test');
You don't need to depend on an external library for this functionality; it's built right into Postgres. Personally, in my own apps I write in PHP, I use a combination of sha1 and md5 to hash user passwords, without depending on Postgres to do the hashing, but the effect is basically the same.
Raymond
Thanks Raymond !!!!<br /><br /> That is something I wanted! It's Great if it is already integrated in Postgre! Superb. Thisis much more easy.<br /><br /> Thank you All.<br /><br /> Best,<br /><blockquote cite="mid:4989A790.7090401@gmail.com"type="cite"></blockquote> Iñigo Barandiaran wrote: <blockquote cite="mid:49897455.7080602@vicomtech.org"type="cite">Thanks! <br /><br /><br /> Ok. I've found <a class="moz-txt-link-freetext"href="http://256.com/sources/md5/" moz-do-not-send="true">http://256.com/sources/md5/</a> library.So the idea is to define in the dataBase a Field of PlainText type. When I want to insert a new user, I define apassword, convert to MD5 hash with the library and store it in the DataBase. Afterwards, any user check should get the contentof the DataBase of do the inverse process with the library. Is it correct? <br /><br /> Thanks so much!!!!!! <br /><br/> Best, <br /><br /></blockquote> Well, you can use the built-in md5 function for this purpose. For instance, you couldinsert a password into the table with a statement like:<br /><br /><blockquote>insert into auth_data (user_id, password)values (1, md5('test'));<br /></blockquote><br /> And compare the supplied password with something like:<br /><br/><blockquote>select true from auth_data where user_id = 1 and password = md5('test');<br /></blockquote><br /> Youdon't need to depend on an external library for this functionality; it's built right into Postgres. Personally, in myown apps I write in PHP, I use a combination of sha1 and md5 to hash user passwords, without depending on Postgres todo the hashing, but the effect is basically the same.<br /><br /> Raymond<br /><br />
Thanks Raymond !!!!<br /><blockquote cite="mid:4989A827.5060104@vicomtech.org" type="cite"><br /> That is something I
wanted!It's Great if it is already integrated in Postgre! Superb. This is much more easy.<br /><br /> Thank you All.<br
/><br/> Best,<br /><blockquote cite="mid:4989A790.7090401@gmail.com" type="cite"> Iñigo Barandiaran wrote: <blockquote
cite="mid:49897455.7080602@vicomtech.org"type="cite"><br /><br /></blockquote> Well, you can use the built-in md5
functionfor this purpose. For instance, you could insert a password into the table with a statement like:<br /><br
/><blockquote>insertinto auth_data (user_id, password) values (1, md5('test'));<br /></blockquote><br /> And compare
thesupplied password with something like:<br /><br /><blockquote>select true from auth_data where user_id = 1 and
password= md5('test');<br /></blockquote><br /> You don't need to depend on an external library for this functionality;
it'sbuilt right into Postgres. Personally, in my own apps I write in PHP, I use a combination of sha1 and md5 to hash
userpasswords, without depending on Postgres to do the hashing, but the effect is basically the same.<br /><br />
Raymond<br/></blockquote><br /></blockquote><br />
On Wed, Feb 04, 2009 at 09:34:56AM -0500, Raymond C. Rodgers wrote: > You don't need to depend on an external library for this functionality; > it's built right into Postgres. Personally, in my own apps I write in > PHP, I use a combination of sha1 and md5 to hash user passwords, > without depending on Postgres to do the hashing, but the effect is > basically the same. Doing the hashing outside PG would reduce the chance of the password being exposed, either accidentally by, say, turning on statement logging, or maliciously. A general rule with passwords is to throw away any copy of a plain text password as quickly as possible, sending the password over to another process would go against this. -- Sam http://samason.me.uk/
Thanks!<br /><br /> This is great. I'm now implementing this functionality.<br /><br /> Thank you all.<br /><br /> You
aregreat!<br /><br /> Best,<br /><blockquote
cite="mid:OFC951B8A8.DA173041-ON80257553.005B3A94-80257553.005BE628@shropshire.gov.uk"type="cite"><br /><font
face="sans-serif"size="2">You should always salt your password hashes.</font><br /><br /><font face="sans-serif"
size="2">Ierandomly generate a salt string, the store this and the password hash:</font><br /><br /><font
face="sans-serif"size="2"> insert into auth (user_id, salt, password) values (1,'blah',md5('blah' + 'test'))
;</font><br/><br /><font face="sans-serif" size="2">then to check the password</font><br /><br /><font
face="sans-serif"size="2"> select true from auth where user_id = 1 and password = md5( salt + 'test')
;</font><br/><br /><br /><font face="sans-serif" size="2">I tend to set a trigger function to auto generate a salt and
hashthe password.</font><br /><br /><br /><br /><font face="sans-serif" size="2">If you want to be really secure, use
botha md5 and sha1 hash, snice it has been proved you can generate hash collisions so you could use:</font><br /><br
/><fontface="sans-serif" size="2"> insert into auth (user_id, salt, password) values (1,'blah',md5('blah' ||
'test')|| sha1('blah' || 'test')) ;</font><br /><br /><font face="sans-serif" size="2">then to check the
password</font><br/><br /><font face="sans-serif" size="2"> select true from auth where user_id = 1 and password
=md5( salt || 'test') || sha1( salt || 'test') ;</font><br /><br /><font face="sans-serif" size="2">Chris
Ellis</font><br/><br /><br /><br /><br /><table width="100%"><tbody><tr valign="top"><td width="40%"><font
face="sans-serif"size="1"><b>"Raymond C. Rodgers" <a class="moz-txt-link-rfc2396E"
href="mailto:sinful622@gmail.com"><sinful622@gmail.com></a></b></font><br /><font face="sans-serif" size="1">Sent
by:<a class="moz-txt-link-abbreviated"
href="mailto:pgsql-general-owner@postgresql.org">pgsql-general-owner@postgresql.org</a></font><p><fontface="sans-serif"
size="1">04/02/200914:34</font></td><td width="59%"><table width="100%"><tbody><tr valign="top"><td><div
align="right"><fontface="sans-serif" size="1">To</font></div></td><td><font face="sans-serif" size="1">Iñigo
Barandiaran<a class="moz-txt-link-rfc2396E"
href="mailto:ibarandiaran@vicomtech.org"><ibarandiaran@vicomtech.org></a></font></td></tr><tr
valign="top"><td><divalign="right"><font face="sans-serif" size="1">cc</font></div></td><td><font face="sans-serif"
size="1"><aclass="moz-txt-link-abbreviated"
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a></font></td></tr><trvalign="top"><td><div
align="right"><fontface="sans-serif" size="1">Subject</font></div></td><td><font face="sans-serif" size="1">Re:
[GENERAL]field with Password</font></td></tr></tbody></table><br /><table><tbody><tr valign="top"><td><br
/></td><td><br/></td></tr></tbody></table><br /></td></tr></tbody></table><br /><br /><br /><font size="3">Iñigo
Barandiaranwrote: </font><br /><font size="3">Thanks! <br /><br /><br /> Ok. I've found </font><a
href="http://256.com/sources/md5/"moz-do-not-send="true"><font color="blue"
size="3"><u>http://256.com/sources/md5/</u></font></a><fontsize="3"> library. So the idea is to define in the dataBase
aField of PlainText type. When I want to insert a new user, I define a password, convert to MD5 hash with the library
andstore it in the DataBase. Afterwards, any user check should get the content of the DataBase of do the inverse
processwith the library. Is it correct? <br /><br /> Thanks so much!!!!!! <br /><br /> Best, <br /></font><br /><font
size="3">Well,you can use the built-in md5 function for this purpose. For instance, you could insert a password into
thetable with a statement like:<br /></font><br /><font size="3">insert into auth_data (user_id, password) values (1,
md5('test'));</font><br/><font size="3"><br /> And compare the supplied password with something like:<br /></font><br
/><fontsize="3">select true from auth_data where user_id = 1 and password = md5('test');</font><br /><font size="3"><br
/>You don't need to depend on an external library for this functionality; it's built right into Postgres. Personally,
inmy own apps I write in PHP, I use a combination of sha1 and md5 to hash user passwords, without depending on
Postgresto do the hashing, but the effect is basically the same.<br /><br /> Raymond</font><br /><p><span
style="font-family:'Courier New'; font-size:
8pt;">******************************************************************************</span><pstyle="line-height:
12pt;"><spanstyle="font-family: 'Helv'; font-size: 9.7pt; color: rgb(0, 0, 0);"><b>If you are not the intended
recipientof this email please do not send it on</b></span><p style="line-height: 12pt;"><span style="font-family:
'Helv';font-size: 9.7pt; color: rgb(0, 0, 0);"><b>to others, open any attachments or file the email locally.
</b></span><pstyle="line-height: 12pt;"><span style="font-family: 'Helv'; font-size: 9.7pt; color: rgb(0, 0,
0);"><b>Pleaseinform the sender of the error and then delete the original email.</b></span><p style="line-height:
12pt;"><spanstyle="font-family: 'Helv'; font-size: 9.7pt; color: rgb(0, 0, 0);"><b>For more information, please refer
to<a class="moz-txt-link-freetext"
href="http://www.shropshire.gov.uk/privacy.nsf">http://www.shropshire.gov.uk/privacy.nsf</a></b></span><p><span
style="font-family:'Courier New'; font-size:
8pt;">******************************************************************************</span><p><spanstyle="font-family:
'CourierNew'; font-size: 8pt;"> </span></blockquote><br />
You should always salt your password hashes.
Ie randomly generate a salt string, the store this and the password hash:
insert into auth (user_id, salt, password) values (1,'blah',md5('blah' + 'test')) ;
then to check the password
select true from auth where user_id = 1 and password = md5( salt + 'test') ;
I tend to set a trigger function to auto generate a salt and hash the password.
If you want to be really secure, use both a md5 and sha1 hash, snice it has been proved you can generate hash collisions so you could use:
insert into auth (user_id, salt, password) values (1,'blah',md5('blah' || 'test') || sha1('blah' || 'test')) ;
then to check the password
select true from auth where user_id = 1 and password = md5( salt || 'test') || sha1( salt || 'test') ;
Chris Ellis
| "Raymond C. Rodgers" <sinful622@gmail.com> Sent by: pgsql-general-owner@postgresql.org 04/02/2009 14:34 |
|
Iñigo Barandiaran wrote:
Thanks!
Ok. I've found http://256.com/sources/md5/ library. So the idea is to define in the dataBase a Field of PlainText type. When I want to insert a new user, I define a password, convert to MD5 hash with the library and store it in the DataBase. Afterwards, any user check should get the content of the DataBase of do the inverse process with the library. Is it correct?
Thanks so much!!!!!!
Best,
Well, you can use the built-in md5 function for this purpose. For instance, you could insert a password into the table with a statement like:
insert into auth_data (user_id, password) values (1, md5('test'));
And compare the supplied password with something like:
select true from auth_data where user_id = 1 and password = md5('test');
You don't need to depend on an external library for this functionality; it's built right into Postgres. Personally, in my own apps I write in PHP, I use a combination of sha1 and md5 to hash user passwords, without depending on Postgres to do the hashing, but the effect is basically the same.
Raymond
******************************************************************************
If you are not the intended recipient of this email please do not send it on
to others, open any attachments or file the email locally.
Please inform the sender of the error and then delete the original email.
For more information, please refer to http://www.shropshire.gov.uk/privacy.nsf
******************************************************************************
On Wed, Feb 04, 2009 at 04:42:05PM +0000, Chris.Ellis@shropshire.gov.uk wrote:
> If you want to be really secure, use both a md5 and sha1 hash, snice it
> has been proved you can generate hash collisions so you could use:
>
> insert into auth (user_id, salt, password) values
> (1,'blah',md5('blah' || 'test') || sha1('blah' || 'test')) ;
That sounds like a really *bad* idea to me; you've just given an
attacker two choices, MD5 is currently easier to attack than SHA1 but
that may change. If an attacker can find a password that hashes to the
same thing using one hash there's a reasonable chance it'll hash to the
same thing using the other and they will have broken your scheme (they
have 16 and 20 octets of state respectively, more than most passwords).
It would be much better just to use a stronger hash function to start
with.
--
Sam http://samason.me.uk/
> On Wed, Feb 04, 2009 at 09:34:56AM -0500, Raymond C. Rodgers wrote: > > You don't need to depend on an external library for this > functionality; > > it's built right into Postgres. Personally, in my own apps I write in > > PHP, I use a combination of sha1 and md5 to hash user passwords, > > without depending on Postgres to do the hashing, but the effect is > > basically the same. > > Doing the hashing outside PG would reduce the chance of the password > being exposed, either accidentally by, say, turning on statement > logging, or maliciously. A general rule with passwords is to throw > away > any copy of a plain text password as quickly as possible, sending the > password over to another process would go against this. > Agreed. Another benefit of this is the hashing support in PHP is more flexible. I personally use the hash() function to get a SHA-256 hash instead of the weaker sha1 or md5.