Just started using the pgcrypt 0.4.2 (very cool stuff) and am having
some strange errors (not so cool).  Can someone make sense of the SQL
below?  I'm not 100% sure what's going on or not going on...


host=# SELECT DIGEST('asdf', 'md5') FROM users_shadow;
                     digest                     
------------------------------------------------
 \221.\310\003\262\316I\344\245A\006\215IZ\265p
(1 row)

host=# SELECT DIGEST(password, 'md5') FROM users_shadow;
ERROR:  Function 'digest(varchar, unknown)' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts
host=# SELECT DIGEST(CAST(password AS bytea), CAST('md5' AS TEXT)) FROM users_shadow;
ERROR:  Cannot cast type 'varchar' to 'bytea'


	Any ideas as to how I can do this?  -sc

-- 
Sean Chittenden

Sat, 5 Jan 2002 00:46:40 -0800
Sean Chittenden <sean@chittenden.org>
pgcryto strangeness...
> host=# SELECT DIGEST('asdf', 'md5') FROM users_shadow;
>                      digest                     
> ------------------------------------------------
>  \221.\310\003\262\316I\344\245A\006\215IZ\265p
> (1 row)

You must encode() the results.

(For the record, I consider that a serious design flaw.
It may not be possible to safely dump and restore tables
containing unencoded 8-bit data.)

> host=# SELECT DIGEST(password, 'md5') FROM users_shadow;
> ERROR:  Function 'digest(varchar, unknown)' does not exist
>         Unable to identify a function that satisfies the given argument types
>         You may need to add explicit typecasts
> host=# SELECT DIGEST(CAST(password AS bytea), CAST('md5' AS TEXT)) FROM users_shadow;
> ERROR:  Cannot cast type 'varchar' to 'bytea'
 
Try dropping the first cast.


Sat, 5 Jan 2002 08:46:38 -0700 (MST)
Bear Giles <bear@coyotesong.com>
Re: pgcryto strangeness...
> > host=# SELECT DIGEST('asdf', 'md5') FROM users_shadow;
> >                      digest                     
> > ------------------------------------------------
> >  \221.\310\003\262\316I\344\245A\006\215IZ\265p
> > (1 row)
> 
> You must encode() the results.

Sorry for not being more clear, this isn't the problem: just proof
that things are working on this end.

> (For the record, I consider that a serious design flaw.
> It may not be possible to safely dump and restore tables
> containing unencoded 8-bit data.)

How about a digest_hex() method?

> > host=# SELECT DIGEST(password, 'md5') FROM users_shadow;
> > ERROR:  Function 'digest(varchar, unknown)' does not exist
> >         Unable to identify a function that satisfies the given argument types
> >         You may need to add explicit typecasts
> > host=# SELECT DIGEST(CAST(password AS bytea), CAST('md5' AS TEXT)) FROM users_shadow;
> > ERROR:  Cannot cast type 'varchar' to 'bytea'
>  
> Try dropping the first cast.

Already have.  I've cast it to text too.  I've even tried having it
operate on char and text column types, it's looking for a bytea data
type, but I don't know how to cast to that correctly and that's the
problem (with the module?).  Sorry I wasn't more explicitly earlier.  -sc

host=# SELECT DIGEST(CAST(enabled AS bytea), CAST('md5' AS TEXT)) FROM users_shadow;
ERROR:  Cannot cast type 'bpchar' to 'bytea'
host=# SELECT DIGEST(CAST(enabled AS text), CAST('md5' AS TEXT)) FROM users_shadow;
ERROR:  Function 'digest(text, text)' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts
host=# SELECT DIGEST(CAST(password AS text), CAST('md5' AS TEXT)) FROM users_shadow;
ERROR:  Function 'digest(text, text)' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts


-- 
Sean Chittenden

Sat, 5 Jan 2002 11:11:10 -0800
Sean Chittenden <sean@chittenden.org>
Re: pgcryto strangeness...
Sean Chittenden wrote:

> 
> Just started using the pgcrypt 0.4.2 (very cool stuff) and am having
> some strange errors (not so cool).  Can someone make sense of the SQL
> below?  I'm not 100% sure what's going on or not going on...
> 
> 
> host=# SELECT DIGEST('asdf', 'md5') FROM users_shadow;
>                      digest                     
> ------------------------------------------------
>  \221.\310\003\262\316I\344\245A\006\215IZ\265p
> (1 row)
> 
> host=# SELECT DIGEST(password, 'md5') FROM users_shadow;
> ERROR:  Function 'digest(varchar, unknown)' does not exist
>         Unable to identify a function that satisfies the given argument types
>         You may need to add explicit typecasts
> host=# SELECT DIGEST(CAST(password AS bytea), CAST('md5' AS TEXT)) FROM users_shadow;
> ERROR:  Cannot cast type 'varchar' to 'bytea'
> 
> 
> 	Any ideas as to how I can do this?  -sc
> 

You can't directly cast varchar to bytea, but you can use decode(in 7.2):

test=# select version();
                            version
-------------------------------------------------------------
  PostgreSQL 7.2b3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

test=# create table users_shadow(password varchar(20));
CREATE
test=# insert into users_shadow values('secret');
INSERT 1492547 1
test=# SELECT DIGEST(decode(password,'escape'), 'md5') FROM users_shadow;
                         digest
------------------------------------------------------
  ^\276"\224\354\320\340\360\216\253v\220\322\246\356i
(1 row)

 
HTH,


-- Joe





Sat, 05 Jan 2002 11:56:43 -0800
Joe Conway <joseph.conway@home.com>
Re: pgcryto strangeness...
> You can't directly cast varchar to bytea, but you can use decode(in 7.2):
> 
> test=# select version();
>                             version
> -------------------------------------------------------------
>   PostgreSQL 7.2b3 on i686-pc-linux-gnu, compiled by GCC 2.96
> (1 row)
> 
> test=# create table users_shadow(password varchar(20));
> CREATE
> test=# insert into users_shadow values('secret');
> INSERT 1492547 1
> test=# SELECT DIGEST(decode(password,'escape'), 'md5') FROM users_shadow;
>                          digest
> ------------------------------------------------------
>   ^\276"\224\354\320\340\360\216\253v\220\322\246\356i
> (1 row)
> 
>  
> HTH,

Yeah, it does... but it also tells me I'm SOL for 7.1.3 even though
pgcrypto comes with a DECODE() function (only supports 'hex' and
'base64').  Any other ideas?  <:~)  -sc

-- 
Sean Chittenden

Sat, 5 Jan 2002 12:09:39 -0800
Sean Chittenden <sean@chittenden.org>
Re: pgcryto strangeness...
Sean Chittenden wrote:

> Yeah, it does... but it also tells me I'm SOL for 7.1.3 even though
> pgcrypto comes with a DECODE() function (only supports 'hex' and
> 'base64').  Any other ideas?  <:~)  -sc
> 


Not sure if you are in a position to do this, but why not make your 
password field bytea instead of varchar? This won't work if you need to 
support multibyte passwords, but I think it should be fine otherwise.


test=# create table users_shadow_2(password bytea);
CREATE
test=# insert into users_shadow_2 values('secret');
INSERT 1492553 1
test=# SELECT DIGEST(password, 'md5') FROM users_shadow_2;
                        digest
------------------------------------------------------
 ^\276"\224\354\320\340\360\216\253v\220\322\246\356i
 

Joe


Sat, 05 Jan 2002 13:12:01 -0800
Joe Conway <joseph.conway@home.com>
Re: pgcryto strangeness...
Sean Chittenden <sean@chittenden.org> writes:
> Yeah, it does... but it also tells me I'm SOL for 7.1.3 even though
> pgcrypto comes with a DECODE() function (only supports 'hex' and
> 'base64').  Any other ideas?  <:~)  -sc

So, create yourself another function.  In pgcrypto.sql.in I see

CREATE FUNCTION digest(bytea, text) RETURNS bytea
  AS 'MODULE_PATHNAME',
  'pg_digest' LANGUAGE 'C';

You could add

CREATE FUNCTION digest(text, text) RETURNS bytea
  AS 'MODULE_PATHNAME',
  'pg_digest' LANGUAGE 'C';

which should work fine since the internal representation of text isn't
really different from that of bytea.

			regards, tom lane

Sat, 05 Jan 2002 16:39:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: pgcryto strangeness... 
> So, create yourself another function.  In pgcrypto.sql.in I see
> 
> CREATE FUNCTION digest(bytea, text) RETURNS bytea
>   AS 'MODULE_PATHNAME',
>   'pg_digest' LANGUAGE 'C';
> 
> You could add
> 
> CREATE FUNCTION digest(text, text) RETURNS bytea
>   AS 'MODULE_PATHNAME',
>   'pg_digest' LANGUAGE 'C';
> 
> which should work fine since the internal representation of text isn't
> really different from that of bytea.

Tom, you're a regular postgres god.  ;~) That works beautifully!  I'm
cooking along now, thanks 'all!

Real quick, is anyone else is interested, I'm turning pgcrypto into a
port for FreeBSD. I'm mostly done, so if anyone has any interest in
testing this (very strange port to make because you have to copy the
backend headers out of the postgres tarball and into the include path.
I know this is changing with 7.2, but it's not out yet.  ::grin::),
please let me know.  -sc

-- 
Sean Chittenden

Sat, 5 Jan 2002 13:52:40 -0800
Sean Chittenden <sean@chittenden.org>
Re: pgcryto strangeness...
> Real quick, is anyone else is interested, I'm turning pgcrypto into a
> port for FreeBSD. I'm mostly done, so if anyone has any interest in
> testing this (very strange port to make because you have to copy the
> backend headers out of the postgres tarball and into the include path.
> I know this is changing with 7.2, but it's not out yet.  ::grin::),
> please let me know.  -sc

All I do to install contribs is this:

cd /usr/ports/databases/postgresql7
make configure
cd work/postgresql-7.1.3/contrib/pgcrypto
gmake all && gmake install
cd /usr/ports/databases/postgresql7
make clean

Chris


Mon, 7 Jan 2002 10:02:47 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: pgcryto strangeness...
> > Real quick, is anyone else is interested, I'm turning pgcrypto into a
> > port for FreeBSD. I'm mostly done, so if anyone has any interest in
> > testing this (very strange port to make because you have to copy the
> > backend headers out of the postgres tarball and into the include path.
> > I know this is changing with 7.2, but it's not out yet.  ::grin::),
> > please let me know.  -sc
> 
> All I do to install contribs is this:
> 
> cd /usr/ports/databases/postgresql7
> make configure
> cd work/postgresql-7.1.3/contrib/pgcrypto
> gmake all && gmake install
> cd /usr/ports/databases/postgresql7
> make clean

Alright, nm.  I'll change the patch to make pgcypto apart of default
FreeBSD postgres installs.  Thanks for the tip.  -sc

-- 
Sean Chittenden

Sun, 6 Jan 2002 18:11:02 -0800
Sean Chittenden <sean@chittenden.org>
Re: pgcryto strangeness...
On Sat, Jan 05, 2002 at 04:39:31PM -0500, Tom Lane wrote:
> You could add
> 
> CREATE FUNCTION digest(text, text) RETURNS bytea
>   AS 'MODULE_PATHNAME',
>   'pg_digest' LANGUAGE 'C';
> 
> which should work fine since the internal representation of text isn't
> really different from that of bytea.

This is so obvious that I would like to make it 'official'.

Seems like the theology around bytea<>text casting kept me from
seeing the simple :)

As this should go under 'polishing' I hope it gets into 7.2.

-- 
marko



Index: contrib/pgcrypto/pgcrypto.sql.in
===================================================================
RCS file: /opt/cvs/pgsql/pgsql/contrib/pgcrypto/pgcrypto.sql.in,v
retrieving revision 1.6
diff -u -r1.6 pgcrypto.sql.in
--- contrib/pgcrypto/pgcrypto.sql.in	29 Sep 2001 03:11:58 -0000	1.6
+++ contrib/pgcrypto/pgcrypto.sql.in	7 Jan 2002 04:11:00 -0000
@@ -1,6 +1,8 @@
 
+-- drop function digest(text, text);
 -- drop function digest(bytea, text);
 -- drop function digest_exists(text);
+-- drop function hmac(text, text, text);
 -- drop function hmac(bytea, bytea, text);
 -- drop function hmac_exists(text);
 -- drop function crypt(text, text);
@@ -14,6 +16,10 @@
 
 
 
+CREATE FUNCTION digest(text, text) RETURNS bytea
+  AS 'MODULE_PATHNAME',
+  'pg_digest' LANGUAGE 'C';
+
 CREATE FUNCTION digest(bytea, text) RETURNS bytea
   AS 'MODULE_PATHNAME',
   'pg_digest' LANGUAGE 'C';
@@ -21,6 +27,10 @@
 CREATE FUNCTION digest_exists(text) RETURNS bool
   AS 'MODULE_PATHNAME',
   'pg_digest_exists' LANGUAGE 'C';
+
+CREATE FUNCTION hmac(text, text, text) RETURNS bytea
+  AS 'MODULE_PATHNAME',
+  'pg_hmac' LANGUAGE 'C';
 
 CREATE FUNCTION hmac(bytea, bytea, text) RETURNS bytea
   AS 'MODULE_PATHNAME',

Mon, 7 Jan 2002 07:34:50 +0200
Marko Kreen <marko@l-t.ee>
Re: [HACKERS] pgcryto strangeness...
Marko Kreen <marko@l-t.ee> writes:
> This is so obvious that I would like to make it 'official'.
> Seems like the theology around bytea<>text casting kept me from
> seeing the simple :)
> As this should go under 'polishing' I hope it gets into 7.2.

Done.

			regards, tom lane

Mon, 07 Jan 2002 13:56:45 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] pgcryto strangeness... 