Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
