email uniqueness is done with case-sensitivity on some databases

[gavinwahl]

Postgres performs string comparison case-sensitively, mysql does it case-insensitive. This means that on postgres, foo@example.com, FOO@example.com and foo@EXAMPLE.com are all distinct emails, and a user could register with each one.

Domain names are not case sensitive, so at least the domain part should lowercased. The local part of an email address is technically case sensitive, but I don't think it is in practice.

The easiest way to accomplish this would be to lowercase email addresses before storing them, but it might be wrong to mangle addresses like that.

[gavinwahl]

A functional index on UPPER(email) would be the ideal solution on postgres, but we'd have to use email__iexact everywhere, and lookups by email wouldn't be indexed at all on mysql.

[pipermerriam]

Maybe we could do get by with just dbindex=True and do our normalization outside of the database. Something similar to what BaseManager does here

Is there a problem just calling .lower() or .upper() on the address part of the email?

[rockymeza]

https://github.com/fusionbox/django-authtools/blob/master/authtools/models.py#L14

Is there anything we can do?

[chris-erickson]

This is a bit of a trouble spot for users entering emails in interesting ways. 👍 for any fix..

[aidanlister]

#31 won't work for anyone on Postgres.

It simply does a .lower() on the incoming login, but not the database User.get operation. So if there's a capitalised email in the database it's impossible to login.

[aidanlister]

Here's a better way:

class AccountManager(models.Manager):
    def get_by_natural_key(self, username):
        field_name = f'{self.model.USERNAME_FIELD}__iexact'
        return self.get(**{field_name: username})

Set this on your custom user model.

[gavinwahl]

Isn't that handled by CaseInsensitiveEmailBackendMixin?

[pawl-bb]

Django-improved-user argues that it's better to store the e-mail addresses in a case sensitive field and use case-insensitive queries to filter for a match: https://django-improved-user.readthedocs.io/en/latest/email_warning.html

@aidanlister's example seems to cover that, but he might also need to have an index on lower(email::text) to prevent duplicates? Without the unique index, another user might be able to create an uppercase version of an existing e-mail and have it match.

Although the first part of the e-mail is supposed to be case sensitive (according to RFC 5321), I don't know of any e-mail providers that actually do that. So maybe it doesn't matter?