i know "password" can be used in creating/altering user
information (as used via GRANT and REVOKE) but is there any
facility within postgres to CRYPT() a value?

	create rule new_folk as on insert to view_folk do instead
		insert into folk_table
			(created,login,password)
			values
			(current_timestamp,new.login,CRYPT(new.password))
			;

or must this be done (say, in perl) before postgres sees it?

-- 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Thu, 26 Apr 2001 09:15:45 -0500
will trillich <will@serensoft.com>
crypt(table.field) ?
will trillich writes:

> i know "password" can be used in creating/altering user
> information (as used via GRANT and REVOKE) but is there any
> facility within postgres to CRYPT() a value?

See contrib/pgcrypto for hashing functions.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Thu, 26 Apr 2001 17:20:53 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: crypt(table.field) ?
On Thu, Apr 26, 2001 at 09:15:45AM -0500, will trillich wrote:
> i know "password" can be used in creating/altering user
> information (as used via GRANT and REVOKE) but is there any
> facility within postgres to CRYPT() a value?

At the moment no.  You should patch your PostgreSQL source for
that.  There is a patch in techdocs site which imports system
crypt to SQL level and there is my pgcrypto package which does
this and more...

   http://www.l-t.ee/marko/pgsql/pgcrypto-0.3.tar.gz

-- 
marko


Thu, 26 Apr 2001 18:13:59 +0200
Marko Kreen <marko@l-t.ee>
Re: crypt(table.field) ?
On Thu, Apr 26, 2001 at 05:20:53PM +0200, Peter Eisentraut wrote:
> will trillich writes:
> 
> > i know "password" can be used in creating/altering user
> > information (as used via GRANT and REVOKE) but is there any
> > facility within postgres to CRYPT() a value?
> 
> See contrib/pgcrypto for hashing functions.

I've got 7.0.3potato on my debian system, and i've also done

	apt-get install postgresql-contrib

which looks like it's got lots of meat to it, but

	dpkg -L postgresql-contrib | grep crypt

shows nada.

Care to explain -- in terms a Debian newbie might grok --
what "contrib/pgcrypto" means?

-- 
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Thu, 26 Apr 2001 14:01:46 -0500
will trillich <will@serensoft.com>
Re: crypt(table.field) ?
will trillich <will@serensoft.com> wrote:
>On Thu, Apr 26, 2001 at 05:20:53PM +0200, Peter Eisentraut wrote:
>> See contrib/pgcrypto for hashing functions.

>Care to explain -- in terms a Debian newbie might grok -- what
>"contrib/pgcrypto" means?

Peter is referring to a directory in the PostgreSQL sources, not to a part
of a binary package. "apt-get source postgresql" and look around.

HTH,
Ray
-- 
Don't think of yourself as an organic pain collector racing toward oblivion.
	Dogbert


Thu, 26 Apr 2001 19:37:10 +0000 (UTC)
jdassen@cistron.nl (J.H.M. Dassen (Ray))
Re: crypt(table.field) ?
On Thu, Apr 26, 2001 at 05:20:53PM +0200, Peter Eisentraut wrote:
> will trillich writes:
> > i know "password" can be used in creating/altering user
> > information (as used via GRANT and REVOKE) but is there any
> > facility within postgres to CRYPT() a value?
> 
> See contrib/pgcrypto for hashing functions.

Problem is the hashing functions are not good for
password storage.


A general question: what is the status on patch acceptance
now, after 7.1 is successfully released?  I did not
want to fuzz around with new code when 7.1 was in freeze,
but what is the status now?

Specifically - pgcrypto current state:

In the pgsql/contrib:

* digest() / encode() - stable.

In my pgcrypto separate release:

* digest() / encode() / hmac() - stable.
  I have changed the internal interfaces compared to main CVS.

* crypt() / gen_salt() - stable.  DES/MD5/Blowfish crypt()
  (Blowfish is unreleased).  Code seems to be working quite
  well.

* encrypt() / decrypt() - unstable.  Not in the 'buggy'-sense,
  but the 0.3 encrypt() is unsatisfactory for long-term storage
  and security and compatibility.  Also their spec is confusing
  to users.  In the next release they will be renamed
  raw_encrypt() / raw_decrypt() as they really are interfaces
  to raw ciphers.  I keep them coz they are good for testing
  pgcrypto code ;) and also they are ok for crypting short
  strings.

* future: encrypt() / decrypt() will be minimal implementation
  of OpenPGP standard (RFC2440). "Symmetrically Encrypted Data"
  with passwords.  (Is it too big? - The crypted data needs some
  structure and I dont think inventing some own format is good.)

Now for this OpenPGP stuff I dont have ATM not even
alpha-quality code.  So full release takes some time.
But hmac() and crypt() code is quite ok and there is no point
on me sitting on it alone.

So I would like to submit the mostly ready parts to main
tree.  When is the right time for it?


-- 
marko


Thu, 26 Apr 2001 22:03:09 +0200
Marko Kreen <marko@l-t.ee>
status after 7.1 and pgcrypto update / crypt(table.field) ?
On Thu, Apr 26, 2001 at 02:01:46PM -0500, will trillich wrote:
> On Thu, Apr 26, 2001 at 05:20:53PM +0200, Peter Eisentraut wrote:
> > will trillich writes:
> > 
> > > i know "password" can be used in creating/altering user
> > > information (as used via GRANT and REVOKE) but is there any
> > > facility within postgres to CRYPT() a value?
> > 
> > See contrib/pgcrypto for hashing functions.
> 
> I've got 7.0.3potato on my debian system, and i've also done

...

> Care to explain -- in terms a Debian newbie might grok --
> what "contrib/pgcrypto" means?

First contrib/pgcrypto is 7.1-only.  It is supposed to be a
place for cryptography-related functions.  At the moment it
contains only hashing and ascii-conversion functions: digest(),
encode(), decode().

Now I have released my newer code as separate release (they were
not fit for 7.1-in-freeze) and it contains more stuff:

crypt(password, salt)
	- like the crypt(3) in UN*X-like systems for password
	  crypting - DES and MD5-based crypt is supported.

gen_salt(type) for above crypt() as generating salts with only
	SQL is pain.

hmac(key, hash_type) is a implementation of RFC2104 "Hashed
	Message Authentication Code".  Sorta passworded-hash.

encrypt(data, key, type) with decrypt() - access to raw ciphers
	with little bit more.  They should be used only when you
	know what you are doing.  In the next release they will
	be renamed to raw_encrypt()/raw_decrypt() and much
	better encrypt()/decrypt() will be provided based on
	OpenPGP (RFC2440) - I am still developing this.

Also pgcrypto-0.3 should work with both 7.0 and 7.1.

-- 
marko


Thu, 26 Apr 2001 22:32:27 +0200
Marko Kreen <marko@l-t.ee>
Re: crypt(table.field) ?
Marko Kreen <marko@l-t.ee> writes:
> A general question: what is the status on patch acceptance
> now, after 7.1 is successfully released?  I did not
> want to fuzz around with new code when 7.1 was in freeze,
> but what is the status now?

We're still in bug-fixes-only mode.  I think the plan is to fork off
a 7.1 stable branch next week, and after that the floodgates will be
open for 7.2 development.

			regards, tom lane

Thu, 26 Apr 2001 18:24:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: status after 7.1 and pgcrypto update / crypt(table.field) ? 
<quote who="J.H.M. Dassen (Ray)">

> will trillich <will@serensoft.com> wrote:
> 
> >Care to explain -- in terms a Debian newbie might grok -- what
> >"contrib/pgcrypto" means?
> 
> Peter is referring to a directory in the PostgreSQL sources, not to a part
> of a binary package. "apt-get source postgresql" and look around.

You'll often find things like these in the /usr/share/doc/<package>/examples
directory under Debian. There's always a few goodies in there anyway. :)

- Jeff

-- 
       o/~ In spite of all those keystrokes, you're addicted to vim.        
                              *ka-ching!* o/~                               

Sun, 29 Apr 2001 15:04:18 +1000
Jeff Waugh <jdub@aphid.net>
Re: Re: crypt(table.field) ?
On Sun, Apr 29, 2001 at 03:04:18PM +1000, Jeff Waugh wrote:
> <quote who="J.H.M. Dassen (Ray)">
> 
> > will trillich <will@serensoft.com> wrote:
> > 
> > >Care to explain -- in terms a Debian newbie might grok -- what
> > >"contrib/pgcrypto" means?
> > 
> > Peter is referring to a directory in the PostgreSQL sources, not to a part
> > of a binary package. "apt-get source postgresql" and look around.
> 
> You'll often find things like these in the /usr/share/doc/<package>/examples
> directory under Debian. There's always a few goodies in there anyway. :)

aha. there's "apt-get install postgresql-crypt" but for 7.0.3
there's no crypt yet. i'll wait. :)

-- 
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Sun, 29 Apr 2001 01:09:33 -0500
will trillich <will@serensoft.com>
Re: Re: crypt(table.field) ?