using types for encrypting fields

["Tobias Herp" <tobias.herp@xxxxxx>]

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? 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