If you were to instead run your inserts, updates, and selects through a
procedure, you could achieve the same affect. You could have the select
require the decode key be passed.
From the method you're asking about, the whole point of encrypting the
column would be undone by *ANYBODY* doing a select on it (I don't think
triggers are capable of receiving parameters).
As for storing the key... You can have the application do it or use some
calculatable key based on some other piece of information in the record
(it's serial field perhaps).
Daniel ?erud wrote:
>
> I have two questions:
>
> (1) SECURITY BY OBSCURITY :)
>
> I have a table
>
> CREATE TABLE tbl (
>
> password text,
> created timestamp not null default current_timestamp
>
> );
>
> I want to make two triggers on select/insert for this. The
> trigger for insert should xor password with the first byte
> (or something) from created. The trigger for select should
> xor it back using the same byte from created. Is this
> possible in plpgsql? Where can I read about it? Has anyone
> done it?
>
> I want to combine this with:
> (2) ENCRYPTING A PASSWORD COLUMN
>
> Has anyone got a tip of how to keep a column encrypted to
> assure yourself that noone can steal it? I have thought of
> a hash algorithm like ripemd160 but it seems this can't do
> the trick because i have to save the key somewhere and
> saving it in the database suck and giving it to the user
> gives him extra trouble.
>
> Anyone has a tip of doing this?
> It would be extra good if I then ould make a function
> called verifyUser(text, text) (username/password) that
> decrypted it and verifyed it (plpgsql, don't like the idea
> of putting C in there).
>
> Hope I was clear enought, thanks for reading through!
>
> Daniel Åkerud
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)