SHA1 on postgres 8.3

On Sun, Jan 20, 2008 at 12:21:01AM -0800, Jon Hancock wrote:

I am trying to port a MySQL db to postgres 8.3rc1.
In MySQL I have a function SHA1, which is critical for storing and
authenticating passwords.
I see some old posts on how to add this function myself. see:
http://raveica.comdurav.com/blog/programming/how-do-i-add-sha1-to-postgresql/

It's in contrib in the pgcrypto module. If you use a distribution you
can usually simply just install it.

http://www.postgresql.org/docs/8.3/static/pgcrypto.html

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

In MySQL I have a function SHA1, which is critical for storing and
authenticating passwords.

It sure would be nice to have this in core. Yeah, there's pgcrypto,
but it's a bit overkill for people who simply want to do a SHA1,
especially when they see we already have a md5(). I also realize that
SHA1 is not a great solution these days either, but I'd at least like
to see a discussion on moving Postgres to somewhere between
"only has md5()" and "all pg_crypto functions inside core", even if
it only means a handful of SHA functions. Moving this over to -hackers.

In summary: what would objections be to my writing a sha1() patch?

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200801201218
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFHk4NIvJuQZxSWSsgRA83ZAJ0SIk36sYvLF30q7hog2sBaQU1+LACeOv15
WkDwrzgzHyrwmNFP85plbBA=
=6P4y
-----END PGP SIGNATURE-----

Greg Sabino Mullane wrote:

I also realize that SHA1 is not a great solution these days either,
but I'd at least like to see a discussion on moving Postgres to
somewhere between "only has md5()" and "all pg_crypto functions inside
core", even if it only means a handful of SHA functions. Moving this
over to -hackers.

In summary: what would objections be to my writing a sha1() patch?

Isn't sha1 considered broken for some uses anyway? Perhaps if you're
going to do that it would make sense to move the whole pgcrypto/sha2.c
stuff to core, I think.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Sun, Jan 20, 2008 at 05:24:11PM -0000, Greg Sabino Mullane wrote:

It sure would be nice to have this in core. Yeah, there's pgcrypto,
but it's a bit overkill for people who simply want to do a SHA1,
especially when they see we already have a md5().

md5() was added with the following commit message:

Attached are two small patches to expose md5 as a user function --
including documentation and regression test mods. It seemed small and
unobtrusive enough to not require a specific proposal on the hackers
list -- but if not, let me know and I'll make a pitch. Otherwise, if
there are no objections please apply.

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/varlena.c#rev1.94

In summary: what would objections be to my writing a sha1() patch?

There wasn't any discussion about it last time. It does seem a bit
wierd to support one but not the other. It's also interesting to note
that the implementation in the backed is commented with:

I do not expect this file to be used for general purpose MD5'ing of
large amounts of data, only for generating hashed passwords from
limited input.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Alvaro Herrera wrote:

Greg Sabino Mullane wrote:

I also realize that SHA1 is not a great solution these days either,
but I'd at least like to see a discussion on moving Postgres to
somewhere between "only has md5()" and "all pg_crypto functions inside
core", even if it only means a handful of SHA functions. Moving this
over to -hackers.

In summary: what would objections be to my writing a sha1() patch?

Isn't sha1 considered broken for some uses anyway? Perhaps if you're
going to do that it would make sense to move the whole pgcrypto/sha2.c
stuff to core, I think.

IIRC not anymore than md5, which we already do...

That said, it would make sense to include sha1() for compatibility
reasons and a stronger sha for people that need something better.

//Magnus

"Greg Sabino Mullane" <greg@turnstep.com> writes:

In summary: what would objections be to my writing a sha1() patch?

Mainly that no one else is dissatisfied with the current split between
core and pgcrypto.

The only reason md5() is in core is to support encryption of passwords
in pg_shadow. There are good reasons not to have any more crypto
capability in core than we absolutely have to; mainly to do with
benighted laws in some countries.

regards, tom lane

Martijn van Oosterhout wrote:

In summary: what would objections be to my writing a sha1() patch?

There wasn't any discussion about it last time. It does seem a bit
wierd to support one but not the other. It's also interesting to note
that the implementation in the backed is commented with:

I proposed md5 without sha1 because we already had md5 code in the
backend, and we did not have sha1 (and still don't). At the time I was
afraid that if I proposed sha1 as well it would become a debate and we
would have ended up with neither.

Personally I'm in favor of having sha1 and one or more of the newer
replacements in the backend. I'd also like to see HMAC built in. But I
think we need to be careful about running afoul of various export
regulations. Keeping the crypto stuff separate allows distributions to
leave the crypto out if they need to. Perhaps cryptographic hashes/HMAC
are not an issue though. Anyone know?

Joe

On Sun, Jan 20, 2008 at 01:42:21PM -0500, Tom Lane wrote:

"Greg Sabino Mullane" <greg@turnstep.com> writes:

In summary: what would objections be to my writing a sha1() patch?

Mainly that no one else is dissatisfied with the current split
between core and pgcrypto.

The only reason md5() is in core is to support encryption of
passwords in pg_shadow. There are good reasons not to have any more
crypto capability in core than we absolutely have to; mainly to do
with benighted laws in some countries.

Is there any country with laws so benighted that they restrict secure
hashing algorithms? Right now, there's a contest between SHA1 and
MD5 as to which one gets broken first, and SHA1 appears to be in the
lead. SHAn for n>1 could preempt the awfulness of losing this race.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

* David Fetter:

Is there any country with laws so benighted that they restrict secure
hashing algorithms? Right now, there's a contest between SHA1 and
MD5 as to which one gets broken first, and SHA1 appears to be in the
lead. SHAn for n>1 could preempt the awfulness of losing this race.

MD5 is broken in the sense that you can create two or more meaningful
documents with the same hash. This is not currently possible for
SHA-1 (at least no one has publicly demonstrated this capability).
SHA-256 etc. are sufficiently similar to MD5 and SHA-1, so it's not
clear if they add significant additional security.

(Sorry if this is what you've said.)

--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

On 1/20/08, Greg Sabino Mullane <greg@turnstep.com> wrote:

In summary: what would objections be to my writing a sha1() patch?

Well.

If you do start adding hashes to core then _please_ pick a path
that allows having all the standard hashes in advance. That means
both md5 and sha-1, sha2 (4 hashes) and there is also sha-3 in the
horizon.

Basically there seems to be 2 variants:

1) Continue the md5() style: md5(), sha1(), sha224(), sha256(),
sha384(), sha512(), plus another 4 for SHA-3.

2) Move hashing functions from pgcrypto to core. That means
digest() and I would suggest hmac() and crypt() too.
I'm also starting to think it may be worth having hexdigest().

I prefer 2). There is some common infrastructure in pgcrypto,
the hash specific parts can be either split out or rewritten
from scratch, hashes need very small amount of code.

I agree that having all of pgcrypto in core is bit overkill,
so please don't think of it as all-or-nothing affair.

--
marko

Florian Weimer <fweimer@bfk.de> writes:

* David Fetter:

Is there any country with laws so benighted that they restrict secure
hashing algorithms? Right now, there's a contest between SHA1 and
MD5 as to which one gets broken first, and SHA1 appears to be in the
lead. SHAn for n>1 could preempt the awfulness of losing this race.

MD5 is broken in the sense that you can create two or more meaningful
documents with the same hash.

Note that this isn't actually very interesting for the purpose for
which the md5() function was put into core: namely, hashing passwords
before they are stored in pg_authid. pg_authid is already secured
against viewing by non-superusers, and a superuser can crack into
your database account anyway, nyet? So the only reason we bother
with hashing here is to keep a superuser from finding out your cleartext
password, which might possibly let him crack into non-database services
that you foolishly used the same password for. Therefore, it doesn't
really matter if he can find another password with the same hash ---
that's not guarding against anything interesting.

Of course, if you want to store other sorts of protected stuff in the
database, you might not want md5, but at that point you're a candidate
to use contrib/pgcrypto.

regards, tom lane

* Tom Lane:

MD5 is broken in the sense that you can create two or more meaningful
documents with the same hash.

Note that this isn't actually very interesting for the purpose for
which the md5() function was put into core: namely, hashing passwords
before they are stored in pg_authid.

No doubt about that. But there are checklists out there, and if you
use MD5 at some point, you need to go to some lengths to explain that
it's okay. That's why I can understand the desire to have sha1 easily
available (even though SHA-1 isn't much better, really, and the
difference doesn't actually matter for many application).

It's a bit like justifying that you don't need a virus scanner on your
non-Windows server or database server. 8-P

BTW, I'd like to see MD5/SHA-1 for BYTEA, not just TEXT, and with a
BYTEA return value. Does pgcrypto provide that?
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

On 1/21/08, Florian Weimer <fweimer@bfk.de> wrote:

BTW, I'd like to see MD5/SHA-1 for BYTEA, not just TEXT, and with a
BYTEA return value. Does pgcrypto provide that?

Yes.

--
marko

On 1/21/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:

MD5 is broken in the sense that you can create two or more meaningful
documents with the same hash.

Note that this isn't actually very interesting for the purpose for
which the md5() function was put into core: namely, hashing passwords
before they are stored in pg_authid.

Note: this was bad idea. The function that should have been
added to core would be pg_password_hash(username, password).

Adding md5() lessens incentive to install pgcrypto or push/accept
digest() into core and gives impression there will be sha1(), etc
in the future.

Now users who want to store passwords in database (the most
popular usage) will probably go with md5() without bothering
with pgcrypto. They probably see "Postgres itself uses MD5 too",
without realizing their situation is totally different from
pg_authid one.

It's like we have solution that is ACID-compliant 99% of the time in core,
so why bother with 100% one.

--
marko

Martijn van Oosterhout wrote:

On Sun, Jan 20, 2008 at 12:21:01AM -0800, Jon Hancock wrote:

I am trying to port a MySQL db to postgres 8.3rc1.
In MySQL I have a function SHA1, which is critical for storing and
authenticating passwords.
I see some old posts on how to add this function myself. see:
http://raveica.comdurav.com/blog/programming/how-do-i-add-sha1-to-postgresql/

It's in contrib in the pgcrypto module. If you use a distribution you
can usually simply just install it.

http://www.postgresql.org/docs/8.3/static/pgcrypto.html

Have a nice day,

After you installed pgcrypto.so, just create a small function:

CREATE OR REPLACE FUNCTION sha1(text) RETURNS text AS '
SELECT
ENCODE(DIGEST($1, ''sha1"),"hex") AS result
' LANGUAGE 'SQL';

--
Regards,

Julio Cesar S�nchez Gonz�lez.

--
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.

www.sistemasyconectividad.com.mx http://darkavngr.blogspot.com/

I am not thrilled about moving _some_ of pgcrypto into the backend ---
pgcrypto right now seems well designed and if we pull part of it out it
seems it will be less clear than what we have now. Perhaps we just need
to document that md5() isn't for general use and some function in
pgcrypto should be used instead?

---------------------------------------------------------------------------

Marko Kreen wrote:

On 1/21/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:

MD5 is broken in the sense that you can create two or more meaningful
documents with the same hash.

Note that this isn't actually very interesting for the purpose for
which the md5() function was put into core: namely, hashing passwords
before they are stored in pg_authid.

Note: this was bad idea. The function that should have been
added to core would be pg_password_hash(username, password).

Adding md5() lessens incentive to install pgcrypto or push/accept
digest() into core and gives impression there will be sha1(), etc
in the future.

Now users who want to store passwords in database (the most
popular usage) will probably go with md5() without bothering
with pgcrypto. They probably see "Postgres itself uses MD5 too",
without realizing their situation is totally different from
pg_authid one.

It's like we have solution that is ACID-compliant 99% of the time in core,
so why bother with 100% one.

--
marko

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

I am not thrilled about moving _some_ of pgcrypto into the backend ---
pgcrypto right now seems well designed and if we pull part of it out it
seems it will be less clear than what we have now. Perhaps we just need
to document that md5() isn't for general use and some function in
pgcrypto should be used instead?

I think looking at this as putting some of pg_crypto into core is looking
at this the wrong way. We are never going to put the whole thing into
core given the current state of cryptography laws, as obviously the
current status of giving users md5() and nothing else is not ideal. What
we're looking for is a middle ground. It seems to me we've narrowed
it down to two questions:

1) Does sha1(), or other hashing algorithms risk running afoul of
cryptography regulations?

I'm 100% sure that sha1() itself is not a problem (it's even a PHP builtin,
and good luck finding a box these days wihout that monstrosity installed).
I highly doubt any of the rest (SHA*, HMAC, etc.) are a problem either:
we're doing a one-way hash, not encrypting data. But common sense and
cryptography have seldom been seen together since the start of the cold war,
so I'll hold my final judgement.

2) Which ones do we include?

Putting sha1() seems a no-brainer, but as Joe points out, why not add all
the rest in at the same time?

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200801281506
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFHnjeJvJuQZxSWSsgRA2fWAKCljvbj5BVaFQ5mEDvckNGhVz6rDgCg0DRc
zaIu/rT1vdDrL61JTsXdIZ8=
=7DKm
-----END PGP SIGNATURE-----

On 1/28/08, Greg Sabino Mullane <greg@turnstep.com> wrote:

I am not thrilled about moving _some_ of pgcrypto into the backend ---
pgcrypto right now seems well designed and if we pull part of it out it
seems it will be less clear than what we have now. Perhaps we just need
to document that md5() isn't for general use and some function in
pgcrypto should be used instead?

I don't think docs will help much. The md5() has broken the
policy of "all crypto in external module" so I'm now thinking
we should just bite the bullet and add digest() to core.

Exctracting the part from pgcrypto is no-brainer, the hashes have
very thin wrapper around them, only thing common with rest of
pgcrypto is error handling, which hashes use the least.

Only thing that needs minor thought is that it would be nice
to access hashes from external module - that means exporting
the find_digest() function to it. Which should be no problem.

I think looking at this as putting some of pg_crypto into core is looking
at this the wrong way. We are never going to put the whole thing into
core given the current state of cryptography laws, as obviously the
current status of giving users md5() and nothing else is not ideal. What
we're looking for is a middle ground. It seems to me we've narrowed
it down to two questions:

1) Does sha1(), or other hashing algorithms risk running afoul of
cryptography regulations?

I'm 100% sure that sha1() itself is not a problem (it's even a PHP builtin,
and good luck finding a box these days wihout that monstrosity installed).
I highly doubt any of the rest (SHA*, HMAC, etc.) are a problem either:
we're doing a one-way hash, not encrypting data. But common sense and
cryptography have seldom been seen together since the start of the cold war,
so I'll hold my final judgement.

2) Which ones do we include?

Putting sha1() seems a no-brainer, but as Joe points out, why not add all
the rest in at the same time?

Considering we have DES and MD5 already, then how can SHA2 be
a problem when SHA1 isnt?

--
marko

* Bruce Momjian:

I am not thrilled about moving _some_ of pgcrypto into the backend ---
pgcrypto right now seems well designed and if we pull part of it out it
seems it will be less clear than what we have now. Perhaps we just need
to document that md5() isn't for general use and some function in
pgcrypto should be used instead?

Yes, that would probably help those folks doing checklist-based
security audits.

--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99