We are attempting to encrypt a column within our database using pgp_sym_encrypt aes 256.
When i run "pgp_sym_encrypt(?,'Netswitch@123','compress-algo=1,cipher-algo=aes256')" on my DB it work successfully and the field will be inserted and encryptd correctly however when I user Hibernate:
I have added the following columntransformer to my entity
@ColumnTransformer(write = "pgp_sym_encrypt(?,'Netswitch@123','compress-algo=1,cipher-algo=aes256')")
@Column(name = "my_field")
String myfield;
When persisting to the db I get the error
sqlException ERROR: function pgp_sym_encrypt(character varying, unknown, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.
So I tried adding casts
@ColumnTransformer(write = "pgp_sym_encrypt(cast(? as Text),cast('Netswitch@123' as Text),cast('compress-algo=1,cipher-algo=aes256' as Text))")
Still get the same
sqlException ERROR: function pgp_sym_encrypt(text, text, text) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.
(ps pgcrypto is added on the db and working as I can run the query outside of hibernate)
I was unable to get this to work So I opted to create a stored procedure which does the insertion for me