using types for encrypting fields

Hi,

I have the need to encrypt some columns in some tables. With Postgres 7.4,
using a tablespace on an encrypted partition is not an option, right?
Furthermore, the content would be contained unencrypted in any database
dump.

Thus, my idea is:

- put the encryption/decryption key in a temporary table

- create types for each type of data which must be encrypted (enc_numeric,
enc_char etc.)

- define input, output, send and receive functions for each of these types

Good idea?

However, the realisation seems to be somewhat difficult. I'm not very
experienced at writing pl/pgsql functions, and I'm not sure how to specify
the internal structure of my new type.

This is what I've written so far:

<snip>
-- encrypted numeric:
CREATE TYPE public.enc_numeric (
INPUT = public.enc_numeric_txt_in, -- read from text
OUTPUT = enc_numeric_txt_out, -- write to text
receive = enc_numeric_in, -- read from numeric
send = enc_numeric_out, -- write to numeric
default = cast (0 as numeric)
);

CREATE OR REPLACE FUNCTION public.enc_numeric_txt_in(cstring)
RETURNS enc_numeric AS
'
DECLARE
data ALIAS FOR $1;
x bytea;
y cstring;
key bytea;
BEGIN
key = select key from keys limit 1;
x = decode(data, \'escape\');
y = encrypt(x, key, \'bf\');
RETURN y::numeric
END;
'
LANGUAGE 'plpgsql' STABLE STRICT;
</snip>

(which is by no means complete, of course).

I reckon something is utterly wrong here; perhaps someone can push me into
the right direction?

Or can't it be done, and I should use triggers (when writing) and change
all concerned views instead?

--
Thanks in advance,

Tobias