EnryptedType in python3 and postgres fails with 'string argument without an encoding'

[aicioara]

When querying a record with a column of EncryptedType in python3.7 with a MySQL backend and packages

• SQLAlchemy==1.3.15
• SQLAlchemy-Utils==0.36.1
• psycopg2-binary==2.8.4

The query fails with TypeError: 'string argument without an encoding'.

The issue appears to be that EncryptedType has an impl of LargeBinary which in python3 processes the returned value with the command

This issue is a duplicate of #366. It appears that the original author of #366 closed the issue because they resolved their issue in the short term by using a different driver, but this issue is reproducible in psycopg2-binary==2.8.4 which is a popular choice for postgres.

This is happening because a string gets converted to bytes in python3 without specifying the encoding inside sqlalchemy's _Bytes type (base of LargeBytes)

https://github.com/sqlalchemy/sqlalchemy/blob/7c6bdc9f0d279be40d6f53b68a3a448b2ccfb73f/lib/sqlalchemy/sql/sqltypes.py#L929

def process(value):
    if value is not None:
        value = bytes(value)
    return value

If we manually fix that issue in sqlalchemy, we run into another problem

  File "/Users/aicioara/dev/python/motuz/venv/lib/python3.7/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 119, in decrypt
    decrypted = base64.b64decode(value)
  File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/base64.py", line 87, in b64decode
    return binascii.a2b_base64(s)
binascii.Error: Invalid base64-encoded string: number of data characters (49) cannot be 1 more than a multiple of 4

It looks like an encrypted key such as

u4l3U4adiXfWV0vbD5C6kQ==

Gets transformed into

<psycopg2.extensions.Binary object at 0x10c9d7a80>

# where
# .__str__() == "'u4l3U4adiXfWV0vbD5C6kQ=='::bytea"

And gets saved into the database as

\x75346c33553461646958665756307662443543366b513d3d

Which looks like it's some base16 encoding of the ASCII value of u4l3U4adiXfWV0vbD5C6kQ==