Issue #252 Case insensitive login

[c8y3]

This is a proposition to enable case insensitive login as described in issue #252. This change does not require any database migration, since only the retrieval of a user by its login has change. It is now done with a case insensitive query (ilike) However this change assumes no two users were, in a previous version of DFIR-IRIS, created with logins which are the same when compared in a case insensitive way (for instance whitekernel and WhiteKernel) Maybe, in addition to these changes, a script which checks this is the case, should be proposed in the migration note...

[c8y3]

This is a bit raw, but here is the rough idea for a script which would warn when the database does not satisfy the expectation about login being case-insensitive unique:

from sqlalchemy import create_engine
from sqlalchemy import text


engine = create_engine('postgresql+psycopg2://postgres:[email protected]:5432/iris_db', echo=True)


warnings = {}
with engine.connect() as conn:
# careful the quotes matter here!!!
    result = conn.execute(text('select * from "user"'))
    for row in result:
        login = row.user.lower()
        result = conn.execute(text('select * from "user" where lower("user")=:user'), user=login)
        if result.rowcount != 1:
            warnings[login] = result.all()

for (lower_case_login, users) in warnings.items():
    count = len(users)
    print(f'WARNING: there at least {count} users with logins which are case sensitive equal to \'{lower_case_login}\':')
    for user in users:
       print(f'id: {user.id}, user: {user.user}, name: {user.name}')