<\/p>\n
In Postgresql, we can capture the record with the trigger, encrypt and write to the table.<\/p>\n
Let’s create the table,<\/p>\n
CREATE TABLE example_table ( syndicate text,deed text,hash bytea default \u2018AES_KEY\u2019);<\/pre>\n<\/p>\n
Then we create our function,<\/p>\n
CREATE OR REPLACE FUNCTION hash_update_tg() RETURNS trigger AS $$\r\nBEGIN\r\nIF tg_op = \u2018INSERT\u2019 OR tg_op = \u2018UPDATE\u2019 THEN\r\nNEW.syndicate = PGP_SYM_ENCRYPT(NEW.syndicate,\u2019AES_KEY\u2019); \u2013digest(NEW.key_codes, \u2018sha256\u2019);\r\nNEW.deed = PGP_SYM_ENCRYPT(NEW.deed,\u2019AES_KEY\u2019);\u2013digest(NEW.key_codes, \u2018sha256\u2019);\r\nRETURN NEW;\r\nEND IF;\r\nEND;\r\n$$ LANGUAGE plpgsql;<\/pre>\n<\/p>\n
Finally, we create our trigger.<\/p>\n
CREATE TRIGGER example_table_hash_insert_update\r\nBEFORE INSERT OR UPDATE ON example_table\r\nFOR EACH ROW EXECUTE PROCEDURE hash_update_tg();<\/pre>\n<\/p>\n
Then, when we add records to the table, we can see that the data is encrypted.<\/p>\n
insert into example_table values ('syndicate_name','x');\r\nselect * from example_table;<\/pre>\n<\/p>\n
With the following sql we can see the unencrypted version.<\/p>\n
select pgp_sym_decrypt(syndicate::bytea, \u2018AES_KEY\u2019),pgp_sym_decrypt(deed::bytea, \u2018AES_KEY\u2019) from example_table;<\/pre>\nI hope it has been helpful.<\/p>\n\n
<\/div>\n