Summary

Upon adding a "unsubscribe email" link to the end of our user's emails, showed us that there are some unfortunately messy usernames in our database. It seems that this bug has been fixed, but the data wasn't cleaned up. This PR loosens the restrictions on the unsubscribe email link and we'll have to run some SQL scripts to clean up usernames.

Additionally, there is a "fix" for an error (see below) that celery is generating.

lookit-api-broker  | 2024-07-02 16:03:51.534 [info] <0.779.0> accepting AMQP connection <0.779.0> (172.18.0.5:36826 -> 172.18.0.3:5672)
lookit-api-broker  | 2024-07-02 16:03:51.536 [info] <0.779.0> connection <0.779.0> (172.18.0.5:36826 -> 172.18.0.3:5672): user 'lookit-admin' authenticated and granted access to vhost '/'
lookit-api-broker  | 2024-07-02 16:03:51.550 [warning] <0.779.0> closing AMQP connection <0.779.0> (172.18.0.5:36826 -> 172.18.0.3:5672, vhost: '/', user: 'lookit-admin'):
lookit-api-broker  | client unexpectedly closed TCP connection <-----
lookit-api-broker  | 2024-07-02 16:03:51.551 [info] <0.790.0> Closing all channels from connection '172.18.0.5:36826 -> 172.18.0.3:5672' because it has been closed

SQL

Below are some scripts that will be used in production to clean up usernames.

Find usernames that have whitespace:

select au.username 
from accounts_user au
where au.username like '%' || chr(9) || '%' 
    or au.username like '%' || chr(10) || '%'
    or au.username like '%' || chr(11) || '%' 
    or au.username like '%' || chr(32) || '%'

Find usernames with uppercase characters:

select au.username, au.last_login 
from accounts_user au 
where trim(au.username) != lower(trim(au.username))

Delete "AnonymousUser":

delete from accounts_user au where au.username = 'AnonymousUser'

Update usernames with whitespace:

update accounts_user 
set username = trim(username)
where username in (...,)

Update usernames with uppercase:

update accounts_user 
set username = lower(username)
where username in (...,)

lookit / lookit-api

Handle Malformed Usernames #1432

Summary

SQL

Quality Gate passed