

  http://www.l-t.ee/marko/pgsql/pgcrypto-0.1.tar.gz    (11k)

Here is a implementation of crypto hashes for PostgreSQL.
It exports 2 functions to SQL level:

  digest(data::text, hash_name::text)
        which returns hexadecimal coded hash over data by
        specified algorithm. eg

        > select digest('blah', 'sha1');
        5bf1fd927dfb8679496a2e6cf00cbe50c1c87145

        (I see no point returning binary hash.. ??)

  digest_exists(hash_name::text)::bool
        which reports if particular hash type exists.

It can be linked with various libraries:

  standalone:
        MD5, SHA1

        (the code is from KAME project.  Actually I hate code
        duplication, but I also want to quarantee that MD5 and
        SHA1 exist)

  mhash (0.8.1):
        MD5, SHA1, CRC32, CRC32B, GOST, TIGER, RIPEMD160,
        HAVAL(256,224,192,160,128)

  openssl:
        MD5, SHA1, RIPEMD160, MD2

  kerberos5 (heimdal):
        MD5, SHA1

As you can see I am thinking making MD5 and SHA1 standard.

And yes, it could be made 'standalone only' but in case of
OpenSSL and mhash the code is imported very cleanly (my
code has no hard-wired hashes)   ???

The code should also be 8-bit clean and TOAST-enabled - but
I am not 100% sure.  And if is declared as digest(text,text)
does it work on bytea?

It is packaged at the moment as stand-alone package, because
I am trying to write general autoconf macros for use with outside
packages.  At the moment any package author must generate those
himself.  Also the contrib stuff should be possible to make use
of this instead of include ../../Makefile.* so they can enjoy
the same (dis)advantages as outside packages.

Status:

  C code        - stable
  autoconf      - beta
  make install  - does not work

If there is interest I can package it as a contrib or even
mainstream diff against CVS ???


-- 
marko


Thu, 19 Oct 2000 19:22:20 +0200
Marko Kreen <marko@l-t.ee>
[ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1

On Thu, 19 Oct 2000, Marko Kreen wrote:

> 
> 
>   http://www.l-t.ee/marko/pgsql/pgcrypto-0.1.tar.gz    (11k)
> 
> Here is a implementation of crypto hashes for PostgreSQL.

> It can be linked with various libraries:
> 
>   standalone:
>         MD5, SHA1
> 
>         (the code is from KAME project.  Actually I hate code
>         duplication, but I also want to quarantee that MD5 and
>         SHA1 exist)
> 
>   mhash (0.8.1):
>         MD5, SHA1, CRC32, CRC32B, GOST, TIGER, RIPEMD160,
>         HAVAL(256,224,192,160,128)
> 
>   openssl:
>         MD5, SHA1, RIPEMD160, MD2
> 
>   kerberos5 (heimdal):
>         MD5, SHA1
> 

 If it's really allows use all this hash methods, it's firts complex
module for PG. And if this module *not-contains* some for law problematic 
crypto code, but call only some standard libs (like SSL) it is cool! But 
your license? 

 I vote for include this into PG's contrib... :-) 

						Karel



Thu, 19 Oct 2000 19:31:28 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1
Marko Kreen <marko@l-t.ee> writes:
> If there is interest I can package it as a contrib or even
> mainstream diff against CVS ???

Sure, I think people would be interested.  It might be best to make it
contrib for now, until you are sure you have dealt with portability and
installation issues --- mainstream code has to build everywhere, whereas
we are more lenient about contrib...

			regards, tom lane

Thu, 19 Oct 2000 13:33:35 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1 
On Thu, Oct 19, 2000 at 07:31:28PM +0200, Karel Zak wrote:
> On Thu, 19 Oct 2000, Marko Kreen wrote:
> > 
> >   http://www.l-t.ee/marko/pgsql/pgcrypto-0.1.tar.gz    (11k)

At the last second I thougt that I should generate the 'configure'
so its 20k actually...

> > 
>  If it's really allows use all this hash methods, it's firts complex

Well, imho its fairly simple :) look at the code...

> module for PG. And if this module *not-contains* some for law problematic 
> crypto code, but call only some standard libs (like SSL) it is cool! But 
> your license? 

Uh, PostgreSQL default license / BSD...

It contains SHA1 and MD5 for the standalone case, but thats not
mandatory...

> 
>  I vote for include this into PG's contrib... :-) 
> 
Thanks.

-- 
marko


Thu, 19 Oct 2000 20:01:16 +0200
Marko Kreen <marko@l-t.ee>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1
On Thu, Oct 19, 2000 at 01:33:35PM -0400, Tom Lane wrote:
> Marko Kreen <marko@l-t.ee> writes:
> > If there is interest I can package it as a contrib or even
> > mainstream diff against CVS ???
> 
> Sure, I think people would be interested.  It might be best to make it
> contrib for now, until you are sure you have dealt with portability and
> installation issues --- mainstream code has to build everywhere, whereas
> we are more lenient about contrib...
> 

Ok, I send it to -patches shortly.

-- 
marko


Thu, 19 Oct 2000 20:03:49 +0200
Marko Kreen <marko@l-t.ee>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1

On Thu, 19 Oct 2000, Marko Kreen wrote:

> On Thu, Oct 19, 2000 at 01:33:35PM -0400, Tom Lane wrote:
> > Marko Kreen <marko@l-t.ee> writes:
> > > If there is interest I can package it as a contrib or even
> > > mainstream diff against CVS ???
> > 
> > Sure, I think people would be interested.  It might be best to make it
> > contrib for now, until you are sure you have dealt with portability and
> > installation issues --- mainstream code has to build everywhere, whereas
> > we are more lenient about contrib...
> > 
> 
> Ok, I send it to -patches shortly.

 But, please try check and set style of your source and files like 
current CVS contrib tree. I a little clean up contrib tree for 7.1
and will bad if anyone add again some mazy files. 

					Karel


Thu, 19 Oct 2000 20:27:00 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1
Marko Kreen writes:

> Here is a implementation of crypto hashes for PostgreSQL.

> It is packaged at the moment as stand-alone package, because
> I am trying to write general autoconf macros for use with outside
> packages.  At the moment any package author must generate those
> himself.  Also the contrib stuff should be possible to make use
> of this instead of include ../../Makefile.* so they can enjoy
> the same (dis)advantages as outside packages.

A coupla comments:

* Your code seems to be quite optimistic about being on a Linux system.

* Use AC_DEFUN, not `define', to create Autoconf macros.

* From PostgreSQL 7.1-to-be on you can detect the location of the include
and library files with `pg_config --includedir` and `pg_config --libdir`
respectively.

(I've been thinking about writing a general-purpose PostgreSQL detection
macro for use by third-party products to be included in the PostgreSQL
source.)

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Thu, 19 Oct 2000 20:55:55 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1
On Thu, Oct 19, 2000 at 08:55:55PM +0200, Peter Eisentraut wrote:
> Marko Kreen writes:
> > Here is a implementation of crypto hashes for PostgreSQL.
> 
> > It is packaged at the moment as stand-alone package, because
> > I am trying to write general autoconf macros for use with outside
> > packages.  At the moment any package author must generate those
> > himself.  Also the contrib stuff should be possible to make use
> > of this instead of include ../../Makefile.* so they can enjoy
> > the same (dis)advantages as outside packages.
> 
> A coupla comments:
> 
> * Your code seems to be quite optimistic about being on a Linux system.

Well, it has a reason :)  Anyway the autoconf stuff is alpha and
contrib patch I will send will use current makefile system,
no autoconf.

> * Use AC_DEFUN, not `define', to create Autoconf macros.

Oh.  Ofcourse.  I have played too much with pure m4...  (I use it
for generating SQL)

> * From PostgreSQL 7.1-to-be on you can detect the location of the include
> and library files with `pg_config --includedir` and `pg_config --libdir`
> respectively.

Nice.  I didn't know.

> (I've been thinking about writing a general-purpose PostgreSQL detection
> macro for use by third-party products to be included in the PostgreSQL
> source.)

Well, that happens to be my idea also :)  Maybe we can, like, join
the effort?

-- 
marko


Thu, 19 Oct 2000 21:41:33 +0200
Marko Kreen <marko@l-t.ee>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1
On Thu, Oct 19, 2000 at 08:27:00PM +0200, Karel Zak wrote:
> On Thu, 19 Oct 2000, Marko Kreen wrote:
> > On Thu, Oct 19, 2000 at 01:33:35PM -0400, Tom Lane wrote:
> > > Marko Kreen <marko@l-t.ee> writes:
> > > > If there is interest I can package it as a contrib or even
> > > > mainstream diff against CVS ???
> > > 
> > > Sure, I think people would be interested.  It might be best to make it
> > > contrib for now, until you are sure you have dealt with portability and
> > > installation issues --- mainstream code has to build everywhere, whereas
> > > we are more lenient about contrib...
> > > 
> > 
> > Ok, I send it to -patches shortly.
> 
>  But, please try check and set style of your source and files like 
> current CVS contrib tree. I a little clean up contrib tree for 7.1
> and will bad if anyone add again some mazy files. 

What especially should I note?  Could you look current stuff?

.  I drop autoconf stuff / use current contrib Makefile stuff
.  add license boilerblates

What else needs doing?

-- 
marko


Thu, 19 Oct 2000 21:46:08 +0200
Marko Kreen <marko@l-t.ee>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1
> > >   http://www.l-t.ee/marko/pgsql/pgcrypto-0.1.tar.gz    (11k)

First of all, thankd for tis contribution. I had impemented a similar thing for my own purposes. A problem I still have is using the digest for "checksumming" rows in my tables - which is a 'MUST' for medico-legal reasons in my case. I use the follwing trigger and function (just a proof of concept implementation)

DROP TRIGGER trig_crc ON crclog;
DROP FUNCTION trigfunc_crc();

CREATE FUNCTION trigfunc_crc()
RETURNS OPAQUE as '
 # create a string by concatenating all field contents
 set cstr "";
 set len [llength $TG_relatts];
 for {set i 1}  {$i < $len} {incr i} {
      set istr [lindex $TG_relatts $i]
      # skip the crc field!
      if {[string compare "crc" $istr] == 0} continue; 
      # beware of NULL fields
      if [catch {set cstr $cstr$NEW($istr)}] continue; 
 }  
 # calculate the strong hash
 spi_exec "select pg_crc32(''$cstr'') as crcs";
 # update the new record
 set NEW(crc) $crcs;
 #spi_exec "insert into logger(crc) values (''$crcs'')";
return [array get NEW]  
' LANGUAGE 'pltcl';   

CREATE TRIGGER trig_crc
BEFORE INSERT OR UPDATE ON crclog
FOR EACH ROW
EXECUTE PROCEDURE trigfunc_crc(); 

----------------------------------------------------------------------

As you can see, the trigfunc_crc is fairly generic and will work with any table containing the attribute "crc".

Have you found a way of 
- making the trigger generic as well (I hate to rebuild all triggers for 300+ tables whenever I modify trigfunc_crc)
- any better performing way to implement trigfunc_crc ?

Horst


Sat, 21 Oct 2000 23:27:54 +1000
"Horst Herb" <hherb@malleenet.net.au>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1
On Sat, Oct 21, 2000 at 11:27:54PM +1000, Horst Herb wrote:
> > > >   http://www.l-t.ee/marko/pgsql/pgcrypto-0.1.tar.gz    (11k)
> 
> First of all, thankd for tis contribution. I had impemented a
> similar thing for my own purposes. A problem I still have is using
> the digest for "checksumming" rows in my tables - which is a 'MUST'
> for medico-legal reasons in my case. I use the follwing trigger and
> function (just a proof of concept implementation)
> 

[ pltcl trigger ]

> As you can see, the trigfunc_crc is fairly generic and will work
> with any table containing the attribute "crc".
> 
> Have you found a way of - making the trigger generic as well (I hate
> to rebuild all triggers for 300+ tables whenever I modify
> trigfunc_crc)

You do a trigfunc_crc_real which is called from trigfunc_crc?  I guess
you could then drop/create as you please?  Sorry, I do not speak Tcl
so I cant show how to do it exactly.  It will be a bit slower though.

> - any better performing way to implement trigfunc_crc ?

Hmm, probably you should at some point drop to C level.  It will
be a pain, so if the need is not too bad then you should avoid it.


Btw, the concept of checksumming rows is kinda new to me.
I needed this to store passwords on a table, so sorry if I
cant be more help.  But I am a litte bit curious, why is it
needed?  Simple checksumming (crc32/md5) does not help malicious
changing of data, only hardware failures, but today's hardware
has itself checksumming builtin...  It probably would be a
more point if you do some pgp/gpg style signing so you would
get some authenticy too, but this is hard to implement right.



-- 
marko


Sat, 21 Oct 2000 15:39:56 +0200
Marko Kreen <marko@l-t.ee>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1
> Btw, the concept of checksumming rows is kinda new to me.
> I needed this to store passwords on a table, so sorry if I
> cant be more help.  But I am a litte bit curious, why is it
> needed?  Simple checksumming (crc32/md5) does not help malicious
> changing of data, only hardware failures, but today's hardware
> has itself checksumming builtin...  It probably would be a
> more point if you do some pgp/gpg style signing so you would
> get some authenticy too, but this is hard to implement right.

1.) checksumming a row will alert you when glitches have changed data. Happened twice in 3 years to me with my previous system (with top end hardware!). This is probably due to file system or hardware failures. There is no other way to find out whether such a glitch has happened other than regularly checking the checksums. Despite all progress in hardware, these errors still happen and I have these happenings well documented. Most of the people never will notice as they do not use such a checking.

2.) We had problems before with tunneled IP connections and corrupted data. These errors are very rare, but again, they can happen - the more complex your network setup is, the more likely you might get a glitch or two per year. I never fou d out what to blame: the protocol implementation, the server, the client ...
With large packet sizes, the checksumming the network protocols use is not as collision proof as one might wish. The same crc works more reliable with small amounts of data than with larger amounts.

3.) This checksumming helps to check whether a complex database setup with lots of triggers and interdependencies really stores the data the way it is supposed to as you can do the same calculation on the client and compare after commitment. Helps a lot while testing such a setup

Horst


Sun, 22 Oct 2000 09:47:34 +1000
"Horst Herb" <horst@hherb.com>
Re: [ANNC][RFC] crypto hashes for PostgreSQL 7.0, 7.1