Thread

1. Data Encryption in PostgreSQL, and a Tutorial.

   Mike Cox <mikecoxlinux@yahoo.com> — 2004-04-09T20:31:38Z

   Has anyone created something like that for Postgresql?  It would be
   really handy to encrypt credit card numbers and other information so
   it stays secure.
   
   If no one has created anything such as this, I am going to code up
   something quite soon, but if it already exists, there is no need for
   me to reinvent the wheel, so speak up!  It is a law in places such as
   the EU that many types of data must be encrypted if the database is
   compromised.
   
   I will put up my solution in a few days if one does not exist.  But
   before I do that, I want to give a quick tutorial on how to create a
   file that will create tables, views and other such essentials.  Most
   people who use PostgreSQL just type in the commands in PostgreSQL, but
   that is not as easily portable or backed up as what I'm about to show
   you!
   
   1. open vi with a file.
   2.  Comments can be made as long as you add to slashes before the
   line:
   --this is a comment.
   3. Next just type in the SQL commands you want!
   4. after you are done, save the file.
   5. then just do this to create the database you made in the file:
   psql database_name < my_vi_file
   6.That is it!
   
   Here is a very simple sample of a file:
   
   --This is a sample file.  Use at your own risk.  No Warranties 
   --Written by Mike Cox, author of the *nix "hm" command.
   
   create table first(
   MYNUMBER INTEGER);
   
   create VIEW myview AS
   select * from first;
   
   --Ok this is the end.  As you can see it is very simple and portable.
   --Try it out. Here's how: psql your_database < this_file
   
   

2. Re: Data Encryption in PostgreSQL, and a Tutorial.

   Joshua D. Drake <jd@commandprompt.com> — 2004-04-10T00:35:17Z

   Hello,
   
   Actually I would use psql with the \e option. This would allow you to do 
   what you suggest but also
   allow you to stay within psql while you debug your statements. Then when 
   you are all done and
   you have used the appropriate amount of COMMENT ON statements, you can 
   just do a pg_dump -s
   and you are good to go.
   
   Sincerely,
   
   Joshua D. Drake
   
   
   Mike Cox wrote:
   
   >Has anyone created something like that for Postgresql?  It would be
   >really handy to encrypt credit card numbers and other information so
   >it stays secure.
   >
   >If no one has created anything such as this, I am going to code up
   >something quite soon, but if it already exists, there is no need for
   >me to reinvent the wheel, so speak up!  It is a law in places such as
   >the EU that many types of data must be encrypted if the database is
   >compromised.
   >
   >I will put up my solution in a few days if one does not exist.  But
   >before I do that, I want to give a quick tutorial on how to create a
   >file that will create tables, views and other such essentials.  Most
   >people who use PostgreSQL just type in the commands in PostgreSQL, but
   >that is not as easily portable or backed up as what I'm about to show
   >you!
   >
   >1. open vi with a file.
   >2.  Comments can be made as long as you add to slashes before the
   >line:
   >--this is a comment.
   >3. Next just type in the SQL commands you want!
   >4. after you are done, save the file.
   >5. then just do this to create the database you made in the file:
   >psql database_name < my_vi_file
   >6.That is it!
   >
   >Here is a very simple sample of a file:
   >
   >--This is a sample file.  Use at your own risk.  No Warranties 
   >--Written by Mike Cox, author of the *nix "hm" command.
   >
   >create table first(
   >MYNUMBER INTEGER);
   >
   >create VIEW myview AS
   >select * from first;
   >
   >--Ok this is the end.  As you can see it is very simple and portable.
   >--Try it out. Here's how: psql your_database < this_file
   >
   >---------------------------(end of broadcast)---------------------------
   >TIP 6: Have you searched our list archives?
   >
   >               http://archives.postgresql.org
   >  
   >
   
   
   -- 
   Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
   Postgresql support, programming shared hosting and dedicated hosting.
   +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
   PostgreSQL Replicator -- production quality replication for PostgreSQL
   
   
   

3. Re: Data Encryption in PostgreSQL, and a Tutorial.

   Chris Browne <cbbrowne@acm.org> — 2004-04-10T01:15:07Z

   In an attempt to throw the authorities off his trail, "T. Relyea" <nospam@nospam.com> transmitted:
   > MySQL has encryption and decryption functions built in, doesn't Postgresql?
   
   But of course.
   
   See the "pgcrypto" contrib module in the source tree.
   
   It is not typically compiled into what gets distributed with the
   typical Linux/BSD distribution because of the library dependencies
   that it forces in, as well as because the legalities surrounding the
   distribution of cryptographic software vary from country to country,
   making it potentially legally unsafe to ubiquitously include it.
   -- 
   let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
   http://www.ntlug.org/~cbbrowne/spreadsheets.html
   "If God  meant us to be vegetarians  why'd He make cows  out of meat?"
   -- seen on a bumper sticker
   
   

4. Re: Data Encryption in PostgreSQL, and a Tutorial.6

   Mike Nolan <nolan@gw.tssi.com> — 2004-04-10T01:45:17Z

   > Has anyone created something like that for Postgresql?  It would be
   > really handy to encrypt credit card numbers and other information so
   > it stays secure.
   
   Is there some reason you can't use contrib/pgcrypto?  I use it
   for storing passwords in an MD5 encryption and credit card data using 
   encrypt/decrypt, because I don't think it supports public/private 
   key encryption.
   --
   Mike Nolan
   
   

5. Re: Data Encryption in PostgreSQL, and a Tutorial.

   Jerry McBride <mcbrides9@comcast.net> — 2004-04-11T16:06:38Z

   T. Relyea wrote:
   
   > Mike Cox wrote:
   > 
   >> Has anyone created something like that for Postgresql?  It would be
   >> really handy to encrypt credit card numbers and other information so
   >> it stays secure.
   >> 
   >> If no one has created anything such as this, I am going to code up
   >> something quite soon, but if it already exists, there is no need for
   >> me to reinvent the wheel, so speak up!  It is a law in places such as
   >> the EU that many types of data must be encrypted if the database is
   >> compromised.
   >> 
   >> I will put up my solution in a few days if one does not exist.  But
   >> before I do that, I want to give a quick tutorial on how to create a
   >> file that will create tables, views and other such essentials.  Most
   >> people who use PostgreSQL just type in the commands in PostgreSQL, but
   >> that is not as easily portable or backed up as what I'm about to show
   >> you!
   >> 
   >> 1. open vi with a file.
   >> 2.  Comments can be made as long as you add to slashes before the
   >> line:
   >> --this is a comment.
   >> 3. Next just type in the SQL commands you want!
   >> 4. after you are done, save the file.
   >> 5. then just do this to create the database you made in the file:
   >> psql database_name < my_vi_file
   >> 6.That is it!
   >> 
   >> Here is a very simple sample of a file:
   >> 
   >> --This is a sample file.  Use at your own risk.  No Warranties
   >> --Written by Mike Cox, author of the *nix "hm" command.
   >> 
   >> create table first(
   >> MYNUMBER INTEGER);
   >> 
   >> create VIEW myview AS
   >> select * from first;
   >> 
   >> --Ok this is the end.  As you can see it is very simple and portable.
   >> --Try it out. Here's how: psql your_database < this_file
   > 
   > MySQL has encryption and decryption functions built in, doesn't
   > Postgresql?
   > 
   > Todd
   
   Obviously not... that's why we don't use it at work....
   
   -- 
   
   ******************************************************************************
                        Registered Linux User Number 185956
             http://groups.google.com/groups?hl=en&safe=off&group=linux
                Join me in chat at #linux-users on irc.freenode.net
   This email account no longers accepts attachments or messages containing
   html.
       12:26pm  up 35 days, 13:39,  2 users,  load average: 2.51, 2.56, 2.58
   
   

6. Re: Data Encryption in PostgreSQL, and a Tutorial.

   scott.marlowe <scott.marlowe@ihs.com> — 2004-04-12T15:20:43Z

   On Fri, 9 Apr 2004, Christopher Browne wrote:
   
   > In an attempt to throw the authorities off his trail, "T. Relyea" <nospam@nospam.com> transmitted:
   > > MySQL has encryption and decryption functions built in, doesn't Postgresql?
   > 
   > But of course.
   > 
   > See the "pgcrypto" contrib module in the source tree.
   > 
   > It is not typically compiled into what gets distributed with the
   > typical Linux/BSD distribution because of the library dependencies
   > that it forces in, as well as because the legalities surrounding the
   > distribution of cryptographic software vary from country to country,
   > making it potentially legally unsafe to ubiquitously include it.
   
   I thought md5() was a built-in nowadays...
   
   
   

7. Re: Data Encryption in PostgreSQL, and a Tutorial.

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-12T22:05:30Z

   "scott.marlowe" <scott.marlowe@ihs.com> writes:
   > On Fri, 9 Apr 2004, Christopher Browne wrote:
   >> See the "pgcrypto" contrib module in the source tree.
   >> 
   >> It is not typically compiled into what gets distributed with the
   >> typical Linux/BSD distribution because of the library dependencies
   >> that it forces in, as well as because the legalities surrounding the
   >> distribution of cryptographic software vary from country to country,
   >> making it potentially legally unsafe to ubiquitously include it.
   
   > I thought md5() was a built-in nowadays...
   
   Yeah, it is, but md5 is not considered cryptography because it is not
   reversible (you can't decrypt to get back what you put in).  As such
   it's not restricted under US munitions law, nor anyone else's that
   I've heard of.
   
   			regards, tom lane
   
   

8. Re: Data Encryption in PostgreSQL, and a Tutorial.

   scott.marlowe <scott.marlowe@ihs.com> — 2004-04-12T22:35:29Z

   On Mon, 12 Apr 2004, Tom Lane wrote:
   
   > "scott.marlowe" <scott.marlowe@ihs.com> writes:
   > > On Fri, 9 Apr 2004, Christopher Browne wrote:
   > >> See the "pgcrypto" contrib module in the source tree.
   > >> 
   > >> It is not typically compiled into what gets distributed with the
   > >> typical Linux/BSD distribution because of the library dependencies
   > >> that it forces in, as well as because the legalities surrounding the
   > >> distribution of cryptographic software vary from country to country,
   > >> making it potentially legally unsafe to ubiquitously include it.
   > 
   > > I thought md5() was a built-in nowadays...
   > 
   > Yeah, it is, but md5 is not considered cryptography because it is not
   > reversible (you can't decrypt to get back what you put in).  As such
   > it's not restricted under US munitions law, nor anyone else's that
   > I've heard of.
   
   True, but the original discussion, I believe, was on storing user 
   passwords etc... for which md5 is the preferred method...
   
   
   

9. Re: Data Encryption in PostgreSQL, and a Tutorial.

   Mike Nolan <nolan@gw.tssi.com> — 2004-04-13T00:32:23Z

   > True, but the original discussion, I believe, was on storing user 
   > passwords etc... for which md5 is the preferred method...
   
   I thought the original question was what to use for storing credit cards,
   for which you want a decryptable method.  (A public/private key method
   would be even better for credit card data IMHO, but I don't think pgcrypto
   includes one.)
   --
   Mike Nolan
   
   

10. Re: Data Encryption in PostgreSQL, and a Tutorial.

    Chris Browne <cbbrowne@acm.org> — 2004-04-13T01:33:08Z

    scott.marlowe@ihs.com ("scott.marlowe") wrote:
    > On Mon, 12 Apr 2004, Tom Lane wrote:
    >
    >> "scott.marlowe" <scott.marlowe@ihs.com> writes:
    >> > On Fri, 9 Apr 2004, Christopher Browne wrote:
    >> >> See the "pgcrypto" contrib module in the source tree.
    >> >> 
    >> >> It is not typically compiled into what gets distributed with the
    >> >> typical Linux/BSD distribution because of the library dependencies
    >> >> that it forces in, as well as because the legalities surrounding the
    >> >> distribution of cryptographic software vary from country to country,
    >> >> making it potentially legally unsafe to ubiquitously include it.
    >> 
    >> > I thought md5() was a built-in nowadays...
    >> 
    >> Yeah, it is, but md5 is not considered cryptography because it is not
    >> reversible (you can't decrypt to get back what you put in).  As such
    >> it's not restricted under US munitions law, nor anyone else's that
    >> I've heard of.
    >
    > True, but the original discussion, I believe, was on storing user 
    > passwords etc... for which md5 is the preferred method...
    
    No, the original discussion was about encrypting fields in the
    database, so MD5 doesn't cut it.
    
    Actually, for the purpose being pointed at, I would actually suggest
    that the Gentle User consider preferring that the database DOESN'T
    directly support encryption, because if it did, it would be tempting
    to pass encryption keys to the database, thereby COMPROMISING the
    security of the system.
    
    After all, suppose the database supports stored procedures of the
    form:
    
      encrypt(key, field) 
       and
      decrypt(key, field)
    
    Then an unscrupulous sysadmin type could replace them with alternative
    stored procedures that add in a couple of inserts...
    
      insert into nefarious_schema.keep_keys (id, key) values (nextval('my_keying'), key);
      insert into nefarious_schema.keep_field (id, field) values (currval('my_keying'), field);
    
    The data can only remain truly secure in the database if encryption
    and decryption don't even take place there.
    
    It is all well and nifty to throw encryption tools into the database,
    but this example quite clearly demonstrates that this is not a recipe
    for _improving_ security of the system...
    -- 
    "cbbrowne","@","ntlug.org"
    http://www3.sympatico.ca/cbbrowne/crypto.html
    "Computers double in speed every  18 months or so, so any "exponential
    time" problem  can be solved in  linear time by  waiting the requisite
    number of months  for the problem to become solvable  in one month and
    then starting the computation."  -- pratt@Sunburn.Stanford.EDU
    
    

11. Re: Data Encryption in PostgreSQL, and a Tutorial.

    Michal Hlavac <hlavki@medium13.sk> — 2004-04-13T09:38:07Z

    I think, that all is about key management. You can store your data with 
    strong RSA encryption. On server you will have only public key and on 
    client PC private key.
    
    it's not so easy to use, but it's more secure than symmetrical cipher.
    
    miso
    
    

12. Re: Data Encryption in PostgreSQL, and a Tutorial.

    scott.marlowe <scott.marlowe@ihs.com> — 2004-04-13T15:08:57Z

    On Mon, 12 Apr 2004, Christopher Browne wrote:
    
    > scott.marlowe@ihs.com ("scott.marlowe") wrote:
    > > On Mon, 12 Apr 2004, Tom Lane wrote:
    > >
    > >> "scott.marlowe" <scott.marlowe@ihs.com> writes:
    > >> > On Fri, 9 Apr 2004, Christopher Browne wrote:
    > >> >> See the "pgcrypto" contrib module in the source tree.
    > >> >> 
    > >> >> It is not typically compiled into what gets distributed with the
    > >> >> typical Linux/BSD distribution because of the library dependencies
    > >> >> that it forces in, as well as because the legalities surrounding the
    > >> >> distribution of cryptographic software vary from country to country,
    > >> >> making it potentially legally unsafe to ubiquitously include it.
    > >> 
    > >> > I thought md5() was a built-in nowadays...
    > >> 
    > >> Yeah, it is, but md5 is not considered cryptography because it is not
    > >> reversible (you can't decrypt to get back what you put in).  As such
    > >> it's not restricted under US munitions law, nor anyone else's that
    > >> I've heard of.
    > >
    > > True, but the original discussion, I believe, was on storing user 
    > > passwords etc... for which md5 is the preferred method...
    > 
    > No, the original discussion was about encrypting fields in the
    > database, so MD5 doesn't cut it.
    
    OK, thanks.  I think I got my threads cross-wired.
    
    > Actually, for the purpose being pointed at, I would actually suggest
    > that the Gentle User consider preferring that the database DOESN'T
    > directly support encryption, because if it did, it would be tempting
    > to pass encryption keys to the database, thereby COMPROMISING the
    > security of the system.
    
    I agree completely.  There's a new italian law that says that everything 
    in a database that's personal data has to be encrypted, and there was 
    another discussion on that.  Did you see that one go by?  Seems the law 
    isn't real clear on where encryption / decryption or key holding should 
    take place.
    
    
    

13. Re: Data Encryption in PostgreSQL, and a Tutorial.

    scott.marlowe <scott.marlowe@ihs.com> — 2004-04-19T20:40:40Z

    On Sun, 11 Apr 2004, Jerry McBride wrote:
    
    > T. Relyea wrote:
    > 
    > > Mike Cox wrote:
    > > 
    > >> Has anyone created something like that for Postgresql?  It would be
    > >> really handy to encrypt credit card numbers and other information so
    > >> it stays secure.
    > >> 
    > >> If no one has created anything such as this, I am going to code up
    > >> something quite soon, but if it already exists, there is no need for
    > >> me to reinvent the wheel, so speak up!  It is a law in places such as
    > >> the EU that many types of data must be encrypted if the database is
    > >> compromised.
    > >> 
    > >> I will put up my solution in a few days if one does not exist.  But
    > >> before I do that, I want to give a quick tutorial on how to create a
    > >> file that will create tables, views and other such essentials.  Most
    > >> people who use PostgreSQL just type in the commands in PostgreSQL, but
    > >> that is not as easily portable or backed up as what I'm about to show
    > >> you!
    > >> 
    > >> 1. open vi with a file.
    > >> 2.  Comments can be made as long as you add to slashes before the
    > >> line:
    > >> --this is a comment.
    > >> 3. Next just type in the SQL commands you want!
    > >> 4. after you are done, save the file.
    > >> 5. then just do this to create the database you made in the file:
    > >> psql database_name < my_vi_file
    > >> 6.That is it!
    > >> 
    > >> Here is a very simple sample of a file:
    > >> 
    > >> --This is a sample file.  Use at your own risk.  No Warranties
    > >> --Written by Mike Cox, author of the *nix "hm" command.
    > >> 
    > >> create table first(
    > >> MYNUMBER INTEGER);
    > >> 
    > >> create VIEW myview AS
    > >> select * from first;
    > >> 
    > >> --Ok this is the end.  As you can see it is very simple and portable.
    > >> --Try it out. Here's how: psql your_database < this_file
    > > 
    > > MySQL has encryption and decryption functions built in, doesn't
    > > Postgresql?
    > > 
    > > Todd
    > 
    > Obviously not... that's why we don't use it at work....
    
    let's see:
    
    su -
    cd /usr/local/src/postgresql-7.4.2
    cd contrib/pgcrypto
    make
    make install
    
    Total time taken: <30 seconds.
    
    If that's a make or break deal for you on a database I'd hate to go car 
    buying with you.
    
    
    

14. Re: Data Encryption in PostgreSQL, and a Tutorial.

    Dias Bantekas <dbadekas@aia.gr> — 2004-05-11T14:57:43Z

    does any one know how to get an md5()-like hash function using pgcrypto
    for postgresql 7.3 ? without upgrading to 7.4
    
    Thanks for any input.
    
    Dias
    
    scott.marlowe wrote:
    > On Sun, 11 Apr 2004, Jerry McBride wrote:
    > 
    > 
    >>T. Relyea wrote:
    >>
    >>
    >>>Mike Cox wrote:
    >>>
    >>>
    >>>>Has anyone created something like that for Postgresql?  It would be
    >>>>really handy to encrypt credit card numbers and other information so
    >>>>it stays secure.
    >>>>
    >>>>If no one has created anything such as this, I am going to code up
    >>>>something quite soon, but if it already exists, there is no need for
    >>>>me to reinvent the wheel, so speak up!  It is a law in places such as
    >>>>the EU that many types of data must be encrypted if the database is
    >>>>compromised.
    >>>>
    >>>>I will put up my solution in a few days if one does not exist.  But
    >>>>before I do that, I want to give a quick tutorial on how to create a
    >>>>file that will create tables, views and other such essentials.  Most
    >>>>people who use PostgreSQL just type in the commands in PostgreSQL, but
    >>>>that is not as easily portable or backed up as what I'm about to show
    >>>>you!
    >>>>
    >>>>1. open vi with a file.
    >>>>2.  Comments can be made as long as you add to slashes before the
    >>>>line:
    >>>>--this is a comment.
    >>>>3. Next just type in the SQL commands you want!
    >>>>4. after you are done, save the file.
    >>>>5. then just do this to create the database you made in the file:
    >>>>psql database_name < my_vi_file
    >>>>6.That is it!
    >>>>
    >>>>Here is a very simple sample of a file:
    >>>>
    >>>>--This is a sample file.  Use at your own risk.  No Warranties
    >>>>--Written by Mike Cox, author of the *nix "hm" command.
    >>>>
    >>>>create table first(
    >>>>MYNUMBER INTEGER);
    >>>>
    >>>>create VIEW myview AS
    >>>>select * from first;
    >>>>
    >>>>--Ok this is the end.  As you can see it is very simple and portable.
    >>>>--Try it out. Here's how: psql your_database < this_file
    >>>
    >>>MySQL has encryption and decryption functions built in, doesn't
    >>>Postgresql?
    >>>
    >>>Todd
    >>
    >>Obviously not... that's why we don't use it at work....
    > 
    > 
    > let's see:
    > 
    > su -
    > cd /usr/local/src/postgresql-7.4.2
    > cd contrib/pgcrypto
    > make
    > make install
    > 
    > Total time taken: <30 seconds.
    > 
    > If that's a make or break deal for you on a database I'd hate to go car 
    > buying with you.
    > 
    > 
    > ---------------------------(end of broadcast)---------------------------
    > TIP 9: the planner will ignore your desire to choose an index scan if your
    >       joining column's datatypes do not match
    > 
    
    
    
    

15. Re: Data Encryption in PostgreSQL, and a Tutorial.

    scott.marlowe <scott.marlowe@ihs.com> — 2004-05-11T15:40:18Z

    I think that's what digest does.  It doesn't appear to install in 7.4 
    since 7.4 has the md5 function.  I don't have a 7.3 box to test it on 
    though...
    
    On Tue, 11 May 2004, Dias Bantekas wrote:
    
    > does any one know how to get an md5()-like hash function using pgcrypto
    > for postgresql 7.3 ? without upgrading to 7.4
    > 
    > Thanks for any input.
    > 
    > Dias
    > 
    > scott.marlowe wrote:
    > > On Sun, 11 Apr 2004, Jerry McBride wrote:
    > > 
    > > 
    > >>T. Relyea wrote:
    > >>
    > >>
    > >>>Mike Cox wrote:
    > >>>
    > >>>
    > >>>>Has anyone created something like that for Postgresql?  It would be
    > >>>>really handy to encrypt credit card numbers and other information so
    > >>>>it stays secure.
    > >>>>
    > >>>>If no one has created anything such as this, I am going to code up
    > >>>>something quite soon, but if it already exists, there is no need for
    > >>>>me to reinvent the wheel, so speak up!  It is a law in places such as
    > >>>>the EU that many types of data must be encrypted if the database is
    > >>>>compromised.
    > >>>>
    > >>>>I will put up my solution in a few days if one does not exist.  But
    > >>>>before I do that, I want to give a quick tutorial on how to create a
    > >>>>file that will create tables, views and other such essentials.  Most
    > >>>>people who use PostgreSQL just type in the commands in PostgreSQL, but
    > >>>>that is not as easily portable or backed up as what I'm about to show
    > >>>>you!
    > >>>>
    > >>>>1. open vi with a file.
    > >>>>2.  Comments can be made as long as you add to slashes before the
    > >>>>line:
    > >>>>--this is a comment.
    > >>>>3. Next just type in the SQL commands you want!
    > >>>>4. after you are done, save the file.
    > >>>>5. then just do this to create the database you made in the file:
    > >>>>psql database_name < my_vi_file
    > >>>>6.That is it!
    > >>>>
    > >>>>Here is a very simple sample of a file:
    > >>>>
    > >>>>--This is a sample file.  Use at your own risk.  No Warranties
    > >>>>--Written by Mike Cox, author of the *nix "hm" command.
    > >>>>
    > >>>>create table first(
    > >>>>MYNUMBER INTEGER);
    > >>>>
    > >>>>create VIEW myview AS
    > >>>>select * from first;
    > >>>>
    > >>>>--Ok this is the end.  As you can see it is very simple and portable.
    > >>>>--Try it out. Here's how: psql your_database < this_file
    > >>>
    > >>>MySQL has encryption and decryption functions built in, doesn't
    > >>>Postgresql?
    > >>>
    > >>>Todd
    > >>
    > >>Obviously not... that's why we don't use it at work....
    > > 
    > > 
    > > let's see:
    > > 
    > > su -
    > > cd /usr/local/src/postgresql-7.4.2
    > > cd contrib/pgcrypto
    > > make
    > > make install
    > > 
    > > Total time taken: <30 seconds.
    > > 
    > > If that's a make or break deal for you on a database I'd hate to go car 
    > > buying with you.
    > > 
    > > 
    > > ---------------------------(end of broadcast)---------------------------
    > > TIP 9: the planner will ignore your desire to choose an index scan if your
    > >       joining column's datatypes do not match
    > > 
    > 
    > 
    > 
    > ---------------------------(end of broadcast)---------------------------
    > TIP 5: Have you checked our extensive FAQ?
    > 
    >                http://www.postgresql.org/docs/faqs/FAQ.html
    > 
    
    
    

16. Re: Data Encryption in PostgreSQL, and a Tutorial.

    Michal Hlavac <hlavki@medium13.sk> — 2004-05-11T15:41:54Z

    Dias Bantekas wrote:
    
    > does any one know how to get an md5()-like hash function using pgcrypto
    > for postgresql 7.3 ? without upgrading to 7.4
    > 
    > Thanks for any input.
    
    SELECT encode(digest(v_password, 'md5'), 'hex');
    
    hlk
    
    
    

17. Re: Data Encryption in PostgreSQL, and a Tutorial.

    Berend Tober <btober@computer.org> — 2004-05-11T15:59:21Z

    > Dias Bantekas wrote:
    >
    >> does any one know how to get an md5()-like hash function using
    >> pgcrypto for postgresql 7.3 ? without upgrading to 7.4
    >>
    >> Thanks for any input.
    >
    > SELECT encode(digest(v_password, 'md5'), 'hex');
    
    BTW,
    
    /usr/share/pgsql/contrib/pgcrypto.sql
    
    is the script that defines the encode and digest functions.
    
    --Berend Tober
    
    
    
    
    

18. Re: Data Encryption in PostgreSQL, and a Tutorial.

    Dias Bantekas <dbadekas@aia.gr> — 2004-05-12T07:00:10Z

    btober@computer.org wrote:
    >>Dias Bantekas wrote:
    >>
    >>
    >>>does any one know how to get an md5()-like hash function using
    >>>pgcrypto for postgresql 7.3 ? without upgrading to 7.4
    >>>
    >>>Thanks for any input.
    >>
    >>SELECT encode(digest(v_password, 'md5'), 'hex');
    > 
    > 
    > BTW,
    > 
    > /usr/share/pgsql/contrib/pgcrypto.sql
    > 
    > is the script that defines the encode and digest functions.
    > 
    > --Berend Tober
    > 
    > 
    > 
    > 
    
    thanks hlk, that's exactly what I was looking for.
    Now I can create a md5() function and do my job!
    
    encode is a native PG string function, it is not defined in pgcrypto.
    
    
    Dias