Turbogears 2: authentication, password in different table, feedback when update

Question

I'm using turbogears 2.2 for writing web application like it seems to be really powerful framework, however there are many blackboxes like authentication as I don't understand them well (repoze.who plugin here).

Requirements

• User password has to be saved in different table
• Decrease database queries and not to load user by every request; however, solve update user (e.g. permissions) when needed
• Do not load password by every user query
• Be ready for openID and similar login
• Have control during authentication (suspended user etc.)

Current state

I have defined basic models in model.auth - user, group, permission - and model.company as foreign key from user. I'm including the user model as most important:

class User(DeclarativeBase):
    __tablename__ = 'user'

    id = Column(Integer, autoincrement = True, primary_key = True)
    email = Column(String, unique = True, nullable = False)
    name = Column(Unicode, nullable = False)
    surname = Column(Unicode, nullable = False)
    phone = Column(String)
    company_id = Column(Integer, ForeignKey('company.id', use_alter = True, name = 'fk_user_company_id'))
    company = relationship('Company', backref = 'users', foreign_keys = [company_id])
    _password = Column('password', Integer, ForeignKey('password.id'))
    active = Column(Boolean, default = True)

    _created = Column(DateTime, default = datetime.now)
    _updated = Column(DateTime)

    def __repr__(self):
        return ('<User: user_name=%s>' % (self.email))

    def __unicode__(self):
        return self.email

    @property
    def permissions(self):
        """Return a set with all permissions granted to the user."""
        perms = set()
        for g in self.groups:
            perms = perms | set(g.permissions)
        return perms

    @classmethod
    def by_email_address(cls, email):
        """Return the user object whose email address is ``email``."""
        return DBSession.query(cls).filter_by(email = email).first()

    @classmethod
    def by_username(cls, username):
        """Return the user object whose user name is ``username``."""
        return DBSession.query(cls).filter_by(_user_name = username).first()

    def _set_password(self, passw):
        ''' Set password. Password is saved in another table and columns references to it via ForeingKey'''
        passwd = DBSession.query(Password).filter_by(id = self._password).first()
        if passwd:
            passwd.password = passw
            DBSession.flush()
            self._password = passwd.id
        else:
            p = Password()
            p.password = passw
            DBSession.add(p)
            DBSession.flush()
            self._password = p.id

    def _get_password(self):
        ''' Return password via ForeingKey'''
        return DBSession.query(Password).filter_by(id = self._password).first().password

    password = synonym('_password', descriptor = property(_get_password, _set_password))

    def validate_password(self, password):
        ''' Validates password. This method has to be also in this class, because repoze.who requires it. '''
        hsh = sha256()
        if isinstance(password, unicode):
            password = password.encode('utf-8')
        hsh.update(password + str(self.password[:64]))
        return self.password[64:] == hsh.hexdigest()

    # This is a hack for repoze.who.plugins.sa, because there is written in code 'user_name' as keyword
    def _set_username(self, email):
        self.email = email

    def _get_username(self):
        return self.email

    def _get_created(self):
        return self._created.strftime(Settings.get('datetime', 'format'))

    def _set_created(self, dt):
        self._created = dt

    def _get_updated(self):
        return self._updated.strftime(Settings.get('datetime', 'format'))

    def _set_updated(self, dt):
        self._updated = dt

    created = synonym('_created', descriptor = property(_get_created, _set_created))
    updated = synonym('_updated', descriptor = property(_get_updated, _set_updated))

    user_name = synonym('email', descriptor = property(_get_username, _set_username))
    username = synonym('email', descriptor = property(_get_username, _set_username))

class Password (DeclarativeBase):
    __tablename__ = 'password'

    id = Column(Integer, autoincrement = True, primary_key = True)
    _password = Column('password', Unicode(128))

    @classmethod
    def _hash_password(cls, password):
        # Make sure password is a str because we cannot hash unicode objects
        if isinstance(password, unicode):
            password = password.encode('utf-8')
        salt = sha256()
        salt.update(os.urandom(60))
        hsh = sha256()
        hsh.update(password + salt.hexdigest())
        password = salt.hexdigest() + hsh.hexdigest()
        # Make sure the hashed password is a unicode object at the end of the
        # process because SQLAlchemy _wants_ unicode objects for Unicode cols
        if not isinstance(password, unicode):
            password = password.decode('utf-8')
        return password

    def _set_password(self, password):
        """Hash ``password`` on the fly and store its hashed version."""
        self._password = self._hash_password(password)

    def _get_password(self):
        """Return the hashed version of the password."""
        return self._password

    password = synonym('_password', descriptor = property(_get_password, _set_password))

    def validate_password(self, password):
        """
        Check the password against existing credentials.

        :param password: the password that was provided by the user to
            try and authenticate. This is the clear text version that we will
            need to match against the hashed one in the database.
        :type password: unicode object.
        :return: Whether the password is valid.
        :rtype: bool

        """
        hsh = sha256()
        if isinstance(password, unicode):
            password = password.encode('utf-8')
        hsh.update(password + str(self.password[:64]))
        return self.password[64:] == hsh.hexdigest()

Here is current state how I get data in app_cfg.py:

class ApplicationAuthMetadata(TGAuthMetadata):
    def __init__(self, sa_auth):
        self.sa_auth = sa_auth
    def get_user(self, identity, userid):
        return self.sa_auth.dbsession.query(self.sa_auth.user_class).options(joinedload('company')).filter_by(user_name = userid).first()
    def get_groups(self, identity, userid):
        return [g.group_name for g in identity['user'].groups]
    def get_permissions(self, identity, userid):
        return [p.permission_name for p in identity['user'].permissions]

And the login action in root.py controller (piece of code I somewhere get):

''' AUTHORIZATION '''
@expose('mizuno.templates.login')
def login(self, came_from = lurl('/')):
    '''Start the user login.'''
    if request.identity and request.identity['user']:
        redirect('/tickets')
    login_counter = request.environ.get('repoze.who.logins', 0)
    if login_counter > 0:
        flash(_('Wrong credentials'), 'warning')
    return {
        'page': 'login',
        'login_counter': str(login_counter),
        'came_from': came_from
    }

However these is getting user information by every request as well the user password with it:

SELECT "user".password AS user_password, "user".id AS user_id, "user".email AS user_email,
    "user".name AS user_name, "user".surname AS user_surname, "user".phone AS user_phone,
    "user".company_id AS user_company_id, "user".active AS user_active, "user"._created AS user__created,
    "user"._updated AS user__updated, company_1.ic AS company_1_ic,
    company_1.id AS company_1_id, company_1.name AS company_1_name, company_1.dic AS company_1_dic,
    company_1.address AS company_1_address, company_1.email AS company_1_email,
    company_1.is_supplier AS company_1_is_supplier, company_1.supplier_id AS company_1_supplier_id,
    company_1.active AS company_1_active, company_1.creator_id AS company_1_creator_id,
    company_1.updator_id AS company_1_updator_id, company_1._created AS company_1__created,
    company_1._updated AS company_1__updated 
FROM "user" LEFT OUTER JOIN company AS company_1 ON company_1.id = "user".company_id 
WHERE "user".email = %(email_1)s 
LIMIT %(param_1)s

Final question

Please, tell me how to understand authentication in Turbogears and fix it to fullfill all the requirements in clean way? Thank you in advance.

UPDATE

Please, provide solution for TG 2.2 as upgrade is not possible.

Answer 1

I suggest you to upgrade to TurboGears 2.3, more recent versions support an authenticate method in ApplicationAuthMetadata which makes easy to provide custom check of username and password validity.

Standard ApplicationAuthMetadata.authenticate implementation looks like:

class ApplicationAuthMetadata(TGAuthMetadata):
    def __init__(self, sa_auth):
        self.sa_auth = sa_auth

    def authenticate(self, environ, identity):
        user = self.sa_auth.dbsession.query(self.sa_auth.user_class).filter_by(user_name=identity['login']).first()
        if user and user.validate_password(identity['password']):
            return identity['login']

    # Here are the get_user, get_groups and get_permissions

If you cannot upgrade TurboGears you must implement a custom repoze.who authenticator which is a bit more complex. You can find some documentation on it at http://turbogears.readthedocs.org/en/latest/turbogears/authentication.html