encrypt data in PostgreSQL using ORM

Question

I want to encrypt the data in the PostgreSQL. I am using the below two methods to insert the data, one using ORM, other without ORM

db = sql.create_engine(connection_string)
metadata = sql.schema.MetaData(bind=db, reflect=True)
inputStringtable = sql.Table('person_info', metadata, autoload=True)

######Using ORM########
class RowInputString(object):
    pass
orm.Mapper(RowInputString, inputStringtable)
Sess = orm.sessionmaker(bind=db)
session = Sess()

inputTable = RowInputString()
inputTable.person_id = personId
inputTable.person_name = personName
session.add(inputTable)
session.commit()
################################

######not using ORM
def inserting_data(personId, personName):
    insertData = inputStringtable.insert().values(person_id=personId, person_name=personName)
    conn = db.connect()
    conn.execute(ins)
inserting_data(personId, personName)

I came across the below snippet to the encrypt and send it to database:

INSERT INTO users(login, passwd)
VALUES('my_login', crypt('my_password', gen_salt('md5')));

I find it little difficult how I can use this snippet in my code?

it looks as if crypt hashes a password with a salt. this would be one-way: it would be computationally infeasable to get the plaintext (my_password) back. that is not your use-case as far as i understand... — hiro protagonist
– hiro protagonist, Commented Feb 18, 2019 at 6:40
On the other hand passwords should not be stored encrypted, but hashed. — Ilja Everilä
– Ilja Everilä, Commented Feb 18, 2019 at 6:55

jspcal · Accepted Answer · 2019-02-18 06:47:33Z

1

For general encryption, you can use the EncryptedType SQLAlchemy type.

For password hashing you can define a custom type in SQLAlchemy:

https://github.com/sqlalchemy/sqlalchemy/wiki/DatabaseCrypt

This uses bind_expression of the TypeDecorator API to map the passed-in column value to an expression involving built-in database functions (gen_salt and crypt).

edited Feb 18, 2019 at 6:47

answered Feb 18, 2019 at 6:43

jspcal

52k7 gold badges76 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cathy Over a year ago

Thanks for the reply, so I am able to encrypt using EncryptedType and store in the database. May I know how I can decrypt it?

Cathy Over a year ago

May I know how can I decrypt it?

Collectives™ on Stack Overflow

encrypt data in PostgreSQL using ORM

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related