Thread: pgcryto strangeness...
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
argumenttypes 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
> > 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
argumenttypes 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
argumenttypes 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
> 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.