field with Password

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:

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:

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,

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:

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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks Raymond !!!!<br>
<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">
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
I&ntilde;igo Barandiaran wrote:
<blockquote cite="mid:49897455.7080602@vicomtech.org" type="cite">Thanks!
<br>
<br>
<br>
Ok. I've found <a moz-do-not-send="true" class="moz-txt-link-freetext"
href="http://256.com/sources/md5/&quot;&gt;http://256.com/sources/md5/&lt;/a&gt;
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? <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 could insert 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>
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&nbsp; 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.<br>
<br>
Raymond<br>
</blockquote>
<br>
</body>
</html>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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">
<meta content="text/html;charset=ISO-8859-1"
http-equiv="Content-Type">
I&ntilde;igo Barandiaran wrote:
<blockquote cite="mid:49897455.7080602@vicomtech.org" type="cite"><br>
<br>
</blockquote>
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:<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>
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&nbsp; 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.<br>
<br>
Raymond<br>
</blockquote>
<br>
</blockquote>
<br>
</body>
</html>

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/

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

To
Iñigo Barandiaran <ibarandiaran@vicomtech.org>
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] field with Password

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
******************************************************************************

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks!<br>
<br>
This is great. I'm now implementing this functionality.<br>
<br>
Thank you all.<br>
<br>
You are great!<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">Ie randomly generate a salt string,
the store this and the password hash:</font>
<br>
<br>
<font face="sans-serif" size="2">&nbsp; &nbsp; &nbsp; &nbsp; 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">&nbsp; &nbsp; &nbsp; &nbsp; 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 hash the password.</font>
<br>
<br>
<br>
<br>
<font face="sans-serif" size="2">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:</font>
<br>
<br>
<font face="sans-serif" size="2">&nbsp; &nbsp; &nbsp; &nbsp; 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">&nbsp; &nbsp; &nbsp; &nbsp; select
true from auth where user_id = 1 and password = md5( salt || 'test')
&nbsp;||
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">&lt;sinful622@gmail.com&gt;</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><font face="sans-serif" size="1">04/02/2009 14:34</font>
</p>
</td>
<td width="59%">
<table width="100%">
<tbody>
<tr valign="top">
<td>
<div align="right"><font face="sans-serif" size="1">To</font></div>
</td>
<td><font face="sans-serif" size="1">I&ntilde;igo Barandiaran
<a class="moz-txt-link-rfc2396E" href="mailto:ibarandiaran@vicomtech.org">&lt;ibarandiaran@vicomtech.org&gt;</a></font>
</td>
</tr>
<tr valign="top">
<td>
<div align="right"><font face="sans-serif" size="1">cc</font></div>
</td>
<td><font face="sans-serif" size="1"><a class="moz-txt-link-abbreviated" href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a></font>
</td>
</tr>
<tr valign="top">
<td>
<div align="right"><font face="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&ntilde;igo Barandiaran wrote: </font>
<br>
<font size="3">Thanks! <br>
<br>
<br>
Ok. I've found </font><a moz-do-not-send="true"
href="http://256.com/sources/md5/&quot;&gt;&lt;font color="blue" size="3"><u>http://256.com/sources/md5/&lt;/u&gt;&lt;/font&gt;&lt;/a&gt;&lt;font
size="3">
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? <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 the table 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>
<font size="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, in my own apps I write in
PHP,
I &nbsp;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.<br>
<br>
Raymond</font>
<br>
<p><span style="font-family: 'Courier New'; font-size: 8pt;">******************************************************************************</span></p>
<p style="line-height: 12pt;"><span
style="font-family: 'Helv'; font-size: 9.7pt; color: rgb(0, 0, 0);"><b>If
you are not the intended recipient of this email please do not send it
on</b></span></p>
<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></p>
<p style="line-height: 12pt;"><span
style="font-family: 'Helv'; font-size: 9.7pt; color: rgb(0, 0, 0);"><b>Please
inform the sender of the error and then delete the original email.</b></span></p>
<p style="line-height: 12pt;"><span
style="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&quot;&gt;http://www.shropshire.gov.uk/privacy.nsf&lt;/a&gt;&lt;/b&gt;&lt;/span&gt;&lt;/p&gt;
<p><span style="font-family: 'Courier New'; font-size: 8pt;">******************************************************************************</span></p>
<p><span style="font-family: 'Courier New'; font-size: 8pt;">&nbsp;</span></p>
</blockquote>
<br>
</body>
</html>

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.