[SOLVED] Is there a MariaDB equivalent of pgp_sym_encrypt from pgcrypto for Postgres?

Is there a MariaDB equivalent of pgp_sym_encrypt from pgcrypto for Postgres?

I've been tasked with migrating a Postgres 13 database to MariaDB. The existing Postgres database uses pgp_sym_encrypt to encrypt certain values of data when inserting:

insert into a_table (
  normal_value,
  secret_value
) values (
  'normal',
  pgp_sym_encrypt(0.123::text, 'secret_key')
)

then decrypts it with:

select
  normal_value,
  cast(pgp_sym_decrypt(secret_value, 'secret_key') as double precision)
from a_table

Does MariaDB have something like this? And what are my options when migrating existing data?

Solution

Option 1: Migrate to a AES ENCRYPT/DECRYPT

Use postgres to change the value in the column to an AES_ENCRYPT/DECRYPT capable value and migrate this:

alter table a_table add secret_sym bytea;
update a_table set secret_sym = encrypt(cast(pgp_sym_decrypt(secret_value, 'secret_key') as bytea), 'secret_key'::bytea, 'aes-ecb/pad:pkcs'::text);
select cast(convert_from(decrypt(secret_sym, 'secret_key'::bytea, 'aes-ecb/pad:pkcs'::text), 'utf-8') as double precision) as decrypted_value from a_table

ref: postgres fiddle

Use MariaDB functions AES_DECRYPT/ENCRYPT which can now decrypt secret_sym.

select normal_value, aes_decrypt(secret_value, 'secret_key') from a_table

ref: mariadb fiddle

ref: noting hopefully soon non-ecb based AES functions

Option 2: Create a PGP UDF

There doesn't appear to be an existing PGP function in the server, and the collection of UDF functions doesn't appear to include it.

It is however pretty easy to use the Rust UDF crate to create your own UDF functions.

The pgp crate appears to support symmetric PGP like Postgresql

A contribution request to the Rust UDF would be a good place to make your work available. If its beyond your ability/time availability you could just request it and offer to pay for its implementation.