Querying Sqlalchemy-utils EncrytedType directly as an SQL query

Question

I have a 'User' table with the email as encrypted type,

class AllUser(db.Model):
    id = db.Column(db.Integer, autoincrement=True, primary_key=True, index=True)
    email = db.Column(EncryptedType(db.String(200), KEY), primary_key=True, index=True)
    password = db.Column(db.Text, default=None)
    firstname = db.Column(StringEncryptedType(db.Text, KEY))
    lastname = db.Column(StringEncryptedType(db.Text, KEY))
    name = db.Column(StringEncryptedType(db.Text, KEY))

And I need to query this table for migration without changing the python code and I cannot find anywhere on the internet how do to the same, we use postgres db so I tried the following queries with pgcrypto extension and it doesn't seem to work.

SELECT decrypt(email::bytea, key::bytea, 'aes') FROM all_user WHERE id=1;
SELECT decrypt(email, key, 'aes') FROM all_user WHERE id=1;

Running the above queries gives me the following error decrypt error: Data not a multiple of block size. Any help would be much appreciated, thank you.

Answer 1

This seems to work, assuming the default 'aes' engine and naive padding (also the default). The trick is using a hash of the key rather than the key itself in the function and when computing the iv. This is the reverse of the EncryptedStringType's decrypt method, but noting that the _update_key method is called before encryption and decryption.

-- Substitute your key here.
\set key 'secret_key'

SELECT
    convert_from(
        decrypt_iv(
            decode(email, 'base64'),
            sha256 (:'key'), 
            substring(sha256 (:'key'), 1, 16),
            'aes/pad:none'
        ),
        'UTF8'
    ) AS plaintext
FROM
    table;

Note that the result will be padded with asterisks to a multiple of 16, for example 'alice@example.com***************'.