Thread: Manually authenticating users in pg_shadow

Is there a way, having a user entered username and password, to select a single row from pg_shadow which is using md5
passwordencryption? 

I'm currently authenticating web users (.asp page) with our own user table that uses crypt()  to store passwords. I'd
liketo remove our 'redundant' user table and use pg_shadow. The authentication function I'm using is included in the
attachment...

Thanks.

 <<auth_user.txt>>
William Harazim, Software Engineer, Fulco Inc. 973-627-2427, x129

"William Harazim" <wharazim@fulcoinc.com> writes:
> Is there a way, having a user entered username and password, to select a si=
> ngle row from pg_shadow which is using md5 password encryption?

I think what you need to know is that the stored passwd field is formed
thus:

    'md5' || md5(password || username);

Substitute this for your crypt() call and you're set.  Don't think you
need the separate step to extract salt (you didn't need it before
either, really).

            regards, tom lane

Ahh, the password || username format of the stored password was the problem. Incidentally, for anyone else not having
themd5() function (is that new to 7.5dev?) I was able to accomplish the same thing using  

   'md5' || encode( digest(password || username, 'md5'), 'hex' )

Thanks!


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, January 27, 2004 7:55 PM
To: William Harazim
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Manually authenticating users in pg_shadow


"William Harazim" <wharazim@fulcoinc.com> writes:
> Is there a way, having a user entered username and password, to select a si=
> ngle row from pg_shadow which is using md5 password encryption?

I think what you need to know is that the stored passwd field is formed
thus:

    'md5' || md5(password || username);

Substitute this for your crypt() call and you're set.  Don't think you
need the separate step to extract salt (you didn't need it before
either, really).

            regards, tom lane

"William Harazim" <wharazim@fulcoinc.com> writes:
> Ahh, the password || username format of the stored password was the problem. Incidentally, for anyone else not having
themd5() function (is that new to 7.5dev?) I was able to accomplish the same thing using  
>    'md5' || encode( digest(password || username, 'md5'), 'hex' )

md5() is in 7.4, but I think it's new in that release.

            regards, tom lane