SQLAlchemy & PassLib

hash, passwords, pyramid, python, sqlalchemy

Solution

PasswordType from sqlalchemy-utils should be the best fit for this issue. It uses passlib. Snipped from the docs:

The following usage will create a password column that will automatically hash new passwords as pbkdf2_sha512 but still compare passwords against pre-existing md5_crypt hashes. As passwords are compared; the password hash in the database will be updated to be pbkdf2_sha512.

class Model(Base):
    password = sa.Column(PasswordType(
        schemes=[
            'pbkdf2_sha512',
            'md5_crypt'
        ],
        deprecated=['md5_crypt']
    ))

Verifying password is as easy as:

target = Model()
target.password = 'b'
# '$5$rounds=80000$H.............'
target.password == 'b'
# True

Problem

tl;dr -- How do I use a Python-side library such as PassLib to hash passwords before inserting them into a MySQL DB with SQLAlchemy? Alright, so I've been banging my head on my desk for a day or two trying to figure this out, so here it goes: I am writing a web application using Pyramid/SQLAlchemy and I'm trying to interface with my MySQL database's Users table. Ultimately, I want to do something like the following: Compare a password to the hash: ``` if user1.password == 'supersecret' ``` Insert a new password: ``` user2.password = 'supersecret' ``` I'd like to be able to use PassLib to hash my passwords before they go to the database, and I'm not really a fan of using the built-in MySQL SHA2 function since it's not salted. However, just to try it, I do have this working using the SQL-side function: ``` from sqlalchemy import func, TypeDecorator, type_coerce from sqlalchemy.dialects.mysql import CHAR, VARCHAR, INTEGER from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column class SHA2Password(TypeDecorator): """Applies the SHA2 function to incoming passwords.""" impl = CHAR(64) def bind_expression(self, bindvalue): return func.sha2(bindvalue, 256) class comparator_factory(CHAR.comparator_factory): def __eq__(self, other): local_pw = type_coerce(self.expr, CHAR) return local_pw == func.sha2(other, 256) class User(Base): __tablename__ = 'Users' _id = Column('userID', INTEGER(unsigned=True), primary_key=True) username = Column(VARCHAR(length=64)) password = Column(SHA2Password(length=64)) def __init__(self, username, password): self.username = username self.password = password ``` This was copied from the example 2 at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DatabaseCrypt So that works and allows me to use the built-in MySQL SHA2 function (by calling `func.sha2()`) and do exactly what I want. However, now I'm trying to replace this with PassLib on the Python side. PassLib presents two functions: one to create a new password hash, and one to verify a password: ``` from passlib.hash import sha256_crypt new_password = sha256_crypt.encrypt("supersecret") sha256_crypt.verify("supersecret", new_password) ``` I can't quite figure out how to actually implement this. Having read all the documentation, I think it is either a different form of TypeDecorator, a custom type declaration, a hybrid value, or a hybrid property. I tried following this, but it doesn't really make sense to me nor does the code suggested there actually run. So, to sum up my question -- how do I overload the `=` and `==` operators so that they run things through the appropriate hash functions?

Original source