auth: fix bugs in the code

* gn3/auth/authorisation/privileges.py: Set id to UUID type
* gn3/auth/authorisation/roles.py: fix parameters to types that sqlite3
  supports
* gn3/auth/db.py: add logging for errors and re-raise the exception
* tests/unit/auth/test_roles.py: fix test

3 files changed, 15 insertions, 6 deletions

diff --git a/gn3/auth/authorisation/privileges.py b/gn3/auth/authorisation/privileges.py
index c60a58c..09439ad 100644
--- a/gn3/auth/authorisation/privileges.py
+++ b/gn3/auth/authorisation/privileges.py
@@ -21,4 +21,4 @@ def user_privileges(conn: db.DbConnection, user_id: UUID) -> Iterable[Privilege]
             (str(user_id),))
         results = cursor.fetchall()
 
-    return (Privilege(row[0], row[1]) for row in results)
+    return (Privilege(UUID(row[0]), row[1]) for row in results)
diff --git a/gn3/auth/authorisation/roles.py b/gn3/auth/authorisation/roles.py
index 7c33ab3..8435c40 100644
--- a/gn3/auth/authorisation/roles.py
+++ b/gn3/auth/authorisation/roles.py
@@ -33,9 +33,10 @@ def create_role(
 
     cursor.execute(
         "INSERT INTO roles(role_id, role_name) VALUES (?, ?)",
-        (role.role_id, role.role_name))
-    cursor.execute(
+        (str(role.role_id), role.role_name))
+    cursor.executemany(
         "INSERT INTO role_privileges(role_id, privilege_id) VALUES (?, ?)",
-        ((role.role_id, priv.privilege_id) for priv in privileges))
+        tuple((str(role.role_id), str(priv.privilege_id))
+              for priv in privileges))
 
     return role
diff --git a/gn3/auth/db.py b/gn3/auth/db.py
index 8760153..e0e009c 100644
--- a/gn3/auth/db.py
+++ b/gn3/auth/db.py
@@ -3,6 +3,10 @@ import sqlite3
 import contextlib
 from typing import Any, Iterator, Protocol
 
+import traceback
+
+from flask import current_app as app
+
 class DbConnection(Protocol):
     """Type annotation for a generic database connection object."""
     def cursor(self) -> Any:
@@ -48,8 +52,10 @@ def connection(db_path: str) -> Iterator[DbConnection]:
     conn = sqlite3.connect(db_path)
     try:
         yield conn
-    except: # pylint: disable=bare-except
+    except sqlite3.Error as exc:
         conn.rollback()
+        app.logger.debug(traceback.format_exc())
+        raise exc
     finally:
         conn.commit()
         conn.close()
@@ -60,8 +66,10 @@ def cursor(conn: DbConnection) -> Iterator[DbCursor]:
     cur = conn.cursor()
     try:
         yield cur
-    except: # pylint: disable=bare-except
+    except sqlite3.Error as exc:
         conn.rollback()
+        app.logger.debug(traceback.format_exc())
+        raise exc
     finally:
         conn.commit()
         cur.close()