Ensure email addresses are lowercase throughout

At least all email addresses being input by end users should be
confverted to lowercase to avoid duplicates.

Update existing users, registrations and election candidates to be
lowercase, and add constraints to them

For things like conference contract addresses that are only set by
superusers, we skip the constraints part and let the user take some more
responsibility.

19 files changed, 89 insertions, 29 deletions

diff --git a/postgresqleu/confreg/migrations/0001_initial.py b/postgresqleu/confreg/migrations/0001_initial.py
index 5752f58c..e4f0283d 100644
--- a/postgresqleu/confreg/migrations/0001_initial.py
+++ b/postgresqleu/confreg/migrations/0001_initial.py
@@ -4,6 +4,7 @@ from __future__ import unicode_literals
 from django.db import migrations, models
 import postgresqleu.confreg.models
 import postgresqleu.util.validators
+from postgresqleu.util.fields import LowercaseEmailField
 import postgresqleu.confreg.dbimage
 from django.conf import settings
 import django.core.validators
@@ -46,8 +47,8 @@ class Migration(migrations.Migration):
                 ('startdate', models.DateField(verbose_name='Start date')),
                 ('enddate', models.DateField(verbose_name='End date')),
                 ('location', models.CharField(max_length=128)),
-                ('contactaddr', models.EmailField(max_length=254, verbose_name='Contact address')),
-                ('sponsoraddr', models.EmailField(max_length=254, verbose_name='Sponsor address')),
+                ('contactaddr', LowercaseEmailField(max_length=254, verbose_name='Contact address')),
+                ('sponsoraddr', LowercaseEmailField(max_length=254, verbose_name='Sponsor address')),
                 ('active', models.BooleanField(default=False, verbose_name='Registration open')),
                 ('callforpapersopen', models.BooleanField(default=False, verbose_name="Call for papers open")),
                 ('callforsponsorsopen', models.BooleanField(default=False, verbose_name="Call for sponsors open")),
@@ -138,7 +139,7 @@ class Migration(migrations.Migration):
                 ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                 ('firstname', models.CharField(max_length=100, verbose_name='First name')),
                 ('lastname', models.CharField(max_length=100, verbose_name='Last name')),
-                ('email', models.EmailField(max_length=254, verbose_name='E-mail address')),
+                ('email', LowercaseEmailField(max_length=254, verbose_name='E-mail address')),
                 ('company', models.CharField(max_length=100, verbose_name='Company', blank=True)),
                 ('address', models.TextField(max_length=200, verbose_name='Address', blank=True)),
                 ('phone', models.CharField(max_length=100, verbose_name='Phone number', blank=True)),
diff --git a/postgresqleu/confreg/migrations/0041_notifications.py b/postgresqleu/confreg/migrations/0041_notifications.py
index 03015aac..b90a0211 100644
--- a/postgresqleu/confreg/migrations/0041_notifications.py
+++ b/postgresqleu/confreg/migrations/0041_notifications.py
@@ -3,6 +3,7 @@
 from __future__ import unicode_literals
 
 from django.db import migrations, models
+from postgresqleu.util.fields import LowercaseEmailField
 
 
 class Migration(migrations.Migration):
@@ -15,7 +16,7 @@ class Migration(migrations.Migration):
         migrations.AddField(
             model_name='conference',
             name='notifyaddr',
-            field=models.EmailField(null=True, max_length=254, verbose_name='Notification address'),
+            field=LowercaseEmailField(null=True, max_length=254, verbose_name='Notification address'),
         ),
 
         migrations.RunSQL("UPDATE confreg_conference SET notifyaddr=contactaddr WHERE notifyaddr IS NULL"),
@@ -23,7 +24,7 @@ class Migration(migrations.Migration):
         migrations.AlterField(
             model_name='conference',
             name='notifyaddr',
-            field=models.EmailField(null=False, max_length=254, verbose_name='Notification address'),
+            field=LowercaseEmailField(null=False, max_length=254, verbose_name='Notification address'),
         ),
 
         migrations.AddField(
diff --git a/postgresqleu/confreg/migrations/0050_lowercase_email.py b/postgresqleu/confreg/migrations/0050_lowercase_email.py
new file mode 100644
index 00000000..22f205c7
--- /dev/null
+++ b/postgresqleu/confreg/migrations/0050_lowercase_email.py
@@ -0,0 +1,24 @@
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.18 on 2019-07-10 22:29
+from __future__ import unicode_literals
+
+from django.db import migrations
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('confreg', '0049_sessiontags'),
+    ]
+
+    operations = [
+        # Slightly ugly, but were going to add this to auth_user even though we're in the
+        # confreg application.
+        migrations.RunSQL("UPDATE auth_user SET email=lower(email) WHERE email!=lower(email)"),
+        migrations.RunSQL("ALTER TABLE auth_user ADD CONSTRAINT email_must_be_lowercase CHECK (email=lower(email))"),
+        migrations.RunSQL("CREATE UNIQUE INDEX auth_user_email_lower_key ON auth_user USING btree(lower(email))"),
+
+        # Then for our own tables as well
+        migrations.RunSQL("UPDATE confreg_conferenceregistration SET email=lower(email) WHERE email!=lower(email)"),
+        migrations.RunSQL("ALTER TABLE confreg_conferenceregistration ADD CONSTRAINT email_must_be_lowercase CHECK (email=lower(email))"),
+    ]
diff --git a/postgresqleu/confreg/models.py b/postgresqleu/confreg/models.py
index 19b21011..22a1c3da 100644
--- a/postgresqleu/confreg/models.py
+++ b/postgresqleu/confreg/models.py
@@ -15,6 +15,8 @@ from postgresqleu.util.validators import validate_lowercase, validate_urlname
 from postgresqleu.util.validators import TwitterValidator
 from postgresqleu.util.validators import PictureUrlValidator
 from postgresqleu.util.forms import ChoiceArrayField
+from postgresqleu.util.fields import LowercaseEmailField
+
 
 from postgresqleu.confreg.dbimage import SpeakerImageStorage
 
@@ -138,9 +140,9 @@ class Conference(models.Model):
     promopicurl = models.URLField(blank=True, null=False, verbose_name="URL to promo picture", validators=[PictureUrlValidator(aspect=2.3)])
     promotext = models.TextField(null=False, blank=True, max_length=1000, verbose_name="Promotion text")
     timediff = models.IntegerField(null=False, blank=False, default=0)
-    contactaddr = models.EmailField(blank=False, null=False, verbose_name="Contact address")
-    sponsoraddr = models.EmailField(blank=False, null=False, verbose_name="Sponsor address")
-    notifyaddr = models.EmailField(blank=False, null=False, verbose_name="Notification address")
+    contactaddr = LowercaseEmailField(blank=False, null=False, verbose_name="Contact address")
+    sponsoraddr = LowercaseEmailField(blank=False, null=False, verbose_name="Sponsor address")
+    notifyaddr = LowercaseEmailField(blank=False, null=False, verbose_name="Notification address")
     notifyregs = models.BooleanField(blank=False, null=False, default=False, verbose_name="Notify about registrations")
     active = models.BooleanField(blank=False, null=False, default=False, verbose_name="Registration open")
     callforpapersopen = models.BooleanField(blank=False, null=False, default=False, verbose_name="Call for papers open")
@@ -481,7 +483,7 @@ class ConferenceRegistration(models.Model):
     registrator = models.ForeignKey(User, null=False, blank=False, related_name="registrator", on_delete=models.CASCADE)
     firstname = models.CharField(max_length=100, null=False, blank=False, verbose_name="First name")
     lastname = models.CharField(max_length=100, null=False, blank=False, verbose_name="Last name")
-    email = models.EmailField(null=False, blank=False, verbose_name="E-mail address")
+    email = LowercaseEmailField(null=False, blank=False, verbose_name="E-mail address")
     company = models.CharField(max_length=100, null=False, blank=True, verbose_name="Company")
     address = models.TextField(max_length=200, null=False, blank=True, verbose_name="Address")
     country = models.ForeignKey(Country, null=True, blank=True, verbose_name="Country", on_delete=models.CASCADE)
diff --git a/postgresqleu/confreg/views.py b/postgresqleu/confreg/views.py
index 37b0b996..97a659b7 100644
--- a/postgresqleu/confreg/views.py
+++ b/postgresqleu/confreg/views.py
@@ -247,7 +247,7 @@ def register(request, confname, whatfor=None):
 
         # No previous registration, grab some data from the user profile
         reg = ConferenceRegistration(conference=conference, attendee=request.user)
-        reg.email = request.user.email
+        reg.email = request.user.email.lower()
         reg.firstname = request.user.first_name
         reg.lastname = request.user.last_name
         reg.created = datetime.now()
@@ -406,11 +406,11 @@ def multireg(request, confname, regid=None):
                 # a separate page for it.
                 # Create a registration but don't save it until we have
                 # details entered.
-                reg.email = newform.cleaned_data['email']
+                reg.email = newform.cleaned_data['email'].lower()
                 regform = ConferenceRegistrationForm(request.user, instance=reg, regforother=True)
                 return render_conference_response(request, conference, 'reg', 'confreg/regmulti_form.html', {
                     'form': regform,
-                    '_email': newform.cleaned_data['email'],
+                    '_email': newform.cleaned_data['email'].lower(),
                 })
         elif request.POST['submit'] == 'Cancel':
             return HttpResponseRedirect(redir_root)
@@ -421,7 +421,7 @@ def multireg(request, confname, regid=None):
                 reg.delete()
             return HttpResponseRedirect(redir_root)
         elif request.POST['submit'] == 'Save':
-            reg.email = request.POST['_email']
+            reg.email = request.POST['_email'].lower()
             regform = ConferenceRegistrationForm(request.user, data=request.POST, instance=reg, regforother=True)
             if regform.is_valid():
                 reg = regform.save(commit=False)
@@ -1476,7 +1476,7 @@ def public_speaker_lookup(request, confname):
     # This is a lookup for speakers that's public. To avoid harvesting, we allow
     # only *prefix* matching of email addresses, and you have to type at least 6 characters
     # before you get anything.
-    prefix = request.GET['query']
+    prefix = request.GET['query'].lower()
     if len(prefix) > 5:
         vals = [{
             'id': s.id,
diff --git a/postgresqleu/confsponsor/scanning.py b/postgresqleu/confsponsor/scanning.py
index ce9fe07d..ec47e860 100644
--- a/postgresqleu/confsponsor/scanning.py
+++ b/postgresqleu/confsponsor/scanning.py
@@ -43,7 +43,7 @@ def sponsor_scanning(request, sponsorid):
                 messages.warning(request, "Cannot add empty address")
                 return HttpResponseRedirect(".")
             try:
-                reg = ConferenceRegistration.objects.get(conference=sponsor.conference, email=request.POST.get('email'))
+                reg = ConferenceRegistration.objects.get(conference=sponsor.conference, email=request.POST.get('email').lower())
                 if not reg.payconfirmedat:
                     messages.error(request, "Attendee is not confirmed")
                     return HttpResponseRedirect(".")
diff --git a/postgresqleu/confsponsor/views.py b/postgresqleu/confsponsor/views.py
index cec66342..24934466 100644
--- a/postgresqleu/confsponsor/views.py
+++ b/postgresqleu/confsponsor/views.py
@@ -136,15 +136,15 @@ def sponsor_manager_add(request, sponsorid):
         messages.warning(request, "Email not specified")
         return HttpResponseRedirect('../../')
     try:
-        user = User.objects.get(email=request.POST['email'])
+        user = User.objects.get(email=request.POST['email'].lower())
         sponsor.managers.add(user)
         sponsor.save()
         messages.info(request, "User %s added as manager." % user.username)
         return HttpResponseRedirect('../../')
     except User.DoesNotExist:
         # Try an upstream search if the user is not here
-        users = user_search(request.POST['email'])
-        if len(users) == 1 and users[0]['e'] == request.POST['email']:
+        users = user_search(request.POST['email'].lower())
+        if len(users) == 1 and users[0]['e'] == request.POST['email'].lower():
             try:
                 user_import(users[0]['u'])
                 try:
@@ -158,7 +158,7 @@ def sponsor_manager_add(request, sponsorid):
             except Exception as e:
                 messages.warning(request, "Failed to import user with email %s (userid %s): %s" % (users[0]['e'], users[0]['u'], e))
         else:
-            messages.warning(request, "Could not find user with email address %s" % request.POST['email'])
+            messages.warning(request, "Could not find user with email address %s" % request.POST['email'].lower())
         return HttpResponseRedirect('../../')
 
 
diff --git a/postgresqleu/elections/migrations/0001_initial.py b/postgresqleu/elections/migrations/0001_initial.py
index 84873238..7c2317ed 100644
--- a/postgresqleu/elections/migrations/0001_initial.py
+++ b/postgresqleu/elections/migrations/0001_initial.py
@@ -2,6 +2,7 @@
 from __future__ import unicode_literals
 
 from django.db import migrations, models
+from postgresqleu.util.fields import LowercaseEmailField
 
 
 class Migration(migrations.Migration):
@@ -15,7 +16,7 @@ class Migration(migrations.Migration):
             fields=[
                 ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                 ('name', models.CharField(max_length=100)),
-                ('email', models.EmailField(max_length=200)),
+                ('email', LowercaseEmailField(max_length=200)),
                 ('presentation', models.TextField()),
             ],
         ),
diff --git a/postgresqleu/elections/migrations/0003_lowercase_email.py b/postgresqleu/elections/migrations/0003_lowercase_email.py
new file mode 100644
index 00000000..ea8fc1dc
--- /dev/null
+++ b/postgresqleu/elections/migrations/0003_lowercase_email.py
@@ -0,0 +1,17 @@
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.18 on 2019-07-10 22:34
+from __future__ import unicode_literals
+
+from django.db import migrations
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('elections', '0002_auto_20160108_1924'),
+    ]
+
+    operations = [
+        migrations.RunSQL("UPDATE elections_candidate SET email=lower(email) WHERE email!=lower(email)"),
+        migrations.RunSQL("ALTER TABLE elections_candidate ADD CONSTRAINT email_must_be_lowercase CHECK (email=lower(email))"),
+    ]
diff --git a/postgresqleu/elections/models.py b/postgresqleu/elections/models.py
index a331e4ae..d3171ec0 100644
--- a/postgresqleu/elections/models.py
+++ b/postgresqleu/elections/models.py
@@ -1,4 +1,5 @@
 from django.db import models
+from postgresqleu.util.fields import LowercaseEmailField
 from postgresqleu.membership.models import Member
 
 
@@ -20,7 +21,7 @@ class Election(models.Model):
 class Candidate(models.Model):
     election = models.ForeignKey(Election, null=False, blank=False, on_delete=models.CASCADE)
     name = models.CharField(max_length=100, null=False, blank=False)
-    email = models.EmailField(max_length=200, null=False, blank=False)
+    email = LowercaseEmailField(max_length=200, null=False, blank=False)
     presentation = models.TextField(null=False, blank=False)
 
     def __str__(self):
diff --git a/postgresqleu/invoices/admin.py b/postgresqleu/invoices/admin.py
index bfdaf269..ad58715a 100644
--- a/postgresqleu/invoices/admin.py
+++ b/postgresqleu/invoices/admin.py
@@ -26,7 +26,7 @@ class InvoiceAdminForm(SelectableWidgetAdminFormMixin, ConcurrentProtectedModelF
     def clean_recipient_email(self):
         if 'finalized' in self.cleaned_data:
             raise ValidationError("Can't edit email field on a finalized invoice!")
-        return self.cleaned_data['recipient_email']
+        return self.cleaned_data['recipient_email'].lower()
 
     def clean_recipient_name(self):
         if 'finalized' in self.cleaned_data:
diff --git a/postgresqleu/invoices/forms.py b/postgresqleu/invoices/forms.py
index a122df9e..e7e4463d 100644
--- a/postgresqleu/invoices/forms.py
+++ b/postgresqleu/invoices/forms.py
@@ -73,7 +73,7 @@ class InvoiceForm(forms.ModelForm):
         if not self.cleaned_data['recipient_user'] and self.cleaned_data.get('recipient_email', None):
             # User not specified. If we can find one by email, auto-populate
             # the field.
-            matches = User.objects.filter(email=self.cleaned_data['recipient_email'])
+            matches = User.objects.filter(email=self.cleaned_data['recipient_email'].lower())
             if len(matches) == 1:
                 self.cleaned_data['recipient_user'] = matches[0]
 
diff --git a/postgresqleu/invoices/migrations/0001_initial.py b/postgresqleu/invoices/migrations/0001_initial.py
index 6ec9af40..c5285aab 100644
--- a/postgresqleu/invoices/migrations/0001_initial.py
+++ b/postgresqleu/invoices/migrations/0001_initial.py
@@ -2,6 +2,7 @@
 from __future__ import unicode_literals
 
 from django.db import migrations, models
+from postgresqleu.util.fields import LowercaseEmailField
 from django.conf import settings
 
 
@@ -16,7 +17,7 @@ class Migration(migrations.Migration):
             name='Invoice',
             fields=[
                 ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
-                ('recipient_email', models.EmailField(max_length=254, blank=True)),
+                ('recipient_email', LowercaseEmailField(max_length=254, blank=True)),
                 ('recipient_name', models.CharField(max_length=100)),
                 ('recipient_address', models.TextField()),
                 ('recipient_secret', models.CharField(max_length=64, null=True, blank=True)),
diff --git a/postgresqleu/invoices/models.py b/postgresqleu/invoices/models.py
index 65c4aa52..ef6e4388 100644
--- a/postgresqleu/invoices/models.py
+++ b/postgresqleu/invoices/models.py
@@ -12,6 +12,7 @@ from .payment import PaymentMethodWrapper
 
 from postgresqleu.util.validators import ListOfEmailAddressValidator
 from postgresqleu.util.checksum import luhn
+from postgresqleu.util.fields import LowercaseEmailField
 from postgresqleu.accounting.models import Account, JournalEntry
 
 
@@ -90,7 +91,7 @@ class Invoice(models.Model):
     # a recipient is matched, the recipient_user field "owns" the
     # recipient information.
     recipient_user = models.ForeignKey(User, null=True, blank=True, on_delete=models.CASCADE)
-    recipient_email = models.EmailField(blank=True, null=False)
+    recipient_email = LowercaseEmailField(blank=True, null=False)
     recipient_name = models.CharField(max_length=100, blank=False, null=False)
     recipient_address = models.TextField(blank=False, null=False)
     recipient_secret = models.CharField(max_length=64, blank=True, null=True)
diff --git a/postgresqleu/membership/migrations/0004_membership_config.py b/postgresqleu/membership/migrations/0004_membership_config.py
index c5ba084d..63c38ad6 100644
--- a/postgresqleu/membership/migrations/0004_membership_config.py
+++ b/postgresqleu/membership/migrations/0004_membership_config.py
@@ -6,6 +6,7 @@ import django.core.validators
 from django.conf import settings
 from django.db import migrations, models
 
+from postgresqleu.util.fields import LowercaseEmailField
 from postgresqleu.membership.models import MembershipConfiguration
 
 
@@ -31,7 +32,7 @@ class Migration(migrations.Migration):
             name='MembershipConfiguration',
             fields=[
                 ('id', models.IntegerField(primary_key=True, serialize=False)),
-                ('sender_email', models.EmailField(max_length=254)),
+                ('sender_email', LowercaseEmailField(max_length=254)),
                 ('membership_years', models.IntegerField(default=1, help_text='Membership length in years', validators=[django.core.validators.MinValueValidator(1), django.core.validators.MaxValueValidator(10)], verbose_name='Membership length')),
                 ('membership_cost', models.IntegerField(default=10, validators=[django.core.validators.MinValueValidator(1)], verbose_name='Membership cost')),
                 ('country_validator', models.CharField(max_length=100, blank=True, choices=[('europe', 'Must be from European country')], help_text='Validate member countries against this rule', verbose_name='Country validator')),
diff --git a/postgresqleu/membership/models.py b/postgresqleu/membership/models.py
index d95df19d..f01d9d56 100644
--- a/postgresqleu/membership/models.py
+++ b/postgresqleu/membership/models.py
@@ -3,6 +3,7 @@ from django.core.validators import MinValueValidator, MaxValueValidator
 from django.contrib.auth.models import User
 from django.conf import settings
 
+from postgresqleu.util.fields import LowercaseEmailField
 from postgresqleu.countries.models import Country
 from postgresqleu.invoices.models import Invoice, InvoicePaymentMethod
 from postgresqleu.membership.util import country_validator_choices
@@ -12,7 +13,7 @@ from datetime import date, datetime, timedelta
 
 class MembershipConfiguration(models.Model):
     id = models.IntegerField(null=False, blank=False, primary_key=True)
-    sender_email = models.EmailField(null=False, blank=False)
+    sender_email = LowercaseEmailField(null=False, blank=False)
     membership_years = models.IntegerField(null=False, blank=False, default=1,
                                            validators=[MinValueValidator(1), MaxValueValidator(10)],
                                            verbose_name="Membership length",
diff --git a/postgresqleu/trustlypayment/views.py b/postgresqleu/trustlypayment/views.py
index 1401ab02..6f32eb20 100644
--- a/postgresqleu/trustlypayment/views.py
+++ b/postgresqleu/trustlypayment/views.py
@@ -44,7 +44,7 @@ def invoicepayment_secret(request, paymentmethod, invoiceid, secret):
             enduserid = request.user.username
             first = request.user.first_name
             last = request.user.last_name
-            email = request.user.email
+            email = request.user.email.lower()
         else:
             first = last = email = None
             # For secret payments, use the invoice secret as the identifier
diff --git a/postgresqleu/util/backendlookups.py b/postgresqleu/util/backendlookups.py
index 136cbbfa..e476f202 100644
--- a/postgresqleu/util/backendlookups.py
+++ b/postgresqleu/util/backendlookups.py
@@ -53,7 +53,7 @@ class GeneralAccountLookup(LookupBase):
             {
                 'id': u.id,
                 'value': '{0} {1} ({2})'.format(u.first_name, u.last_name, u.username),
-                'email': u.email,
+                'email': u.email.lower(),
             }
             for u in User.objects.filter(
                 Q(username__icontains=query) | Q(first_name__icontains=query) | Q(last_name__icontains=query)
diff --git a/postgresqleu/util/fields.py b/postgresqleu/util/fields.py
new file mode 100644
index 00000000..b1ed1190
--- /dev/null
+++ b/postgresqleu/util/fields.py
@@ -0,0 +1,9 @@
+from django.db import models
+
+
+class LowercaseEmailField(models.EmailField):
+    def get_prep_value(self, value):
+        value = super(models.EmailField, self).get_prep_value(value)
+        if value is not None:
+            value = value.lower()
+        return value