Hi,
I need to encrypt some fields in my database. The solution I came up with
is:
- create an additional field w/suffix "_encr" for every field which must be
encrypted;
- create update triggers which write the encrypted value into the new field
and null into the original field (not null constraints must be removed, of
course)
- change the views to use and decrypt the encrypted fields.
- add polymorpic encrypt(...) functions which are distinguished from their
argument types and take the key from a temporary table.
I'm pretty sure I need to use BYTEA fields to store the encrypted values;
and I need to convert TEXT <--> BYTEA, don't I?
So I created the following functions:
REATE OR REPLACE FUNCTION text2bytea(text)
RETURNS bytea AS
$BODY$BEGIN
RETURN decode($1, 'escape');
END;$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION bytea2text(bytea)
RETURNS text AS
$BODY$BEGIN
RETURN encode($1, 'escape');
END;$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;
ALTER FUNCTION text2bytea(text) OWNER TO postgres;
Now, unfortunately there are backslashes in many varchar fields in the rows
wich must be encrypted; and for those strings, encode/decode seems to crash:
select decode('abc', 'escape'); -- yields 'abc'
select decode('ab\c', 'escape'); -- yields 'abc'
select encode('ab\c', 'escape'); -- yields 'abc'
select decode('ab\\c', 'escape'); -- ERROR: invalid input syntax for type
bytea
select encode('ab\\c', 'escape'); -- ERROR: invalid input syntax for type
bytea
select decode('ab\\\\c', 'escape'); -- yields 'ab\\c'
select encode('ab\\\\c', 'escape'); -- yields 'ab\\c'
This looks to me like a bug: the backslash should be escaped with another
backslash instead of raising an error.
Any ideas how to get around this?
--
TIA,
Tobias
