Madison Kelly schrieb:
Hi all,
Is it possible to take a string (ie: a user's password) and have
postgres encrypt the string before performing the query?
At the moment, I am using postgresql + postfix for email. I need to
save the passwords in clear text in the DB and I don't feel safe doing
that. I'd like to save the passwords as an SHA1 hash. Then when postfix
checks the password it uses a query that converts the passed password
into an SHA1 hash then performs the comparison.
So a pseudo code of what I'd like is:
SELECT foo FROM table WHERE passwd=sha1($password);
I did a search on postgres's website and the only reference to SHA1 I
saw was in the connection to psql. I hope this doesn't mean it's not
possible. I'm much less familiar with postfix and am hoping to avoid
mucking around with it. :P
You can use contrib/pgcrypto:
http://developer.postgresql.org/pgdocs/pgsql/contrib/pgcrypto/
contrib is often installed allong with postgres and contains
not (yet) core included extensions.
http://developer.postgresql.org/pgdocs/pgsql/contrib/pgcrypto/sql/sha1.sql
shows example usage.
HTH
Tino Wildenhain
