Question: pgp_sym_decrypt/pgp_sym_encrypt

Hi,

I’m trying to use pgcrypto for encryption/ decryption of column from SpringBoot Java application. The function I am using is pgp_sym_encrypt/ decrypt.

 

Postgres Version: 12.3

E.g.

insert into employee values (1, 'Jay', '1 down str',  20, pgp_sym_encrypt('ABC-220','emp_sec_key'));

select empno, ename, address, pgp_sym_decrypt(account_number::bytea,'emp_sec_key') from employee;

 

We want key to be stored and read from secure vault. We explored several options, however not able set the key in encrypt/ decrypt function from variable whose value is read from vault. It seems the function is not recognizing the variable value.

 

With hardcoded key in function the encryption and decryption is working fine.

 

Could you pls assist with:

1. Can we pass key to function pgp_sym_decrypt/ encrypt from variable?
2. Any sample code where this has been achieved?

 

Thanks

Atirek