In Postgresql, we can capture the record with the trigger, encrypt and write to the table.
Let’s create the table,
|
1 |
CREATE TABLE example_table ( syndicate text,deed text,hash bytea default ‘AES_KEY’); |
Then we create our function,
|
1 2 3 4 5 6 7 8 9 |
CREATE OR REPLACE FUNCTION hash_update_tg() RETURNS trigger AS $$ BEGIN IF tg_op = ‘INSERT’ OR tg_op = ‘UPDATE’ THEN NEW.syndicate = PGP_SYM_ENCRYPT(NEW.syndicate,’AES_KEY’); –digest(NEW.key_codes, ‘sha256’); NEW.deed = PGP_SYM_ENCRYPT(NEW.deed,’AES_KEY’);–digest(NEW.key_codes, ‘sha256’); RETURN NEW; END IF; END; $$ LANGUAGE plpgsql; |
Finally, we create our trigger.
|
1 2 3 |
CREATE TRIGGER example_table_hash_insert_update BEFORE INSERT OR UPDATE ON example_table FOR EACH ROW EXECUTE PROCEDURE hash_update_tg(); |
Then, when we add records to the table, we can see that the data is encrypted.
|
1 2 |
insert into example_table values ('syndicate_name','x'); select * from example_table; |
With the following sql we can see the unencrypted version.
|
1 |
select pgp_sym_decrypt(syndicate::bytea, ‘AES_KEY’),pgp_sym_decrypt(deed::bytea, ‘AES_KEY’) from example_table; |
I hope it has been helpful.
