Thread: How to encrypt data in Postgresql
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!
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:
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
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
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!
>
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.
-------------------------------------------------
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
> 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.