Обсуждение: encrypting a query string
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 Thanks!! Madison
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