custom encryption for db columns/fields

[Justvuur]

Is it possible to create a custom encryption/decryption algorithm that can be used with sqlalchemy querying/filtering?

When querying and filtering, I would like the field to automatically decrypt using the custom algorithm.

[Justvuur]

Versions:

SQLAlchemy = 1.2

PostgreSQL = 11.2

[Jonathan Vanasco]

On Tuesday, 19 May 2020 19:24:48 UTC+2, Justvuur wrote:

Is it possible to create a custom encryption/decryption algorithm that can be used with sqlalchemy querying/filtering?

When querying and filtering, I would like the field to automatically decrypt using the custom algorithm.

You can create a SqlAlchemy CustomType that will encrypt/decrypt values to the database.    (https://docs.sqlalchemy.org/en/13/core/custom_types.html)

There is an example of this when dealing with JSON:

https://docs.sqlalchemy.org/en/13/core/custom_types.html#marshal-json-strings


In order to query/filter the encrypted values, you will have to extend PostgreSQL to perform the encryption, decryption, and querying of that data.

[Justvuur]

Thanks! This is great!

Regarding the extension of PostgreSQL, do you have an example for that?

[Justvuur]

Regarding the extension of PostgreSQL, do you have an example for that?

On Tuesday, 19 May 2020 22:13:28 UTC+2, Jonathan Vanasco wrote:

[Jonathan Vanasco]

That would be purely PostgreSQL. You can look on StackOverflow for answers. Newer versions of postgresql also have a crypto library that can be compiled into the server, which may help. https://www.postgresql.org/docs/current/pgcrypto.html