database should not have a unique constraint on username

[heartsucker]

Traceback (most recent call last):
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: users.username

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/heartsucker/code/freedomofpress/securedrop-client/securedrop_client/logic.py", line 176, in <lambda>
    lambda: self.completed_api_call(new_thread_id, callback))
  File "/home/heartsucker/code/freedomofpress/securedrop-client/securedrop_client/logic.py", line 223, in completed_api_call
    user_callback(result_data)
  File "/home/heartsucker/code/freedomofpress/securedrop-client/securedrop_client/logic.py", line 380, in on_synced
    remote_replies, self.data_dir)
  File "/home/heartsucker/code/freedomofpress/securedrop-client/securedrop_client/storage.py", line 90, in update_local_storage
    update_replies(remote_replies, local_replies, session, data_dir)
  File "/home/heartsucker/code/freedomofpress/securedrop-client/securedrop_client/storage.py", line 211, in update_replies
    reply.journalist_username, session)
  File "/home/heartsucker/code/freedomofpress/securedrop-client/securedrop_client/storage.py", line 245, in find_or_create_user
    session.commit()
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 954, in commit
    self.transaction.commit()
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2313, in flush
    self._flush(objects)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2440, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
    raise value
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2404, in _flush
    flush_context.execute()
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 395, in execute
    rec.execute(self)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 560, in execute
    uow
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 872, in _emit_insert_statements
    execute(statement, params)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/heartsucker/.local/share/virtualenvs/securedrop-client-GExBtLKv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: users.username [SQL: 'INSERT INTO users (uuid, username) VALUES (?, ?)'] [parameters: ('8b4e5016-1758-4269-bed5-08b88b8b437f', 'journalist')] (Background on this error at: http://sqlalche.me/e/gkpj)

[redshiftzero]

So this can occur in dev when you use the same database / client run with multiple runs of the development container (probably how you found this), i.e.:

1. Start client
2. Start container
3. Login to client, uuid=X and username=journalist added to database
4. Restart container
5. Login to client, uuid=Y and username=journalist added to database
6. Unique constraint violation since username=journalist is associated with two rows, uuid=X and uuid=Y

And imho you are right that this is a bug, since this can occur in prod (albeit in a less likely scenario), e.g.:

1. User signs in to client with username=A, they have uuid=B
2. User with uuid=B changes their username to username=C via web application
3. Another user with uuid=D changes their username to username=A
4. User with uuid=D logs in
5. Unique constraint violation since username=A is now associated with uuid=B and uuid=D

Proposed solution: Remove unique constraint on username column in users table