diff options
30 files changed, 1153 insertions, 116 deletions
diff --git a/.guix-channel b/.guix-channel index 2c37401..bfc31db 100644 --- a/.guix-channel +++ b/.guix-channel @@ -34,11 +34,12 @@ (channel (name guix-bioinformatics) (url "https://git.genenetwork.org/guix-bioinformatics") - (commit "903465c85c9b2ae28480b236c3364da873ca8f51")) + (commit "9b0955f14ec725990abb1f6af3b9f171e4943f77")) (channel (name guix-past) (url "https://codeberg.org/guix-science/guix-past") (branch "master") + (commit "473c942b509ab3ead35159d27dfbf2031a36cd4d") (introduction (channel-introduction (version 0) @@ -49,6 +50,7 @@ (name guix-rust-past-crates) (url "https://codeberg.org/guix/guix-rust-past-crates.git") (branch "trunk") + (commit "b8b7ffbd1cec9f56f93fae4da3a74163bbc9c570") (introduction (channel-introduction (version 0) diff --git a/gn_auth/auth/authentication/oauth2/models/oauth2client.py b/gn_auth/auth/authentication/oauth2/models/oauth2client.py index 1639e2e..fe12ff9 100644 --- a/gn_auth/auth/authentication/oauth2/models/oauth2client.py +++ b/gn_auth/auth/authentication/oauth2/models/oauth2client.py @@ -2,6 +2,7 @@ import json import datetime from uuid import UUID +from urllib.parse import urlparse from functools import cached_property from dataclasses import asdict, dataclass from typing import Any, Sequence, Optional @@ -135,7 +136,9 @@ class OAuth2Client(ClientMixin): """ Check whether the given `redirect_uri` is one of the expected ones. """ - return redirect_uri in self.redirect_uris + uri = urlparse(redirect_uri)._replace( + query="")._replace(fragment="").geturl() + return uri in self.redirect_uris @cached_property def response_types(self) -> Sequence[str]: diff --git a/gn_auth/auth/authentication/oauth2/views.py b/gn_auth/auth/authentication/oauth2/views.py index 0e2c4eb..6c3de51 100644 --- a/gn_auth/auth/authentication/oauth2/views.py +++ b/gn_auth/auth/authentication/oauth2/views.py @@ -44,7 +44,7 @@ def authorise(): or str(uuid.uuid4())) client = server.query_client(client_id) if not bool(client): - flash("Invalid OAuth2 client.", "alert-danger") + flash("Invalid OAuth2 client.", "alert alert-danger") if request.method == "GET": def __forgot_password_table_exists__(conn): @@ -88,15 +88,15 @@ def authorise(): email=email["email"]), code=307) return server.create_authorization_response(request=request, grant_user=user) - flash(email_passwd_msg, "alert-danger") + flash(email_passwd_msg, "alert alert-danger") return redirect_response # type: ignore[return-value] except EmailNotValidError as _enve: app.logger.debug(traceback.format_exc()) - flash(email_passwd_msg, "alert-danger") + flash(email_passwd_msg, "alert alert-danger") return redirect_response # type: ignore[return-value] except NotFoundError as _nfe: app.logger.debug(traceback.format_exc()) - flash(email_passwd_msg, "alert-danger") + flash(email_passwd_msg, "alert alert-danger") return redirect_response # type: ignore[return-value] return with_db_connection(__authorise__) diff --git a/gn_auth/auth/authentication/users.py b/gn_auth/auth/authentication/users.py index 140ce36..fded79f 100644 --- a/gn_auth/auth/authentication/users.py +++ b/gn_auth/auth/authentication/users.py @@ -1,6 +1,6 @@ """User-specific code and data structures.""" import datetime -from typing import Tuple +from typing import Tuple, Union from uuid import UUID, uuid4 from dataclasses import dataclass @@ -26,7 +26,7 @@ class User: return self.user_id @staticmethod - def from_sqlite3_row(row: sqlite3.Row): + def from_sqlite3_row(row: Union[sqlite3.Row, dict]): """Generate a user from a row in an SQLite3 resultset""" return User(user_id=UUID(row["user_id"]), email=row["email"], diff --git a/gn_auth/auth/authorisation/data/phenotypes.py b/gn_auth/auth/authorisation/data/phenotypes.py index 3e45af3..788b9e7 100644 --- a/gn_auth/auth/authorisation/data/phenotypes.py +++ b/gn_auth/auth/authorisation/data/phenotypes.py @@ -4,17 +4,24 @@ from dataclasses import asdict from typing import Any, Iterable from gn_libs import mysqldb as gn3db +from gn_libs import sqlite3 as authdb from MySQLdb.cursors import DictCursor +from flask import request, jsonify, Response, Blueprint, current_app as app -from gn_auth.auth.db import sqlite3 as authdb +from gn_auth.auth.authentication.oauth2.resource_server import require_oauth from gn_auth.auth.errors import AuthorisationError from gn_auth.auth.authorisation.checks import authorised_p +from gn_auth.auth.authorisation.resources.checks import can_delete from gn_auth.auth.authorisation.resources.system.models import system_resource from gn_auth.auth.authorisation.resources.groups.models import Group, group_resource + +from gn_auth.auth.authorisation.checks import require_json from gn_auth.auth.authorisation.resources.checks import authorised_for2 +phenosbp = Blueprint("phenotypes", __name__) + def linked_phenotype_data( authconn: authdb.DbConnection, gn3conn: gn3db.Connection, species: str = "") -> Iterable[dict[str, Any]]: @@ -155,3 +162,115 @@ def link_phenotype_data( "group": asdict(group), "traits": params } + + +def unlink_from_resources( + cursor: authdb.DbCursor, + data_link_ids: tuple[uuid.UUID, ...] +) -> tuple[uuid.UUID, ...]: + """Unlink phenotypes from resources.""" + # TODO: Delete in batches + cursor.executemany("DELETE FROM phenotype_resources " + "WHERE data_link_id=? RETURNING resource_id", + tuple((str(_id),) for _id in data_link_ids)) + return tuple(uuid.UUID(row["resource_id"]) for row in cursor.fetchall()) + + +def delete_resources( + cursor: authdb.DbCursor, + resource_ids: tuple[uuid.UUID, ...] +) -> tuple[uuid.UUID, ...]: + """Delete the specified phenotype resources.""" + # TODO: Delete in batches + cursor.executemany("DELETE FROM resources " + "WHERE resource_id=? RETURNING resource_id", + tuple((str(_id),) for _id in resource_ids)) + return tuple(uuid.UUID(row["resource_id"]) for row in cursor.fetchall()) + + +def fetch_data_link_ids( + cursor: authdb.DbCursor, + species_id: int, + population_id: int, + dataset_id: int, + xref_ids: tuple[int, ...] +) -> tuple[uuid.UUID, ...]: + """Fetch `data_link_id` values for phenotypes.""" + paramstr = ", ".join(["(?, ?, ?, ?)"] * len(xref_ids)) + cursor.execute( + "SELECT data_link_id FROM linked_phenotype_data " + "WHERE (SpeciesId, InbredSetId, PublishFreezeId, PublishXRefId) IN " + f"({paramstr})", + tuple(str(field) for arow in + ((species_id, population_id, dataset_id, xref_id) + for xref_id in xref_ids) + for field in arow)) + return tuple(uuid.UUID(row["data_link_id"]) for row in cursor.fetchall()) + + +def fetch_resource_id(cursor: authdb.DbCursor, + data_link_ids: tuple[uuid.UUID, ...]) -> uuid.UUID: + """Retrieve the ID of the resource where the data is linked to. + + RAISES: InvalidResourceError in the case where more the data_link_ids belong + to more than one resource.""" + _paramstr = ", ".join(["?"] * len(data_link_ids)) + cursor.execute( + "SELECT DISTINCT(resource_id) FROM phenotype_resources " + f"WHERE data_link_id IN ({_paramstr})", + tuple(str(_id) for _id in data_link_ids)) + _ids = tuple(uuid.UUID(row['resource_id']) for row in cursor.fetchall()) + if len(_ids) != 1: + raise AuthorisationError( + f"Expected data from 1 resource, got {len(_ids)} resources.") + return _ids[0] + + +def delete_linked_data( + cursor: authdb.DbCursor, + data_link_ids: tuple[uuid.UUID, ...] +) -> int: + """Delete the actual linked data.""" + # TODO: Delete in batches + cursor.executemany("DELETE FROM linked_phenotype_data " + "WHERE data_link_id=?", + tuple((str(_id),) for _id in data_link_ids)) + return cursor.rowcount + + +@phenosbp.route("/<int:species_id>/<int:population_id>/<int:dataset_id>/delete", + methods=["POST"]) +@require_json +def delete_linked_phenotypes_data( + species_id: int, + population_id: int, + dataset_id: int +) -> Response: + """Delete the linked phenotypes data from the database.""" + db_uri = app.config["AUTH_DB"] + with (require_oauth.acquire("profile group resource") as _token, + authdb.connection(db_uri) as auth_conn, + authdb.cursor(auth_conn) as cursor): + _deleted = 0 + xref_ids = tuple(request.json.get("xref_ids", []))#type: ignore[union-attr] + if len(xref_ids) > 0: + # TODO: Use background job, for huge number of xref_ids + data_link_ids = fetch_data_link_ids( + cursor, species_id, population_id, dataset_id, xref_ids) + resource_id = fetch_resource_id(cursor, data_link_ids) + # - Does user have DELETE privilege on the data + if not can_delete(auth_conn, _token.user.user_id, resource_id): + # - No: Raise `AuthorisationError` and bail! + raise AuthorisationError( + "You are not allowed to delete this resource's data.") + # - YES: go ahead and delete data as below. + _resources_ids = unlink_from_resources(cursor, data_link_ids) + delete_resources(cursor, _resources_ids) + _deleted = delete_linked_data(cursor, data_link_ids) + + return jsonify({ + # TODO: "status": "sent-to-background"/"completed"/"failed" + # TODO: "status-url": <status-check-uri> + "requested": len(xref_ids), + "deleted": _deleted + }) diff --git a/gn_auth/auth/authorisation/data/views.py b/gn_auth/auth/authorisation/data/views.py index 9123949..1526070 100644 --- a/gn_auth/auth/authorisation/data/views.py +++ b/gn_auth/auth/authorisation/data/views.py @@ -35,11 +35,12 @@ from ..resources.models import ( from ...authentication.users import User from ...authentication.oauth2.resource_server import require_oauth -from ..data.mrna import link_mrna_data, ungrouped_mrna_data -from ..data.phenotypes import link_phenotype_data, pheno_traits_from_db -from ..data.genotypes import link_genotype_data, ungrouped_genotype_data +from .mrna import link_mrna_data, ungrouped_mrna_data +from .genotypes import link_genotype_data, ungrouped_genotype_data +from .phenotypes import phenosbp, link_phenotype_data, pheno_traits_from_db data = Blueprint("data", __name__) +data.register_blueprint(phenosbp, url_prefix="/phenotypes") def build_trait_name(trait_fullname): """ @@ -94,7 +95,7 @@ def authorisation() -> Response: with require_oauth.acquire("profile group resource") as _token: user = _token.user resources = attach_resources_data( - auth_conn, user_resources(auth_conn, _token.user)) + auth_conn, user_resources(auth_conn, _token.user)[0]) resources_roles = user_resource_roles(auth_conn, _token.user) privileges = { resource_id: tuple( diff --git a/gn_auth/auth/authorisation/resources/base.py b/gn_auth/auth/authorisation/resources/base.py index 333ba0d..e4a1239 100644 --- a/gn_auth/auth/authorisation/resources/base.py +++ b/gn_auth/auth/authorisation/resources/base.py @@ -1,10 +1,17 @@ """Base types for resources.""" +import logging +import datetime from uuid import UUID from dataclasses import dataclass -from typing import Any, Sequence +from typing import Any, Sequence, Optional import sqlite3 +from gn_auth.auth.authentication.users import User + + +logger = logging.getLogger(__name__) + @dataclass(frozen=True) class ResourceCategory: @@ -22,10 +29,49 @@ class Resource: resource_category: ResourceCategory public: bool resource_data: Sequence[dict[str, Any]] = tuple() + created_by: Optional[User] = None + created_at: datetime.datetime = datetime.datetime(1970, 1, 1, 0, 0, 0) + + @staticmethod + def from_resource(# pylint: disable=[too-many-arguments, too-many-positional-arguments] + resource, + resource_id: Optional[UUID] = None, + resource_name: Optional[str] = None, + resource_category: Optional[ResourceCategory] = None, + public: Optional[bool] = None, + resource_data: Optional[Sequence[dict[str, Any]]] = None, + created_by: Optional[User] = None, + created_at: Optional[datetime.datetime] = None + ): + """Takes a Resource object `resource` and updates the attributes specified in `kwargs`.""" + return Resource( + resource_id=resource_id or resource.resource_id, + resource_name=resource_name or resource.resource_name, + resource_category=resource_category or resource.resource_category, + public=bool(public) or resource.public, + resource_data=resource_data or resource.resource_data, + created_by=created_by or resource.created_by, + created_at=created_at or resource.created_at) def resource_from_dbrow(row: sqlite3.Row): """Convert an SQLite3 resultset row into a resource.""" + try: + created_at = datetime.datetime.fromtimestamp(row["created_at"]) + except IndexError as _ie: + created_at = datetime.datetime(1970, 1, 1, 0, 0, 0) + + try: + created_by = User.from_sqlite3_row({ + "user_id": row["creator_user_id"], + "email": row["creator_email"], + "name": row["creator_name"], + "verified": row["creator_verified"], + "created": row["creator_created"] + }) + except IndexError as _ie: + created_by = None + return Resource( resource_id=UUID(row["resource_id"]), resource_name=row["resource_name"], @@ -33,4 +79,6 @@ def resource_from_dbrow(row: sqlite3.Row): UUID(row["resource_category_id"]), row["resource_category_key"], row["resource_category_description"]), - public=bool(int(row["public"]))) + public=bool(int(row["public"])), + created_by=created_by, + created_at=created_at) diff --git a/gn_auth/auth/authorisation/resources/checks.py b/gn_auth/auth/authorisation/resources/checks.py index ce2b821..004c780 100644 --- a/gn_auth/auth/authorisation/resources/checks.py +++ b/gn_auth/auth/authorisation/resources/checks.py @@ -1,18 +1,25 @@ """Handle authorisation checks for resources""" import uuid +import logging import warnings from functools import reduce from typing import Sequence +import gn_libs.sqlite3 as authdb from gn_libs.privileges import check from .base import Resource +from .system.models import system_resource from ...db import sqlite3 as db from ...authentication.users import User from ..privileges.models import db_row_to_privilege + +logger = logging.getLogger(__name__) + + def __organise_privileges_by_resource_id__(rows): def __organise__(privs, row): resource_id = uuid.UUID(row["resource_id"]) @@ -120,3 +127,75 @@ def authorised_for_spec( (str(resource_id), str(user_id))) _privileges = tuple(row["privilege_id"] for row in cursor.fetchall()) return check(auth_spec, _privileges) + + +def can_delete( + conn: authdb.DbConnection, + user_id: uuid.UUID, + resource_id: uuid.UUID +) -> bool: + """Check whether user is allowed delete a resource and/or its data.""" + return ( + authorised_for_spec(# resource-level delete access + conn, + user_id, + resource_id, + "(OR group:resource:delete-resource system:resource:delete)") + or + authorised_for_spec(# system-wide delete access + conn, + user_id, + system_resource(conn).resource_id, + "(AND system:system-wide:data:delete)")) + + +def can_view( + conn: authdb.DbConnection, + user_id: uuid.UUID, + resource_id: uuid.UUID +) -> bool: + """Check whether user is allowed view a resource and/or its data.""" + with authdb.cursor(conn) as cursor: + cursor.execute("SELECT public FROM resources WHERE resource_id=?", + (str(resource_id),)) + row = cursor.fetchone() + is_public = bool(row) and bool(int(row["public"])) + + return ( + is_public# The resource is public, everyone can view! + or + authorised_for_spec( + # resource-level view access: user has view access to his resource. + conn, + user_id, + resource_id, + "(OR group:resource:view-resource system:resource:view)") + or + authorised_for_spec( + # system-wide view access: user can view any/all resource(s). + conn, + user_id, + system_resource(conn).resource_id, + "(OR system:system-wide:data:view system:resource:view)")) + + +def can_edit( + conn: authdb.DbConnection, + user_id: uuid.UUID, + resource_id: uuid.UUID +) -> bool: + """Check whether user is allowed edit a resource and/or its data.""" + return ( + authorised_for_spec( + # resource-level edit access: user has edit access to his resource. + conn, + user_id, + resource_id, + "(OR group:resource:edit-resource system:resource:edit)") + or + authorised_for_spec( + # system-wide edit access: user can edit any/all resource(s). + conn, + user_id, + system_resource(conn).resource_id, + "(OR system:system-wide:data:edit system:resource:edit)")) diff --git a/gn_auth/auth/authorisation/resources/groups/models.py b/gn_auth/auth/authorisation/resources/groups/models.py index 6a7af4c..07e6dbe 100644 --- a/gn_auth/auth/authorisation/resources/groups/models.py +++ b/gn_auth/auth/authorisation/resources/groups/models.py @@ -1,5 +1,6 @@ """Handle the management of resource/user groups.""" import json +import datetime from uuid import UUID, uuid4 from functools import reduce from dataclasses import dataclass @@ -100,8 +101,12 @@ def user_membership(conn: db.DbConnection, user: User) -> Sequence[Group]: "create a new group."), oauth2_scope="profile group") def create_group( - conn: db.DbConnection, group_name: str, group_leader: User, - group_description: Optional[str] = None) -> Group: + conn: db.DbConnection, + group_name: str, + group_leader: User, + group_description: Optional[str] = None, + creator: Optional[User] = None +) -> Group: """Create a new group.""" def resource_category_by_key( cursor: db.DbCursor, category_key: str): @@ -134,11 +139,15 @@ def create_group( resource_category_by_key( cursor, "group")["resource_category_id"] ), - "public": 0 + "public": 0, + "created_by": str( + creator.user_id if creator else group_leader.user_id), + "created_at": datetime.datetime.now().timestamp() } cursor.execute( "INSERT INTO resources VALUES " - "(:resource_id, :resource_name, :resource_category_id, :public)", + "(:resource_id, :resource_name, :resource_category_id, :public, " + ":created_by, :created_at)", _group_resource) cursor.execute( "INSERT INTO group_resources(resource_id, group_id) " diff --git a/gn_auth/auth/authorisation/resources/models.py b/gn_auth/auth/authorisation/resources/models.py index a4df363..27ef183 100644 --- a/gn_auth/auth/authorisation/resources/models.py +++ b/gn_auth/auth/authorisation/resources/models.py @@ -1,4 +1,6 @@ """Handle the management of resources.""" +import logging +from datetime import datetime from dataclasses import asdict from uuid import UUID, uuid4 from functools import reduce, partial @@ -14,10 +16,9 @@ from gn_auth.auth.authorisation.privileges import Privilege from gn_auth.auth.authorisation.checks import authorised_p from gn_auth.auth.errors import NotFoundError, AuthorisationError -from .system.models import system_resource -from .checks import authorised_for, authorised_for_spec +from .common import assign_resource_owner_role +from .checks import can_edit, authorised_for_spec from .base import Resource, ResourceCategory, resource_from_dbrow -from .common import assign_resource_owner_role, grant_access_to_sysadmins from .groups.models import Group, is_group_leader from .inbredset.models import resource_data as inbredset_resource_data from .mrna import ( @@ -37,6 +38,9 @@ from .phenotypes.models import ( unlink_data_from_resource as phenotype_unlink_data_from_resource) +logger = logging.getLogger(__name__) + + @authorised_p(("group:resource:create-resource",), error_description="Insufficient privileges to create a resource", oauth2_scope="profile resource") @@ -46,17 +50,20 @@ def create_resource(# pylint: disable=[too-many-arguments, too-many-positional-a resource_category: ResourceCategory, user: User, group: Group, - public: bool + public: bool, + created_at: datetime = datetime.now() ) -> Resource: """Create a resource item.""" def __create_resource__(cursor: db.DbCursor) -> Resource: resource = Resource(uuid4(), resource_name, resource_category, public) cursor.execute( - "INSERT INTO resources VALUES (?, ?, ?, ?)", + "INSERT INTO resources VALUES (?, ?, ?, ?, ?, ?)", (str(resource.resource_id), resource_name, str(resource.resource_category.resource_category_id), - 1 if resource.public else 0)) + 1 if resource.public else 0, + str(user.user_id), + created_at.timestamp())) # TODO: @fredmanglis,@rookie101 # 1. Move the actions below into a (the?) hooks system # 2. Do more checks: A resource can have varying hooks depending on type @@ -71,8 +78,6 @@ def create_resource(# pylint: disable=[too-many-arguments, too-many-positional-a "VALUES (?, ?)", (str(group.group_id), str(resource.resource_id))) assign_resource_owner_role(cursor, resource.resource_id, user.user_id) - grant_access_to_sysadmins( - cursor, resource.resource_id, system_resource(conn).resource_id) return resource @@ -98,6 +103,27 @@ def delete_resource(conn: db.DbConnection, resource_id: UUID): (str(resource_id),)) +def edit_resource(conn: db.DbConnection, resource_id: UUID, name: str) -> Resource: + """Edit basic resource details.""" + with db.cursor(conn) as cursor: + cursor.execute("UPDATE resources SET resource_name=? " + "WHERE resource_id=?", + (name, str(resource_id))) + cursor.execute( + "SELECT r.*, rc.* FROM resources AS r " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE r.resource_id=?", + (str(resource_id),)) + _resource = resource_from_dbrow(cursor.fetchone()) + cursor.execute( + "SELECT u.* FROM resources AS r INNER JOIN users AS u " + "ON r.created_by=u.user_id WHERE r.resource_id=?", + (str(resource_id),)) + return Resource.from_resource( + _resource, created_by=User.from_sqlite3_row(cursor.fetchone())) + + def resource_category_by_id( conn: db.DbConnection, category_id: UUID) -> ResourceCategory: """Retrieve a resource category by its ID.""" @@ -125,6 +151,18 @@ def resource_categories(conn: db.DbConnection) -> Sequence[ResourceCategory]: for row in cursor.fetchall()) return tuple() + +def __fetch_creators__(cursor, creators_ids: tuple[str, ...]): + cursor.execute( + ("SELECT * FROM users " + f"WHERE user_id IN ({', '.join(['?'] * len(creators_ids))})"), + creators_ids) + return { + row["user_id"]: User.from_sqlite3_row(row) + for row in cursor.fetchall() + } + + def public_resources(conn: db.DbConnection) -> Sequence[Resource]: """List all resources marked as public""" categories = { @@ -132,10 +170,19 @@ def public_resources(conn: db.DbConnection) -> Sequence[Resource]: } with db.cursor(conn) as cursor: cursor.execute("SELECT * FROM resources WHERE public=1") - results = cursor.fetchall() + resource_rows = tuple(cursor.fetchall()) + _creators_ = __fetch_creators__( + cursor, tuple(row["created_by"] for row in resource_rows)) return tuple( - Resource(UUID(row[0]), row[1], categories[row[2]], bool(row[3])) - for row in results) + Resource( + UUID(row[0]), + row[1], + categories[row[2]], + bool(row[3]), + created_by=_creators_[row["created_by"]], + created_at=datetime.fromtimestamp(row["created_at"])) + for row in resource_rows) + def group_leader_resources( conn: db.DbConnection, user: User, group: Group, @@ -155,22 +202,63 @@ def group_leader_resources( for row in cursor.fetchall()) return tuple() -def user_resources(conn: db.DbConnection, user: User) -> Sequence[Resource]: + +def user_resources( + conn: db.DbConnection, + user: User, + start_at: int = 0, + count: int = 0, + text_filter: str = "" +) -> tuple[Sequence[Resource], int]: """List the resources available to the user""" - with db.cursor(conn) as cursor: - cursor.execute( - ("SELECT DISTINCT(r.resource_id), r.resource_name, " - "r.resource_category_id, r.public, rc.resource_category_key, " - "rc.resource_category_description " + text_filter = text_filter.strip() + query_template = ("SELECT %%COLUMNS%% " "FROM user_roles AS ur " "INNER JOIN resources AS r ON ur.resource_id=r.resource_id " "INNER JOIN resource_categories AS rc " "ON r.resource_category_id=rc.resource_category_id " - "WHERE ur.user_id=?"), + "WHERE ur.user_id=? %%LIKE%% %%LIMITS%%") + with db.cursor(conn) as cursor: + cursor.execute( + query_template.replace( + "%%COLUMNS%%", "COUNT(DISTINCT(r.resource_id)) AS count" + ).replace( + "%%LIKE%%", "" + ).replace( + "%%LIMITS%%", ""), (str(user.user_id),)) + _total_records = int(cursor.fetchone()["count"]) + cursor.execute( + query_template.replace( + "%%COLUMNS%%", + "DISTINCT(r.resource_id), r.resource_name, " + "r.resource_category_id, r.public, r.created_by, r.created_at, " + "rc.resource_category_key, rc.resource_category_description" + ).replace( + "%%LIKE%%", + ("" if text_filter == "" else ( + "AND (r.resource_name LIKE ? OR " + "rc.resource_category_key LIKE ? OR " + "rc.resource_category_description LIKE ? )")) + ).replace( + "%%LIMITS%%", + ("" if count <= 0 else f"LIMIT {count} OFFSET {start_at}")), + (str(user.user_id),) + ( + tuple() if text_filter == "" else + tuple(f"%{text_filter}%" for _ in range(0, 3)) + )) rows = cursor.fetchall() or [] - return tuple(resource_from_dbrow(row) for row in rows) + _creators_ = __fetch_creators__( + cursor, tuple(row["created_by"] for row in rows)) + + return tuple( + Resource.from_resource( + resource_from_dbrow(row), + created_by=_creators_[row["created_by"]], + created_at=datetime.fromtimestamp(row["created_at"]) + ) for row in rows), _total_records + def resource_data(conn, resource, offset: int = 0, limit: Optional[int] = None) -> tuple[dict, ...]: @@ -243,12 +331,9 @@ def link_data_to_resource( data_link_ids: tuple[UUID, ...] ) -> tuple[dict, ...]: """Link data to resource.""" - if not authorised_for( - conn, user, ("group:resource:edit-resource",), - (resource_id,))[resource_id]: + if not can_edit(conn, user.user_id, resource_id): raise AuthorisationError( - "You are not authorised to link data to resource with id " - f"{resource_id}") + "You are not authorised to link/unlink data to this resource.") resource = with_db_connection(partial( resource_by_id, user=user, resource_id=resource_id)) @@ -261,12 +346,9 @@ def link_data_to_resource( def unlink_data_from_resource( conn: db.DbConnection, user: User, resource_id: UUID, data_link_id: UUID): """Unlink data from resource.""" - if not authorised_for( - conn, user, ("group:resource:edit-resource",), - (resource_id,))[resource_id]: + if not can_edit(conn, user.user_id, resource_id): raise AuthorisationError( - "You are not authorised to link data to resource with id " - f"{resource_id}") + "You are not authorised to link/unlink data this resource.") resource = with_db_connection(partial( resource_by_id, user=user, resource_id=resource_id)) @@ -359,9 +441,7 @@ def save_resource( conn: db.DbConnection, user: User, resource: Resource) -> Resource: """Update an existing resource.""" resource_id = resource.resource_id - authorised = authorised_for( - conn, user, ("group:resource:edit-resource",), (resource_id,)) - if authorised[resource_id]: + if can_edit(conn, user.user_id, resource_id): with db.cursor(conn) as cursor: cursor.execute( "UPDATE resources SET " diff --git a/gn_auth/auth/authorisation/resources/system/models.py b/gn_auth/auth/authorisation/resources/system/models.py index 303b0ac..25089fa 100644 --- a/gn_auth/auth/authorisation/resources/system/models.py +++ b/gn_auth/auth/authorisation/resources/system/models.py @@ -1,9 +1,10 @@ """Base functions and utilities for system resources.""" from uuid import UUID from functools import reduce -from typing import Sequence +from typing import Union, Sequence + +from gn_libs import sqlite3 as db -from gn_auth.auth.db import sqlite3 as db from gn_auth.auth.errors import NotFoundError from gn_auth.auth.authentication.users import User @@ -52,9 +53,9 @@ def user_roles_on_system(conn: db.DbConnection, user: User) -> Sequence[Role]: return tuple() -def system_resource(conn: db.DbConnection) -> Resource: +def system_resource(conn: Union[db.DbConnection, db.DbCursor]) -> Resource: """Retrieve the system resource.""" - with db.cursor(conn) as cursor: + def __fetch_sys_resource__(cursor: db.DbCursor) -> Resource: cursor.execute( "SELECT resource_categories.*, resources.resource_id, " "resources.resource_name, resources.public " @@ -65,4 +66,10 @@ def system_resource(conn: db.DbConnection) -> Resource: if row: return resource_from_dbrow(row) - raise NotFoundError("Could not find a system resource!") + raise NotFoundError("Could not find a system resource!") + + if hasattr(conn, "cursor"): # is connection + with db.cursor(conn) as cursor: + return __fetch_sys_resource__(cursor) + else: + return __fetch_sys_resource__(conn) diff --git a/gn_auth/auth/authorisation/resources/system/views.py b/gn_auth/auth/authorisation/resources/system/views.py index b0d40c2..d7a57a9 100644 --- a/gn_auth/auth/authorisation/resources/system/views.py +++ b/gn_auth/auth/authorisation/resources/system/views.py @@ -1,19 +1,34 @@ """Views relating to `System` resource(s).""" +import logging from dataclasses import asdict -from flask import jsonify, Blueprint +from flask import request, jsonify, Blueprint, current_app as app -from gn_auth.auth.db.sqlite3 import with_db_connection +from gn_libs import sqlite3 as authdb +from gn_auth.auth.authorisation.roles.models import db_rows_to_roles from gn_auth.auth.authentication.oauth2.resource_server import require_oauth from .models import user_roles_on_system +logger = logging.getLogger(__name__) system = Blueprint("system", __name__) + @system.route("/roles") def system_roles(): """Get the roles that a user has that act on the system.""" - with require_oauth.acquire("profile group") as the_token: - roles = with_db_connection( - lambda conn: user_roles_on_system(conn, the_token.user)) - return jsonify(tuple(asdict(role) for role in roles)) + with (authdb.connection(app.config["AUTH_DB"]) as conn, + authdb.cursor(conn) as cursor): + if not bool(request.headers.get("Authorization", False)): + cursor.execute( + "SELECT r.*, p.* FROM roles AS r " + "INNER JOIN role_privileges AS rp ON r.role_id=rp.role_id " + "INNER JOIN privileges AS p ON rp.privilege_id=p.privilege_id " + "WHERE r.role_name='public-view'") + return jsonify(tuple( + asdict(role) for role in db_rows_to_roles(cursor.fetchall()))) + + with require_oauth.acquire("profile group") as the_token: + return jsonify(tuple( + asdict(role) for role in + user_roles_on_system(conn, the_token.user))) diff --git a/gn_auth/auth/authorisation/resources/views.py b/gn_auth/auth/authorisation/resources/views.py index a960ca3..ab44795 100644 --- a/gn_auth/auth/authorisation/resources/views.py +++ b/gn_auth/auth/authorisation/resources/views.py @@ -1,9 +1,10 @@ """The views/routes for the resources package""" -from uuid import UUID, uuid4 +import time import json +import logging import operator import sqlite3 -import time +from uuid import UUID, uuid4 from dataclasses import asdict from functools import reduce @@ -13,6 +14,7 @@ from authlib.jose import jwt from authlib.integrations.flask_oauth2.errors import _HTTPException from flask import (make_response, request, jsonify, Response, Blueprint, current_app as app) +import gn_libs.privileges.resources from gn_auth.auth.requests import request_json @@ -43,14 +45,18 @@ from .inbredset.views import popbp from .genotypes.views import genobp from .phenotypes.views import phenobp from .errors import MissingGroupError +from .system.models import system_resource from .groups.models import Group, user_group -from .checks import authorised_for, authorised_for_spec +from .checks import can_delete, authorised_for from .models import ( Resource, resource_data, resource_by_id, public_resources, resource_categories, assign_resource_user, link_data_to_resource, unassign_resource_user, resource_category_by_id, user_roles_on_resources, unlink_data_from_resource, create_resource as _create_resource, - get_resource_id, delete_resource as _delete_resource) + get_resource_id, delete_resource as _delete_resource, + edit_resource as _edit_resource) + +logger = logging.getLogger(__name__) resources = Blueprint("resources", __name__) resources.register_blueprint(popbp, url_prefix="/") @@ -95,8 +101,7 @@ def create_resource() -> Response: "resources.resource_name"): raise InconsistencyError( "You cannot have duplicate resource names.") from sql3ie - app.logger.debug( - f"{type(sql3ie)=}: {sql3ie=}") + logger.debug("type(sql3ie)=%s: sql3ie=%s", type(sql3ie), sql3ie) raise @@ -114,6 +119,43 @@ def view_resource(resource_id: UUID) -> Response: ) ) + +@resources.route("/<uuid:resource_id>/edit", methods=["POST"]) +@require_oauth("profile group resource") +def edit_resource(resource_id: UUID) -> Response: + """Update/edit basic details regarding a resource.""" + db_uri = app.config["AUTH_DB"] + with (require_oauth.acquire("profile group resource") as _token, + db.connection(db_uri) as conn): + _privileges = tuple( + privilege.privilege_id + for role in ( + role for resource in user_roles_on_resources( + conn, + _token.user, + (resource_id, system_resource(conn).resource_id) + ).values() + for role in resource.get("roles", tuple())) + for privilege in role.privileges) + if not gn_libs.privileges.resources.can_edit(_privileges): + return make_response(jsonify({ + "error": "AuthorisationError", + "error_description": "You are not allowed to edit this resource." + }), 401) + + name = (request_json().get("resource_name") or "").strip() + if bool(name): + return jsonify({ + "resource": asdict(_edit_resource(conn, resource_id, name)), + "message": "Resource updated successfully", + "status": "success" + }) + + return make_response(jsonify({ + "error_description": "Expected `resource_name` to be provided.", + "error": "InvalidInput" + }), 400) + def __safe_get_requests_page__(key: str = "page") -> int: """Get the results page if it exists or default to the first page.""" try: @@ -468,7 +510,7 @@ def resources_authorisation(): }) resp.status_code = 400 except Exception as _exc:#pylint: disable=[broad-except] - app.logger.debug("Generic exception.", exc_info=True) + logger.debug("Generic exception.", exc_info=True) resp = jsonify({ "status": "general-exception", "error_description": ( @@ -506,7 +548,6 @@ def get_user_roles_on_resource(name) -> Response: response = make_response({ # Flatten this list "roles": roles, - "silly": "ausah", }) iat = int(time.time()) jose_header = { @@ -685,13 +726,9 @@ def delete_resource(): form = request_json() try: resource_id = UUID(form.get("resource_id")) - if not authorised_for_spec( - conn, - the_token.user.user_id, - resource_id, - "(OR group:resource:delete-resource system:resource:delete)"): - raise AuthorisationError("You do not have the appropriate " - "privileges to delete this resource.") + if not can_delete(conn, the_token.user.user_id, resource_id): + raise AuthorisationError( + "You are not allowed to delete this resource.") data = resource_data( conn, @@ -709,13 +746,13 @@ def delete_resource(): "description": f"Successfully deleted resource with ID '{resource_id}'." }) except ValueError as _verr: - app.logger.debug("Error!", exc_info=True) + logger.debug("Error!", exc_info=True) return jsonify({ "error": "ValueError", "error-description": "An invalid identifier was provided" }), 400 except TypeError as _terr: - app.logger.debug("Error!", exc_info=True) + logger.debug("Error!", exc_info=True) return jsonify({ "error": "TypeError", "error-description": "An invalid identifier was provided" diff --git a/gn_auth/auth/authorisation/users/views.py b/gn_auth/auth/authorisation/users/views.py index c248ac3..a706067 100644 --- a/gn_auth/auth/authorisation/users/views.py +++ b/gn_auth/auth/authorisation/users/views.py @@ -1,5 +1,6 @@ """User authorisation endpoints.""" import uuid +import logging import sqlite3 import secrets import traceback @@ -57,6 +58,8 @@ from .models import list_users from .masquerade.views import masq from .collections.views import collections +logger = logging.getLogger(__name__) + users = Blueprint("users", __name__) users.register_blueprint(masq, url_prefix="/masquerade") users.register_blueprint(collections, url_prefix="/collections") @@ -235,11 +238,11 @@ def register_user() -> Response: redirect_uri=form["redirect_uri"]) return jsonify(asdict(user)) except sqlite3.IntegrityError as sq3ie: - current_app.logger.error(traceback.format_exc()) + logger.error(traceback.format_exc()) raise UserRegistrationError( "A user with that email already exists") from sq3ie except EmailNotValidError as enve: - current_app.logger.error(traceback.format_exc()) + logger.error(traceback.format_exc()) raise(UserRegistrationError(f"Email Error: {str(enve)}")) from enve raise Exception(# pylint: disable=[broad-exception-raised] @@ -317,12 +320,21 @@ def user_group() -> Response: @require_oauth("profile resource") def user_resources() -> Response: """Retrieve the resources a user has access to.""" + _request_params = request_json() with require_oauth.acquire("profile resource") as the_token: db_uri = current_app.config["AUTH_DB"] with db.connection(db_uri) as conn: - return jsonify([ - asdict(resource) for resource in - _user_resources(conn, the_token.user)]) + _resources, _total_records = _user_resources( + conn, + the_token.user, + start_at=int(_request_params.get("start", 0)), + count=int(_request_params.get("length", 0)), + text_filter=_request_params.get("text_filter", "")) + return jsonify({ + "resources": [asdict(resource) for resource in _resources], + "total-records": _total_records, + "filtered-records": len(_resources) + }) @users.route("group/join-request", methods=["GET"]) @require_oauth("profile group") diff --git a/gn_auth/auth/errors.py b/gn_auth/auth/errors.py index 77b73aa..c499e86 100644 --- a/gn_auth/auth/errors.py +++ b/gn_auth/auth/errors.py @@ -6,7 +6,7 @@ class AuthorisationError(Exception): All exceptions in this package should inherit from this class. """ - error_code: int = 400 + error_code: int = 401 class ForbiddenAccess(AuthorisationError): """Raised for forbidden access.""" diff --git a/gn_auth/errors/authlib.py b/gn_auth/errors/authlib.py index 09862e3..c85b67c 100644 --- a/gn_auth/errors/authlib.py +++ b/gn_auth/errors/authlib.py @@ -11,7 +11,7 @@ logger = logging.getLogger(__name__) def __description__(body): """Improve description for errors in authlib.oauth2.rfc6749.errors""" - _desc = body["error_description"] + _desc = body.get("error_description", body["error"]) match body["error"]: case "missing_authorization": return ( diff --git a/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py b/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py new file mode 100644 index 0000000..63e807a --- /dev/null +++ b/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py @@ -0,0 +1,61 @@ +""" +add role systemwide-data-curator. +""" +import uuid +import contextlib + +from yoyo import step + +__depends__ = {'20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members'} + + +def create_systemwide_data_curator_role(conn): + """Create a new 'systemwide-data-curator' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "INSERT INTO roles(role_id, role_name, user_editable) " + "VALUES (?, 'systemwide-data-curator', 0)", + (str(uuid.uuid4()),)) + + +def link_privileges_to_role(conn): + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT role_id FROM roles " + "WHERE role_name='systemwide-data-curator'") + role_id = cursor.fetchone()[0] + cursor.executemany("INSERT INTO role_privileges(role_id, privilege_id) " + "VALUES (?, ?)", + tuple((role_id, priv) for priv in + ("system:system-wide:data:edit", + "system:system-wide:data:delete"))) + + +def unlink_privileges_from_role(conn): + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT role_id FROM roles " + "WHERE role_name='systemwide-data-curator'") + role_id = cursor.fetchone()[0] + cursor.executemany("DELETE FROM role_privileges " + "WHERE role_id=? AND privilege_id=?", + tuple((role_id, priv) for priv in + ("system:system-wide:data:edit", + "system:system-wide:data:delete"))) + + +steps = [ + step(# Add new privileges + """ + INSERT INTO privileges (privilege_id, privilege_description) + VALUES + ('system:system-wide:data:edit', + 'A user with this privilege can edit any data on the entire system.'), + ('system:system-wide:data:delete', + 'A user with this privilege can delete any data from the system.') + """, + """ + DELETE FROM privileges WHERE privilege_id IN + ('system:system-wide:data:edit', 'system:system-wide:data:delete')"""), + step(create_systemwide_data_curator_role, + "DELETE FROM roles WHERE role_name='systemwide-data-curator'"), + step(link_privileges_to_role, unlink_privileges_from_role) +] diff --git a/migrations/auth/20260311_01_TfRlV-add-privilege-for-gn-docs-documentation-editing.py b/migrations/auth/20260311_01_TfRlV-add-privilege-for-gn-docs-documentation-editing.py new file mode 100644 index 0000000..d618f14 --- /dev/null +++ b/migrations/auth/20260311_01_TfRlV-add-privilege-for-gn-docs-documentation-editing.py @@ -0,0 +1,62 @@ +""" +add privilege for gn-docs documentation editing +""" +import uuid +import contextlib + +from yoyo import step + +__depends__ = {'20260206_01_v3f4P-add-role-systemwide-data-curator'} + +ROLE_NAME = 'systemwide-docs-editor' + + +def create_systemwide_docs_editor_role(conn): + """Create a new 'systemwide-data-curator' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "INSERT INTO roles(role_id, role_name, user_editable) " + "VALUES (?, ?, 0)", + (str(uuid.uuid4()), ROLE_NAME)) + + +def delete_systemwide_docs_editor_role(conn): + """Create a new 'systemwide-data-curator' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("DELETE FROM roles WHERE role_name=?", (ROLE_NAME,)) + + +def assign_edit_priv_to_docs_editor(conn): + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT role_id FROM roles WHERE role_name=?", + (ROLE_NAME,)) + role_id = cursor.fetchone()[0] + + cursor.execute( + "INSERT INTO role_privileges(role_id, privilege_id) " + "VALUES (?, ?)", + (role_id, "system:documentation:edit")) + + +def revoke_edit_priv_to_docs_editor(conn): + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT role_id FROM roles WHERE role_name=?", + (ROLE_NAME,)) + role_id = cursor.fetchone()[0] + + cursor.execute( + "DELETE FROM role_privileges WHERE role_id=? AND privilege_id=?", + (role_id, "system:documentation:edit")) + + +steps = [ + step( + """INSERT INTO privileges(privilege_id, privilege_description) + VALUES( + 'system:documentation:edit', + 'Allows the holder to edit documentation presented with the Genenetwork system.' + )""", + "DELETE FROM privileges WHERE privilege_id='system:documentation:edit'"), + step(create_systemwide_docs_editor_role, delete_systemwide_docs_editor_role), + step(assign_edit_priv_to_docs_editor, revoke_edit_priv_to_docs_editor) +] diff --git a/migrations/auth/20260311_02_v3EFQ-assign-systemwide-docs-editor-role-to-sysadmins.py b/migrations/auth/20260311_02_v3EFQ-assign-systemwide-docs-editor-role-to-sysadmins.py new file mode 100644 index 0000000..e79ef6a --- /dev/null +++ b/migrations/auth/20260311_02_v3EFQ-assign-systemwide-docs-editor-role-to-sysadmins.py @@ -0,0 +1,66 @@ +""" +Assign 'systemwide-docs-editor' role to sysadmins +""" +import uuid +import contextlib + +from yoyo import step + +__depends__ = {'20260311_01_TfRlV-add-privilege-for-gn-docs-documentation-editing'} + + +def fetch_docs_editor_role_id(cursor): + """Fetch ID of systemwide-docs-editor role""" + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='systemwide-docs-editor'") + return cursor.fetchone()[0] + + +def fetch_sys_resource_id(cursor): + """Fetch the resource ID of the system.""" + cursor.execute("SELECT resource_id FROM resources " + "WHERE resource_name='GeneNetwork System'") + return cursor.fetchone()[0] + + +def fetch_sys_admin_ids(cursor): + """Fetch the sysadmins' IDs.""" + cursor.execute( + "SELECT user_roles.user_id FROM resources INNER JOIN user_roles " + "ON resources.resource_id=user_roles.resource_id INNER JOIN roles " + "ON user_roles.role_id=roles.role_id " + "WHERE resources.resource_name='GeneNetwork System' " + "AND roles.role_name='system-administrator'") + return tuple(row[0] for row in cursor.fetchall()) + + +def __build_params__(cursor): + sysresourceid = fetch_sys_resource_id(cursor) + sysadminids = fetch_sys_admin_ids(cursor) + roleid = fetch_docs_editor_role_id(cursor) + return tuple({ + "user_id": userid, + "role_id": roleid, + "resource_id": sysresourceid + } for userid in sysadminids) + + +def assign_systemwide_docs_editor_role_to_sysadmins(conn): + with contextlib.closing(conn.cursor()) as cursor: + cursor.executemany( + "INSERT INTO user_roles(user_id, role_id, resource_id) " + "VALUES(:user_id, :role_id, :resource_id)", + __build_params__(cursor)) + + +def revoke_systemwide_docs_editor_role_from_sysadmins(conn): + with contextlib.closing(conn.cursor()) as cursor: + cursor.executemany( + "DELETE FROM user_roles WHERE user_id=:user_id " + "AND role_id=:role_id AND resource_id=:resource_id", + __build_params__(cursor)) + +steps = [ + step(assign_systemwide_docs_editor_role_to_sysadmins, + revoke_systemwide_docs_editor_role_from_sysadmins) +] diff --git a/migrations/auth/20260311_03_vxBCX-restrict-access-to-resources-make-public-feature.py b/migrations/auth/20260311_03_vxBCX-restrict-access-to-resources-make-public-feature.py new file mode 100644 index 0000000..bdf8a56 --- /dev/null +++ b/migrations/auth/20260311_03_vxBCX-restrict-access-to-resources-make-public-feature.py @@ -0,0 +1,49 @@ +""" +Restrict access to resources' 'Make Public' feature. +""" +import contextlib + +from yoyo import step + +__depends__ = {'20260311_02_v3EFQ-assign-systemwide-docs-editor-role-to-sysadmins'} + + +def fetch_systemwide_data_curator_role_id(cursor): + "Fetch the role's ID." + cursor.execute("SELECT role_id FROM roles " + "WHERE role_name='systemwide-data-curator'") + return cursor.fetchone()[0] + + +def assign_make_public_to_systemwide_data_curator(conn): + """Assign privilege to 'systemwide-data-curator' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "INSERT INTO role_privileges(role_id, privilege_id) " + "VALUES(?, 'system:resource:make-public')", + (fetch_systemwide_data_curator_role_id(cursor),)) + + +def revoke_make_public_from_systemwide_data_curator(conn): + """Revoke privilege from 'systemwide-data-curator' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "DELETE FROM role_privileges " + "WHERE role_id=? AND privilege_id='system:resource:make-public'", + (fetch_systemwide_data_curator_role_id(cursor),)) + + +steps = [ + step( + """ + INSERT INTO privileges(privilege_id, privilege_description) + VALUES( + 'system:resource:make-public', + 'Allow user to make a resource publicly accessible.') + """, + """ + DELETE FROM privileges WHERE privilege_id='system:resource:make-public' + """), + step(assign_make_public_to_systemwide_data_curator, + revoke_make_public_from_systemwide_data_curator), +] diff --git a/migrations/auth/20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator.py b/migrations/auth/20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator.py new file mode 100644 index 0000000..22863ae --- /dev/null +++ b/migrations/auth/20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator.py @@ -0,0 +1,69 @@ +""" +Add privileges to role systemwide-data-curator +""" +import contextlib + +from yoyo import step + +__depends__ = {'20260311_03_vxBCX-restrict-access-to-resources-make-public-feature'} + + +__new_privileges__ = ( + ("system:system-wide:inbredset:view-case-attribute", + "Enable view of any and all inbredset case attributes system-wide."), + ("system:system-wide:inbredset:edit-case-attribute", + "Enable edit of any and all inbredset case attributes system-wide."), + ("system:system-wide:inbredset:delete-case-attribute", + "Enable deletion of any and all inbredset case attributes system-wide."), + ("system:system-wide:inbredset:apply-case-attribute-edit", + "Enable applying changes to any and all inbredset case attributes system-wide."), + ("system:system-wide:inbredset:reject-case-attribute-edit", + "Enable rejecting changes to any and all inbredset case attributes system-wide.")) + + +def fetch_systemwide_data_curator_role_id(cursor): + "Fetch the role's ID." + cursor.execute("SELECT role_id FROM roles " + "WHERE role_name='systemwide-data-curator'") + return cursor.fetchone()[0] + + +def create_new_privileges(conn): + """Create new privileges for the system.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.executemany( + "INSERT INTO privileges(privilege_id, privilege_description) " + "VALUES (?, ?)", + __new_privileges__) + + +def delete_new_privileges(conn): + """Delete these new privileges from the system.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.executemany("DELETE FROM privileges WHERE privilege_id=?", + tuple((priv[0],) for priv in __new_privileges__)) + + +def assign_new_privileges(conn): + """Assign the new privileges to the `systemwide-data-curator` role.""" + with contextlib.closing(conn.cursor()) as cursor: + role_id = fetch_systemwide_data_curator_role_id(cursor) + cursor.executemany( + "INSERT INTO role_privileges(role_id, privilege_id) VALUES (?, ?)", + tuple((role_id, privilege[0]) for privilege in __new_privileges__)) + + +def revoke_new_privileges(conn): + """Revoke the new privileges from the `systemwide-data-curator` role.""" + with contextlib.closing(conn.cursor()) as cursor: + role_id = fetch_systemwide_data_curator_role_id(cursor) + cursor.executemany( + "DELETE FROM role_privileges WHERE role_id=? AND privilege_id=?", + tuple((role_id, privilege[0]) for privilege in __new_privileges__)) + + + +steps = [ + step(create_new_privileges, delete_new_privileges), + step(assign_new_privileges, revoke_new_privileges) +] diff --git a/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py b/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py new file mode 100644 index 0000000..702c418 --- /dev/null +++ b/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py @@ -0,0 +1,185 @@ +""" +Add user and time tracking to resources table +""" +import random +import contextlib +from datetime import datetime + +from yoyo import step + +__depends__ = {'20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator'} + +GN_AUTH_INIT_TIMESTAMP = 1691130509.0 +__admin_id__ = "" + + +def fetch_acentenos_id(conn): + """Fetch the default resource creator.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT user_id FROM users WHERE email=?", + (("acent" "eno@" "uthsc" "." "edu"),)) + res = cursor.fetchone() + return res[0] if bool(res) else None + + +def fetch_a_sysadmin_id(conn, resources_table): + """Fetch one ID out of all system administrator users.""" + global __admin_id__ + + def __fetch__(): + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + f"SELECT ur.user_id FROM {resources_table} AS rsc " + "INNER JOIN user_roles AS ur ON rsc.resource_id=ur.resource_id " + "INNER JOIN roles AS r ON ur.role_id=r.role_id " + "WHERE resource_name='GeneNetwork System' " + "AND r.role_name='system-administrator'" + ) + return tuple(row[0] for row in cursor.fetchall()) + + if not bool(__admin_id__): + __admins__ = __fetch__() + if len(__admins__) > 0: + __admin_id__ = random.choice(__admins__) + + return __admin_id__ + + +def add_user_and_time_tracking_columns(conn): + """Add user and time tracking columns.""" + conn.execute( + """ + CREATE TABLE resources_new( + resource_id TEXT NOT NULL, + resource_name TEXT NOT NULL UNIQUE, + resource_category_id TEXT NOT NULL, + public INTEGER NOT NULL DEFAULT 0 CHECK (public=0 or public=1), + created_by TEXT NOT NULL, + created_at REAL NOT NULL DEFAULT '1691130509.0', + PRIMARY KEY(resource_id), + FOREIGN KEY(resource_category_id) + REFERENCES resource_categories(resource_category_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(created_by) + REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + + +def drop_user_and_time_tracking_columns(conn): + """Drop user and time tracking columns.""" + conn.execute("PRAGMA foreign_keys = OFF") + conn.execute("DROP TABLE IF EXISTS resources") + conn.execute("ALTER TABLE resources_old RENAME TO resources") + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + + +def update_data_for_new_resources_table(conn): + """Add creator and time to original data.""" + __creator__ = ( + fetch_acentenos_id(conn) or fetch_a_sysadmin_id(conn, "resources")) + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT * FROM resources") + cursor.executemany( + "INSERT INTO resources_new(" + " resource_id," + " resource_name," + " resource_category_id," + " public," + " created_by," + " created_at" + ") VALUES (?, ?, ?, ?, ?, ?)", + tuple( + tuple(row) + (__creator__, GN_AUTH_INIT_TIMESTAMP) + for row in cursor.fetchall())) + + +def restore_data_for_old_resources_table(conn): + """Remove creator and time from data.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT * FROM resources") + cursor.executemany( + "INSERT INTO resources_old(" + " resource_id," + " resource_name," + " resource_category_id," + " public" + ") VALUES (?, ?, ?, ?)", + tuple(tuple(row)[0:4] for row in cursor.fetchall())) + + +def replace_old_table_with_new_table(conn): + """Restore old resources table with the new resources table.""" + conn.execute("PRAGMA foreign_keys = OFF") + conn.execute("DROP TABLE resources") + conn.execute("ALTER TABLE resources_new RENAME TO resources") + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + + +def restore_old_table(conn): + """Restore old 'resources' table schema.""" + conn.execute( + """ + CREATE TABLE resources_old( + resource_id TEXT NOT NULL, + resource_name TEXT NOT NULL UNIQUE, + resource_category_id TEXT NOT NULL, + public INTEGER NOT NULL DEFAULT 0 CHECK (public=0 or public=1), + PRIMARY KEY(resource_id), + FOREIGN KEY(resource_category_id) + REFERENCES resource_categories(resource_category_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + + +def parse_creator_and_time(cursor, row): + __return__ = None + + __name_parts__ = row[1].split("—") + if len(__name_parts__) == 4: + __email__, __inbredsetname__, __datetimestr__, count = __name_parts__ + cursor.execute("SELECT user_id FROM users WHERE email=?", + (__email__.strip(),)) + results = cursor.fetchone() + if bool(results): + __return__ = { + "resource_id": row[0], + "creator": results[0], + "created": datetime.fromisoformat(__datetimestr__).timestamp() + } + + return __return__ + + +def update_creators_and_time(conn): + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT resource_id, resource_name FROM resources") + cursor.executemany( + "UPDATE resources SET created_by=:creator, created_at=:created " + "WHERE resource_id=:resource_id", + tuple(item for item in + (parse_creator_and_time(cursor, row) + for row in cursor.fetchall()) + if item is not None)) + + + +def restore_default_creators_and_time(conn): + with contextlib.closing(conn.cursor()) as cursor: + __creator__ = ( + fetch_acentenos_id(conn) or fetch_a_sysadmin_id(conn, "resources")) + cursor.execute("UPDATE resources SET created_by=?, created_at=?", + (__creator__, GN_AUTH_INIT_TIMESTAMP)) + + +steps = [ + step(add_user_and_time_tracking_columns, + drop_user_and_time_tracking_columns), + step(update_data_for_new_resources_table, + restore_data_for_old_resources_table), + step(replace_old_table_with_new_table, restore_old_table), + step(update_creators_and_time, restore_default_creators_and_time) +] diff --git a/migrations/auth/20260428_01_Tak6O-new-privilege-system-system-wide-data-view.py b/migrations/auth/20260428_01_Tak6O-new-privilege-system-system-wide-data-view.py new file mode 100644 index 0000000..2dddc56 --- /dev/null +++ b/migrations/auth/20260428_01_Tak6O-new-privilege-system-system-wide-data-view.py @@ -0,0 +1,19 @@ +""" +New privilege: system:system-wide:data:view +""" + +from yoyo import step + +__depends__ = {'20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table'} + +steps = [ + step( + """ + INSERT INTO privileges(privilege_id, privilege_description) + VALUES('system:system-wide:data:view', + 'A user with this privilege can view any data on the entire system.') + """, + """ + DELETE FROM privileges WHERE privilege_id='system:system-wide:data:view' + """) +] diff --git a/migrations/auth/20260428_02_L6zIV-add-privileges-to-batch-editors-role.py b/migrations/auth/20260428_02_L6zIV-add-privileges-to-batch-editors-role.py new file mode 100644 index 0000000..537bf9b --- /dev/null +++ b/migrations/auth/20260428_02_L6zIV-add-privileges-to-batch-editors-role.py @@ -0,0 +1,62 @@ +""" +Add privileges to batch-editors role +""" +import contextlib + +from yoyo import step + +__depends__ = {'20260428_01_Tak6O-new-privilege-system-system-wide-data-view'} + + +def fetch_batch_editors_role_id(cursor): + """Fetch the ID of the batch-editors role.""" + cursor.execute("SELECT role_id FROM roles WHERE role_name='Batch Editors'") + res = cursor.fetchone() + if not bool(res): + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='batch-editors'") + res = cursor.fetchone() + + return res[0] if bool(res) else None + + +def rename_role(conn): + """Rename role from 'Batch Editors' to 'batch-editors'.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "UPDATE roles SET role_name='batch-editors' WHERE role_id=?", + (fetch_batch_editors_role_id(cursor),)) + + +def restore_old_role_name(conn): + """Rename role from 'batch-editors' to 'Batch Editors'.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "UPDATE roles SET role_name='Batch Editors' WHERE role_id=?", + (fetch_batch_editors_role_id(cursor),)) + + +def add_new_privileges(conn): + """Add new privileges to 'batch-editors' role.""" + with contextlib.closing(conn.cursor()) as cursor: + role_id = fetch_batch_editors_role_id(cursor) + cursor.executemany( + "INSERT INTO role_privileges(role_id, privilege_id) VALUES(?, ?)", + tuple((role_id, priv) for priv in ( + "system:system-wide:data:view", + "system:system-wide:data:edit"))) + + +def remove_new_privileges(conn): + """Remove new privileges from 'batch-editors' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "DELETE FROM role_privileges WHERE role_id=? AND privilege_id IN " + "('system:system-wide:data:view', 'system:system-wide:data:edit')", + (fetch_batch_editors_role_id(cursor),)) + + +steps = [ + step(rename_role, restore_old_role_name), + step(add_new_privileges, remove_new_privileges) +] diff --git a/tests/unit/auth/fixtures/group_fixtures.py b/tests/unit/auth/fixtures/group_fixtures.py index 2e8cd9a..da1c4cd 100644 --- a/tests/unit/auth/fixtures/group_fixtures.py +++ b/tests/unit/auth/fixtures/group_fixtures.py @@ -1,5 +1,6 @@ """Fixtures and utilities for group-related tests""" import uuid +import datetime import pytest @@ -7,8 +8,12 @@ from gn_auth.auth.db import sqlite3 as db from gn_auth.auth.authorisation.resources.groups import Group from gn_auth.auth.authorisation.resources import Resource, ResourceCategory +from .user_fixtures import TEST_USERS from .resource_fixtures import TEST_RESOURCES + +_created_ = datetime.datetime.now() + TEST_GROUP_01 = Group(uuid.UUID("9988c21d-f02f-4d45-8966-22c968ac2fbf"), "TheTestGroup", {}) TEST_GROUP_02 = Group(uuid.UUID("e37d59d7-c05e-4d67-b479-81e627d8d634"), @@ -24,16 +29,20 @@ GROUPS_AS_RESOURCES = tuple({ "resource_id": res_id, "resource_name": group.group_name, "category_id": str(GROUP_CATEGORY.resource_category_id), - "public": "0" + "public": "0", + "created_by": str(TEST_USERS[0].user_id), + "created_at": _created_.timestamp() } for res_id, group in zip( ("38d1807d-105f-44a7-8327-7e2d973b6d8d", "89458ef6-e090-4b53-8c2c-59eaf2785f11"), TEST_GROUPS)) GROUP_RESOURCES = tuple( - Resource(uuid.UUID(row["resource_id"]), - row["resource_name"], + Resource(uuid.UUID(row["resource_id"]),# type: ignore[arg-type] + row["resource_name"],# type: ignore[arg-type] GROUP_CATEGORY, - False) + False, + created_by=TEST_USERS[0], + created_at=_created_) for row in GROUPS_AS_RESOURCES) @@ -46,7 +55,7 @@ def __gtuple__(cursor): return tuple(dict(row) for row in cursor.fetchall()) @pytest.fixture(scope="function") -def fxtr_group(conn_after_auth_migrations):# pylint: disable=[redefined-outer-name] +def fxtr_group(conn_after_auth_migrations, fxtr_users):# pylint: disable=[redefined-outer-name, unused-argument] """Fixture: setup a test group.""" with db.cursor(conn_after_auth_migrations) as cursor: cursor.executemany( @@ -57,7 +66,7 @@ def fxtr_group(conn_after_auth_migrations):# pylint: disable=[redefined-outer-na cursor.executemany( "INSERT INTO resources " - "VALUES(:resource_id, :resource_name, :category_id, :public)", + "VALUES(:resource_id, :resource_name, :category_id, :public, :created_by, :created_at)", GROUPS_AS_RESOURCES) cursor.executemany( diff --git a/tests/unit/auth/fixtures/resource_fixtures.py b/tests/unit/auth/fixtures/resource_fixtures.py index e06f64e..b570a49 100644 --- a/tests/unit/auth/fixtures/resource_fixtures.py +++ b/tests/unit/auth/fixtures/resource_fixtures.py @@ -1,11 +1,15 @@ """Fixtures and utilities for resource-related tests""" import uuid +import datetime import pytest from gn_auth.auth.db import sqlite3 as db from gn_auth.auth.authorisation.resources import Resource, ResourceCategory +from .user_fixtures import TEST_USERS + +_created_ = datetime.datetime.now() SYSTEM_CATEGORY = ResourceCategory( uuid.UUID("aa3d787f-af6a-44fa-9b0b-c82d40e54ad2"), @@ -15,48 +19,74 @@ SYSTEM_RESOURCE = Resource( uuid.UUID("0248b289-b277-4eaa-8c94-88a434d14b6e"), "GeneNetwork System", SYSTEM_CATEGORY, - True) + True, + resource_data=tuple(), + created_by=TEST_USERS[4], + created_at=_created_) TEST_RESOURCES = ( Resource(uuid.UUID("26ad1668-29f5-439d-b905-84d551f85955"), "ResourceG01R01", ResourceCategory(uuid.UUID("48056f84-a2a6-41ac-8319-0e1e212cba2a"), "genotype", "Genotype Dataset"), - True), + True, + resource_data=tuple(), + created_by=TEST_USERS[0], + created_at=_created_), Resource(uuid.UUID("2130aec0-fefd-434d-92fd-9ca342348b2d"), "ResourceG01R02", ResourceCategory(uuid.UUID("548d684b-d4d1-46fb-a6d3-51a56b7da1b3"), "phenotype", "Phenotype (Publish) Dataset"), - False), + False, + resource_data=tuple(), + created_by=TEST_USERS[0], + created_at=_created_), Resource(uuid.UUID("e9a1184a-e8b4-49fb-b713-8d9cbeea5b83"), "ResourceG01R03", ResourceCategory(uuid.UUID("fad071a3-2fc8-40b8-992b-cdefe7dcac79"), "mrna", "mRNA Dataset"), - False), + False, + resource_data=tuple(), + created_by=TEST_USERS[0], + created_at=_created_), Resource(uuid.UUID("14496a1c-c234-49a2-978c-8859ea274054"), "ResourceG02R01", ResourceCategory(uuid.UUID("48056f84-a2a6-41ac-8319-0e1e212cba2a"), "genotype", "Genotype Dataset"), - False), + False, + resource_data=tuple(), + created_by=TEST_USERS[0], + created_at=_created_), Resource(uuid.UUID("04ad9e09-94ea-4390-8a02-11f92999806b"), "ResourceG02R02", ResourceCategory(uuid.UUID("fad071a3-2fc8-40b8-992b-cdefe7dcac79"), "mrna", "mRNA Dataset"), - True)) + True, + resource_data=tuple(), + created_by=TEST_USERS[0], + created_at=_created_)) TEST_RESOURCES_PUBLIC = (SYSTEM_RESOURCE, TEST_RESOURCES[0], TEST_RESOURCES[4]) @pytest.fixture(scope="function") -def fxtr_resources(conn_after_auth_migrations): +def fxtr_resources(conn_after_auth_migrations, fxtr_users):# pylint: disable=[unused-argument] """fixture: setup test resources in the database""" conn = conn_after_auth_migrations with db.cursor(conn) as cursor: cursor.executemany( - "INSERT INTO resources VALUES (?,?,?,?)", + "INSERT INTO resources VALUES (?,?,?,?,?,?)", ((str(res.resource_id), res.resource_name, str(res.resource_category.resource_category_id), - 1 if res.public else 0) for res in TEST_RESOURCES)) + 1 if res.public else 0, + str(res.created_by.user_id), + res.created_at.timestamp()) for res in TEST_RESOURCES)) + cursor.execute( + "UPDATE resources SET created_by=?, created_at=? " + "WHERE resource_id=?", + (str(SYSTEM_RESOURCE.created_by.user_id), + SYSTEM_RESOURCE.created_at.timestamp(), + str(SYSTEM_RESOURCE.resource_id))) yield (conn, TEST_RESOURCES) diff --git a/tests/unit/auth/fixtures/role_fixtures.py b/tests/unit/auth/fixtures/role_fixtures.py index 63a3fca..24e8e9f 100644 --- a/tests/unit/auth/fixtures/role_fixtures.py +++ b/tests/unit/auth/fixtures/role_fixtures.py @@ -108,7 +108,7 @@ def fxtr_resource_roles(fxtr_resources, fxtr_roles):# pylint: disable=[redefined @pytest.fixture(scope="function") -def fxtr_setup_group_leaders(fxtr_users): +def fxtr_setup_group_leaders(fxtr_users, fxtr_group):# pylint: disable=[unused-argument] """Define what roles users have that target resources of type 'Group'.""" conn, users = fxtr_users with db.cursor(conn) as cursor: diff --git a/tests/unit/auth/fixtures/user_fixtures.py b/tests/unit/auth/fixtures/user_fixtures.py index 1cf0e20..0872142 100644 --- a/tests/unit/auth/fixtures/user_fixtures.py +++ b/tests/unit/auth/fixtures/user_fixtures.py @@ -1,28 +1,35 @@ """Fixtures and utilities for user-related tests""" import uuid +import datetime import pytest from gn_auth.auth.db import sqlite3 as db from gn_auth.auth.authentication.users import User, hash_password +_created_ = datetime.datetime.now() + TEST_USERS = ( User(uuid.UUID("ecb52977-3004-469e-9428-2a1856725c7f"), "group@lead.er", - "Group Leader"), + "Group Leader", created=_created_), User(uuid.UUID("21351b66-8aad-475b-84ac-53ce528451e3"), - "group@mem.ber01", "Group Member 01"), + "group@mem.ber01", "Group Member 01", created=_created_), User(uuid.UUID("ae9c6245-0966-41a5-9a5e-20885a96bea7"), - "group@mem.ber02", "Group Member 02"), + "group@mem.ber02", "Group Member 02", created=_created_), User(uuid.UUID("9a0c7ce5-2f40-4e78-979e-bf3527a59579"), - "unaff@iliated.user", "Unaffiliated User")) + "unaff@iliated.user", "Unaffiliated User", created=_created_), + User(uuid.UUID("60faf8a7-832b-471e-b6a0-bd4013f1fa0e"), + "sys@admin.user", "System Admin User", created=_created_)) @pytest.fixture(scope="function") -def fxtr_users(conn_after_auth_migrations, fxtr_group):# pylint: disable=[redefined-outer-name, unused-argument] +def fxtr_users(conn_after_auth_migrations):# pylint: disable=[redefined-outer-name, unused-argument] """Fixture: setup test users.""" - query = "INSERT INTO users(user_id, email, name) VALUES (?, ?, ?)" + query = ( + "INSERT INTO users(user_id, email, name, created) VALUES (?, ?, ?, ?)") with db.cursor(conn_after_auth_migrations) as cursor: cursor.executemany(query, ( - (str(user.user_id), user.email, user.name) for user in TEST_USERS)) + (str(user.user_id), user.email, user.name, user.created.timestamp()) + for user in TEST_USERS)) yield (conn_after_auth_migrations, TEST_USERS) diff --git a/tests/unit/auth/test_groups.py b/tests/unit/auth/test_groups.py index 346beb9..6f1e8cd 100644 --- a/tests/unit/auth/test_groups.py +++ b/tests/unit/auth/test_groups.py @@ -61,6 +61,8 @@ def __cleanup_create_group__(conn, user, group): (str(user.user_id), str(grp_rsc["resource_id"]))) cursor.execute("DELETE FROM group_resources WHERE group_id=?", (str(group.group_id),)) + cursor.execute("DELETE FROM resources WHERE resource_id=?", + (grp_rsc["resource_id"],)) cursor.execute("DELETE FROM groups WHERE group_id=?", (str(group.group_id),)) diff --git a/tests/unit/auth/test_resources.py b/tests/unit/auth/test_resources.py index 04da6df..81f967e 100644 --- a/tests/unit/auth/test_resources.py +++ b/tests/unit/auth/test_resources.py @@ -114,19 +114,19 @@ def test_public_resources(fxtr_resources): "user,expected", tuple(zip( conftest.TEST_USERS, - (sorted( + ((sorted( {res.resource_id: res for res in ((conftest.GROUP_RESOURCES[0],) + conftest.TEST_RESOURCES_GROUP_01 + conftest.TEST_RESOURCES_PUBLIC)}.values(), - key=sort_key_resources), - sorted( + key=sort_key_resources), 6), + (sorted( {res.resource_id: res for res in ((conftest.TEST_RESOURCES_GROUP_01[1],) + conftest.TEST_RESOURCES_PUBLIC)}.values() , - key=sort_key_resources), - PUBLIC_RESOURCES, PUBLIC_RESOURCES)))) + key=sort_key_resources), 4), + (PUBLIC_RESOURCES, 3), (PUBLIC_RESOURCES, 3))))) def test_user_resources(fxtr_resource_user_roles, user, expected): """ GIVEN: some resources in the database @@ -134,6 +134,10 @@ def test_user_resources(fxtr_resource_user_roles, user, expected): THEN: list only the resources for which the user can access """ conn, *_others = fxtr_resource_user_roles + uresources, count = user_resources(conn, user) + eresources, ecount = expected + assert count == ecount assert sorted( - {res.resource_id: res for res in user_resources(conn, user) - }.values(), key=sort_key_resources) == expected + {res.resource_id: res for res in uresources}.values(), + key=sort_key_resources + ) == eresources |