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
