Re: password function for PostgreSQL

On Sun, 14 Oct 2001 hodges(at)xprt(dot)net wrote:

> Looking for a postgresql function similar to the password function in
> MySQL. In MySQL putting --password($password)-- in an insert query
> encrypts the value of $password that is stored in the table. You can
> use password($password) to compare a password entered by a user in a
> later query with the stored value of $password.
>
> This is not a password for a user registered in pg_shadow, rather it
> is to limit someone going in through a public web interface to only
> those records in a table that are associated with a certain login and
> password combination.
>
> The web interface is written with PHP so I may have to simply encrypt
> the password with a PHP function before storing or comparing it.
>
> Tom
> Tom Hodges, hodges(at)xprt(dot)net or tom_hodges(at)yahoo(dot)com

Tom --

Three possibilities, one straightforward and current, one better, and one
cool and bleeding
edge:

(1) the function crypt(text, text) can be used like the MySQL function
password(). You would use this like:

SELECT COUNT(*) FROM tblUser WHERE username='...' AND
password=CRPYT(<password>, <salt>)

crypt() takes two parameters--string to encrypt and salt. 'man 3 salt' on
a Unix box will give you the full story.

(2) crypt uses the crypt() algorithm, which is cryptographically weak. PG
can use stronger algorithms. Look in the contrib/ directory for pgcrypto,
which will let you use algorithms like md5, etc. (RPM users: install the
PostgreSQL-contribs RPM).

(3) (perhaps even better): you can use the new field type "chkpass", which
stores a hash of a password, which you can compare against. This has the
advantage that you don't have to call the CRYPT() or md5() functions;
instead you can do a normal comparison, like:

SELECT COUNT(*) FROM tblUser WHERE username='...' AND password='...'

and it will handle the crypting/unencrpying for you. This is a new type,
in the contribs/ directory of the 7.2devel version.

Good luck w/your web app, and HTH.

--

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant