Thread: password function for PostgreSQL

password function for PostgreSQL

From
hodges@xprt.net
Date:
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@xprt.net or tom_hodges@yahoo.com
ICQ 10149621, YahooMessenger tom_hodges
Mail: 14314 SW Allen Blvd, #317; Beaverton OR 97005 USA

Re: password function for PostgreSQL

From
"Josh Berkus"
Date:
Hodges,

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

This is the recommended approach.  Further, you should do the actual
encryption call in an included php file (rather than the main login
page) for security.

If you are designing a PHP/Postgres application, I recommend that you
join the pgsql-php mailing list.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Attachment

Re: password function for PostgreSQL

From
Joel Burton
Date:
On Sun, 14 Oct 2001 hodges@xprt.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@xprt.net or tom_hodges@yahoo.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@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant