Re: encrypting a query string

From: Tino Wildenhain <tino(at)wildenhain(dot)de>
To: Madison Kelly <linux(at)alteeve(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: encrypting a query string
Date: 2007-07-16 15:06:16
Message-ID: 469B8968.3000804@wildenhain.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2007-07-16 15:14:31 Re: pg_dump without blobs
Previous Message Vivek Khera 2007-07-16 15:00:58 Re: pg_dump without blobs