Re: Functions in postgres

On Thu, Apr 06, 2000 at 04:48:02PM -0500, Jeff Gerhart wrote:
> Ross,
> Thats for the input. Have a couple of additional questions:
>
> 1- I have the code running and it appears that I get a different result each
> time I sqlcrypt the same string e.g. select sqlcrypt('xyz') gives me a
> different encryption each time I execute it. Pardon my ignorance, but how
> does unix validate a password i.e. compare the results of crypt'd inout
> password against the previously crypt'd password.

It's selecting a random 'salt' each time, since you're not passing it
one. The first two characters of the hash are the salt, so, for example:

reedstrm=> select sqlcrypt('secret');
sqlcrypt
-------------
PfB9b6nH6QgbA
(1 row)

reedstrm=> select sqlcrypt('secret');
sqlcrypt
-------------
acI.WsXmTid6k
(1 row)

reedstrm=> select sqlcrypt('secret','Pf');
sqlcrypt
-------------
PfB9b6nH6QgbA
(1 row)

So, if we pass in the salt, we get the same hash back. That's what the
example select from my original post was for: it includes the salt:

SELECT * FROM "Personnel" WHERE "PerUsername" = 'RJReedstrom' AND
  "PerPassword" = sqlcrypt('secret',substr("PerPassword",1,2));

This will return the row IFF the the submitted password 'secret' is
the same as the password that was originally hashed in. Note that any
encryption strategy that relys on the backend DB to do the encryption
is on the wrong end of the network link! The password still goes clear
text to the database, and is visible in the postgresql logs, if you
log queries.  But it _does_ keep me from seeing all the bad passwords
people chose actually in my tables!

> 2- Is there something simple process I can use to encrypt text for storage
> in the database and then de-encrypt it later. I would assume I would need to
> maintain the key or seed I used for encryption of the string to de-encrypt
> it later.
>

I'm not really an encryption expert, but what your looking for is a
reversible encryption algorithm. Some suggestions of the top of my head:
SHA, Blowfish, Hmm, can't think of any more. Note my caveat above,
about what's on the wire.  You _really_ want the client app to do the
encrypting/decrypting, not the backend.


Ross
P.S. I copied GENERAL on this, just so there's a record with the
original post.

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005