CODEWITHSUNDEEP
pythonpostgresqlencryptionsqlalchemysqlalchemy-utils
Cathy
Cathy

Reputation: 377

Encrypting data into Postgres and decrypting from Postgres using sqlalchemy and ORM

I want to encrypt data into Postgres and then decrypt and read from it. I prefer using sqlalchemy and ORM but if it is difficult to do using sqlalchemy and ORM then I am curious to know the other ways also

I tried using the below code, It is encrypting into the database but it is not asking me for any key or anything for the decryption. May I know why?

import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from sqlalchemy_utils import EncryptedType
from sqlalchemy_utils.types.encrypted.encrypted_type import AesEngine

secret_key = 'secretkey1234'
connection_string = '***********'
engine = create_engine(connection_string)
connection = engine.connect()
sa.orm.configure_mappers()
Session = sessionmaker(bind=connection)
session = Session()
Base = declarative_base()
class User(Base):
    __tablename__ = "user"
    id = sa.Column(sa.Integer, primary_key=True)
    username = sa.Column(EncryptedType(sa.Unicode,secret_key,AesEngine,'pkcs5'))
    number_of_accounts = sa.Column(EncryptedType(sa.Integer,secret_key,AesEngine,'oneandzeroes'))
Base.metadata.create_all(connection)

I run the below code for the decryption:

user_id = user.id
session.expunge_all()
user_instance = session.query(User).get(user_id)
print('username: {}'.format(user_instance.username))

Upvotes: 1

Views: 3069

Answers (1)

abowden1
abowden1

Reputation: 63

You have likely figured this out by now, as this question is a few years old, but for anyone else looking:

You are interacting with your Postgres tables through the model classes you define (in your example User).

When you execute a query, data is returned and passed through the class to determine how to process the response. From your example a query will return results for id, username and number_of_accounts. If you were to log each element returned, id would be processed as an int because that is how it is defined in your model.

Similarly, username and number_of_accounts will also be processed based on the definition in the User class - as an EncryptedType() value. This is a more complex datatype though. Your model defines the key to use for encryption/decryption. Prior to storing the value, the results are decrypted based on the context provided in your model. In this case using the AESEngine and decrypted with the key of 'secretkey1234'. That is why you don't need to specify a key on read. It is already defined in your model.

If you were to run a select * from user limit 1; query directly on your Postgres db, the values displayed for your two encrypted columns would remain encrypted, as you would not be passing the results through your defined model.

Upvotes: 1

Related Questions