Thread: How to encrypt data in Postgresql

How to encrypt data in Postgresql

From
"Terence Chang"
Date:
Hi there:
 
I know this might be an easy answer, but I was unable to figure out the solution.
 
I would like to encrypt a password field in the table. I could not figure out how phpPGAdmin did.
 
Should I use PHP's MD5 to encrypt the password? Is there a function in PostgreSQL that can encrypt the data with MD5?
 
I would like to encrypt the data in PostgreSQL, so other program can use the same function. Can anyone give me some hints? What key word should I search in the document?
 
Thansk!
 

Re: How to encrypt data in Postgresql

From
Franco Bruno Borghesi
Date:
You must install pgcrypto (its in your contrib/pgcrypto directory).

Then, the functions crypt and gen_salt will become available.

As an example, to insert a new user (peter) with an encrypted password (1234) you can do:
INSERT INTO myUsers(name, pass) VALUES ('peter', crypt('1234', gen_salt('md5'));

To verify that <anypassword> is OK:
SELECT (<anypassword>=crypt(<anypassword>, pass)) WHERE name='peter';

The package includes many other functions, listed in README.pgcrypto.

On Thu, 2003-07-24 at 15:18, Terence Chang wrote:
Hi there:
 
I know this might be an easy answer, but I was unable to figure out the solution.
 
I would like to encrypt a password field in the table. I could not figure out how phpPGAdmin did.
 
Should I use PHP's MD5 to encrypt the password? Is there a function in PostgreSQL that can encrypt the data with MD5?
 
I would like to encrypt the data in PostgreSQL, so other program can use the same function. Can anyone give me some hints? What key word should I search in the document?
 
Thansk!
 
Attachment

Re: How to encrypt data in Postgresql

From
Richard Welty
Date:
On Thu, 24 Jul 2003 11:18:03 -0700 Terence Chang <TChang@nqueue.com> wrote:
> Should I use PHP's MD5 to encrypt the password? Is there a function in
> PostgreSQL that can encrypt the data with MD5?

look in the contrib subdirectory (on rh8.0, /usr/share/pgsql/contrib/) for
pgcrypto.sql

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

Re: How to encrypt data in Postgresql

From
Douglas Trainor
Date:
biguns> psql dados
Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

dados=# select encode(digest('texto', 'md5'), 'hex');
              encode
----------------------------------
 62059a74e9330e9dc2f537f712b8797c
(1 row)

Of course, you'd probably have a field instead of 'texto', but you get
the idea.
And you will first need to suck in the contributed code from Marko Kreen
and the WIDE Project, which if you have the source will be in the directory
postgresql-7.3.3/contrib/pgcrypto

There are many variations on how you can use md5.  You could do it in PHP,
or you could do it closer to the database...

    douglas

Terence Chang wrote:

> Hi there:
>
> I know this might be an easy answer, but I was unable to figure out
> the solution.
>
> I would like to encrypt a password field in the table. I could not
> figure out how phpPGAdmin did.
>
> Should I use PHP's MD5 to encrypt the password? Is there a function in
> PostgreSQL that can encrypt the data with MD5?
>
> I would like to encrypt the data in PostgreSQL, so other program can
> use the same function. Can anyone give me some hints? What key word
> should I search in the document?
>
> Thansk!
>





Re: How to encrypt data in Postgresql

From
"Reuben D. Budiardja"
Date:
On Thursday 24 July 2003 02:59 pm, Franco Bruno Borghesi wrote:
> You must install pgcrypto (its in your contrib/pgcrypto directory).
>
> Then, the functions crypt and gen_salt will become available.
>
> As an example, to insert a new user (peter) with an encrypted password
> (1234) you can do:
> INSERT INTO myUsers(name, pass) VALUES ('peter', crypt('1234',
> gen_salt('md5'));
>
> To verify that <anypassword> is OK:
> SELECT (<anypassword>=crypt(<anypassword>, pass)) WHERE name='peter';

I think if you encrypt MD5 before storing it into the table, then there is no
way to retrieve the corresponding clear text right? since MD5 is one-way
encryption..

RDB


> The package includes many other functions, listed in README.pgcrypto.
>
> On Thu, 2003-07-24 at 15:18, Terence Chang wrote:
> > Hi there:
> >
> > I know this might be an easy answer, but I was unable to figure out
> > the solution.
> >
> > I would like to encrypt a password field in the table. I could not
> > figure out how phpPGAdmin did.
> >
> > Should I use PHP's MD5 to encrypt the password? Is there a function in
> > PostgreSQL that can encrypt the data with MD5?
> >
> > I would like to encrypt the data in PostgreSQL, so other program can
> > use the same function. Can anyone give me some hints? What key word
> > should I search in the document?
> >
> > Thansk!

--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN
-------------------------------------------------
/"\  ASCII Ribbon Campaign against HTML
\ /  email and proprietary format
 X   attachments.
/ \
-------------------------------------------------
Have you been used by Microsoft today?
Choose your life. Choose freedom.
Choose LINUX.
-------------------------------------------------


Re: How to encrypt data in Postgresql

From
Richard Welty
Date:
On Fri, 25 Jul 2003 09:33:30 -0400 "Reuben D. Budiardja" <techlist@voyager.phys.utk.edu> wrote:

> I think if you encrypt MD5 before storing it into the table, then there
> is no
> way to retrieve the corresponding clear text right? since MD5 is one-way
> encryption..

yes, but normally when doing passwords, one encrypts and compares the
encrypted form. being able to decrypt stored passwords is generally
considered to be a "bad thing".

this goes back to the earliest days of Un*x, at the very least. i know it
was standard in V7, it probably was standard in V6, and likely was being
done that way even before then (V7 is where my Un*x experience starts.)

one of the raps on Windows NT & friends is that the password hashes are
easily reversable, which means that if you manage to steal them, you're
well positioned to take ownership of the system.

but this is kind of OT for a postgresql list now...

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

Re: How to encrypt data in Postgresql

From
Benjamin Jury
Date:
> I think if you encrypt MD5 before storing it into the table,
> then there is no
> way to retrieve the corresponding clear text right? since MD5
> is one-way
> encryption..

MD5 is a hash, not encryption. MD5 creates a 16 byte hash of a set of data.
You can then use the hash to compare against a second piece of data to see
if they match. As you can not get back to the original data, its perfect for
passwords, etc.

From http://userpages.umbc.edu/~mabzug1/cs/md5/md5.html
[The MD5 algorithm] takes as input a message of arbitrary length and
produces as output a 128-bit "fingerprint" or "message digest" of the input.
It is conjectured that it is computationally infeasible to produce two
messages having the same message digest, or to produce any message having a
given prespecified target message digest. The MD5 algorithm is intended for
digital signature applications, where a large file must be "compressed" in a
secure manner before being encrypted with a private (secret) key under a
public-key cryptosystem such as RSA.