SQLAlchemy & PassLib

Question

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?

Answer 1

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