Search code examples
postgresqlmariadbpgcrypto

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.