Last active
May 13, 2023 19:00
-
-
Save james2doyle/01f577a9ae5aa8661b361f0b1655eac6 to your computer and use it in GitHub Desktop.
A Postgresql trigger and function to encrypt and decrypt a field
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION encrypt_field() | |
RETURNS TRIGGER | |
AS $func$ | |
BEGIN | |
IF PGP_SYM_ENCRYPT(NEW.encrypted_field, 'the-password-here')::text != OLD.encrypted_field::text THEN | |
NEW.encrypted_field := PGP_SYM_ENCRYPT(NEW.encrypted_field, 'the-password-here')::text; | |
END IF; | |
RETURN NEW; | |
END | |
$func$ | |
LANGUAGE plpgsql; | |
CREATE TRIGGER on_insert_encypt_field | |
BEFORE INSERT | |
ON some_table | |
FOR EACH ROW | |
EXECUTE PROCEDURE encrypt_field(); | |
CREATE TRIGGER on_insert_encypt_field | |
BEFORE UPDATE | |
ON some_table | |
FOR EACH ROW | |
EXECUTE PROCEDURE encrypt_field(); | |
CREATE OR REPLACE FUNCTION decrypt_field(field text) | |
RETURNS text | |
AS $$ | |
BEGIN | |
RETURN PGP_SYM_DECRYPT(field::bytea, 'the-password-here'); | |
END; | |
$$ | |
LANGUAGE plpgsql; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Consider Supabase Vault extension instead: https://github.com/supabase/vault