Обсуждение: migrate hashname function from 8.1.x to 8.4

On 01/11/2011 02:09 AM, Nicolas Garfinkiel wrote:
> Hi all,
>
> I’m trying to upgrade our pgsql from 8.1 to 8.4, but our system’s login
> uses the hashname() function in order to get the proper password
> validation. Now pgsql’s 8.4 hashname function is not compatible with
> 8.1’s function. Do you have any ideas how I can reproduce 8.1 function
> in 8.4?

In what regard is it 'not compatible' ?

Please provide output from 8.1 and 8.4 for one of the samples you're
using, along with an explanation of what's causing you problems.

--
Craig Ringer

Hi Craig,

Thank you for your reply!

well, what i mean is that hashname() function would return a different value
depending the pgsql version.

Consider this query (note that i'm using some extra implicit cast functions
here):

SELECT password FROM user WHERE password = hashname('AZALEA')
UNION ALL
SELECT hashname('AZALEA')::text;

in 8.1.x it returns:

"-588380923"
"-588380923"

whereas in 8.4 this query only returns:

"-10546138"

And tweaking the query to show the difference more clearly:

SELECT password FROM user WHERE login = 'SIST'
UNION ALL
SELECT hashname('AZALEA')::text;

this returns:

"-588380923"
"-10546138"

Am I missing something?

Just a side note: our goal with this upgrade is to improve our system's performance
and stability (which 8.1 is not) while not embarking in any kind of refactoring,
as plans are underway to develop a new system. what we are looking for is
just a quick fix, if there's such thing out there!

Any thoughts?

 Original Message --
>Date: Tue, 11 Jan 2011 10:25:59 +1100
>From: Craig Ringer <craig@postnewspapers.com.au>
>To: Nicolas Garfinkiel <nicolas.garfinkiel@genesis-manlab.com.ar>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] migrate hashname function from 8.1.x to 8.4
>
>
>On 01/11/2011 02:09 AM, Nicolas Garfinkiel wrote:
>> Hi all,
>>
>> I?m trying to upgrade our pgsql from 8.1 to 8.4, but our system?s login
>> uses the hashname() function in order to get the proper password
>> validation. Now pgsql?s 8.4 hashname function is not compatible with
>> 8.1?s function. Do you have any ideas how I can reproduce 8.1 function
>> in 8.4?
>
>In what regard is it 'not compatible' ?
>
>Please provide output from 8.1 and 8.4 for one of the samples you're
>using, along with an explanation of what's causing you problems.
>
>--
>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

On 01/11/2011 03:02 PM, nicolas.garfinkiel@genesis-manlab.com.ar wrote:

> well, what i mean is that hashname() function would return a different value
> depending the pgsql version.

Yep. It seems to be an undocumented function with no particular defined
behaviour. This isn't especially surprising. I personally wish such
functions were in a different schema or were prefixed with something
like "pg_int_" ... but they're not.

> SELECT password FROM user WHERE password = hashname('AZALEA')

!!WTF!!

I hope you're not trying to use this as a secure one-way hash function.
If so, find whoever decided to do so, and smack them. This function is
intended for fast hashing for clustering things into random buckets, and
isn't in any way intended to be even vaguely suitable for security use.

See:

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

Unlike the hashname() function, crypt() etc are well-defined functions
with well-understood, stable results compatible not only between Pg
versions but also between Pg and other software.

> Just a side note: our goal with this upgrade is to improve our system's performance
> and stability (which 8.1 is not) while not embarking in any kind of refactoring,
> as plans are underway to develop a new system. what we are looking for is
> just a quick fix, if there's such thing out there!

Fix your app to use pgcrypto. Use a digest function from pgcrypto,
storing both password digest and password salt. For that matter even
unsalted use of md5() would be better, or crypt() with hardcoded salt,
though both are pretty shocking.


Failing that, extract hashname() from 8.1 and bundle it into a loadable
C function module compatible with 8.1 and 8.4. Call your function
"myhash()" or something, and use it instead of hashname() in both versions.

A look at at hashname() in 9.0 shows it living in
src/backend/access/hash/hashfunc.c . It calls hash_any(...) in the same
file to do the real work. As it seems pretty well contained, assuming
the 8.1 implementation is similar it should be easy to adapt to use as a
loadable module. I haven't checked 8.1's code to check.

See the tutorial for help on how to implement C extension modules.

Personally I think you're way better off fixing your app to use pgcrypto
and a decent hash function.

--
Craig Ringer

Indeed Craig, I would like to smack the guy as well, for this and many many other creepy crawlers in my everyday job!

Violence aside, thanks for your answer. The C module is what I was planning to do, but was not sure if there is another
way.Of course using crypt would be the right thing to do, but I cannot afford it, as users from our system can barely
remembertheir password, let alone collect them and recreate them! Of course I could reset their pwds, but that's gonna
bea hard sell to my boss. 

Cheers and thanks again!



On 11/01/2011, at 07:29, Craig Ringer <craig@postnewspapers.com.au> wrote:

> On 01/11/2011 03:02 PM, nicolas.garfinkiel@genesis-manlab.com.ar wrote:
>
>> well, what i mean is that hashname() function would return a different value
>> depending the pgsql version.
>
> Yep. It seems to be an undocumented function with no particular defined behaviour. This isn't especially surprising.
Ipersonally wish such functions were in a different schema or were prefixed with something like "pg_int_" ... but
they'renot. 
>
>> SELECT password FROM user WHERE password = hashname('AZALEA')
>
> !!WTF!!
>
> I hope you're not trying to use this as a secure one-way hash function. If so, find whoever decided to do so, and
smackthem. This function is intended for fast hashing for clustering things into random buckets, and isn't in any way
intendedto be even vaguely suitable for security use. 
>
> See:
>
>  http://www.postgresql.org/docs/current/static/pgcrypto.html
>
> Unlike the hashname() function, crypt() etc are well-defined functions with well-understood, stable results
compatiblenot only between Pg versions but also between Pg and other software. 
>
>> Just a side note: our goal with this upgrade is to improve our system's performance
>> and stability (which 8.1 is not) while not embarking in any kind of refactoring,
>> as plans are underway to develop a new system. what we are looking for is
>> just a quick fix, if there's such thing out there!
>
> Fix your app to use pgcrypto. Use a digest function from pgcrypto, storing both password digest and password salt.
Forthat matter even unsalted use of md5() would be better, or crypt() with hardcoded salt, though both are pretty
shocking.
>
>
> Failing that, extract hashname() from 8.1 and bundle it into a loadable C function module compatible with 8.1 and
8.4.Call your function "myhash()" or something, and use it instead of hashname() in both versions. 
>
> A look at at hashname() in 9.0 shows it living in src/backend/access/hash/hashfunc.c . It calls hash_any(...) in the
samefile to do the real work. As it seems pretty well contained, assuming the 8.1 implementation is similar it should
beeasy to adapt to use as a loadable module. I haven't checked 8.1's code to check. 
>
> See the tutorial for help on how to implement C extension modules.
>
> Personally I think you're way better off fixing your app to use pgcrypto and a decent hash function.
>
> --
> Craig Ringer

I’m trying to upgrade our pgsql from 8.1 to 8.4, but our system’s login uses the hashname() function in order to get the proper password validation. Now pgsql’s 8.4 hashname function is not compatible with 8.1’s function. Do you have any ideas how I can reproduce 8.1 function in 8.4?

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

Nicolas Garfinkiel wrote:

I’m trying to upgrade our pgsql from 8.1 to 8.4, but our system’s login uses the hashname() function in order to get the proper password validation. Now pgsql’s 8.4 hashname function is not compatible with 8.1’s function. Do you have any ideas how I can reproduce 8.1 function in 8.4?

https://github.com/petere/pgvihash provides the function you're looking for.

I agree with Craig's concerns here, but this may let you convert toward a better long-term format more easily.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

On 2011-01-11, =?utf-8?Q?Nicol=C3=A1s_Garfinkiel?= <nicolas.garfinkiel@gmail.com> wrote:

>  Violence aside, thanks for your answer. The C module is what I was
> planning to do, but was not sure if there is another way. Of course
> using crypt would be the right thing to do, but I cannot afford it, as
> users from our system can barely remember their password, let alone
> collect them and recreate them! Of course I could reset their pwds,
> but that's gonna be a hard sell to my boss.

Use a password logger to ease the trasition away from that old function.

Modify your application to collect new-style password hashes (created
using crypt() for example) when it calculates an old-style hash and
update the user record with the new hash (in a new column).

In 6 months time you'll have new hashes for everyone who uses the
application semi-regularly, the few that were missed by this should be
able to be resolved by your support team. or just send them an email
asking them to log into the application to confirm their user account.

--
⚂⚃ 100% natural