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.

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

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

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

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

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

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

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

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

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',

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