Re: contrib/pgcrypto

On Wed, Mar 16, 2005 at 07:46:23AM -0800, Moran.Michael wrote:
> How do you encrypt() & decrypt() data of types INT4 or DATE?
>
> The PGCrypto methods encrypt() and decrypt() each take BYTEA as input:
>
> i.e.,
> encrypt( data::bytea, key::bytea, type::text)
> decrypt( data::bytea, key::bytea, type::text)
>
> So how do you convert INT4 and DATE data into BYTEA so that they can be
> passed as input into the PGCrypto methods encrypt() & decrypt()?

It think you should convert int and date fields to text and
encrypt those.

Now the question becomes, how to encrypt text fields?

I used to simply declare encrypt() decrypt() with text arguments
as they are internally same, but this is a gross hack.

Someone (I cant find the post right now) posted into lists
nicer hack, which did the converion via plpgsql, so encrypting
int's can go like this:

------------------------------------------------
create or replace function txt2bytea(text) returns bytea
as 'begin return $1; end;' language plpgsql;

create or replace function bytea2txt(bytea) returns text
as 'begin return $1; end;' language plpgsql;

create or replace function encrypt_int(integer, bytea) returns bytea
as '
begin
return encrypt(txt2bytea($1), $2, ''bf'');
end;
' language plpgsql;

create or replace function decrypt_int(bytea, bytea) returns text
as '
begin
return decrypt($1, $2, ''bf'');
end;
' language plpgsql;
-------------------------------------------------

This uses implicit conversion between int<->text, for date's
you should put 'to_char(XX)' and 'date XX' where needed.

I hope to get pgp_encrypt/decrypt into 8.1 (yes, its brewing),
which make the bytea<->text mess unnecessary, as I can tag
the data as text, thus also decrypting as text.

--
marko