Tests: Tests the credentials check system

* gn3/auth/authentication.py: Fix issues caught by tests
* tests/unit/auth/test_credentials.py: Add fixtures and tests for credentials
  checking

2 files changed, 84 insertions, 5 deletions

diff --git a/gn3/auth/authentication.py b/gn3/auth/authentication.py
index 781380e..42ceacb 100644
--- a/gn3/auth/authentication.py
+++ b/gn3/auth/authentication.py
@@ -11,11 +11,14 @@ def credentials_in_database(cursor, email: str, password: str) -> bool:
         ("SELECT "
          "users.email, user_credentials.password "
          "FROM users LEFT JOIN user_credentials "
-         "ON users.email = :email"),
+         "ON users.user_id = user_credentials.user_id "
+         "WHERE users.email = :email"),
         {"email": email})
     results = cursor.fetchall()
     if len(results) == 0:
         return False
 
-    assert len(results) > 1, "Expected one row."
-    return (email == row[0] and bcrypt.checkpw(value.encode("utf-8"), row[1]))
+    assert len(results) == 1, "Expected one row."
+    row = results[0]
+    return (email == row[0] and
+            bcrypt.checkpw(password.encode("utf-8"), row[1]))
diff --git a/tests/unit/auth/test_credentials.py b/tests/unit/auth/test_credentials.py
index fbfd303..c1fc97a 100644
--- a/tests/unit/auth/test_credentials.py
+++ b/tests/unit/auth/test_credentials.py
@@ -3,19 +3,95 @@ import sqlite3
 
 import pytest
 from contextlib import closing
+from yoyo.migrations import MigrationList
 from hypothesis import given, settings, strategies, HealthCheck
 
+from tests.unit.auth.conftest import migrations_up_to
 from gn3.auth.authentication import credentials_in_database
+from gn3.migrations import get_migration, apply_migrations, rollback_migrations
+
+@pytest.fixture
+def with_credentials_table(backend, auth_testdb_path):
+    migrations_dir = "migrations/auth"
+    migration = f"{migrations_dir}/20221103_02_sGrIs-create-user-credentials-table.py"
+    migrations = (migrations_up_to(migration, migrations_dir) +
+                  MigrationList([get_migration(migration)]))
+    apply_migrations(backend, migrations)
+    with closing(sqlite3.connect(auth_testdb_path)) as conn:
+        yield conn
+
+    rollback_migrations(backend, migrations)
+
+@pytest.fixture
+def with_credentials(with_credentials_table):
+    with closing(with_credentials_table.cursor()) as cursor:
+        cursor.executemany(
+            "INSERT INTO users VALUES (:user_id, :email, :name)",
+            ({"user_id": "82552014-21ee-4321-b96a-b8788b97b862",
+              "email": "first@test.user",
+              "name": "First Test User"
+              },
+             {"user_id": "bdd5cb7a-072d-4c2b-9872-d0cecb718523",
+              "email": "second@test.user",
+              "name": "Second Test User"
+              }))
+        cursor.executemany(
+            "INSERT INTO user_credentials VALUES (:user_id, :password)",
+            ({"user_id": "82552014-21ee-4321-b96a-b8788b97b862",
+              "password": b'$2b$12$LAh1PYtUgAFK7d5fA0EfL.4AdTZuYEAfzwO.p.jXVboxcP8bXNj7a'
+              },
+             {"user_id": "bdd5cb7a-072d-4c2b-9872-d0cecb718523",
+              "password": b'$2b$12$zX77QCFSJuwIjAZGc0Jq5.rCWMHEMKD9Zf3Ay4C0AzwsiZ7SSPdKO'
+              }))
+
+        yield with_credentials_table
+
+        cursor.executemany("DELETE FROM user_credentials WHERE user_id=?",
+                           (("82552014-21ee-4321-b96a-b8788b97b862",),
+                            ("bdd5cb7a-072d-4c2b-9872-d0cecb718523",)))
+        cursor.executemany("DELETE FROM users WHERE user_id=?",
+                           (("82552014-21ee-4321-b96a-b8788b97b862",),
+                            ("bdd5cb7a-072d-4c2b-9872-d0cecb718523",)))
 
 @pytest.mark.unit_test
 @given(strategies.emails(), strategies.text())
 @settings(suppress_health_check=[HealthCheck.function_scoped_fixture])
-def test_credentials_not_in_database(conn_after_auth_migrations, email, password):
+def test_credentials_not_in_database(with_credentials, email, password):
     """
     GIVEN: credentials that do not exist in the database
     WHEN: the `credentials_in_database` function is run against the credentials
     THEN: check that the function returns false in all cases.
     """
-    with closing(conn_after_auth_migrations.cursor()) as cursor:
+    with closing(with_credentials.cursor()) as cursor:
+        results = credentials_in_database(cursor, email, password)
+        assert credentials_in_database(cursor, email, password) is False
+
+@pytest.mark.unit_test
+@pytest.mark.parametrize(
+    "email,password",
+    (("first@test.user", "wrongpassword"),
+     ("first@tes.user", "testuser01")))
+def test_partially_wrong_credentials(with_credentials, email, password):
+    """
+    GIVEN: credentials that exist in the database
+    WHEN: the credentials are checked with partially wrong values
+    THEN: the check fails since the credentials are not correct
+    """
+    with closing(with_credentials.cursor()) as cursor:
         results = credentials_in_database(cursor, email, password)
         assert credentials_in_database(cursor, email, password) is False
+
+@pytest.mark.unit_test
+@pytest.mark.parametrize(
+    "email,password",
+    (("first@test.user", "testuser01"),
+     ("second@test.user", "testuser02")))
+def test_partially_wrong_credentials(with_credentials, email, password):
+    """
+    GIVEN: credentials that exist in the database
+    WHEN: the credentials are checked with correct values
+    THEN: the check passes
+    """
+    with closing(with_credentials.cursor()) as cursor:
+        results = credentials_in_database(cursor, email, password)
+        assert credentials_in_database(cursor, email, password) is True