Email address rejected if not lowercase

chrisrouch · September 6, 2018, 9:11am

One of our users received a traceback with this at the end:

IntegrityError) new row for relation “emails” violates check constraint “ck_emails_lowercase_email”
DETAIL: Failing row contains (3111, 3207, Alan.Mick@jhuapl.edu, t, f).

Is there a reason why the email has to be lowercase? If not, can I request that this check is removed.

Regards,

Chris

ThiefMaster · September 6, 2018, 9:15am

Where did this happen? Any code in Indico that deals with emails is supposed to convert the email address to lowercase; if it doesn’t that’s a bug.

The reason why we enforce lowercase is that otherwise you are likely to end up with duplicates that just differ in casing.

chrisrouch · September 6, 2018, 9:21am

His comment was : After I just tried to create a new user, a window popped up telling me that “There was a database error while processing [my] request”.
The traceback looks like this:

Traceback
---------
Traceback (most recent call last):
  File "/opt/indico/.venv/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/opt/indico/.venv/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/opt/indico/.venv/lib/python2.7/site-packages/indico/web/flask/util.py", line 114, in wrapper
    return obj().process()
  File "/opt/indico/.venv/lib/python2.7/site-packages/indico/web/rh.py", line 299, in process
    handle_sqlalchemy_database_error()  # this will re-raise an exception
  File "/opt/indico/.venv/lib/python2.7/site-packages/indico/web/rh.py", line 289, in process
    res = self._do_process()
  File "/opt/indico/.venv/lib/python2.7/site-packages/indico/web/rh.py", line 259, in _do_process
    rv = self._process()
  File "/opt/indico/.venv/lib/python2.7/site-packages/indico/modules/users/controllers.py", line 416, in _process
    user = create_user(data.pop('email'), data, identity, from_moderation=True)
  File "/opt/indico/.venv/lib/python2.7/site-packages/indico/modules/users/operations.py", line 67, in create_user
    user.settings.set_multi(settings)
  File "/opt/indico/.venv/lib/python2.7/site-packages/indico/modules/users/models/settings.py", line 66, in wrapper
    return f(self, user, *args, **kwargs)
  File "/opt/indico/.venv/lib/python2.7/site-packages/indico/modules/users/models/settings.py", line 123, in set_multi
    UserSetting.set_multi(self.module, items, **user)
  File "/opt/indico/.venv/lib/python2.7/site-packages/indico/core/settings/models/base.py", line 144, in set_multi
    existing = cls.get_all_settings(module, **kwargs)
  File "/opt/indico/.venv/lib/python2.7/site-packages/indico/core/settings/models/base.py", line 113, in get_all_settings
    return {s.name: s for s in cls.find(module=module, **kwargs)}
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2924, in __iter__
    self.session._autoflush()
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1444, in _autoflush
    util.raise_from_cause(e)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1434, in _autoflush
    self.flush()
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
    flush_context.execute()
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 866, in _emit_insert_statements
    execute(statement, params)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/opt/indico/.venv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (psycopg2.IntegrityError) new row for relation "emails" violates check constraint "ck_emails_lowercase_email"
DETAIL:  Failing row contains (3110, 3206, Alan.Mick@jhuapl.edu, t, f).
 [SQL: 'INSERT INTO users.emails (user_id, email, is_primary, is_user_deleted) VALUES (%(user_id)s, %(email)s, %(is_primary)s, %(is_user_deleted)s) RETURNING users.emails.id'] [parameters: {'is_primary': True, 'user_id': 3206, 'email': u'Alan.Mick@jhuapl.edu', 'is_user_deleted': False}] (Background on this error at: http://sqlalche.me/e/gkpj)

ThiefMaster · September 6, 2018, 11:29am

Ah, looks like this was an admin manually creating a user? Looks like we forgot the lowercase conversion there. If you want to fix it before a release contains a proper fix, you can just call .lower() in there after getting the email. i.e. data.pop('email').lower():

github.com

indico/indico/blob/master/indico/modules/users/controllers.py#L419


def _process(self):
    form = AdminAccountRegistrationForm()
    if form.validate_on_submit():
        data = form.data
        if data.pop('create_identity', False):
            identity = Identity(provider='indico', identifier=data.pop('username'), password=data.pop('password'))
        else:
            identity = None
            data.pop('username', None)
            data.pop('password', None)
        user = create_user(data.pop('email'), data, identity, from_moderation=True)
        msg = Markup('{} <a href="{}">{}</a>').format(
            escape(_('The account has been created.')),
            url_for('users.user_profile', user),
            escape(_('Show details'))
        )
        flash(msg, 'success')
        return jsonify_data()
    return jsonify_template('users/users_admin_create.html', form=form)

(or just ask your admins to enter email addresses in lowercase)

chrisrouch · September 6, 2018, 12:15pm

I’m reluctant to make changes to a production system for a minor problem like this, so I’ll take your second suggestion.

Thanks for the fast response.

Chris