diff options
Diffstat (limited to 'gn_auth')
113 files changed, 5438 insertions, 392 deletions
diff --git a/gn_auth/__init__.py b/gn_auth/__init__.py index d6591e5..d03c9ef 100644 --- a/gn_auth/__init__.py +++ b/gn_auth/__init__.py @@ -61,33 +61,24 @@ def load_secrets_conf(app: Flask) -> None: app.config.from_pyfile(secretsfile) -def dev_loggers(appl: Flask) -> None: +def dev_loggers(appl: Flask) -> logging.Logger: """Setup the logging handlers.""" stderr_handler = logging.StreamHandler(stream=sys.stderr) appl.logger.addHandler(stderr_handler) + appl.logger.setLevel(appl.config["LOGLEVEL"]) - root_logger = logging.getLogger() - root_logger.addHandler(stderr_handler) - root_logger.setLevel(appl.config["LOGLEVEL"]) + return appl.logger -def gunicorn_loggers(appl: Flask) -> None: +def gunicorn_loggers(appl: Flask) -> logging.Logger: """Use gunicorn logging handlers for the application.""" logger = logging.getLogger("gunicorn.error") appl.logger.handlers = logger.handlers appl.logger.setLevel(logger.level) + return appl.logger -_LOGGABLE_MODULES_ = ( - "gn_auth.errors", - "gn_auth.errors.common", - "gn_auth.errors.authlib", - "gn_auth.errors.http.http_4xx_errors", - "gn_auth.errors.http.http_5xx_errors" -) - - -def setup_logging(appl: Flask) -> None: +def setup_logging(appl: Flask, loggable_modules: tuple[str, ...] = tuple()) -> None: """ Setup the loggers according to the WSGI server used to run the application. """ @@ -96,14 +87,11 @@ def setup_logging(appl: Flask) -> None: # https://peps.python.org/pep-3333/#id4 software, *_version_and_comments = os.environ.get( "SERVER_SOFTWARE", "").split('/') - if bool(software): - gunicorn_loggers(appl) - else: - dev_loggers(appl) - - loglevel = logging.getLevelName(appl.logger.getEffectiveLevel()) - for module_logger in _LOGGABLE_MODULES_: - logging.getLogger(module_logger).setLevel(loglevel) + logger = gunicorn_loggers(appl) if bool(software) else dev_loggers(appl) + for _logger in ( + item for item in logger.manager.loggerDict.values() + if isinstance(item, logging.Logger)): + _logger.addFilter(lambda record: record.name in loggable_modules) def create_app(config: Optional[dict] = None) -> Flask: @@ -112,18 +100,18 @@ def create_app(config: Optional[dict] = None) -> Flask: # ====== Setup configuration ====== app.config.from_object(settings) # Default settings - # Override defaults with startup settings - app.config.update(config or {}) # Override app settings with site-local settings if "GN_AUTH_CONF" in os.environ: app.config.from_envvar("GN_AUTH_CONF") override_settings_with_envvars(app) + # Override defaults with startup settings + app.config.update(config or {}) load_secrets_conf(app) # ====== END: Setup configuration ====== - setup_logging(app) + setup_logging(app, tuple(app.config.get("LOGGABLE_MODULES", []))) check_mandatory_settings(app) setup_oauth2_server(app) diff --git a/gn_auth/auth/authentication/oauth2/endpoints/introspection.py b/gn_auth/auth/authentication/oauth2/endpoints/introspection.py index 200b25d..cebb3be 100644 --- a/gn_auth/auth/authentication/oauth2/endpoints/introspection.py +++ b/gn_auth/auth/authentication/oauth2/endpoints/introspection.py @@ -23,7 +23,7 @@ class IntrospectionEndpoint(_IntrospectionEndpoint): CLIENT_AUTH_METHODS = ['client_secret_post'] def query_token(self, token_string: str, token_type_hint: str): """Query the token.""" - return _query_token(self, token_string, token_type_hint) + return _query_token(token_string, token_type_hint) # pylint: disable=[no-self-use] def introspect_token(self, token: OAuth2Token) -> dict: diff --git a/gn_auth/auth/authentication/oauth2/endpoints/revocation.py b/gn_auth/auth/authentication/oauth2/endpoints/revocation.py index 80922f1..0979694 100644 --- a/gn_auth/auth/authentication/oauth2/endpoints/revocation.py +++ b/gn_auth/auth/authentication/oauth2/endpoints/revocation.py @@ -15,7 +15,7 @@ class RevocationEndpoint(_RevocationEndpoint): CLIENT_AUTH_METHODS = ['client_secret_post'] def query_token(self, token_string: str, token_type_hint: str): """Query the token.""" - return _query_token(self, token_string, token_type_hint) + return _query_token(token_string, token_type_hint) def revoke_token(self, token: OAuth2Token, request): """Revoke token `token`.""" diff --git a/gn_auth/auth/authentication/oauth2/endpoints/utilities.py b/gn_auth/auth/authentication/oauth2/endpoints/utilities.py index 08b2a3b..490c141 100644 --- a/gn_auth/auth/authentication/oauth2/endpoints/utilities.py +++ b/gn_auth/auth/authentication/oauth2/endpoints/utilities.py @@ -1,5 +1,5 @@ """endpoint utilities""" -from typing import Any, Optional +from typing import Optional from flask import current_app from pymonad.maybe import Nothing @@ -8,9 +8,7 @@ from gn_auth.auth.db import sqlite3 as db from gn_auth.auth.authentication.oauth2.models.oauth2token import ( OAuth2Token, token_by_access_token, token_by_refresh_token) -def query_token(# pylint: disable=[unused-argument] - endpoint_object: Any, token_str: str, token_type_hint) -> Optional[ - OAuth2Token]: +def query_token(token_str: str, token_type_hint) -> Optional[OAuth2Token]: """Retrieve the token from the database.""" def __identity__(val): """Identity function.""" diff --git a/gn_auth/auth/authentication/oauth2/grants/jwt_bearer_grant.py b/gn_auth/auth/authentication/oauth2/grants/jwt_bearer_grant.py index c802091..63f979c 100644 --- a/gn_auth/auth/authentication/oauth2/grants/jwt_bearer_grant.py +++ b/gn_auth/auth/authentication/oauth2/grants/jwt_bearer_grant.py @@ -1,9 +1,8 @@ """JWT as Authorisation Grant""" import uuid import time - +import logging from typing import Optional -from flask import current_app as app from authlib.jose import jwt from authlib.common.encoding import to_native @@ -12,12 +11,17 @@ from authlib.oauth2.rfc7523.jwt_bearer import JWTBearerGrant as _JWTBearerGrant from authlib.oauth2.rfc7523.token import ( JWTBearerTokenGenerator as _JWTBearerTokenGenerator) -from gn_auth.debug import __pk__ +from gn_libs.debug import make_peeker + from gn_auth.auth.db.sqlite3 import with_db_connection from gn_auth.auth.authentication.users import User, user_by_id from gn_auth.auth.authentication.oauth2.models.oauth2client import OAuth2Client +logger = logging.getLogger(__name__) +__pk__ = make_peeker(logger) + + class JWTBearerTokenGenerator(_JWTBearerTokenGenerator): """ A JSON Web Token formatted bearer token generator for jwt-bearer grant type. @@ -149,6 +153,6 @@ class JWTBearerGrant(_JWTBearerGrant): include_refresh_token=self.request.client.check_grant_type( "refresh_token") ) - app.logger.debug('Issue token %r to %r', token, self.request.client) + logger.debug('Issue token %r to %r', token, self.request.client) self.save_token(token) return 200, token, self.TOKEN_RESPONSE_HEADER diff --git a/gn_auth/auth/authentication/oauth2/models/oauth2client.py b/gn_auth/auth/authentication/oauth2/models/oauth2client.py index fe12ff9..dfe5d79 100644 --- a/gn_auth/auth/authentication/oauth2/models/oauth2client.py +++ b/gn_auth/auth/authentication/oauth2/models/oauth2client.py @@ -1,5 +1,6 @@ """OAuth2 Client model.""" import json +import logging import datetime from uuid import UUID from urllib.parse import urlparse @@ -8,13 +9,12 @@ from dataclasses import asdict, dataclass from typing import Any, Sequence, Optional import requests -from flask import current_app as app from requests.exceptions import JSONDecodeError from authlib.jose import KeySet, JsonWebKey from authlib.oauth2.rfc6749 import ClientMixin from pymonad.maybe import Just, Maybe, Nothing +from gn_libs.debug import make_peeker -from gn_auth.debug import __pk__ from gn_auth.auth.db import sqlite3 as db from gn_auth.auth.errors import NotFoundError from gn_auth.auth.authentication.users import (User, @@ -23,6 +23,10 @@ from gn_auth.auth.authentication.users import (User, same_password) +logger = logging.getLogger(__name__) +__pk__ = make_peeker(logger) + + @dataclass(frozen=True) class OAuth2Client(ClientMixin): """ @@ -66,7 +70,7 @@ class OAuth2Client(ClientMixin): jwksuri = self.client_metadata.get("public-jwks-uri") __pk__(f"PUBLIC JWKs link for client {self.client_id}", jwksuri) if not bool(jwksuri): - app.logger.debug("No Public JWKs URI set for client!") + logger.debug("No Public JWKs URI set for client!") return KeySet([]) try: ## IMPORTANT: This can cause a deadlock if the client is working in @@ -78,13 +82,12 @@ class OAuth2Client(ClientMixin): timeout=300, allow_redirects=True).json()["jwks"]]) except requests.ConnectionError as _connerr: - app.logger.debug( + logger.debug( "Could not connect to provided URI: %s", jwksuri, exc_info=True) except JSONDecodeError as _jsonerr: - app.logger.debug( - "Could not convert response to JSON", exc_info=True) + logger.debug("Could not convert response to JSON", exc_info=True) except Exception as _exc:# pylint: disable=[broad-except] - app.logger.debug( + logger.debug( "Error retrieving the JWKs for the client.", exc_info=True) return KeySet([]) diff --git a/gn_auth/auth/authentication/oauth2/resource_server.py b/gn_auth/auth/authentication/oauth2/resource_server.py index 8ecf923..edab02c 100644 --- a/gn_auth/auth/authentication/oauth2/resource_server.py +++ b/gn_auth/auth/authentication/oauth2/resource_server.py @@ -1,4 +1,5 @@ """Protect the resources endpoints""" +import logging from datetime import datetime, timezone, timedelta from flask import current_app as app @@ -16,6 +17,9 @@ from gn_auth.auth.authentication.oauth2.models.jwt_bearer_token import ( from gn_auth.auth.authentication.oauth2.models.oauth2token import ( token_by_access_token) +logger = logging.getLogger(__name__) + + class BearerTokenValidator(_BearerTokenValidator): """Extends `authlib.oauth2.rfc6750.BearerTokenValidator`""" def authenticate_token(self, token_string: str): @@ -66,7 +70,7 @@ class JWTBearerTokenValidator(_JWTBearerTokenValidator): claims.validate() return claims except JoseError as error: - app.logger.debug('Authenticate token failed. %r', error) + logger.debug('Authenticate token failed. %r', error) return None diff --git a/gn_auth/auth/authentication/oauth2/views.py b/gn_auth/auth/authentication/oauth2/views.py index 6c3de51..8cc123f 100644 --- a/gn_auth/auth/authentication/oauth2/views.py +++ b/gn_auth/auth/authentication/oauth2/views.py @@ -1,5 +1,6 @@ """Endpoints for the oauth2 server""" import uuid +import logging import traceback from urllib.parse import urlparse @@ -27,8 +28,10 @@ from .endpoints.revocation import RevocationEndpoint from .endpoints.introspection import IntrospectionEndpoint +logger = logging.getLogger(__name__) auth = Blueprint("auth", __name__) + @auth.route("/delete-client/<uuid:client_id>", methods=["GET", "POST"]) def delete_client(client_id: uuid.UUID): """Delete an OAuth2 client.""" @@ -91,11 +94,11 @@ def authorise(): flash(email_passwd_msg, "alert alert-danger") return redirect_response # type: ignore[return-value] except EmailNotValidError as _enve: - app.logger.debug(traceback.format_exc()) + logger.debug(traceback.format_exc()) flash(email_passwd_msg, "alert alert-danger") return redirect_response # type: ignore[return-value] except NotFoundError as _nfe: - app.logger.debug(traceback.format_exc()) + logger.debug(traceback.format_exc()) flash(email_passwd_msg, "alert alert-danger") return redirect_response # type: ignore[return-value] 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/genotypes.py b/gn_auth/auth/authorisation/data/genotypes.py index ddb0add..d44cbfb 100644 --- a/gn_auth/auth/authorisation/data/genotypes.py +++ b/gn_auth/auth/authorisation/data/genotypes.py @@ -1,7 +1,9 @@ """Handle linking of Genotype data to the Auth(entic|oris)ation system.""" import uuid -from dataclasses import asdict +import logging from typing import Iterable +from functools import reduce +from dataclasses import asdict from gn_libs import mysqldb as gn3db from MySQLdb.cursors import DictCursor @@ -11,6 +13,9 @@ from gn_auth.auth.db import sqlite3 as authdb from gn_auth.auth.authorisation.checks import authorised_p from gn_auth.auth.authorisation.resources.groups.models import Group + +logger = logging.getLogger(__name__) + def linked_genotype_data(conn: authdb.DbConnection) -> Iterable[dict]: """Retrieve genotype data that is linked to user groups.""" with authdb.cursor(conn) as cursor: @@ -95,3 +100,37 @@ def link_genotype_data( "group": asdict(group), "datasets": datasets } + + +def resources_by_datasets_and_traits( + authconn: authdb.DbConnection, + dsets_traits: tuple[tuple[str, str], ...] +) -> tuple[dict, ...]: + """Fetch resources by their attached datasets and traits.""" + traits_by_datasets: dict[str, tuple[str, ...]] = reduce( + lambda acc, curr: { + **acc, + curr[0]: acc.get(curr[0], tuple()) + (curr[1],) + }, + dsets_traits, + {}) + paramstr = ", ".join(["?"] * len(dsets_traits)) + query = ( + "SELECT r.*, rc.*, lgd.dataset_name FROM linked_genotype_data AS lgd " + "INNER JOIN genotype_resources AS mr ON lgd.data_link_id=mr.data_link_id " + "INNER JOIN resources AS r ON mr.resource_id=r.resource_id " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE lgd.dataset_name " + f"IN ({paramstr})") + logger.debug("QUERY: %s", query) + with authdb.cursor(authconn) as cursor: + params = tuple(traits_by_datasets.keys()) + logger.debug("QUERY PARAMS: %s", params) + cursor.execute(query, tuple(traits_by_datasets.keys())) + return tuple({ + "resource_id": row["resource_id"], + "resource_data": tuple( + f'{row["dataset_name"]}::{trait_id}' + for trait_id in traits_by_datasets[row["dataset_name"]]) + } for row in cursor.fetchall()) diff --git a/gn_auth/auth/authorisation/data/mrna.py b/gn_auth/auth/authorisation/data/mrna.py index 0cc644e..fcf6ea3 100644 --- a/gn_auth/auth/authorisation/data/mrna.py +++ b/gn_auth/auth/authorisation/data/mrna.py @@ -1,7 +1,9 @@ """Handle linking of mRNA Assay data to the Auth(entic|oris)ation system.""" import uuid -from dataclasses import asdict +import logging from typing import Iterable +from functools import reduce +from dataclasses import asdict from gn_libs import mysqldb as gn3db from MySQLdb.cursors import DictCursor @@ -11,6 +13,10 @@ from gn_auth.auth.db import sqlite3 as authdb from gn_auth.auth.authorisation.checks import authorised_p from gn_auth.auth.authorisation.resources.groups.models import Group + +logger = logging.getLogger(__name__) + + def linked_mrna_data(conn: authdb.DbConnection) -> Iterable[dict]: """Retrieve mRNA Assay data that is linked to user groups.""" with authdb.cursor(conn) as cursor: @@ -100,3 +106,35 @@ def link_mrna_data( "group": asdict(group), "datasets": datasets } + + +def resources_by_datasets_and_traits( + authconn: authdb.DbConnection, + dsets_traits: tuple[tuple[str, str], ...] +) -> tuple[dict, ...]: + """Fetch resources by their attached datasets and traits.""" + traits_by_datasets: dict[str, tuple[str, ...]] = reduce( + lambda acc, curr: { + **acc, + curr[0]: acc.get(curr[0], tuple()) + (curr[1],) + }, + dsets_traits, + {}) + paramstr = ", ".join(["?"] * len(dsets_traits)) + query = ( + "SELECT r.*, rc.*, lmd.dataset_name FROM linked_mrna_data AS lmd " + "INNER JOIN mrna_resources AS mr ON lmd.data_link_id=mr.data_link_id " + "INNER JOIN resources AS r ON mr.resource_id=r.resource_id " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE lmd.dataset_name " + f"IN ({paramstr})") + logger.debug("QUERY: %s", query) + with authdb.cursor(authconn) as cursor: + cursor.execute(query, tuple(traits_by_datasets.keys())) + return tuple({ + "resource_id": row["resource_id"], + "resource_data": tuple( + f'{row["dataset_name"]}::{trait_id}' + for trait_id in traits_by_datasets[row["dataset_name"]]) + } for row in cursor.fetchall()) diff --git a/gn_auth/auth/authorisation/data/phenotypes.py b/gn_auth/auth/authorisation/data/phenotypes.py index 788b9e7..92cbe89 100644 --- a/gn_auth/auth/authorisation/data/phenotypes.py +++ b/gn_auth/auth/authorisation/data/phenotypes.py @@ -1,5 +1,7 @@ """Handle linking of Phenotype data to the Auth(entic|oris)ation system.""" import uuid +import logging +from functools import reduce from dataclasses import asdict from typing import Any, Iterable @@ -11,7 +13,6 @@ from flask import request, jsonify, Response, Blueprint, current_app as app 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 @@ -20,8 +21,10 @@ from gn_auth.auth.authorisation.resources.groups.models import Group, group_reso from gn_auth.auth.authorisation.checks import require_json from gn_auth.auth.authorisation.resources.checks import authorised_for2 +logger = logging.getLogger(__name__) phenosbp = Blueprint("phenotypes", __name__) + def linked_phenotype_data( authconn: authdb.DbConnection, gn3conn: gn3db.Connection, species: str = "") -> Iterable[dict[str, Any]]: @@ -58,41 +61,6 @@ def linked_phenotype_data( gn3cursor.execute(query, params) return (item for item in gn3cursor.fetchall()) -@authorised_p(("system:data:link-to-group",), - error_description=( - "You do not have sufficient privileges to link data to (a) " - "group(s)."), - oauth2_scope="profile group resource") -def ungrouped_phenotype_data( - authconn: authdb.DbConnection, gn3conn: gn3db.Connection): - """Retrieve phenotype data that is not linked to any user group.""" - with gn3conn.cursor() as cursor: - params = tuple( - (row["SpeciesId"], row["InbredSetId"], row["PublishFreezeId"], - row["PublishXRefId"]) - for row in linked_phenotype_data(authconn, gn3conn)) - paramstr = ", ".join(["(?, ?, ?, ?)"] * len(params)) - query = ( - "SELECT spc.SpeciesId, spc.SpeciesName, iset.InbredSetId, " - "iset.InbredSetName, pf.Id AS PublishFreezeId, " - "pf.Name AS dataset_name, pf.FullName AS dataset_fullname, " - "pf.ShortName AS dataset_shortname, pxr.Id AS PublishXRefId " - "FROM " - "Species AS spc " - "INNER JOIN InbredSet AS iset " - "ON spc.SpeciesId=iset.SpeciesId " - "INNER JOIN PublishFreeze AS pf " - "ON iset.InbredSetId=pf.InbredSetId " - "INNER JOIN PublishXRef AS pxr " - "ON pf.InbredSetId=pxr.InbredSetId") - if len(params) > 0: - query = query + ( - f" WHERE (iset.InbredSetId, pf.Id, pxr.Id) NOT IN ({paramstr})") - - cursor.execute(query, params) - return tuple(dict(row) for row in cursor.fetchall()) - - return tuple() def pheno_traits_from_db(gn3conn: gn3db.Connection, params: tuple[dict, ...]) -> tuple[dict, ...]: """An internal utility function. Don't use outside of this module.""" @@ -274,3 +242,44 @@ def delete_linked_phenotypes_data( "requested": len(xref_ids), "deleted": _deleted }) + + +def __organise_resources_data__(acc, curr) -> dict: + logger.debug("ORGANISING... %s", dict(curr)) + resource_row = acc.get(curr["resource_id"], { + "resource_id": curr["resource_id"], + "resource_data": tuple(), + }) + return { + **acc, + curr["resource_id"]: { + **resource_row, + "resource_data": resource_row["resource_data"] + ( + f'{curr["dataset_name"]}::{curr["trait_id"]}',) + } + } + + +def resources_by_datasets_and_traits( + authconn: authdb.DbConnection, + dsets_traits: tuple[tuple[str, str], ...] +) -> tuple[dict, ...]: + """Fetch resources by their attached datasets and traits.""" + paramstr = ", ".join(["(?, ?)"] * len(dsets_traits)) + query = ( + "SELECT r.*, rc.*, lpd.dataset_name, lpd.PublishXRefId AS trait_id " + "FROM linked_phenotype_data AS lpd " + "INNER JOIN phenotype_resources AS pr " + "ON lpd.data_link_id=pr.data_link_id " + "INNER JOIN resources AS r ON pr.resource_id=r.resource_id " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE (lpd.dataset_name, lpd.PublishXRefId) " + f"IN ({paramstr})") + with authdb.cursor(authconn) as cursor: + cursor.execute( + query, tuple(item for row in dsets_traits for item in row)) + return tuple(reduce( + __organise_resources_data__, + cursor.fetchall(), + {}).values()) diff --git a/gn_auth/auth/authorisation/data/views.py b/gn_auth/auth/authorisation/data/views.py index 4bf6746..228d95f 100644 --- a/gn_auth/auth/authorisation/data/views.py +++ b/gn_auth/auth/authorisation/data/views.py @@ -2,9 +2,9 @@ import sys import uuid import json -from dataclasses import asdict +import logging from typing import Any -from functools import partial +from functools import reduce, partial import redis from MySQLdb.cursors import DictCursor @@ -13,6 +13,7 @@ from flask import request, jsonify, Response, Blueprint, current_app as app from gn_libs import mysqldb as gn3db +from gn_libs import sqlite3 as db from gn_auth import jobs from gn_auth.commands import run_async_cmd @@ -21,54 +22,32 @@ from gn_auth.auth.requests import request_json from gn_auth.auth.errors import InvalidData, NotFoundError from gn_auth.auth.authorisation.resources.groups.models import group_by_id -from ...db import sqlite3 as db -from ...db.sqlite3 import with_db_connection +from gn_auth.auth.db.sqlite3 import with_db_connection # Replace this with gn_libs alternative from ..checks import require_json -from ..users.models import user_resource_roles - -from ..resources.checks import authorised_for -from ..resources.models import ( - user_resources, public_resources, attach_resources_data) - from ...authentication.users import User from ...authentication.oauth2.resource_server import require_oauth -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 - +from .mrna import ( + link_mrna_data, + ungrouped_mrna_data, + resources_by_datasets_and_traits as mrna_resources_by_datasets_and_traits) +from .genotypes import ( + link_genotype_data, + ungrouped_genotype_data, + resources_by_datasets_and_traits as geno_resources_by_datasets_and_traits) +from .phenotypes import ( + phenosbp, + link_phenotype_data, + pheno_traits_from_db, + resources_by_datasets_and_traits as pheno_resources_by_datasets_and_traits) + + +logger = logging.getLogger(__name__) data = Blueprint("data", __name__) data.register_blueprint(phenosbp, url_prefix="/phenotypes") -def build_trait_name(trait_fullname): - """ - Initialises the trait's name, and other values from the search data provided - - This is a copy of `gn3.db.traits.build_trait_name` function. - """ - def dataset_type(dset_name): - if dset_name.find('Temp') >= 0: - return "Temp" - if dset_name.find('Geno') >= 0: - return "Geno" - if dset_name.find('Publish') >= 0: - return "Publish" - return "ProbeSet" - - name_parts = trait_fullname.split("::") - assert len(name_parts) >= 2, f"Name format error: '{trait_fullname}'" - dataset_name = name_parts[0] - dataset_type = dataset_type(dataset_name) - return { - "db": { - "dataset_name": dataset_name, - "dataset_type": dataset_type}, - "trait_fullname": trait_fullname, - "trait_name": name_parts[1], - "cellid": name_parts[2] if len(name_parts) == 3 else "" - } @data.route("species") def list_species() -> Response: @@ -83,98 +62,141 @@ def list_species() -> Response: def authorisation() -> Response: """Retrieve the authorisation level for datasets/traits for the user.""" # Access endpoint with something like: - # curl -X POST http://127.0.0.1:8080/api/oauth2/data/authorisation \ + # curl -X POST http://127.0.0.1:8081/auth/data/authorisation \ # -H "Content-Type: application/json" \ # -d '{"traits": ["HC_M2_0606_P::1442370_at", "BXDGeno::01.001.695", # "BXDPublish::10001"]}' + def __organise_traits__(acc, curr): + dset, _trt = curr + key = "ProbeSet" + if dset.endswith("Publish"): + key = "Publish" + elif dset.endswith("Geno"): + key="Geno" + elif dset.endswith("Temp"): + key = "Temp" + else: + key = "ProbeSet" + + return { + **acc, + key: acc.get(key, tuple()) + (curr,) + } + _dset_traits: dict[str, tuple[tuple[str, str], ...]] = reduce( + __organise_traits__, + ( + (dset.strip(), trt.strip()) for dset, trt in + (trtstr.split("::") for trtstr in + request_json().get("traits", []))), + {key: tuple() for key in ("Publish", "ProbeSet", "Geno", "Temp")}) + db_uri = app.config["AUTH_DB"] - privileges = {} user = User(uuid.uuid4(), "anon@ymous.user", "Anonymous User") - with db.connection(db_uri) as auth_conn: - try: - with require_oauth.acquire("profile group resource") as _token: - user = _token.user - resources = attach_resources_data( - auth_conn, user_resources(auth_conn, _token.user)) - resources_roles = user_resource_roles(auth_conn, _token.user) - privileges = { - resource_id: tuple( - privilege.privilege_id - for roles in resources_roles[resource_id] - for privilege in roles.privileges)#("group:resource:view-resource",) - for resource_id, is_authorised - in authorised_for( - auth_conn, _token.user, - ("group:resource:view-resource",), tuple( - resource.resource_id for resource in resources)).items() - if is_authorised - } - except _HTTPException as exc: - err_msg = json.loads(exc.body) - if err_msg["error"] == "missing_authorization": - resources = attach_resources_data( - auth_conn, public_resources(auth_conn)) - else: - raise exc from None - - def __gen_key__(resource, data_item): - if resource.resource_category.resource_category_key.lower() == "phenotype": - return ( - f"{resource.resource_category.resource_category_key.lower()}::" - f"{data_item['dataset_name']}::{data_item['PublishXRefId']}") - return ( - f"{resource.resource_category.resource_category_key.lower()}::" - f"{data_item['dataset_name']}") - - data_to_resource_map = { - __gen_key__(resource, data_item): resource.resource_id - for resource in resources - for data_item in resource.resource_data + with (db.connection(db_uri) as authconn, db.cursor(authconn) as cursor): + _all_resources = { + _rrow["resource_id"]: _rrow + for _rtypes in ( + pheno_resources_by_datasets_and_traits( + authconn, _dset_traits["Publish"]), + geno_resources_by_datasets_and_traits( + authconn, _dset_traits["Geno"]), + mrna_resources_by_datasets_and_traits( + authconn, _dset_traits["ProbeSet"])) + for _rrow in _rtypes } - privileges = { - **{ - resource.resource_id: ("system:resource:public-read",) - for resource in resources if resource.public - }, - **privileges} - - args = request.get_json() - traits_names = args["traits"] # type: ignore[index] - def __translate__(val): + if (len(_all_resources.keys()) == 0 and + len(_dset_traits.get("Temp", tuple())) == 0): + raise NotFoundError( + "No resource(s) found for specified trait(s). Do(es) the " + "trait(s) actually exist?") + + # Handle Temp traits specially - they should be public/anonymous resources + if len(_dset_traits.get("Temp", tuple())) > 0: + # Create a synthetic public resource for Temp traits + # Use a predictable ID to identify synthetic temp resources + temp_resource_id = "gn-auth-temp-traits" + _all_resources[temp_resource_id] = { + "resource_id": temp_resource_id, + "resource_data": tuple(f"{dset}::{trait}" for dset, trait in _dset_traits["Temp"]) + } + + _resource_ids = tuple(_all_resources.keys()) + + + def __explode_resource_data__(trait_fullname): + _dset, _trt = trait_fullname.split("::") return { - "Temp": "Temp", - "ProbeSet": "mRNA", - "Geno": "Genotype", - "Publish": "Phenotype" - }[val] - - def __trait_key__(trait): - dataset_type = __translate__(trait['db']['dataset_type']).lower() - dataset_name = trait["db"]["dataset_name"] - if dataset_type == "phenotype": - return f"{dataset_type}::{dataset_name}::{trait['trait_name']}" - return f"{dataset_type}::{dataset_name}" - - return jsonify(tuple( - { - "user": asdict(user), - **{key:trait[key] for key in ("trait_fullname", "trait_name")}, - "dataset_name": trait["db"]["dataset_name"], - "dataset_type": __translate__(trait["db"]["dataset_type"]), - "resource_id": data_to_resource_map.get(__trait_key__(trait)), - "privileges": privileges.get( - data_to_resource_map.get( - __trait_key__(trait), - uuid.UUID("4afa415e-94cb-4189-b2c6-f9ce2b6a878d")), - tuple()) + ( - # Temporary traits do not exist in db: Set them - # as public-read - ("system:resource:public-read",) - if trait["db"]["dataset_type"] == "Temp" - else tuple()) - } for trait in - (build_trait_name(trait_fullname) - for trait_fullname in traits_names))) + "dataset_name": _dset, + "dataset_type": ( + "Phenotype" if _dset.endswith("Publish") + else ("Genotype" if _dset.endswith("Geno") + else ("Temporary" if _dset.endswith("Temp") + else "mRNA"))), + "trait_name": _trt, + "trait_fullname": trait_fullname + } + + _paramstr = ", ".join(["?"] * len(_resource_ids)) + _privileges_by_resource: dict[str, tuple[str, ...]] = {} + + # Separate synthetic temp resources from real resources + temp_resource_id = "gn-auth-temp-traits" + real_resource_ids = tuple(rid for rid in _resource_ids if rid != temp_resource_id) + + # Query privileges only for real resources + if len(real_resource_ids) > 0: + real_paramstr = ", ".join(["?"] * len(real_resource_ids)) + try: + with require_oauth.acquire("profile group resource") as _token: + user = _token.user + cursor.execute( + "SELECT ur.resource_id, r.role_id, rp.privilege_id " + "FROM user_roles AS ur " + "INNER JOIN roles AS r ON ur.role_id=r.role_id " + "INNER JOIN role_privileges AS rp ON r.role_id=rp.role_id " + "WHERE ur.user_id = ? " + f"AND ur.resource_id IN ({real_paramstr})", + (str(user.user_id),) + real_resource_ids + ) + _privileges_by_resource = reduce( + lambda acc, curr: { + **acc, + curr["resource_id"]: ( + acc.get(curr["resource_id"], tuple()) + + (curr["privilege_id"],)) + }, + cursor.fetchall(), + {}) + except _HTTPException as exc: + err_msg = json.loads(exc.body) + if err_msg["error"] == "missing_authorization": + cursor.execute( + "SELECT rsc.resource_id " + "FROM resources AS rsc " + "WHERE rsc.public = '1' " + f"AND rsc.resource_id IN ({real_paramstr}) ", + real_resource_ids) + _privileges_by_resource = { + row["resource_id"]: ('group:resource:view-resource',) + for row in cursor.fetchall() + } + else: + raise exc from None + + # Temp resources are always publicly viewable + if temp_resource_id in _resource_ids: + _privileges_by_resource[temp_resource_id] = ('group:resource:view-resource',) + + return jsonify({ + "authorisation": [{ + **resource, + "resource_data": [ + __explode_resource_data__(item) + for item in resource["resource_data"]], + "privileges": _privileges_by_resource.get(resource["resource_id"], tuple()) + } for resource in _all_resources.values()] + }) + def __search_mrna__(): query = __request_key__("query", "") @@ -219,7 +241,7 @@ def __search_phenotypes__(): job_id = uuid.uuid4() selected = __request_key__("selected_traits", []) command =[ - sys.executable, "-m", "scripts.search_phenotypes", + sys.executable, "-m", "gn_auth.scripts.search_phenotypes", __request_key__("species_name"), __request_key__("query"), str(job_id), 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 bc9e4da..252df2f 100644 --- a/gn_auth/auth/authorisation/resources/checks.py +++ b/gn_auth/auth/authorisation/resources/checks.py @@ -135,6 +135,11 @@ def can_delete( resource_id: uuid.UUID ) -> bool: """Check whether user is allowed delete a resource and/or its data.""" + warnings.warn( + (f"Function '{__name__}.can_delete' is deprecated. " + "Use `gn_libs.privileges.resources.can_delete` instead."), + category=DeprecationWarning, + stacklevel=2) return ( authorised_for_spec(# resource-level delete access conn, @@ -149,42 +154,17 @@ def can_delete( "(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.""" + warnings.warn( + (f"Function '{__name__}.can_edit' is deprecated. " + "Use `gn_libs.privileges.resources.can_edit` instead."), + category=DeprecationWarning, + stacklevel=2) return ( authorised_for_spec( # resource-level edit access: user has edit access to his resource. @@ -199,4 +179,3 @@ def can_edit( 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 b8eaacb..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 .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, ...]: @@ -245,8 +333,7 @@ def link_data_to_resource( """Link data to resource.""" 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,8 +348,7 @@ def unlink_data_from_resource( """Unlink data from resource.""" 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)) 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 e4401c5..f114476 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 @@ -39,12 +41,11 @@ from gn_auth.auth.authorisation.roles.models import ( from gn_auth.auth.authentication.oauth2.resource_server import require_oauth from gn_auth.auth.authentication.users import User, user_by_id, user_by_email -from .system.models import system_resource - 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 can_delete, authorised_for from .models import ( @@ -52,7 +53,10 @@ from .models import ( 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="/") @@ -97,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 @@ -116,6 +119,49 @@ 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): + def __extract_privileges__(roles: tuple[Role, ...]) -> tuple[str, ...]: + return tuple( + priv.privilege_id for role in roles + for priv in role.privileges) + + _sys_resource = system_resource(conn) + _privileges = { + ("system_privileges" + if _rid == _sys_resource.resource_id + else "resource_privileges"): __extract_privileges__(_rroles) + for _rid, _rroles in user_roles_on_resources( + conn, + _token.user, + (resource_id, _sys_resource.resource_id) + ).items() + } + 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: @@ -234,9 +280,11 @@ def resource_users(resource_id: UUID): **users_n_roles, user_id: { "user": user, - "user_group": Group( - UUID(row["group_id"]), row["group_name"], - json.loads(row["group_metadata"])), + "user_group": ( + Group(UUID(row["group_id"]), + row["group_name"], + json.loads(row["group_metadata"])) + if bool(row["group_id"]) else False) , "roles": users_n_roles.get( user_id, {}).get("roles", tuple()) + (role,) } @@ -244,7 +292,7 @@ def resource_users(resource_id: UUID): cursor.execute( "SELECT g.*, u.*, r.* " "FROM groups AS g INNER JOIN group_users AS gu " - "ON g.group_id=gu.group_id INNER JOIN users AS u " + "ON g.group_id=gu.group_id RIGHT JOIN users AS u " "ON gu.user_id=u.user_id INNER JOIN user_roles AS ur " "ON u.user_id=ur.user_id INNER JOIN roles AS r " "ON ur.role_id=r.role_id " @@ -257,7 +305,8 @@ def resource_users(resource_id: UUID): results = ( { "user": asdict(row["user"]), - "user_group": asdict(row["user_group"]), + "user_group": ( + asdict(row["user_group"]) if row["user_group"] else False), "roles": tuple(asdict(role) for role in row["roles"]) } for row in ( user_row for user_id, user_row @@ -470,7 +519,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": ( @@ -508,7 +557,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 = { @@ -707,13 +755,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/admin/models.py b/gn_auth/auth/authorisation/users/admin/models.py index 3d68932..0594864 100644 --- a/gn_auth/auth/authorisation/users/admin/models.py +++ b/gn_auth/auth/authorisation/users/admin/models.py @@ -4,6 +4,7 @@ import warnings from gn_auth.auth.db import sqlite3 as db from gn_auth.auth.authentication.users import User from gn_auth.auth.authorisation.roles.models import Role, db_rows_to_roles +from gn_auth.auth.authorisation.resources.system.models import system_resource def sysadmin_role(conn: db.DbConnection) -> Role: @@ -28,14 +29,14 @@ def grant_sysadmin_role(cursor: db.DbCursor, user: User) -> User: cursor.execute( "SELECT * FROM roles WHERE role_name='system-administrator'") admin_role = cursor.fetchone() - cursor.execute("SELECT resources.resource_id FROM resources") - cursor.executemany( + sysresource = system_resource(cursor) + cursor.execute( "INSERT INTO user_roles VALUES (:user_id, :role_id, :resource_id)", - tuple({ + { "user_id": str(user.user_id), "role_id": admin_role["role_id"], - "resource_id": resource_id - } for resource_id in cursor.fetchall())) + "resource_id": str(sysresource.resource_id) + }) return user diff --git a/gn_auth/auth/authorisation/users/admin/views.py b/gn_auth/auth/authorisation/users/admin/views.py index 9bc1c36..62eccfd 100644 --- a/gn_auth/auth/authorisation/users/admin/views.py +++ b/gn_auth/auth/authorisation/users/admin/views.py @@ -1,6 +1,5 @@ """UI for admin stuff""" import uuid -import json import random import string from typing import Optional @@ -240,13 +239,6 @@ def register_client(): client_secret = raw_client_secret) -def __parse_client__(sqlite3_row) -> dict: - """Parse the client details into python datatypes.""" - return { - **dict(sqlite3_row), - "client_metadata": json.loads(sqlite3_row["client_metadata"]) - } - @admin.route("/list-client", methods=["GET"]) @is_admin def list_clients(): diff --git a/gn_auth/auth/authorisation/users/collections/views.py b/gn_auth/auth/authorisation/users/collections/views.py index f619c3d..5ed2c23 100644 --- a/gn_auth/auth/authorisation/users/collections/views.py +++ b/gn_auth/auth/authorisation/users/collections/views.py @@ -1,4 +1,5 @@ """Views regarding user collections.""" +import logging from uuid import UUID from redis import Redis @@ -25,8 +26,10 @@ from .models import ( REDIS_COLLECTIONS_KEY, delete_collections as _delete_collections) +logger = logging.getLogger(__name__) collections = Blueprint("collections", __name__) + @collections.route("/list") @require_oauth("profile user") def list_user_collections() -> Response: @@ -44,7 +47,7 @@ def list_anonymous_collections(anon_id: UUID) -> Response: def __list__(conn: db.DbConnection) -> tuple: try: _user = user_by_id(conn, anon_id) - current_app.logger.warning( + logger.warning( "Fetch collections for authenticated user using the " "`list_user_collections()` endpoint.") return tuple() diff --git a/gn_auth/auth/authorisation/users/models.py b/gn_auth/auth/authorisation/users/models.py index d30bfd0..ab7a980 100644 --- a/gn_auth/auth/authorisation/users/models.py +++ b/gn_auth/auth/authorisation/users/models.py @@ -1,5 +1,6 @@ """Functions for acting on users.""" import uuid +import warnings from functools import reduce from datetime import datetime, timedelta @@ -128,3 +129,40 @@ def user_resource_roles(conn: db.DbConnection, user: User) -> dict[uuid.UUID, tu (str(user.user_id),)) return __build_resource_roles__( (dict(row) for row in cursor.fetchall())) + + +def delete_users_by_id( + conn: db.DbConnection, + user_ids: tuple[uuid.UUID, ...] +) -> int: + """Delete users unconditionally by ID, removing all dependent data. + + Unlike the HTTP endpoint, this bypasses all policy checks — users are + deleted regardless of their roles or group memberships. Returns the + number of users removed from the users table. + """ + warnings.warn( + (f"Running dangerous function `{__name__}.delete_users_by_id`. " + "Do ensure that is what you actually want."), + category=RuntimeWarning) + if not user_ids: + return 0 + _ids = tuple(str(uid) for uid in user_ids) + _paramstr = ", ".join(["?"] * len(_ids)) + _dependent_tables = ( + ("authorisation_code", "user_id"), + ("forgot_password_tokens", "user_id"), + ("group_join_requests", "requester_id"), + ("jwt_refresh_tokens", "user_id"), + ("oauth2_tokens", "user_id"), + ("user_credentials", "user_id"), + ("user_roles", "user_id"), + ("user_verification_codes", "user_id"), + ) + with db.cursor(conn) as cursor: + for table, col in _dependent_tables: + cursor.execute( + f"DELETE FROM {table} WHERE {col} IN ({_paramstr})", _ids) + cursor.execute( + f"DELETE FROM users WHERE user_id IN ({_paramstr})", _ids) + return cursor.rowcount 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/db/sqlite3.py b/gn_auth/auth/db/sqlite3.py index 12a46c7..5f54752 100644 --- a/gn_auth/auth/db/sqlite3.py +++ b/gn_auth/auth/db/sqlite3.py @@ -1,63 +1,28 @@ """Handle connection to auth database.""" -import sqlite3 -import logging -import contextlib -from typing import Any, Protocol, Callable, Iterator - -import traceback +import warnings +from typing import Any, Callable from flask import current_app -from .protocols import DbCursor - -class DbConnection(Protocol): - """Type annotation for a generic database connection object.""" - def cursor(self) -> Any: - """A cursor object""" - - def commit(self) -> Any: - """Commit the transaction.""" - - def rollback(self) -> Any: - """Rollback the transaction.""" +from gn_libs.sqlite3 import cursor, connection # pylint: disable=[unused-import] +from gn_libs.protocols import DbCursor, DbConnection # pylint: disable=[unused-import] -@contextlib.contextmanager -def connection(db_path: str, row_factory: Callable = sqlite3.Row) -> Iterator[DbConnection]: - """Create the connection to the auth database.""" - logging.debug("SQLite3 DB Path: '%s'.", db_path) - conn = sqlite3.connect(db_path) - conn.row_factory = row_factory - conn.set_trace_callback(logging.debug) - conn.execute("PRAGMA foreign_keys = ON") - try: - yield conn - except sqlite3.Error as exc: - conn.rollback() - logging.debug(traceback.format_exc()) - raise exc - finally: - conn.commit() - conn.close() +warnings.warn( + f"Module '{__name__}' is deprecated. Use `gn_libs.sqlite3` instead.", + category=DeprecationWarning, + stacklevel=2) -@contextlib.contextmanager -def cursor(conn: DbConnection) -> Iterator[DbCursor]: - """Get a cursor from the given connection to the auth database.""" - cur = conn.cursor() - try: - yield cur - conn.commit() - except sqlite3.Error as exc: - conn.rollback() - logging.debug(traceback.format_exc()) - raise exc - finally: - cur.close() def with_db_connection(func: Callable[[DbConnection], Any]) -> Any: """ Takes a function of one argument `func`, whose one argument is a database connection. """ + warnings.warn( + (f"Function '{__name__}.with_db_connection' is deprecated. " + "Use `gn_libs.sqlite3.with_db_connection` instead."), + category=DeprecationWarning, + stacklevel=2) db_uri = current_app.config["AUTH_DB"] with connection(db_uri) as conn: return func(conn) 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/debug.py b/gn_auth/debug.py deleted file mode 100644 index 6b7173b..0000000 --- a/gn_auth/debug.py +++ /dev/null @@ -1,22 +0,0 @@ -"""Debug utilities""" -import logging -from flask import current_app - -__this_module_name__ = __name__ - - -# pylint: disable=invalid-name -def getLogger(name: str): - """Return a logger""" - return ( - logging.getLogger(name) - if not bool(current_app) - else current_app.logger) - -def __pk__(*args): - """Format log entry""" - value = args[-1] - title_vals = " => ".join(args[0:-1]) - logger = getLogger(__this_module_name__) - logger.debug("%s: %s", title_vals, value) - return value 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/gn_auth/migrations.py b/gn_auth/migrations/__init__.py index 3451e07..6acb058 100644 --- a/gn_auth/migrations.py +++ b/gn_auth/migrations/__init__.py @@ -1,4 +1,5 @@ -"""Run the migrations in the app, rather than with yoyo CLI.""" +"""Migrations package: Provides the migrations, and some utility functions to +help with dealing with migrations.""" from pathlib import Path from typing import Union diff --git a/gn_auth/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py b/gn_auth/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py new file mode 100644 index 0000000..d511f5d --- /dev/null +++ b/gn_auth/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py @@ -0,0 +1,19 @@ +""" +Initialise the auth(entic|oris)ation database. +""" + +from yoyo import step + +__depends__ = {} # type: ignore[var-annotated] + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS users( + user_id TEXT PRIMARY KEY NOT NULL, + email TEXT UNIQUE NOT NULL, + name TEXT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS users") +] diff --git a/gn_auth/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py b/gn_auth/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py new file mode 100644 index 0000000..48bd663 --- /dev/null +++ b/gn_auth/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py @@ -0,0 +1,20 @@ +""" +create user_credentials table +""" + +from yoyo import step + +__depends__ = {'20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS user_credentials( + user_id TEXT PRIMARY KEY, + password TEXT NOT NULL, + FOREIGN KEY(user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS user_credentials") +] diff --git a/gn_auth/migrations/auth/20221108_01_CoxYh-create-the-groups-table.py b/gn_auth/migrations/auth/20221108_01_CoxYh-create-the-groups-table.py new file mode 100644 index 0000000..29f92d4 --- /dev/null +++ b/gn_auth/migrations/auth/20221108_01_CoxYh-create-the-groups-table.py @@ -0,0 +1,19 @@ +""" +Create the groups table +""" + +from yoyo import step + +__depends__ = {'20221103_02_sGrIs-create-user-credentials-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS groups( + group_id TEXT PRIMARY KEY NOT NULL, + group_name TEXT NOT NULL, + group_metadata TEXT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS groups") +] diff --git a/gn_auth/migrations/auth/20221108_02_wxTr9-create-privileges-table.py b/gn_auth/migrations/auth/20221108_02_wxTr9-create-privileges-table.py new file mode 100644 index 0000000..67720b2 --- /dev/null +++ b/gn_auth/migrations/auth/20221108_02_wxTr9-create-privileges-table.py @@ -0,0 +1,18 @@ +""" +Create privileges table +""" + +from yoyo import step + +__depends__ = {'20221108_01_CoxYh-create-the-groups-table'} + +steps = [ + step( + """ + CREATE TABLE privileges( + privilege_id TEXT PRIMARY KEY, + privilege_name TEXT NOT NULL + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS privileges") +] diff --git a/gn_auth/migrations/auth/20221108_03_Pbhb1-create-resource-categories-table.py b/gn_auth/migrations/auth/20221108_03_Pbhb1-create-resource-categories-table.py new file mode 100644 index 0000000..ce752ef --- /dev/null +++ b/gn_auth/migrations/auth/20221108_03_Pbhb1-create-resource-categories-table.py @@ -0,0 +1,19 @@ +""" +Create resource_categories table +""" + +from yoyo import step + +__depends__ = {'20221108_02_wxTr9-create-privileges-table'} + +steps = [ + step( + """ + CREATE TABLE resource_categories( + resource_category_id TEXT PRIMARY KEY, + resource_category_key TEXT NOT NULL, + resource_category_description TEXT NOT NULL + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS resource_categories") +] diff --git a/gn_auth/migrations/auth/20221108_04_CKcSL-init-data-in-resource-categories-table.py b/gn_auth/migrations/auth/20221108_04_CKcSL-init-data-in-resource-categories-table.py new file mode 100644 index 0000000..76ffbef --- /dev/null +++ b/gn_auth/migrations/auth/20221108_04_CKcSL-init-data-in-resource-categories-table.py @@ -0,0 +1,25 @@ +""" +Init data in resource_categories table +""" + +from yoyo import step + +__depends__ = {'20221108_03_Pbhb1-create-resource-categories-table'} + +steps = [ + step( + """ + INSERT INTO resource_categories VALUES + ('fad071a3-2fc8-40b8-992b-cdefe7dcac79', 'mrna', 'mRNA Dataset'), + ('548d684b-d4d1-46fb-a6d3-51a56b7da1b3', 'phenotype', 'Phenotype (Publish) Dataset'), + ('48056f84-a2a6-41ac-8319-0e1e212cba2a', 'genotype', 'Genotype Dataset') + """, + """ + DELETE FROM resource_categories WHERE resource_category_id IN + ( + 'fad071a3-2fc8-40b8-992b-cdefe7dcac79', + '548d684b-d4d1-46fb-a6d3-51a56b7da1b3', + '48056f84-a2a6-41ac-8319-0e1e212cba2a' + ) + """) +] diff --git a/gn_auth/migrations/auth/20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field.py b/gn_auth/migrations/auth/20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field.py new file mode 100644 index 0000000..6c829b1 --- /dev/null +++ b/gn_auth/migrations/auth/20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field.py @@ -0,0 +1,17 @@ +""" +Add 'resource_meta' field to 'resource_categories' field. +""" + +from yoyo import step + +__depends__ = {'20221108_04_CKcSL-init-data-in-resource-categories-table'} + +steps = [ + step( + """ + ALTER TABLE resource_categories + ADD COLUMN + resource_meta TEXT NOT NULL DEFAULT '[]' + """, + "ALTER TABLE resource_categories DROP COLUMN resource_meta") +] diff --git a/gn_auth/migrations/auth/20221110_01_WtZ1I-create-resources-table.py b/gn_auth/migrations/auth/20221110_01_WtZ1I-create-resources-table.py new file mode 100644 index 0000000..abc8895 --- /dev/null +++ b/gn_auth/migrations/auth/20221110_01_WtZ1I-create-resources-table.py @@ -0,0 +1,26 @@ +""" +Create 'resources' table +""" + +from yoyo import step + +__depends__ = {'20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS resources( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, + resource_name TEXT NOT NULL UNIQUE, + resource_category_id TEXT NOT NULL, + PRIMARY KEY(group_id, resource_id), + FOREIGN KEY(group_id) REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(resource_category_id) + REFERENCES resource_categories(resource_category_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS resources") +] diff --git a/gn_auth/migrations/auth/20221110_05_BaNtL-create-roles-table.py b/gn_auth/migrations/auth/20221110_05_BaNtL-create-roles-table.py new file mode 100644 index 0000000..51e19e8 --- /dev/null +++ b/gn_auth/migrations/auth/20221110_05_BaNtL-create-roles-table.py @@ -0,0 +1,19 @@ +""" +Create 'roles' table +""" + +from yoyo import step + +__depends__ = {'20221110_01_WtZ1I-create-resources-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS roles( + role_id TEXT NOT NULL PRIMARY KEY, + role_name TEXT NOT NULL, + user_editable INTEGER NOT NULL DEFAULT 1 CHECK (user_editable=0 or user_editable=1) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS roles") +] diff --git a/gn_auth/migrations/auth/20221110_06_Pq2kT-create-generic-roles-table.py b/gn_auth/migrations/auth/20221110_06_Pq2kT-create-generic-roles-table.py new file mode 100644 index 0000000..2b55c2b --- /dev/null +++ b/gn_auth/migrations/auth/20221110_06_Pq2kT-create-generic-roles-table.py @@ -0,0 +1,24 @@ +""" +Create 'generic_roles' table + +The roles in this table will be template roles, defining some common roles that +can be used within the groups. + +They could also be used to define system-level roles, though those will not be +provided to the "common" users. +""" + +from yoyo import step + +__depends__ = {'20221110_05_BaNtL-create-roles-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS generic_roles( + role_id TEXT PRIMARY KEY, + role_name TEXT NOT NULL + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS generic_roles") +] diff --git a/gn_auth/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py b/gn_auth/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py new file mode 100644 index 0000000..0d0eeb9 --- /dev/null +++ b/gn_auth/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py @@ -0,0 +1,29 @@ +""" +Create 'role_privileges' table +""" + +from yoyo import step + +__depends__ = {'20221110_06_Pq2kT-create-generic-roles-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS role_privileges( + role_id TEXT NOT NULL, + privilege_id TEXT NOT NULL, + PRIMARY KEY(role_id, privilege_id), + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS role_privileges"), + step( + """ + CREATE INDEX IF NOT EXISTS idx_tbl_role_privileges_cols_role_id + ON role_privileges(role_id) + """, + "DROP INDEX IF EXISTS idx_tbl_role_privileges_cols_role_id") +] diff --git a/gn_auth/migrations/auth/20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table.py b/gn_auth/migrations/auth/20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table.py new file mode 100644 index 0000000..077182b --- /dev/null +++ b/gn_auth/migrations/auth/20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table.py @@ -0,0 +1,22 @@ +""" +Add 'privilege_category' and 'privilege_description' columns to 'privileges' table +""" + +from yoyo import step + +__depends__ = {'20221110_07_7WGa1-create-role-privileges-table'} + +steps = [ + step( + """ + ALTER TABLE privileges ADD COLUMN + privilege_category TEXT NOT NULL DEFAULT 'common' + """, + "ALTER TABLE privileges DROP COLUMN privilege_category"), + step( + """ + ALTER TABLE privileges ADD COLUMN + privilege_description TEXT + """, + "ALTER TABLE privileges DROP COLUMN privilege_description") +] diff --git a/gn_auth/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py b/gn_auth/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py new file mode 100644 index 0000000..072f226 --- /dev/null +++ b/gn_auth/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py @@ -0,0 +1,66 @@ +""" +Enumerate initial privileges +""" + +from yoyo import step + +__depends__ = {'20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table'} + +steps = [ + step( + """ + INSERT INTO + privileges(privilege_id, privilege_name, privilege_category, + privilege_description) + VALUES + -- group-management privileges + ('4842e2aa-38b9-4349-805e-0a99a9cf8bff', 'create-group', + 'group-management', 'Create a group'), + ('3ebfe79c-d159-4629-8b38-772cf4bc2261', 'view-group', + 'group-management', 'View the details of a group'), + ('52576370-b3c7-4e6a-9f7e-90e9dbe24d8f', 'edit-group', + 'group-management', 'Edit the details of a group'), + ('13ec2a94-4f1a-442d-aad2-936ad6dd5c57', 'delete-group', + 'group-management', 'Delete a group'), + ('ae4add8c-789a-4d11-a6e9-a306470d83d9', 'add-group-member', + 'group-management', 'Add a user to a group'), + ('f1bd3f42-567e-4965-9643-6d1a52ddee64', 'remove-group-member', + 'group-management', 'Remove a user from a group'), + ('80f11285-5079-4ec0-907c-06509f88a364', 'assign-group-leader', + 'group-management', 'Assign user group-leader privileges'), + ('d4afe2b3-4ca0-4edd-b37d-966535b5e5bd', + 'transfer-group-leadership', 'group-management', + 'Transfer leadership of the group to some other member'), + + -- resource-management privileges + ('aa25b32a-bff2-418d-b0a2-e26b4a8f089b', 'create-resource', + 'resource-management', 'Create a resource object'), + ('7f261757-3211-4f28-a43f-a09b800b164d', 'view-resource', + 'resource-management', 'view a resource and use it in computations'), + ('2f980855-959b-4339-b80e-25d1ec286e21', 'edit-resource', + 'resource-management', 'edit/update a resource'), + ('d2a070fd-e031-42fb-ba41-d60cf19e5d6d', 'delete-resource', + 'resource-management', 'Delete a resource'), + + -- role-management privileges + ('221660b1-df05-4be1-b639-f010269dbda9', 'create-role', + 'role-management', 'Create a new role'), + ('7bcca363-cba9-4169-9e31-26bdc6179b28', 'edit-role', + 'role-management', 'edit/update an existing role'), + ('5103cc68-96f8-4ebb-83a4-a31692402c9b', 'assign-role', + 'role-management', 'Assign a role to an existing user'), + ('1c59eff5-9336-4ed2-a166-8f70d4cb012e', 'delete-role', + 'role-management', 'Delete an existing role'), + + -- user-management privileges + ('e7252301-6ee0-43ba-93ef-73b607cf06f6', 'reset-any-password', + 'user-management', 'Reset the password for any user'), + ('1fe61370-cae9-4983-bd6c-ce61050c510f', 'delete-any-user', + 'user-management', 'Delete any user from the system'), + + -- sytem-admin privileges + ('519db546-d44e-4fdc-9e4e-25aa67548ab3', 'masquerade', + 'system-admin', 'Masquerade as some other user') + """, + "DELETE FROM privileges") +] diff --git a/gn_auth/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py b/gn_auth/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py new file mode 100644 index 0000000..2048f4a --- /dev/null +++ b/gn_auth/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py @@ -0,0 +1,35 @@ +""" +Create 'generic_role_privileges' table + +This table links the generic_roles to the privileges they provide +""" + +from yoyo import step + +__depends__ = {'20221113_01_7M0hv-enumerate-initial-privileges'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS generic_role_privileges( + generic_role_id TEXT NOT NULL, + privilege_id TEXT NOT NULL, + PRIMARY KEY(generic_role_id, privilege_id), + FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS generic_role_privileges"), + step( + """ + CREATE INDEX IF NOT EXISTS + idx_tbl_generic_role_privileges_cols_generic_role_id + ON generic_role_privileges(generic_role_id) + """, + """ + DROP INDEX IF EXISTS + idx_tbl_generic_role_privileges_cols_generic_role_id + """) +] diff --git a/gn_auth/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py b/gn_auth/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py new file mode 100644 index 0000000..6bd101b --- /dev/null +++ b/gn_auth/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py @@ -0,0 +1,41 @@ +""" +Drop 'generic_role*' tables +""" + +from yoyo import step + +__depends__ = {'20221114_01_n8gsF-create-generic-role-privileges-table'} + +steps = [ + step( + """ + DROP INDEX IF EXISTS + idx_tbl_generic_role_privileges_cols_generic_role_id + """, + """ + CREATE INDEX IF NOT EXISTS + idx_tbl_generic_role_privileges_cols_generic_role_id + ON generic_role_privileges(generic_role_id) + """), + step( + "DROP TABLE IF EXISTS generic_role_privileges", + """ + CREATE TABLE IF NOT EXISTS generic_role_privileges( + generic_role_id TEXT NOT NULL, + privilege_id TEXT NOT NULL, + PRIMARY KEY(generic_role_id, privilege_id), + FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """), + step( + "DROP TABLE IF EXISTS generic_roles", + """ + CREATE TABLE IF NOT EXISTS generic_roles( + role_id TEXT PRIMARY KEY, + role_name TEXT NOT NULL + ) WITHOUT ROWID + """) +] diff --git a/gn_auth/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py b/gn_auth/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py new file mode 100644 index 0000000..a7e7b45 --- /dev/null +++ b/gn_auth/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py @@ -0,0 +1,29 @@ +""" +Create 'group_roles' table +""" + +from yoyo import step + +__depends__ = {'20221114_02_DKKjn-drop-generic-role-tables'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS group_roles( + group_id TEXT NOT NULL, + role_id TEXT NOT NULL, + PRIMARY KEY(group_id, role_id), + FOREIGN KEY(group_id) REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS group_roles"), + step( + """ + CREATE INDEX IF NOT EXISTS idx_tbl_group_roles_cols_group_id + ON group_roles(group_id) + """, + "DROP INDEX IF EXISTS idx_tbl_group_roles_cols_group_id") +] diff --git a/gn_auth/migrations/auth/20221114_04_tLUzB-initialise-basic-roles.py b/gn_auth/migrations/auth/20221114_04_tLUzB-initialise-basic-roles.py new file mode 100644 index 0000000..386f481 --- /dev/null +++ b/gn_auth/migrations/auth/20221114_04_tLUzB-initialise-basic-roles.py @@ -0,0 +1,56 @@ +""" +Initialise basic roles +""" + +from yoyo import step + +__depends__ = {'20221114_03_PtWjc-create-group-roles-table'} + +steps = [ + step( + """ + INSERT INTO roles(role_id, role_name, user_editable) VALUES + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', 'group-leader', '0'), + ('522e4d40-aefc-4a64-b7e0-768b8be517ee', 'resource-owner', '0') + """, + "DELETE FROM roles"), + step( + """ + INSERT INTO role_privileges(role_id, privilege_id) + VALUES + -- group-management + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '4842e2aa-38b9-4349-805e-0a99a9cf8bff'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '3ebfe79c-d159-4629-8b38-772cf4bc2261'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '52576370-b3c7-4e6a-9f7e-90e9dbe24d8f'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '13ec2a94-4f1a-442d-aad2-936ad6dd5c57'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'ae4add8c-789a-4d11-a6e9-a306470d83d9'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'f1bd3f42-567e-4965-9643-6d1a52ddee64'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'd4afe2b3-4ca0-4edd-b37d-966535b5e5bd'), + + -- resource-management + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'aa25b32a-bff2-418d-b0a2-e26b4a8f089b'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '7f261757-3211-4f28-a43f-a09b800b164d'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '2f980855-959b-4339-b80e-25d1ec286e21'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'd2a070fd-e031-42fb-ba41-d60cf19e5d6d'), + ('522e4d40-aefc-4a64-b7e0-768b8be517ee', + 'aa25b32a-bff2-418d-b0a2-e26b4a8f089b'), + ('522e4d40-aefc-4a64-b7e0-768b8be517ee', + '7f261757-3211-4f28-a43f-a09b800b164d'), + ('522e4d40-aefc-4a64-b7e0-768b8be517ee', + '2f980855-959b-4339-b80e-25d1ec286e21'), + ('522e4d40-aefc-4a64-b7e0-768b8be517ee', + 'd2a070fd-e031-42fb-ba41-d60cf19e5d6d') + """, + "DELETE FROM role_privileges") +] diff --git a/gn_auth/migrations/auth/20221114_05_hQun6-create-user-roles-table.py b/gn_auth/migrations/auth/20221114_05_hQun6-create-user-roles-table.py new file mode 100644 index 0000000..e0de751 --- /dev/null +++ b/gn_auth/migrations/auth/20221114_05_hQun6-create-user-roles-table.py @@ -0,0 +1,29 @@ +""" +Create 'user_roles' table. +""" + +from yoyo import step + +__depends__ = {'20221114_04_tLUzB-initialise-basic-roles'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS user_roles( + user_id TEXT NOT NULL, + role_id TEXT NOT NULL, + PRIMARY KEY(user_id, role_id), + FOREIGN KEY(user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS user_roles"), + step( + """ + CREATE INDEX IF NOT EXISTS idx_tbl_user_roles_cols_user_id + ON user_roles(user_id) + """, + "DROP INDEX IF EXISTS idx_tbl_user_roles_cols_user_id") +] diff --git a/gn_auth/migrations/auth/20221116_01_nKUmX-add-privileges-to-group-leader-role.py b/gn_auth/migrations/auth/20221116_01_nKUmX-add-privileges-to-group-leader-role.py new file mode 100644 index 0000000..2e4ae28 --- /dev/null +++ b/gn_auth/migrations/auth/20221116_01_nKUmX-add-privileges-to-group-leader-role.py @@ -0,0 +1,35 @@ +""" +Add privileges to 'group-leader' role. +""" + +from yoyo import step + +__depends__ = {'20221114_05_hQun6-create-user-roles-table'} + +steps = [ + step( + """ + INSERT INTO role_privileges(role_id, privilege_id) + VALUES + -- role management + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '221660b1-df05-4be1-b639-f010269dbda9'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '7bcca363-cba9-4169-9e31-26bdc6179b28'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '5103cc68-96f8-4ebb-83a4-a31692402c9b'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '1c59eff5-9336-4ed2-a166-8f70d4cb012e') + """, + """ + DELETE FROM role_privileges + WHERE + role_id='a0e67630-d502-4b9f-b23f-6805d0f30e30' + AND privilege_id IN ( + '221660b1-df05-4be1-b639-f010269dbda9', + '7bcca363-cba9-4169-9e31-26bdc6179b28', + '5103cc68-96f8-4ebb-83a4-a31692402c9b', + '1c59eff5-9336-4ed2-a166-8f70d4cb012e' + ) + """) +] diff --git a/gn_auth/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py b/gn_auth/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py new file mode 100644 index 0000000..a4d7806 --- /dev/null +++ b/gn_auth/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py @@ -0,0 +1,52 @@ +""" +Modify 'group_roles': add 'group_role_id' + +At this point, there is no data in the `group_roles` table and therefore, it +should be safe to simply recreate it. +""" + +from yoyo import step + +__depends__ = {'20221116_01_nKUmX-add-privileges-to-group-leader-role'} + +steps = [ + step( + "DROP INDEX IF EXISTS idx_tbl_group_roles_cols_group_id", + """ + CREATE INDEX IF NOT EXISTS idx_tbl_group_roles_cols_group_id + ON group_roles(group_id) + """), + step( + "DROP TABLE IF EXISTS group_roles", + """ + CREATE TABLE IF NOT EXISTS group_roles( + group_id TEXT NOT NULL, + role_id TEXT NOT NULL, + PRIMARY KEY(group_id, role_id), + FOREIGN KEY(group_id) REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """), + step( + """ + CREATE TABLE IF NOT EXISTS group_roles( + group_role_id TEXT PRIMARY KEY, + group_id TEXT NOT NULL, + role_id TEXT NOT NULL, + UNIQUE (group_id, role_id), + FOREIGN KEY(group_id) REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS group_roles"), + step( + """ + CREATE INDEX IF NOT EXISTS idx_tbl_group_roles_cols_group_id + ON group_roles(group_id) + """, + "DROP INDEX IF EXISTS idx_tbl_group_roles_cols_group_id") +] diff --git a/gn_auth/migrations/auth/20221117_02_fmuZh-create-group-users-table.py b/gn_auth/migrations/auth/20221117_02_fmuZh-create-group-users-table.py new file mode 100644 index 0000000..92885ef --- /dev/null +++ b/gn_auth/migrations/auth/20221117_02_fmuZh-create-group-users-table.py @@ -0,0 +1,25 @@ +""" +Create 'group_users' table. +""" + +from yoyo import step + +__depends__ = {'20221117_01_RDlfx-modify-group-roles-add-group-role-id'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS group_users( + group_id TEXT NOT NULL, + user_id TEXT NOT NULL UNIQUE, -- user can only be in one group + PRIMARY KEY(group_id, user_id) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS group_users"), + step( + """ + CREATE INDEX IF NOT EXISTS tbl_group_users_cols_group_id + ON group_users(group_id) + """, + "DROP INDEX IF EXISTS tbl_group_users_cols_group_id") +] diff --git a/gn_auth/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py b/gn_auth/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py new file mode 100644 index 0000000..9aa3667 --- /dev/null +++ b/gn_auth/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py @@ -0,0 +1,39 @@ +""" +Create 'group_user_roles_on_resources' table +""" + +from yoyo import step + +__depends__ = {'20221117_02_fmuZh-create-group-users-table'} + +steps = [ + step( + """ + CREATE TABLE group_user_roles_on_resources ( + group_id TEXT NOT NULL, + user_id TEXT NOT NULL, + role_id TEXT NOT NULL, + resource_id TEXT NOT NULL, + PRIMARY KEY (group_id, user_id, role_id, resource_id), + FOREIGN KEY (user_id) + REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (group_id, role_id) + REFERENCES group_roles(group_id, role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS group_user_roles_on_resources"), + step( + """ + CREATE INDEX IF NOT EXISTS + idx_tbl_group_user_roles_on_resources_group_user_resource + ON group_user_roles_on_resources(group_id, user_id, resource_id) + """, + """ + DROP INDEX IF EXISTS + idx_tbl_group_user_roles_on_resources_group_user_resource""") +] diff --git a/gn_auth/migrations/auth/20221208_01_sSdHz-add-public-column-to-resources-table.py b/gn_auth/migrations/auth/20221208_01_sSdHz-add-public-column-to-resources-table.py new file mode 100644 index 0000000..2238069 --- /dev/null +++ b/gn_auth/migrations/auth/20221208_01_sSdHz-add-public-column-to-resources-table.py @@ -0,0 +1,16 @@ +""" +Add 'public' column to 'resources' table +""" + +from yoyo import step + +__depends__ = {'20221206_01_BbeF9-create-group-user-roles-on-resources-table'} + +steps = [ + step( + """ + ALTER TABLE resources ADD COLUMN + public INTEGER NOT NULL DEFAULT 0 CHECK (public=0 or public=1) + """, + "ALTER TABLE resources DROP COLUMN public") +] diff --git a/gn_auth/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py b/gn_auth/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py new file mode 100644 index 0000000..475be01 --- /dev/null +++ b/gn_auth/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py @@ -0,0 +1,25 @@ +""" +create oauth2_clients table +""" + +from yoyo import step + +__depends__ = {'20221208_01_sSdHz-add-public-column-to-resources-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS oauth2_clients( + client_id TEXT NOT NULL, + client_secret TEXT NOT NULL, + client_id_issued_at INTEGER NOT NULL, + client_secret_expires_at INTEGER NOT NULL, + client_metadata TEXT, + user_id TEXT NOT NULL, + PRIMARY KEY(client_id), + FOREIGN KEY(user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS oauth2_clients") +] diff --git a/gn_auth/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py b/gn_auth/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py new file mode 100644 index 0000000..778282b --- /dev/null +++ b/gn_auth/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py @@ -0,0 +1,31 @@ +""" +create oauth2_tokens table +""" + +from yoyo import step + +__depends__ = {'20221219_01_CI3tN-create-oauth2-clients-table'} + +steps = [ + step( + """ + CREATE TABLE oauth2_tokens( + token_id TEXT NOT NULL, + client_id TEXT NOT NULL, + token_type TEXT NOT NULL, + access_token TEXT UNIQUE NOT NULL, + refresh_token TEXT, + scope TEXT, + revoked INTEGER CHECK (revoked = 0 or revoked = 1), + issued_at INTEGER NOT NULL, + expires_in INTEGER NOT NULL, + user_id TEXT NOT NULL, + PRIMARY KEY(token_id), + FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS oauth2_tokens") +] diff --git a/gn_auth/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py b/gn_auth/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py new file mode 100644 index 0000000..1683f87 --- /dev/null +++ b/gn_auth/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py @@ -0,0 +1,31 @@ +""" +create authorisation_code table +""" + +from yoyo import step + +__depends__ = {'20221219_02_buSEU-create-oauth2-tokens-table'} + +steps = [ + step( + """ + CREATE TABLE authorisation_code ( + code_id TEXT NOT NULL, + code TEXT UNIQUE NOT NULL, + client_id NOT NULL, + redirect_uri TEXT, + scope TEXT, + nonce TEXT, + auth_time INTEGER NOT NULL, + code_challenge TEXT, + code_challenge_method TEXT, + user_id TEXT NOT NULL, + PRIMARY KEY (code_id), + FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS authorisation_code") +] diff --git a/gn_auth/migrations/auth/20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader.py b/gn_auth/migrations/auth/20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader.py new file mode 100644 index 0000000..7e7fda2 --- /dev/null +++ b/gn_auth/migrations/auth/20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader.py @@ -0,0 +1,40 @@ +""" +remove 'create-group' privilege from group-leader. +""" + +from yoyo import step + +__depends__ = {'20221219_03_PcTrb-create-authorisation-code-table'} + +steps = [ + step( + """ + DELETE FROM role_privileges + WHERE role_id='a0e67630-d502-4b9f-b23f-6805d0f30e30' + AND privilege_id='4842e2aa-38b9-4349-805e-0a99a9cf8bff' + """, + """ + INSERT INTO role_privileges VALUES + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', + '4842e2aa-38b9-4349-805e-0a99a9cf8bff') + """), + step( + """ + INSERT INTO roles(role_id, role_name, user_editable) VALUES + ('ade7e6b0-ba9c-4b51-87d0-2af7fe39a347', 'group-creator', '0') + """, + """ + DELETE FROM roles WHERE role_id='ade7e6b0-ba9c-4b51-87d0-2af7fe39a347' + """), + step( + """ + INSERT INTO role_privileges VALUES + ('ade7e6b0-ba9c-4b51-87d0-2af7fe39a347', + '4842e2aa-38b9-4349-805e-0a99a9cf8bff') + """, + """ + DELETE FROM role_privileges + WHERE role_id='ade7e6b0-ba9c-4b51-87d0-2af7fe39a347' + AND privilege_id='4842e2aa-38b9-4349-805e-0a99a9cf8bff' + """) +] diff --git a/gn_auth/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py b/gn_auth/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py new file mode 100644 index 0000000..1ef5ab0 --- /dev/null +++ b/gn_auth/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py @@ -0,0 +1,111 @@ +""" +rework privileges schema +""" +import contextlib + +from yoyo import step + +__depends__ = {'20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader'} + +privileges = ( # format: (original_id, original_name, new_id, category) + ("13ec2a94-4f1a-442d-aad2-936ad6dd5c57", "delete-group", + "system:group:delete-group", "group-management"), + ("1c59eff5-9336-4ed2-a166-8f70d4cb012e", "delete-role", + "group:role:delete-role", "role-management"), + ("1fe61370-cae9-4983-bd6c-ce61050c510f", "delete-any-user", + "system:user:delete-user", "user-management"), + ("221660b1-df05-4be1-b639-f010269dbda9", "create-role", + "group:role:create-role", "role-management"), + ("2f980855-959b-4339-b80e-25d1ec286e21", "edit-resource", + "group:resource:edit-resource", "resource-management"), + ("3ebfe79c-d159-4629-8b38-772cf4bc2261", "view-group", + "system:group:view-group", "group-management"), + ("4842e2aa-38b9-4349-805e-0a99a9cf8bff", "create-group", + "system:group:create-group", "group-management"), + ("5103cc68-96f8-4ebb-83a4-a31692402c9b", "assign-role", + "group:user:assign-role", "role-management"), + ("519db546-d44e-4fdc-9e4e-25aa67548ab3", "masquerade", + "system:user:masquerade", "system-admin"), + ("52576370-b3c7-4e6a-9f7e-90e9dbe24d8f", "edit-group", + "system:group:edit-group", "group-management"), + ("7bcca363-cba9-4169-9e31-26bdc6179b28", "edit-role", + "group:role:edit-role", "role-management"), + ("7f261757-3211-4f28-a43f-a09b800b164d", "view-resource", + "group:resource:view-resource", "resource-management"), + ("80f11285-5079-4ec0-907c-06509f88a364", "assign-group-leader", + "system:user:assign-group-leader", "group-management"), + ("aa25b32a-bff2-418d-b0a2-e26b4a8f089b", "create-resource", + "group:resource:create-resource", "resource-management"), + ("ae4add8c-789a-4d11-a6e9-a306470d83d9", "add-group-member", + "group:user:add-group-member", "group-management"), + ("d2a070fd-e031-42fb-ba41-d60cf19e5d6d", "delete-resource", + "group:resource:delete-resource", "resource-management"), + ("d4afe2b3-4ca0-4edd-b37d-966535b5e5bd", "transfer-group-leadership", + "system:group:transfer-group-leader", "group-management"), + ("e7252301-6ee0-43ba-93ef-73b607cf06f6", "reset-any-password", + "system:user:reset-password", "user-management"), + ("f1bd3f42-567e-4965-9643-6d1a52ddee64", "remove-group-member", + "group:user:remove-group-member", "group-management")) + +def rework_privileges_table(cursor): + "rework the schema" + cursor.executemany( + ("UPDATE privileges SET privilege_id=:id " + "WHERE privilege_id=:old_id"), + ({"id": row[2], "old_id": row[0]} for row in privileges)) + cursor.execute("ALTER TABLE privileges DROP COLUMN privilege_category") + cursor.execute("ALTER TABLE privileges DROP COLUMN privilege_name") + +def restore_privileges_table(cursor): + "restore the schema" + cursor.execute(( + "CREATE TABLE privileges_restore (" + " privilege_id TEXT PRIMARY KEY," + " privilege_name TEXT NOT NULL," + " privilege_category TEXT NOT NULL DEFAULT 'common'," + " privilege_description TEXT" + ")")) + id_dict = {row[2]: {"id": row[0], "name": row[1], "cat": row[3]} + for row in privileges} + cursor.execute( + "SELECT privilege_id, privilege_description FROM privileges") + params = ({**id_dict[row[0]], "desc": row[1]} for row in cursor.fetchall()) + cursor.executemany( + "INSERT INTO privileges_restore VALUES (:id, :name, :cat, :desc)", + params) + cursor.execute("DROP TABLE privileges") + cursor.execute("ALTER TABLE privileges_restore RENAME TO privileges") + +def update_privilege_ids_in_role_privileges(cursor): + """Update the ids to new form.""" + cursor.executemany( + ("UPDATE role_privileges SET privilege_id=:new_id " + "WHERE privilege_id=:old_id"), + ({"new_id": row[2], "old_id": row[0]} for row in privileges)) + +def restore_privilege_ids_in_role_privileges(cursor): + """Restore original ids""" + cursor.executemany( + ("UPDATE role_privileges SET privilege_id=:old_id " + "WHERE privilege_id=:new_id"), + ({"new_id": row[2], "old_id": row[0]} for row in privileges)) + +def change_schema(conn): + """Change the privileges schema and IDs""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("PRAGMA foreign_keys=OFF") + rework_privileges_table(cursor) + update_privilege_ids_in_role_privileges(cursor) + cursor.execute("PRAGMA foreign_keys=ON") + +def restore_schema(conn): + """Change the privileges schema and IDs""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("PRAGMA foreign_keys=OFF") + restore_privilege_ids_in_role_privileges(cursor) + restore_privileges_table(cursor) + cursor.execute("PRAGMA foreign_keys=ON") + +steps = [ + step(change_schema, restore_schema) +] diff --git a/gn_auth/migrations/auth/20230207_01_r0bkZ-create-group-join-requests-table.py b/gn_auth/migrations/auth/20230207_01_r0bkZ-create-group-join-requests-table.py new file mode 100644 index 0000000..ceae5ea --- /dev/null +++ b/gn_auth/migrations/auth/20230207_01_r0bkZ-create-group-join-requests-table.py @@ -0,0 +1,29 @@ +""" +Create group_requests table +""" + +from yoyo import step + +__depends__ = {'20230116_01_KwuJ3-rework-privileges-schema'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS group_join_requests( + request_id TEXT NOT NULL, + group_id TEXT NOT NULL, + requester_id TEXT NOT NULL, + timestamp REAL NOT NULL, + status TEXT NOT NULL DEFAULT 'PENDING', + message TEXT, + PRIMARY KEY(request_id, group_id), + FOREIGN KEY(group_id) REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE CASCADE, + FOREIGN KEY (requester_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE CASCADE, + UNIQUE(group_id, requester_id), + CHECK (status IN ('PENDING', 'ACCEPTED', 'REJECTED')) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS group_join_requests") +] diff --git a/gn_auth/migrations/auth/20230210_01_8xMa1-system-admin-privileges-for-data-distribution.py b/gn_auth/migrations/auth/20230210_01_8xMa1-system-admin-privileges-for-data-distribution.py new file mode 100644 index 0000000..8b406a6 --- /dev/null +++ b/gn_auth/migrations/auth/20230210_01_8xMa1-system-admin-privileges-for-data-distribution.py @@ -0,0 +1,22 @@ +""" +System admin privileges for data distribution + +These privileges are focussed on allowing the system administrator to link the +datasets and traits in the main database to specific groups in the auth system. +""" + +from yoyo import step + +__depends__ = {'20230207_01_r0bkZ-create-group-join-requests-table'} + +steps = [ + step( + """ + INSERT INTO privileges VALUES + ('system:data:link-to-group', 'Link a dataset or trait to a group.') + """, + """ + DELETE FROM privileges WHERE privilege_id IN + ('system:data:link-to-group') + """) +] diff --git a/gn_auth/migrations/auth/20230210_02_lDK14-create-system-admin-role.py b/gn_auth/migrations/auth/20230210_02_lDK14-create-system-admin-role.py new file mode 100644 index 0000000..9b3fc2b --- /dev/null +++ b/gn_auth/migrations/auth/20230210_02_lDK14-create-system-admin-role.py @@ -0,0 +1,38 @@ +""" +Create system-admin role +""" +import uuid +from contextlib import closing + +from yoyo import step + +__depends__ = {'20230210_01_8xMa1-system-admin-privileges-for-data-distribution'} + +def create_sys_admin_role(conn): + with closing(conn.cursor()) as cursor: + role_id = uuid.uuid4() + cursor.execute( + "INSERT INTO roles VALUES (?, 'system-administrator', '0')", + (str(role_id),)) + + cursor.executemany( + "INSERT INTO role_privileges VALUES (:role_id, :privilege_id)", + ({"role_id": f"{role_id}", "privilege_id": priv} + for priv in ( + "system:data:link-to-group", + "system:group:create-group", + "system:group:delete-group", + "system:group:edit-group", + "system:group:transfer-group-leader", + "system:group:view-group", + "system:user:assign-group-leader", + "system:user:delete-user", + "system:user:masquerade", + "system:user:reset-password"))) + +def drop_sys_admin_role(conn): + pass + +steps = [ + step(create_sys_admin_role, drop_sys_admin_role) +] diff --git a/gn_auth/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py b/gn_auth/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py new file mode 100644 index 0000000..84bbd49 --- /dev/null +++ b/gn_auth/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py @@ -0,0 +1,26 @@ +""" +Add system:user:list privilege +""" +import contextlib + +from yoyo import step + +__depends__ = {'20230210_02_lDK14-create-system-admin-role'} + +def insert_users_list_priv(conn): + """Create a new 'system:user:list' privilege.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "INSERT INTO privileges(privilege_id, privilege_description) " + "VALUES('system:user:list', 'List users in the system') " + "ON CONFLICT (privilege_id) DO NOTHING") + +def delete_users_list_priv(conn): + """Delete the new 'system:user:list' privilege.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "DELETE FROM privileges WHERE privilege_id='system:user:list'") + +steps = [ + step(insert_users_list_priv, delete_users_list_priv) +] diff --git a/gn_auth/migrations/auth/20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles.py b/gn_auth/migrations/auth/20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles.py new file mode 100644 index 0000000..3caad55 --- /dev/null +++ b/gn_auth/migrations/auth/20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles.py @@ -0,0 +1,42 @@ +""" +Add system:user:list privilege to system-administrator and group-leader roles. +""" +import uuid +import contextlib + +from yoyo import step + +__depends__ = {'20230306_01_pRfxl-add-system-user-list-privilege'} + +def role_ids(cursor): + """Get role ids from names""" + cursor.execute( + "SELECT * FROM roles WHERE role_name IN " + "('system-administrator', 'group-leader')") + return (uuid.UUID(row[0]) for row in cursor.fetchall()) + +def add_privilege_to_roles(conn): + """ + Add 'system:user:list' privilege to 'system-administrator' and + 'group-leader' roles.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.executemany( + "INSERT INTO role_privileges(role_id,privilege_id) " + "VALUES(?, ?)", + tuple((str(role_id), "system:user:list") + for role_id in role_ids(cursor))) + +def del_privilege_from_roles(conn): + """ + Delete 'system:user:list' privilege to 'system-administrator' and + 'group-leader' roles. + """ + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "DELETE FROM role_privileges WHERE " + "role_id IN (?, ?) AND privilege_id='system:user:list'", + tuple(str(role_id) for role_id in role_ids(cursor))) + +steps = [ + step(add_privilege_to_roles, del_privilege_from_roles) +] diff --git a/gn_auth/migrations/auth/20230322_01_0dDZR-create-linked-phenotype-data-table.py b/gn_auth/migrations/auth/20230322_01_0dDZR-create-linked-phenotype-data-table.py new file mode 100644 index 0000000..647325f --- /dev/null +++ b/gn_auth/migrations/auth/20230322_01_0dDZR-create-linked-phenotype-data-table.py @@ -0,0 +1,30 @@ +""" +Create linked-phenotype-data table +""" + +from yoyo import step + +__depends__ = {'20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS linked_phenotype_data + -- Link the data in MariaDB to user groups in the auth system + ( + data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system + group_id TEXT NOT NULL, -- The user group the data is linked to + SpeciesId TEXT NOT NULL, -- The species in MariaDB + InbredSetId TEXT NOT NULL, -- The traits group in MariaDB + PublishFreezeId TEXT NOT NULL, -- The dataset Id in MariaDB + dataset_name TEXT, -- dataset Name in MariaDB + dataset_fullname, -- dataset FullName in MariaDB + dataset_shortname, -- dataset ShortName in MariaDB + PublishXRefId TEXT NOT NULL, -- The trait's ID in MariaDB + FOREIGN KEY (group_id) + REFERENCES groups(group_id) ON UPDATE CASCADE ON DELETE RESTRICT + UNIQUE (SpeciesId, InbredSetId, PublishFreezeId, PublishXRefId) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS linked_phenotype_data") +] diff --git a/gn_auth/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py b/gn_auth/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py new file mode 100644 index 0000000..7c9e986 --- /dev/null +++ b/gn_auth/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py @@ -0,0 +1,29 @@ +""" +Create phenotype_resources table +""" + +from yoyo import step + +__depends__ = {'20230322_01_0dDZR-create-linked-phenotype-data-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS phenotype_resources + -- Link phenotype data to specific resources + ( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, -- A resource can have multiple data items + data_link_id TEXT NOT NULL, + PRIMARY KEY(group_id, resource_id, data_link_id), + UNIQUE (data_link_id), -- ensure data is linked to only one resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) + REFERENCES linked_phenotype_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS phenotype_resources") +] diff --git a/gn_auth/migrations/auth/20230404_01_VKxXg-create-linked-genotype-data-table.py b/gn_auth/migrations/auth/20230404_01_VKxXg-create-linked-genotype-data-table.py new file mode 100644 index 0000000..02e8718 --- /dev/null +++ b/gn_auth/migrations/auth/20230404_01_VKxXg-create-linked-genotype-data-table.py @@ -0,0 +1,29 @@ +""" +Create linked genotype data table +""" + +from yoyo import step + +__depends__ = {'20230322_02_Ll854-create-phenotype-resources-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS linked_genotype_data + -- Link genotype data in MariaDB to user groups in auth system + ( + data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system + group_id TEXT NOT NULL, -- The user group the data is linked to + SpeciesId TEXT NOT NULL, -- The species in MariaDB + InbredSetId TEXT NOT NULL, -- The traits group in MariaDB + GenoFreezeId TEXT NOT NULL, -- The dataset Id in MariaDB + dataset_name TEXT, -- dataset Name in MariaDB + dataset_fullname, -- dataset FullName in MariaDB + dataset_shortname, -- dataset ShortName in MariaDB + FOREIGN KEY (group_id) + REFERENCES groups(group_id) ON UPDATE CASCADE ON DELETE RESTRICT + UNIQUE (SpeciesId, InbredSetId, GenoFreezeId) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS linked_genotype_data") +] diff --git a/gn_auth/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py b/gn_auth/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py new file mode 100644 index 0000000..1a865e0 --- /dev/null +++ b/gn_auth/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py @@ -0,0 +1,29 @@ +""" +Create genotype resources table +""" + +from yoyo import step + +__depends__ = {'20230404_01_VKxXg-create-linked-genotype-data-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS genotype_resources + -- Link genotype data to specific resource + ( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, -- A resource can have multiple items + data_link_id TEXT NOT NULL, + PRIMARY KEY (group_id, resource_id, data_link_id), + UNIQUE (data_link_id) -- ensure data is linked to single resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) + REFERENCES linked_genotype_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS genotype_resources") +] diff --git a/gn_auth/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py b/gn_auth/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py new file mode 100644 index 0000000..db9a6bf --- /dev/null +++ b/gn_auth/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py @@ -0,0 +1,30 @@ +""" +Create linked mrna data table +""" + +from yoyo import step + +__depends__ = {'20230404_02_la33P-create-genotype-resources-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS linked_mrna_data + -- Link mRNA Assay data in MariaDB to user groups in auth system + ( + data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system + group_id TEXT NOT NULL, -- The user group the data is linked to + SpeciesId TEXT NOT NULL, -- The species in MariaDB + InbredSetId TEXT NOT NULL, -- The traits group in MariaDB + ProbeFreezeId TEXT NOT NULL, -- The study ID in MariaDB + ProbeSetFreezeId TEXT NOT NULL, -- The dataset Id in MariaDB + dataset_name TEXT, -- dataset Name in MariaDB + dataset_fullname, -- dataset FullName in MariaDB + dataset_shortname, -- dataset ShortName in MariaDB + FOREIGN KEY (group_id) + REFERENCES groups(group_id) ON UPDATE CASCADE ON DELETE RESTRICT + UNIQUE (SpeciesId, InbredSetId, ProbeFreezeId, ProbeSetFreezeId) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS linked_mrna_data") +] diff --git a/gn_auth/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py b/gn_auth/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py new file mode 100644 index 0000000..2ad1056 --- /dev/null +++ b/gn_auth/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py @@ -0,0 +1,28 @@ +""" +Create mRNA resources table +""" + +from yoyo import step + +__depends__ = {'20230410_01_8mwaf-create-linked-mrna-data-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS mrna_resources + -- Link mRNA data to specific resource + ( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, -- A resource can have multiple items + data_link_id TEXT NOT NULL, + PRIMARY KEY (resource_id, data_link_id), + UNIQUE (data_link_id) -- ensure data is linked to single resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) REFERENCES linked_mrna_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS mrna_resources") +] diff --git a/gn_auth/migrations/auth/20230907_01_pjnxz-refactor-add-resource-ownership-table.py b/gn_auth/migrations/auth/20230907_01_pjnxz-refactor-add-resource-ownership-table.py new file mode 100644 index 0000000..37fcfe7 --- /dev/null +++ b/gn_auth/migrations/auth/20230907_01_pjnxz-refactor-add-resource-ownership-table.py @@ -0,0 +1,32 @@ +""" +refactor: add resource_ownership table +""" + +from yoyo import step + +__depends__ = {'20230410_02_WZqSf-create-mrna-resources-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS resource_ownership( + -- This table links resources to groups, where relevant + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, + PRIMARY KEY(group_id, resource_id), + FOREIGN KEY(group_id) + REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(resource_id) + REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS resource_ownership"), + step(# Copy over data + """ + INSERT INTO resource_ownership + SELECT group_id, resource_id FROM resources + """ + ) +] diff --git a/gn_auth/migrations/auth/20230907_02_Enicg-refactor-add-system-and-group-resource-categories.py b/gn_auth/migrations/auth/20230907_02_Enicg-refactor-add-system-and-group-resource-categories.py new file mode 100644 index 0000000..c4397c9 --- /dev/null +++ b/gn_auth/migrations/auth/20230907_02_Enicg-refactor-add-system-and-group-resource-categories.py @@ -0,0 +1,29 @@ +""" +refactor: add 'system' and 'group' resource categories +""" + +from yoyo import step + +__depends__ = {'20230907_01_pjnxz-refactor-add-resource-ownership-table'} + +steps = [ + step( + """ + INSERT INTO resource_categories VALUES + ('aa3d787f-af6a-44fa-9b0b-c82d40e54ad2', + 'system', + 'The overall system.', + '{"default-access-level": "public-read"}'), + ('1e0f70ee-add5-4358-8c6c-43de77fa4cce', + 'group', + 'A group resource.', + '{}') + """, + """ + DELETE FROM resource_categories + WHERE resource_category_id IN ( + 'aa3d787f-af6a-44fa-9b0b-c82d40e54ad2', + '1e0f70ee-add5-4358-8c6c-43de77fa4cce' + ) + """) +] diff --git a/gn_auth/migrations/auth/20230907_03_BwAmf-refactor-drop-group-id-from-resources-table.py b/gn_auth/migrations/auth/20230907_03_BwAmf-refactor-drop-group-id-from-resources-table.py new file mode 100644 index 0000000..0f491c2 --- /dev/null +++ b/gn_auth/migrations/auth/20230907_03_BwAmf-refactor-drop-group-id-from-resources-table.py @@ -0,0 +1,325 @@ +""" +refactor: drop 'group_id' from 'resources' table. +""" + +import sqlite3 +from yoyo import step + +__depends__ = {'20230907_02_Enicg-refactor-add-system-and-group-resource-categories'} + +def drop_group_id_from_group_user_roles_on_resources(conn): + conn.execute( + "ALTER TABLE group_user_roles_on_resources " + "RENAME TO group_user_roles_on_resources_bkp") + conn.execute( + """ + CREATE TABLE group_user_roles_on_resources ( + group_id TEXT NOT NULL, + user_id TEXT NOT NULL, + role_id TEXT NOT NULL, + resource_id TEXT NOT NULL, + PRIMARY KEY (group_id, user_id, role_id, resource_id), + FOREIGN KEY (user_id) + REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (group_id, role_id) + REFERENCES group_roles(group_id, role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (resource_id) + REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + conn.execute( + "INSERT INTO group_user_roles_on_resources " + "(group_id, user_id, role_id, resource_id)" + "SELECT group_id, user_id, role_id, resource_id " + "FROM group_user_roles_on_resources_bkp") + conn.execute("DROP TABLE IF EXISTS group_user_roles_on_resources_bkp") + +def drop_group_id_from_mrna_resources(conn): + conn.execute("ALTER TABLE mrna_resources RENAME TO mrna_resources_bkp") + conn.execute( + """ + CREATE TABLE IF NOT EXISTS mrna_resources + -- Link mRNA data to specific resource + ( + resource_id TEXT NOT NULL, -- A resource can have multiple items + data_link_id TEXT NOT NULL, + PRIMARY KEY (resource_id, data_link_id), + UNIQUE (data_link_id) -- ensure data is linked to single resource + FOREIGN KEY (resource_id) + REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) REFERENCES linked_mrna_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + conn.execute( + "INSERT INTO mrna_resources " + "SELECT resource_id, data_link_id FROM mrna_resources_bkp") + conn.execute("DROP TABLE IF EXISTS mrna_resources_bkp") + +def drop_group_id_from_genotype_resources(conn): + conn.execute( + "ALTER TABLE genotype_resources RENAME TO genotype_resources_bkp") + conn.execute( + """ + CREATE TABLE IF NOT EXISTS genotype_resources + -- Link genotype data to specific resource + ( + resource_id TEXT NOT NULL, -- A resource can have multiple items + data_link_id TEXT NOT NULL, + PRIMARY KEY (resource_id, data_link_id), + UNIQUE (data_link_id) -- ensure data is linked to single resource + FOREIGN KEY (resource_id) + REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) + REFERENCES linked_genotype_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + conn.execute( + "INSERT INTO genotype_resources " + "SELECT resource_id, data_link_id FROM genotype_resources_bkp") + conn.execute("DROP TABLE IF EXISTS genotype_resources_bkp") + +def drop_group_id_from_phenotype_resources(conn): + conn.execute( + "ALTER TABLE phenotype_resources RENAME TO phenotype_resources_bkp") + conn.execute( + """ + CREATE TABLE IF NOT EXISTS phenotype_resources + -- Link phenotype data to specific resources + ( + resource_id TEXT NOT NULL, -- A resource can have multiple data items + data_link_id TEXT NOT NULL, + PRIMARY KEY(resource_id, data_link_id), + UNIQUE (data_link_id), -- ensure data is linked to only one resource + FOREIGN KEY (resource_id) + REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) + REFERENCES linked_phenotype_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + conn.execute( + "INSERT INTO phenotype_resources " + "SELECT resource_id, data_link_id FROM phenotype_resources_bkp") + conn.execute("DROP TABLE IF EXISTS phenotype_resources_bkp") + +def drop_group_id_from_resources_table(conn): + conn.row_factory = sqlite3.Row + conn.execute("PRAGMA foreign_keys = OFF") + conn.execute( + """ + CREATE TABLE IF NOT EXISTS 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), + PRIMARY KEY(resource_id), + FOREIGN KEY(resource_category_id) + REFERENCES resource_categories(resource_category_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + conn.execute( + "INSERT INTO resources_new " + "SELECT resource_id, resource_name, resource_category_id, public " + "FROM resources") + conn.execute("DROP TABLE IF EXISTS resources") + conn.execute("ALTER TABLE resources_new RENAME TO resources") + + drop_group_id_from_mrna_resources(conn) + drop_group_id_from_genotype_resources(conn) + drop_group_id_from_phenotype_resources(conn) + drop_group_id_from_group_user_roles_on_resources(conn) + + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + +def restore_group_id_from_group_user_roles_on_resources(conn): + conn.execute( + "ALTER TABLE group_user_roles_on_resources " + "RENAME TO group_user_roles_on_resources_bkp") + conn.execute( + """ + CREATE TABLE group_user_roles_on_resources ( + group_id TEXT NOT NULL, + user_id TEXT NOT NULL, + role_id TEXT NOT NULL, + resource_id TEXT NOT NULL, + PRIMARY KEY (group_id, user_id, role_id, resource_id), + FOREIGN KEY (user_id) + REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (group_id, role_id) + REFERENCES group_roles(group_id, role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + conn.execute( + "INSERT INTO group_user_roles_on_resources " + "(group_id, user_id, role_id, resource_id)" + "SELECT group_id, user_id, role_id, resource_id " + "FROM group_user_roles_on_resources_bkp") + conn.execute("DROP TABLE IF EXISTS group_user_roles_on_resources_bkp") + +def restore_group_id_from_mrna_resources(conn, resource_group_map): + conn.execute("ALTER TABLE mrna_resources RENAME TO mrna_resources_bkp") + conn.execute( + """ + CREATE TABLE IF NOT EXISTS mrna_resources + -- Link mRNA data to specific resource + ( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, -- A resource can have multiple items + data_link_id TEXT NOT NULL, + PRIMARY KEY (resource_id, data_link_id), + UNIQUE (data_link_id) -- ensure data is linked to single resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) REFERENCES linked_mrna_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + + cursor = conn.cursor() + cursor.execute("SELECT * FROM mrna_resources_bkp") + resources = tuple({ + "group_id": resource_group_map[row["resource_id"]], + **dict(row) + } for row in cursor.fetchall()) + cursor.executemany( + "INSERT INTO mrna_resources(group_id, resource_id, data_link_id) " + "VALUES(:group_id, :resource_id, :data_link_id)", + resources) + conn.execute("DROP TABLE IF EXISTS mrna_resources_bkp") + +def restore_group_id_from_genotype_resources(conn, resource_group_map): + conn.execute( + "ALTER TABLE genotype_resources RENAME TO genotype_resources_bkp") + conn.execute( + """ + CREATE TABLE IF NOT EXISTS genotype_resources + -- Link genotype data to specific resource + ( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, -- A resource can have multiple items + data_link_id TEXT NOT NULL, + PRIMARY KEY (group_id, resource_id, data_link_id), + UNIQUE (data_link_id) -- ensure data is linked to single resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) + REFERENCES linked_genotype_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + + cursor = conn.cursor() + cursor.execute("SELECT * FROM genotype_resources_bkp") + resources = tuple({ + "group_id": resource_group_map[row["resource_id"]], + **dict(row) + } for row in cursor.fetchall()) + cursor.executemany( + "INSERT INTO genotype_resources(group_id, resource_id, data_link_id) " + "VALUES(:group_id, :resource_id, :data_link_id)", + resources) + conn.execute("DROP TABLE IF EXISTS genotype_resources_bkp") + +def restore_group_id_from_phenotype_resources(conn, resource_group_map): + conn.execute( + "ALTER TABLE phenotype_resources RENAME TO phenotype_resources_bkp") + conn.execute( + """ + CREATE TABLE IF NOT EXISTS phenotype_resources + -- Link phenotype data to specific resources + ( + group_id TEXT NOT NULL, + resource_id TEXT NOT NULL, -- A resource can have multiple data items + data_link_id TEXT NOT NULL, + PRIMARY KEY(group_id, resource_id, data_link_id), + UNIQUE (data_link_id), -- ensure data is linked to only one resource + FOREIGN KEY (group_id, resource_id) + REFERENCES resources(group_id, resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (data_link_id) + REFERENCES linked_phenotype_data(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + + cursor = conn.cursor() + cursor.execute("SELECT * FROM phenotype_resources_bkp") + resources = tuple({ + "group_id": resource_group_map[row["resource_id"]], + **dict(row) + } for row in cursor.fetchall()) + cursor.executemany( + "INSERT INTO phenotype_resources(group_id, resource_id, data_link_id) " + "VALUES(:group_id, :resource_id, :data_link_id)", + resources) + conn.execute("DROP TABLE IF EXISTS phenotype_resources_bkp") + +def restore_group_id_to_resources_table(conn): + conn.row_factory = sqlite3.Row + conn.execute("PRAGMA foreign_keys = OFF") + + cursor = conn.cursor() + cursor.execute("ALTER TABLE resources RENAME TO resources_bkp") + cursor.execute( + "SELECT r.*, ro.group_id FROM resources_bkp AS r " + "INNER JOIN resource_ownership AS ro " + "ON r.resource_id=ro.resource_id") + group_resources = tuple(dict(row) for row in cursor.fetchall()) + cursor.execute( + """ + CREATE TABLE IF NOT EXISTS resources( + group_id TEXT NOT NULL, + 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(group_id, resource_id), + FOREIGN KEY(group_id) + REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(resource_category_id) + REFERENCES resource_categories(resource_category_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + cursor.executemany( + "INSERT INTO resources" + "(group_id, resource_id, resource_name, resource_category_id)" + "VALUES " + "(:group_id, :resource_id, :resource_name, :resource_category_id)", + group_resources) + cursor.execute("DROP TABLE IF EXISTS resources_bkp") + + resource_group_map = { + res["resource_id"]: res["group_id"] + for res in group_resources + } + restore_group_id_from_group_user_roles_on_resources(conn) + restore_group_id_from_mrna_resources(conn, resource_group_map) + restore_group_id_from_genotype_resources(conn, resource_group_map) + restore_group_id_from_phenotype_resources(conn, resource_group_map) + + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + +steps = [ + step( + drop_group_id_from_resources_table, restore_group_id_to_resources_table) +] diff --git a/gn_auth/migrations/auth/20230907_04_3LnrG-refactor-create-group-resources-table.py b/gn_auth/migrations/auth/20230907_04_3LnrG-refactor-create-group-resources-table.py new file mode 100644 index 0000000..a26834a --- /dev/null +++ b/gn_auth/migrations/auth/20230907_04_3LnrG-refactor-create-group-resources-table.py @@ -0,0 +1,58 @@ +""" +refactor: create 'group_resources' table. +""" + +import uuid +import random +import string + +import sqlite3 +from yoyo import step + +__depends__ = {'20230907_03_BwAmf-refactor-drop-group-id-from-resources-table'} + +def randstr(length: int = 5): + """Generate random string.""" + return "".join(random.choices( + string.ascii_letters + string.digits, k=length)) + +def create_and_link_resources_for_existing_groups(conn): + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute("SELECT group_id, group_name FROM groups") + resources = tuple({ + "group_id": row["group_id"], + "resource_id": str(uuid.uuid4()), + "resource_name": f"{randstr(10)}: {row['group_name']}", + "resource_category_id": "1e0f70ee-add5-4358-8c6c-43de77fa4cce" + } for row in cursor.fetchall()) + cursor.executemany( + "INSERT INTO " + "resources(resource_id, resource_name, resource_category_id) " + "VALUES (:resource_id, :resource_name, :resource_category_id)", + resources) + cursor.executemany( + "INSERT INTO group_resources(resource_id, group_id) " + "VALUES (:resource_id, :group_id)", + resources) + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS group_resources( + -- Links groups to the resources of type 'group' that control access to + -- each group + resource_id TEXT NOT NULL, + group_id TEXT NOT NULL, + PRIMARY KEY(resource_id, group_id), + FOREIGN KEY (resource_id) + REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (group_id) + REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS group_resources"), + step(create_and_link_resources_for_existing_groups) +] diff --git a/gn_auth/migrations/auth/20230912_01_BxrhE-add-system-resource.py b/gn_auth/migrations/auth/20230912_01_BxrhE-add-system-resource.py new file mode 100644 index 0000000..66c6461 --- /dev/null +++ b/gn_auth/migrations/auth/20230912_01_BxrhE-add-system-resource.py @@ -0,0 +1,39 @@ +""" +Add 'system' resource. +""" + +import uuid + +import sqlite3 +from yoyo import step + +__depends__ = {'20230907_04_3LnrG-refactor-create-group-resources-table'} + +def add_system_resource(conn): + """Add a system resource.""" + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute( + "SELECT resource_category_id FROM resource_categories " + "WHERE resource_category_key='system'") + category_id = cursor.fetchone()["resource_category_id"] + cursor.execute( + "INSERT INTO " + "resources(resource_id, resource_name, resource_category_id, public) " + "VALUES(?, ?, ?, ?)", + (str(uuid.uuid4()), "GeneNetwork System", category_id, "1")) + +def delete_system_resource(conn): + """Add a system resource.""" + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute( + "SELECT resource_category_id FROM resource_categories " + "WHERE resource_category_key='system'") + category_id = cursor.fetchone()["resource_category_id"] + cursor.execute("DELETE FROM resources WHERE resource_category_id = ?", + (category_id,)) + +steps = [ + step(add_system_resource, delete_system_resource) +] diff --git a/gn_auth/migrations/auth/20230912_02_hFmSn-drop-group-id-and-fix-foreign-key-references-on-group-user-roles-on-resources-table.py b/gn_auth/migrations/auth/20230912_02_hFmSn-drop-group-id-and-fix-foreign-key-references-on-group-user-roles-on-resources-table.py new file mode 100644 index 0000000..1b3f0b1 --- /dev/null +++ b/gn_auth/migrations/auth/20230912_02_hFmSn-drop-group-id-and-fix-foreign-key-references-on-group-user-roles-on-resources-table.py @@ -0,0 +1,227 @@ +""" +Drop 'group_id' and fix foreign key references on 'group_user_roles_on_resources' table +""" + +import sqlite3 +from yoyo import step + +__depends__ = {'20230912_01_BxrhE-add-system-resource'} + +def drop_group_id(conn): + """Drop `group_id` from `group_user_roles_on_resources` table.""" + conn.execute("PRAGMA foreign_keys = OFF") + + conn.execute( + """ + ALTER TABLE group_user_roles_on_resources + RENAME TO group_user_roles_on_resources_bkp + """) + conn.execute( + """ + CREATE TABLE IF NOT EXISTS group_user_roles_on_resources ( + user_id TEXT NOT NULL, + role_id TEXT NOT NULL, + resource_id TEXT NOT NULL, + PRIMARY KEY (user_id, role_id, resource_id), + FOREIGN KEY (user_id) + REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (role_id) + REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (resource_id) + REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + conn.execute( + "INSERT INTO group_user_roles_on_resources " + "SELECT user_id, role_id, resource_id " + "FROM group_user_roles_on_resources_bkp") + conn.execute("DROP TABLE IF EXISTS group_user_roles_on_resources_bkp") + + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + +def restore_group_id(conn): + """Restore `group_id` to `group_user_roles_on_resources` table.""" + conn.execute("PRAGMA foreign_keys = OFF") + + conn.execute( + """ + ALTER TABLE group_user_roles_on_resources + RENAME TO group_user_roles_on_resources_bkp + """) + conn.execute( + """ + CREATE TABLE IF NOT EXISTS group_user_roles_on_resources ( + group_id TEXT NOT NULL, + user_id TEXT NOT NULL, + role_id TEXT NOT NULL, + resource_id TEXT NOT NULL, + PRIMARY KEY (group_id, user_id, role_id, resource_id), + FOREIGN KEY (user_id) + REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (group_id, role_id) + REFERENCES group_roles(group_id, role_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (resource_id) + REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + + cursor = conn.cursor() + cursor.execute( + """ + INSERT INTO group_user_roles_on_resources + SELECT + ro.group_id, gurorb.user_id, gurorb.role_id, gurorb.resource_id + FROM resource_ownership AS ro + INNER JOIN group_user_roles_on_resources_bkp AS gurorb + ON ro.resource_id=gurorb.resource_id + """) + + conn.execute("DROP TABLE IF EXISTS group_user_roles_on_resources_bkp") + + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + +def link_sys_admin_user_roles(conn): + """Link system-admins to the system resource.""" + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute( + "SELECT ur.* FROM user_roles AS ur " + "INNER JOIN roles AS r ON ur.role_id=r.role_id " + "WHERE r.role_name='system-administrator'") + admins = cursor.fetchall() + cursor.execute( + "SELECT r.resource_id FROM resources AS r " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE rc.resource_category_key='system'") + system_resource_id = cursor.fetchone()["resource_id"] + cursor.executemany( + "INSERT INTO " + "group_user_roles_on_resources(user_id, role_id, resource_id) " + "VALUES (:user_id, :role_id, :resource_id)", + tuple({**admin, "resource_id": system_resource_id} for admin in admins)) + +def restore_sys_admin_user_roles(conn): + """Restore fields into older `user_roles` table.""" + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute( + "SELECT guror.user_id, guror.role_id " + "FROM group_user_roles_on_resources AS guror " + "INNER JOIN resources AS r " + "ON guror.resource_id=r.resource_id " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE rc.resource_category_key='system'") + user_roles = tuple(cursor.fetchall()) + cursor.executemany( + "INSERT INTO user_roles(user_id, role_id) " + "VALUES (:user_id, :role_id)", + user_roles) + +def link_group_leader_user_roles(conn): + """Link group leaders to their resources.""" + conn.execute( + """ + INSERT INTO group_user_roles_on_resources(user_id, role_id, resource_id) + SELECT gu.user_id, r.role_id, gr.resource_id + FROM group_resources AS gr INNER JOIN group_users AS gu + ON gr.group_id=gu.group_id INNER JOIN user_roles AS ur + ON gu.user_id=ur.user_id INNER JOIN roles AS r + ON ur.role_id=r.role_id + WHERE r.role_name='group-leader' + """) + +def restore_group_leader_user_roles(conn): + """Restore group admins to older `user_roles` table.""" + conn.execute( + """ + INSERT INTO user_roles(user_id, role_id) + SELECT guror.user_id, guror.role_id + FROM group_user_roles_on_resources AS guror + INNER JOIN resources AS r ON guror.resource_id=r.resource_id + INNER JOIN resource_categories AS rc + ON r.resource_category_id=rc.resource_category_id + WHERE rc.resource_category_key='group' + """) + +def link_group_creator_user_roles(conn): + """Link group-creators to system.""" + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute( + "SELECT ur.* FROM user_roles AS ur " + "INNER JOIN roles AS r ON ur.role_id=r.role_id " + "WHERE r.role_name='group_creator'") + creators = cursor.fetchall() + cursor.execute( + "SELECT r.resource_id FROM resources AS r " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE rc.resource_category_key='system'") + sys_res_id = cursor.fetchone()["resource_id"] + cursor.executemany( + "INSERT INTO " + "group_user_roles_on_resources(user_id, role_id, resource_id) " + "VALUES (:user_id, :role_id, :resource_id)", + tuple({**creator, "resource_id": sys_res_id} for creator in creators)) + +def restore_group_creator_user_roles(conn): + "Restore group-creator user roles." + conn.execute( + """ + INSERT INTO user_roles + SELECT guror.user_id, guror.role_id + FROM group_user_roles_on_resources AS guror + INNER JOIN roles AS r ON guror.role_id=r.role_id + WHERE r.role_name='group-creator'""") + +def rename_table(conn): + "rename `group_user_roles_on_resources`, drop `user_roles`." + conn.execute("PRAGMA foreign_keys = OFF") + + conn.execute("DROP TABLE IF EXISTS user_roles") + conn.execute( + "ALTER TABLE group_user_roles_on_resources RENAME TO user_roles") + + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + +def restore_tables(conn): + "rename to `group_user_roles_on_resources`, recreate original `user_roles`." + conn.execute("PRAGMA foreign_keys = OFF") + + conn.execute( + "ALTER TABLE user_roles RENAME TO group_user_roles_on_resources") + conn.execute( + """ + CREATE TABLE user_roles( + user_id TEXT NOT NULL, + role_id TEXT NOT NULL, + PRIMARY KEY(user_id, role_id), + FOREIGN KEY(user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + +steps = [ + step(drop_group_id, restore_group_id), + step(link_sys_admin_user_roles, restore_sys_admin_user_roles), + step(link_group_leader_user_roles, restore_group_leader_user_roles), + step(link_group_creator_user_roles, restore_group_creator_user_roles), + step(rename_table, restore_tables) +] + diff --git a/gn_auth/migrations/auth/20230925_01_TWJuR-add-new-public-view-role.py b/gn_auth/migrations/auth/20230925_01_TWJuR-add-new-public-view-role.py new file mode 100644 index 0000000..1172034 --- /dev/null +++ b/gn_auth/migrations/auth/20230925_01_TWJuR-add-new-public-view-role.py @@ -0,0 +1,61 @@ +""" +Add new "public-view" role +""" + +import sqlite3 + +from yoyo import step + +__depends__ = {'20230912_02_hFmSn-drop-group-id-and-fix-foreign-key-references-on-group-user-roles-on-resources-table'} + +def grant_to_all_users_public_view_role(conn): + """Grant the `public-view` role to all existing users.""" + conn.row_factory = sqlite3.Row + conn.execute("PRAGMA foreign_keys = ON") + cursor = conn.cursor() + cursor.execute("SELECT user_id FROM users") + user_ids = tuple(row["user_id"] for row in cursor.fetchall()) + + cursor.execute("SELECT resource_id FROM resources WHERE public=1") + resource_ids = tuple(row["resource_id"] for row in cursor.fetchall()) + + params = tuple({ + "user_id": user_id, + "resource_id": resource_id, + "role_id": "fd88bfed-d869-4969-87f2-67c4e8446ecb" + } for user_id in user_ids for resource_id in resource_ids) + cursor.executemany( + "INSERT INTO user_roles(user_id, role_id, resource_id) " + "VALUES (:user_id, :role_id, :resource_id) ", + params) + +def revoke_from_all_users_public_view_role(conn): + """Revoke the `public-view` role from all existing users.""" + conn.execute("PRAGMA foreign_keys = ON") + conn.execute( + "DELETE FROM user_roles " + "WHERE role_id='fd88bfed-d869-4969-87f2-67c4e8446ecb'") + +steps = [ + step( + """ + INSERT INTO roles(role_id, role_name, user_editable) + VALUES('fd88bfed-d869-4969-87f2-67c4e8446ecb', 'public-view', 0) + """, + """ + DELETE FROM roles WHERE role_id='fd88bfed-d869-4969-87f2-67c4e8446ecb' + """), + step( + """ + INSERT INTO role_privileges(role_id, privilege_id) + VALUES( + 'fd88bfed-d869-4969-87f2-67c4e8446ecb', + 'group:resource:view-resource') + """, + """ + DELETE FROM role_privileges + WHERE role_id='fd88bfed-d869-4969-87f2-67c4e8446ecb' + """), + step(grant_to_all_users_public_view_role, + revoke_from_all_users_public_view_role) +] diff --git a/gn_auth/migrations/auth/20231002_01_tzxTf-link-inbredsets-to-auth-system.py b/gn_auth/migrations/auth/20231002_01_tzxTf-link-inbredsets-to-auth-system.py new file mode 100644 index 0000000..402e9a5 --- /dev/null +++ b/gn_auth/migrations/auth/20231002_01_tzxTf-link-inbredsets-to-auth-system.py @@ -0,0 +1,84 @@ +""" +link InbredSets to auth system +""" + +from yoyo import step + +__depends__ = {'20230925_01_TWJuR-add-new-public-view-role', '__init__'} + +steps = [ + step( + """ + INSERT INTO resource_categories + ( + resource_category_id, + resource_category_key, + resource_category_description, + resource_meta + ) + VALUES + ( + 'b3654600-4ab0-4745-8292-5849b34173a7', + 'inbredset-group', + 'A resource that controls access to a particular InbredSet group', + '{"default-access-level":"public-read"}' + ) + """, + """ + DELETE FROM resource_categories WHERE + resource_category_id = 'b3654600-4ab0-4745-8292-5849b34173a7' + """ + ), + step( + """ + CREATE TABLE IF NOT EXISTS linked_inbredset_groups + -- Link InbredSet groups in MariaDB to auth system + ( + data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system + SpeciesId TEXT NOT NULL, -- Species ID in MariaDB + InbredSetId TEXT NOT NULL, -- The InbredSet ID in MariaDB + InbredSetName TEXT NOT NULL, -- The InbredSet group's name in MariaDB + InbredSetFullName TEXT NOT NULL, -- The InbredSet group's full name in MariaDB + UNIQUE(SpeciesId, InbredSetId) + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS linked_inbredset_groups"), + step( + """ + CREATE TABLE IF NOT EXISTS inbredset_group_resources + -- Link the InbredSet data to a specific resource + ( + resource_id TEXT NOT NULL, -- Linked resource: one-to-one + data_link_id TEXT NOT NULL, + PRIMARY KEY(resource_id, data_link_id), + UNIQUE(resource_id), -- resource is linked to only one InbredSet + UNIQUE(data_link_id), -- InbredSet is linked to only one resource + FOREIGN KEY(resource_id) + REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(data_link_id) + REFERENCES linked_inbredset_groups(data_link_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS inbredset_group_resources"), + step( + """ + INSERT INTO privileges(privilege_id, privilege_description) VALUES + ('system:inbredset:create-case-attribute', 'Create a new case attribute for an InbredSet group.'), + ('system:inbredset:delete-case-attribute', 'Delete an existing case-attribute from an InbredSet group'), + ('system:inbredset:edit-case-attribute', 'Edit the values of case-attributes of an InbredSet group'), + ('system:inbredset:view-case-attribute', 'View the case-attributes of an InbredSet group'), + ('system:inbredset:apply-case-attribute-edit', 'Apply an edit to case-attributes performed by another user for an InbredSet group'), + ('system:inbredset:reject-case-attribute-edit', 'Reject an edit to case-attributes performed by another user for an InbredSet group') + """, + """ + DELETE FROM privileges WHERE privilege_id IN ( + 'system:inbredset:create-case-attribute', + 'system:inbredset:delete-case-attribute', + 'system:inbredset:edit-case-attribute', + 'system:inbredset:view-case-attribute', + 'system:inbredset:apply-case-attribute-edit', + 'system:inbredset:reject-case-attribute-edit') + """) +] diff --git a/gn_auth/migrations/auth/20231011_01_CS8NZ-create-new-inbredset-group-owner-role.py b/gn_auth/migrations/auth/20231011_01_CS8NZ-create-new-inbredset-group-owner-role.py new file mode 100644 index 0000000..a4238ed --- /dev/null +++ b/gn_auth/migrations/auth/20231011_01_CS8NZ-create-new-inbredset-group-owner-role.py @@ -0,0 +1,40 @@ +""" +Create new 'inbredset-group-owner' role +""" + +from yoyo import step + +__depends__ = {'20231002_01_tzxTf-link-inbredsets-to-auth-system'} + +steps = [ + step( + """ + INSERT INTO roles(role_id, role_name, user_editable) + VALUES('bde1c08b-b067-4d56-8353-462fc5928c32', 'inbredset-group-owner', 0) + """, + """ + DELETE FROM roles WHERE role_id='bde1c08b-b067-4d56-8353-462fc5928c32' + """), + step( + """ + INSERT INTO role_privileges(role_id, privilege_id) + VALUES + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:apply-case-attribute-edit'), + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:create-case-attribute'), + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:delete-case-attribute'), + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:edit-case-attribute'), + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:reject-case-attribute-edit'), + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:view-case-attribute') + """, + """ + DELETE FROM role_privileges + WHERE (role_id, privilege_id) + IN + (('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:apply-case-attribute-edit'), + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:create-case-attribute'), + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:delete-case-attribute'), + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:edit-case-attribute'), + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:reject-case-attribute-edit'), + ('bde1c08b-b067-4d56-8353-462fc5928c32', 'system:inbredset:view-case-attribute')) + """) +] diff --git a/gn_auth/migrations/auth/20240506_01_798tW-create-jwt-refresh-tokens-table.py b/gn_auth/migrations/auth/20240506_01_798tW-create-jwt-refresh-tokens-table.py new file mode 100644 index 0000000..049ac6b --- /dev/null +++ b/gn_auth/migrations/auth/20240506_01_798tW-create-jwt-refresh-tokens-table.py @@ -0,0 +1,34 @@ +""" +Create jwt_refresh_tokens table +""" + +from yoyo import step + +__depends__ = {'20231011_01_CS8NZ-create-new-inbredset-group-owner-role'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS jwt_refresh_tokens + -- Store refresh tokens to verify refresh attempts + ( + token TEXT NOT NULL, + client_id TEXT NOT NULL, + user_id TEXT NOT NULL, + issued_with TEXT NOT NULL UNIQUE, -- JWT ID of JWT issued along with this refresh token + issued_at INTEGER NOT NULL, + expires INTEGER NOT NULL, + scope TEXT NOT NULL, + revoked INTEGER CHECK (revoked = 0 or revoked = 1), + parent_of TEXT UNIQUE, + PRIMARY KEY(token), + FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY (parent_of) REFERENCES jwt_refresh_tokens(token) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS jwt_refresh_tokens") +] diff --git a/gn_auth/migrations/auth/20240529_01_ALNWj-update-schema-for-user-verification.py b/gn_auth/migrations/auth/20240529_01_ALNWj-update-schema-for-user-verification.py new file mode 100644 index 0000000..0cab1c3 --- /dev/null +++ b/gn_auth/migrations/auth/20240529_01_ALNWj-update-schema-for-user-verification.py @@ -0,0 +1,64 @@ +""" +update schema for user-verification +""" + +from yoyo import step + +__depends__ = {'20240506_01_798tW-create-jwt-refresh-tokens-table'} + +def add_verification_cols_to_users_table(conn): + "add verification columns to users table"; + conn.execute("PRAGMA foreign_keys = OFF") + + conn.execute( + """ + CREATE TABLE users_new( + user_id TEXT PRIMARY KEY NOT NULL, + email TEXT UNIQUE NOT NULL, + name TEXT, + created INTEGER NOT NULL DEFAULT (unixepoch()), + verified INTEGER NOT NULL DEFAULT 0 CHECK (verified=0 or verified=1) + ) WITHOUT ROWID + """) + conn.execute( + """ + INSERT INTO users_new(user_id, email, name) + SELECT user_id, email, name FROM users + """) + # the original table `users` has dependents, so we cannot simply do a + # `ALTER TABLE … RENAME TO …` since according to + # https://sqlite.org/lang_altertable.html#alter_table_rename + # from versions 3.26.0 onward, the foreign key references are **ALWAYS** + # changed. In this case, we create the new table first, do data transfers, + # drop the original and rename the new table to the same name as the + # original. + conn.execute("DROP TABLE IF EXISTS users") + conn.execute("ALTER TABLE users_new RENAME TO users") + + + print("turning foreign keys should back on.") + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + +def drop_verification_cols_from_users_table(conn): + "Drop verification columns from users table" + conn.execute("ALTER TABLE users DROP COLUMN created") + conn.execute("ALTER TABLE users DROP COLUMN verified") + +steps = [ + step(add_verification_cols_to_users_table, + drop_verification_cols_from_users_table), + step( + """ + CREATE TABLE IF NOT EXISTS user_verification_codes( + user_id TEXT NOT NULL, + code TEXT NOT NULL, + generated INTEGER NOT NULL, + expires INTEGER NOT NULL, + PRIMARY KEY(user_id), + FOREIGN KEY(user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE CASCADE + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS verification_codes") +] diff --git a/gn_auth/migrations/auth/20240606_01_xQDwL-move-role-manipulation-privileges-from-group-to-resources.py b/gn_auth/migrations/auth/20240606_01_xQDwL-move-role-manipulation-privileges-from-group-to-resources.py new file mode 100644 index 0000000..a45fd30 --- /dev/null +++ b/gn_auth/migrations/auth/20240606_01_xQDwL-move-role-manipulation-privileges-from-group-to-resources.py @@ -0,0 +1,94 @@ +""" +Move role-manipulation privileges from group to resources +""" +import sqlite3 +from yoyo import step + +__depends__ = {'20240529_01_ALNWj-update-schema-for-user-verification'} + +def role_by_name(cursor, role_name): + """Fetch group-admin role""" + cursor.execute("SELECT * FROM roles WHERE role_name=?", + (role_name,)) + return dict(cursor.fetchone()) + + +def move_privileges_to_resources(conn): + """Move role-manipulation privileges from group to resource.""" + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute( + "DELETE FROM role_privileges WHERE privilege_id IN (" + " 'group:role:create-role'," + " 'group:role:delete-role'," + " 'group:role:edit-role'," + " 'group:user:assign-role'" + ")") + cursor.execute( + "DELETE FROM privileges WHERE privilege_id IN (" + " 'group:role:create-role'," + " 'group:role:delete-role'," + " 'group:role:edit-role'," + " 'group:user:assign-role'" + ")") + + resource_owner_role = role_by_name(cursor, "resource-owner") + privileges = ( + ("resource:role:create-role", + "Create a new role on a specific resource"), + ("resource:role:delete-role", + "Delete an existing role from a specific resource"), + ("resource:role:edit-role", + "Edit an existing role on a specific resource"), + ("resource:user:assign-role", + "Assign a user to a role on a specific resource")) + cursor.executemany( + ("INSERT INTO privileges(privilege_id, privilege_description) " + "VALUES (?, ?)"), + privileges) + cursor.executemany( + ("INSERT INTO role_privileges(role_id, privilege_id) " + "VALUES(?, ?)"), + tuple((resource_owner_role["role_id"], privilege[0]) + for privilege in privileges)) + cursor.close() + +def move_privileges_to_groups(conn): + """Move role-manipulation privileges from resource to group.""" + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute( + "DELETE FROM role_privileges WHERE privilege_id IN (" + " 'resource:role:create-role'," + " 'resource:role:delete-role'," + " 'resource:role:edit-role'," + " 'resource:user:assign-role'" + ")") + cursor.execute( + "DELETE FROM privileges WHERE privilege_id IN (" + " 'resource:role:create-role'," + " 'resource:role:delete-role'," + " 'resource:role:edit-role'," + " 'resource:user:assign-role'" + ")") + + group_leader_role = role_by_name(cursor, "group-leader") + privileges = ( + ("group:role:create-role", "Create a new role"), + ("group:role:delete-role", "Delete an existing role"), + ("group:role:edit-role", "edit/update an existing role"), + ("group:user:assign-role", "Assign a role to an existing user")) + cursor.executemany( + ("INSERT INTO privileges(privilege_id, privilege_description) " + "VALUES (?, ?)"), + privileges) + cursor.executemany( + ("INSERT INTO role_privileges(role_id, privilege_id) " + "VALUES(?, ?)"), + tuple((group_leader_role["role_id"], privilege[0]) + for privilege in privileges)) + cursor.close() + +steps = [ + step(move_privileges_to_resources, move_privileges_to_groups) +] diff --git a/gn_auth/migrations/auth/20240606_02_ubZri-create-resource-roles-table.py b/gn_auth/migrations/auth/20240606_02_ubZri-create-resource-roles-table.py new file mode 100644 index 0000000..0695c0e --- /dev/null +++ b/gn_auth/migrations/auth/20240606_02_ubZri-create-resource-roles-table.py @@ -0,0 +1,36 @@ +""" +Create 'resource_roles' table. +""" + +from yoyo import step + +__depends__ = {'20240606_01_xQDwL-move-role-manipulation-privileges-from-group-to-resources'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS resource_roles( + resource_id TEXT NOT NULL, + role_created_by TEXT NOT NULL, + role_id TEXT NOT NULL, + PRIMARY KEY (resource_id, role_created_by, role_id), + FOREIGN KEY(resource_id) REFERENCES resources(resource_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_created_by) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS resource_roles"), + step( + """ + CREATE INDEX IF NOT EXISTS + tbl_resource_roles_cols_resource_id_role_created_by + ON resource_roles(resource_id, role_created_by) + """, + """ + DROP INDEX IF EXISTS + tbl_resource_roles_cols_resource_id_role_created_by + """) +] diff --git a/gn_auth/migrations/auth/20240606_03_BY7Us-drop-group-roles-table.py b/gn_auth/migrations/auth/20240606_03_BY7Us-drop-group-roles-table.py new file mode 100644 index 0000000..45d689c --- /dev/null +++ b/gn_auth/migrations/auth/20240606_03_BY7Us-drop-group-roles-table.py @@ -0,0 +1,35 @@ +""" +Drop 'group_roles' table. +""" +import sqlite3 +from yoyo import step + +__depends__ = {'20240606_02_ubZri-create-resource-roles-table'} + +def restore_group_roles(conn): + """Restore the `group_roles` table.""" + conn.row_factory = sqlite3.Row + cursor = conn.cursor() + cursor.execute( + """ + CREATE TABLE group_roles( + group_role_id TEXT PRIMARY KEY, + group_id TEXT NOT NULL, + role_id TEXT NOT NULL, + UNIQUE (group_id, role_id), + FOREIGN KEY(group_id) REFERENCES groups(group_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(role_id) REFERENCES roles(role_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + cursor.execute( + """ + CREATE INDEX idx_tbl_group_roles_cols_group_id + ON group_roles(group_id) + """) + cursor.close() + +steps = [ + step("DROP TABLE IF EXISTS group_roles", restore_group_roles) +] diff --git a/gn_auth/migrations/auth/20240819_01_p2vXR-create-forgot-password-tokens-table.py b/gn_auth/migrations/auth/20240819_01_p2vXR-create-forgot-password-tokens-table.py new file mode 100644 index 0000000..44318bd --- /dev/null +++ b/gn_auth/migrations/auth/20240819_01_p2vXR-create-forgot-password-tokens-table.py @@ -0,0 +1,26 @@ +""" +Create forgot_password_tokens table + +This will be used to enable users to validate/verify their password change +requests. +""" + +from yoyo import step + +__depends__ = {'20240606_03_BY7Us-drop-group-roles-table'} + +steps = [ + step( + """ + CREATE TABLE IF NOT EXISTS forgot_password_tokens( + user_id TEXT NOT NULL, + token TEXT NOT NULL, + generated INTEGER NOT NULL, + expires INTEGER NOT NULL, + PRIMARY KEY(user_id), + FOREIGN KEY(user_id) REFERENCES users(user_id) + ON UPDATE CASCADE ON DELETE CASCADE + ) WITHOUT ROWID + """, + "DROP TABLE IF EXISTS forgot_password_tokens") +] diff --git a/gn_auth/migrations/auth/20240924_01_thbvh-hooks-for-edu-domains.py b/gn_auth/migrations/auth/20240924_01_thbvh-hooks-for-edu-domains.py new file mode 100644 index 0000000..5c6e81d --- /dev/null +++ b/gn_auth/migrations/auth/20240924_01_thbvh-hooks-for-edu-domains.py @@ -0,0 +1,24 @@ +""" +hooks_for_edu_domains +""" + +from yoyo import step + +__depends__ = {'20240819_01_p2vXR-create-forgot-password-tokens-table'} + +steps = [ + step( + """ + INSERT INTO roles(role_id, role_name, user_editable) VALUES + ('9bb203a2-7897-4fe3-ac4a-75e6a4f96f5d', 'hook-role-from-edu-domain', '0') + """, + "DELETE FROM roles WHERE role_name='hook-role-from-edu-domain'"), + step( + """ + INSERT INTO role_privileges(role_id, privilege_id) VALUES + ('9bb203a2-7897-4fe3-ac4a-75e6a4f96f5d', 'group:resource:view-resource'), + ('9bb203a2-7897-4fe3-ac4a-75e6a4f96f5d', 'group:resource:edit-resource') + """, + "DELETE FROM role_privileges WHERE role_id='9bb203a2-7897-4fe3-ac4a-75e6a4f96f5d'" + ) +] diff --git a/gn_auth/migrations/auth/20250328_01_72EFk-add-admin-ui-privilege-to-system-administrator-role.py b/gn_auth/migrations/auth/20250328_01_72EFk-add-admin-ui-privilege-to-system-administrator-role.py new file mode 100644 index 0000000..d22ad01 --- /dev/null +++ b/gn_auth/migrations/auth/20250328_01_72EFk-add-admin-ui-privilege-to-system-administrator-role.py @@ -0,0 +1,42 @@ +""" +add admin ui privilege to system-administrator role +""" +import contextlib + +from yoyo import step + +__depends__ = {'20240924_01_thbvh-hooks-for-edu-domains'} + +def get_system_admin_id(cursor): + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='system-administrator'") + return cursor.fetchone()[0] + +def add_admin_ui_privilege(conn): + with contextlib.closing(conn.cursor()) as cursor: + # Create admin-ui privilege + cursor.execute( + "INSERT INTO privileges (privilege_id, privilege_description) " + "VALUES(?, ?)", + ("system:user:admin-ui", "View UI elements that should only be visible to system administrators")) + + # Add UI privilege to system-administrator role + cursor.execute( + "INSERT INTO role_privileges (role_id, privilege_id) " + "VALUES(?, ?)", + (get_system_admin_id(cursor), "system:user:admin-ui") + ) + +def remove_admin_ui_privilege(conn): + with contextlib.closing(conn.cursor()) as cursor: + # Remove UI privilege from system-administrator role + cursor.execute( + "DELETE FROM role_privileges WHERE privilege_id='system:user:admin-ui'") + + # Remove UI privilege from privileges table + cursor.execute( + "DELETE FROM privileges WHERE privilege_id='system:user:admin-ui'") + +steps = [ + step(add_admin_ui_privilege, remove_admin_ui_privilege) +] diff --git a/gn_auth/migrations/auth/20250609_01_LB60X-add-batch-edit-privileges.py b/gn_auth/migrations/auth/20250609_01_LB60X-add-batch-edit-privileges.py new file mode 100644 index 0000000..73a4880 --- /dev/null +++ b/gn_auth/migrations/auth/20250609_01_LB60X-add-batch-edit-privileges.py @@ -0,0 +1,49 @@ +""" +Add Batch Edit privileges +""" + +import contextlib + +from yoyo import step + +__depends__ = {'20250328_01_72EFk-add-admin-ui-privilege-to-system-administrator-role'} + +def add_batch_edit_privilege_and_role(conn): + with contextlib.closing(conn.cursor()) as cursor: + # Create batch edit privilege + cursor.execute( + "INSERT INTO privileges (privilege_id, privilege_description) " + "VALUES(?, ?)", + ("system:data:batch-edit", "Batch Edit")) + + # Create batch editor role + cursor.execute( + "INSERT INTO roles (role_id, role_name, user_editable) " + "VALUES(?, ?, ?)", + ("0f391910-5225-476a-bb8d-9c0adc9d81cc", "Batch Editors", 0)) + + # Link role/privilege + cursor.execute( + "INSERT INTO role_privileges (role_id, privilege_id) " + "VALUES(?, ?)", + ("0f391910-5225-476a-bb8d-9c0adc9d81cc", "system:data:batch-edit") + ) + +def remove_batch_edit_privilege_and_role(conn): + with contextlib.closing(conn.cursor()) as cursor: + # Remove batch edit role/privilege link + cursor.execute( + "DELETE FROM role_privileges WHERE privilege_id='system:data:batch-edit'") + + # Remove Batch Editor role + cursor.execute( + "DELETE FROM roles WHERE role_id='0f391910-5225-476a-bb8d-9c0adc9d81cc'") + + # Remove Batch Edit privilege + cursor.execute( + "DELETE FROM privileges WHERE privilege_id='system:data:batch-edit'") + + +steps = [ + step(add_batch_edit_privilege_and_role, remove_batch_edit_privilege_and_role) +] diff --git a/gn_auth/migrations/auth/20250609_01_bj9Pl-add-new-group-data-link-to-group-privilege.py b/gn_auth/migrations/auth/20250609_01_bj9Pl-add-new-group-data-link-to-group-privilege.py new file mode 100644 index 0000000..3b9e928 --- /dev/null +++ b/gn_auth/migrations/auth/20250609_01_bj9Pl-add-new-group-data-link-to-group-privilege.py @@ -0,0 +1,19 @@ +""" +Add new 'group:data:link-to-group' privilege. +""" + +from yoyo import step + +__depends__ = {'20240924_01_thbvh-hooks-for-edu-domains'} + +steps = [ + step( + """ + INSERT INTO privileges(privilege_id, privilege_description) + VALUES( + 'group:data:link-to-group', + 'Allow linking data to only one specific group.' + ) + """, + "DELETE FROM privileges WHERE privilege_id='group:data:link-to-group'") +] diff --git a/gn_auth/migrations/auth/20250609_02_9UBPl-assign-group-data-link-to-group-privilege-to-group-leader.py b/gn_auth/migrations/auth/20250609_02_9UBPl-assign-group-data-link-to-group-privilege-to-group-leader.py new file mode 100644 index 0000000..5d9c306 --- /dev/null +++ b/gn_auth/migrations/auth/20250609_02_9UBPl-assign-group-data-link-to-group-privilege-to-group-leader.py @@ -0,0 +1,23 @@ +""" +Assign 'group:data:link-to-group' privilege to group leader. +""" + +from yoyo import step + +__depends__ = {'20250609_01_bj9Pl-add-new-group-data-link-to-group-privilege'} + +steps = [ + step( + """ + INSERT INTO role_privileges(role_id, privilege_id) + VALUES( + 'a0e67630-d502-4b9f-b23f-6805d0f30e30', + 'group:data:link-to-group' + ) + """, + """ + DELETE FROM role_privileges + WHERE role_id='a0e67630-d502-4b9f-b23f-6805d0f30e30' + AND privilege_id='group:data:link-to-group' + """) +] diff --git a/gn_auth/migrations/auth/20250703_01_aDVwP-add-role-management-privileges-to-group-leader-role.py b/gn_auth/migrations/auth/20250703_01_aDVwP-add-role-management-privileges-to-group-leader-role.py new file mode 100644 index 0000000..6335152 --- /dev/null +++ b/gn_auth/migrations/auth/20250703_01_aDVwP-add-role-management-privileges-to-group-leader-role.py @@ -0,0 +1,27 @@ +""" +Add role management privileges to group-leader role +""" + +from yoyo import step + +__depends__ = {'20250609_01_LB60X-add-batch-edit-privileges', '20250609_02_9UBPl-assign-group-data-link-to-group-privilege-to-group-leader'} + +steps = [ + step( + """ + INSERT INTO role_privileges(role_id, privilege_id) + VALUES + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', 'resource:role:create-role'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', 'resource:role:delete-role'), + ('a0e67630-d502-4b9f-b23f-6805d0f30e30', 'resource:role:edit-role') + """, + """ + DELETE FROM role_privileges + WHERE role_id='a0e67630-d502-4b9f-b23f-6805d0f30e30' + AND privilege_id IN ( + 'resource:role:create-role', + 'resource:role:delete-role', + 'resource:role:edit-role' + ) + """) +] diff --git a/gn_auth/migrations/auth/20250722_01_7Gro7-create-new-system-user-edit-privilege.py b/gn_auth/migrations/auth/20250722_01_7Gro7-create-new-system-user-edit-privilege.py new file mode 100644 index 0000000..f00ab11 --- /dev/null +++ b/gn_auth/migrations/auth/20250722_01_7Gro7-create-new-system-user-edit-privilege.py @@ -0,0 +1,18 @@ +""" +Create new 'system:user:edit' privilege. +""" + +from yoyo import step + +__depends__ = {'20250703_01_aDVwP-add-role-management-privileges-to-group-leader-role'} + +steps = [ + step( + """ + INSERT INTO privileges(privilege_id, privilege_description) + VALUES( + 'system:user:edit', + 'Allow general user-information edit.') + """, + "DELETE FROM privileges WHERE privilege_id='system:user:edit'") +] diff --git a/gn_auth/migrations/auth/20250722_02_M8TXv-add-system-user-edit-privilege-to-system-admin-role.py b/gn_auth/migrations/auth/20250722_02_M8TXv-add-system-user-edit-privilege-to-system-admin-role.py new file mode 100644 index 0000000..b956bef --- /dev/null +++ b/gn_auth/migrations/auth/20250722_02_M8TXv-add-system-user-edit-privilege-to-system-admin-role.py @@ -0,0 +1,36 @@ +""" +Add 'system:user:edit' privilege to 'system-admin' role. +""" +import contextlib + +from yoyo import step + +__depends__ = {'20250722_01_7Gro7-create-new-system-user-edit-privilege'} + + +def system_administrator_role_id(cursor): + """Fetch ID for role 'system-administrator'.""" + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='system-administrator'") + return cursor.fetchone()[0] + + +def add_system_user_edit_privilege(conn): + """Add the 'system:user:edit' to the 'system-administrator' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "INSERT INTO role_privileges(role_id, privilege_id) " + "VALUES(?, ?)", + (system_administrator_role_id(cursor), 'system:user:edit')) + + +def remove_system_user_edit_privilege(conn): + """Remove the 'system:user:edit' from the 'system-administrator' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "DELETE FROM role_privileges WHERE role_id=? AND privilege_id=?", + (system_administrator_role_id(cursor), 'system:user:edit')) + +steps = [ + step(add_system_user_edit_privilege, remove_system_user_edit_privilege) +] diff --git a/gn_auth/migrations/auth/20250729_01_CNn2p-create-initial-system-wide-resources-access-privileges.py b/gn_auth/migrations/auth/20250729_01_CNn2p-create-initial-system-wide-resources-access-privileges.py new file mode 100644 index 0000000..be0d022 --- /dev/null +++ b/gn_auth/migrations/auth/20250729_01_CNn2p-create-initial-system-wide-resources-access-privileges.py @@ -0,0 +1,31 @@ +""" +Create initial system-wide resources access privileges +""" + +from yoyo import step + +__depends__ = {'20250722_02_M8TXv-add-system-user-edit-privilege-to-system-admin-role'} + +steps = [ + step( + """ + INSERT INTO privileges(privilege_id, privilege_description) + VALUES + ("system:resource:view", + "View the wrapper resource object (not attached data). This is mostly for administration purposes."), + ("system:resource:edit", + "Edit/update the wrapper resource object (not attached data). This is mostly for administration purposes."), + ("system:resource:delete", + "Delete the wrapper resource object (not attached data). This is mostly for administration purposes."), + ("system:resource:reassign-group", + "Reassign the resource, and its data, to a different user group."), + ("system:resource:assign-owner", + "Assign ownership of any resource to any user.") + """, + """ + DELETE FROM privileges WHERE privilege_id IN + ("system:resource:view", "system:resource:edit", + "system:resource:delete", "system:resource:reassign-group", + "system:resource:assign-owner") + """) +] diff --git a/gn_auth/migrations/auth/20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins.py b/gn_auth/migrations/auth/20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins.py new file mode 100644 index 0000000..e79ab1c --- /dev/null +++ b/gn_auth/migrations/auth/20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins.py @@ -0,0 +1,53 @@ +""" +Assign initial system-wide resources-access privileges to sys-admins. +""" +import contextlib + +from yoyo import step + +def system_administrator_role_id(cursor): + """Fetch ID for role 'system-administrator'.""" + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='system-administrator'") + return cursor.fetchone()[0] + + +def assign_system_wide_resource_access_to_sysadmin(conn): + """ + Assign initial system-wide resources-access privileges to + `system-administrator` role. + """ + with contextlib.closing(conn.cursor()) as cursor: + sysadmin_role_id = system_administrator_role_id(cursor) + cursor.executemany( + "INSERT INTO role_privileges(role_id, privilege_id) " + "VALUES(?, ?)", + ((sysadmin_role_id, "system:resource:view"), + (sysadmin_role_id, "system:resource:edit"), + (sysadmin_role_id, "system:resource:delete"), + (sysadmin_role_id, "system:resource:reassign-group"), + (sysadmin_role_id, "system:resource:assign-owner"))) + + +def revoke_system_wide_resource_access_from_sysadmin(conn): + """ + Revoke initial system-wide resources-access privileges from + `system-administrator` role. + """ + with contextlib.closing(conn.cursor()) as cursor: + sysadmin_role_id = system_administrator_role_id(cursor) + cursor.executemany( + "DELETE FROM role_privileges " + "WHERE role_id=? AND privilege_id=?", + ((sysadmin_role_id, "system:resource:view"), + (sysadmin_role_id, "system:resource:edit"), + (sysadmin_role_id, "system:resource:delete"), + (sysadmin_role_id, "system:resource:reassign-group"), + (sysadmin_role_id, "system:resource:assign-owner"))) + +__depends__ = {'20250729_01_CNn2p-create-initial-system-wide-resources-access-privileges'} + +steps = [ + step(assign_system_wide_resource_access_to_sysadmin, + revoke_system_wide_resource_access_from_sysadmin) +] diff --git a/gn_auth/migrations/auth/20250729_03_oCvvq-grant-role-to-all-resources-to-sys-admin-users.py b/gn_auth/migrations/auth/20250729_03_oCvvq-grant-role-to-all-resources-to-sys-admin-users.py new file mode 100644 index 0000000..e3bdc8f --- /dev/null +++ b/gn_auth/migrations/auth/20250729_03_oCvvq-grant-role-to-all-resources-to-sys-admin-users.py @@ -0,0 +1,75 @@ +""" +Grant role to ALL resources to sys-admin users. +""" +import itertools +import contextlib + +from yoyo import step + +__depends__ = {'20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins'} + + +def system_administrator_role_id(cursor): + """Fetch ID for role 'system-administrator'.""" + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='system-administrator'") + return cursor.fetchone()[0] + + +def system_resource_id(cursor): + cursor.execute( + "SELECT resources.resource_id FROM resource_categories " + "INNER JOIN resources ON resource_categories.resource_category_id=resources.resource_category_id " + "WHERE resource_category_key = 'system'") + return cursor.fetchone()[0] + + +def fetch_ids_for_sysadmin_users(cursor): + """Fetch all sysadmin users' IDs.""" + cursor.execute( + "SELECT user_roles.user_id FROM roles INNER JOIN user_roles " + "ON roles.role_id=user_roles.role_id " + "WHERE role_name='system-administrator' AND resource_id=?", + (system_resource_id(cursor),)) + return tuple(row[0] for row in cursor.fetchall()) + + +def fetch_non_system_resources(cursor): + """Fetch IDs for all resources that are not of the 'system' category.""" + cursor.execute( + "SELECT resources.resource_id FROM resource_categories " + "INNER JOIN resources " + "ON resource_categories.resource_category_id=resources.resource_category_id " + "WHERE resource_category_key != 'system'") + return tuple(row[0] for row in cursor.fetchall()) + + +def assign_sysadmin_role_on_non_system_resources(conn): + """Assign sysadmins the sysadmin role on all non-system resources.""" + with contextlib.closing(conn.cursor()) as cursor: + sysadminroleid = system_administrator_role_id(cursor) + cursor.executemany( + "INSERT INTO user_roles(user_id, resource_id, role_id) " + "VALUES (?, ?, ?)", + tuple(item + (sysadminroleid,) + for item in itertools.product( + fetch_ids_for_sysadmin_users(cursor), + fetch_non_system_resources(cursor)))) + + +def revoke_sysadmin_role_on_non_system_resources(conn): + """Revoke sysadmins the sysadmin role on all non-system resources.""" + with contextlib.closing(conn.cursor()) as cursor: + sysadminroleid = system_administrator_role_id(cursor) + cursor.executemany( + "DELETE FROM user_roles " + "WHERE user_id=? AND resource_id=? AND role_id=?", + tuple(item + (sysadminroleid,) + for item in itertools.product( + fetch_ids_for_sysadmin_users(cursor), + fetch_non_system_resources(cursor)))) + +steps = [ + step(assign_sysadmin_role_on_non_system_resources, + revoke_sysadmin_role_on_non_system_resources) +] diff --git a/gn_auth/migrations/auth/20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members.py b/gn_auth/migrations/auth/20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members.py new file mode 100644 index 0000000..95a6fbb --- /dev/null +++ b/gn_auth/migrations/auth/20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members.py @@ -0,0 +1,70 @@ +""" +Add sysadmin privileges for acting on groups: mostly handling user management. +""" +import itertools +import contextlib + +from yoyo import step + +__depends__ = {'20250729_03_oCvvq-grant-role-to-all-resources-to-sys-admin-users'} + + +def system_administrator_role_id(cursor): + """Fetch ID for role 'system-administrator'.""" + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='system-administrator'") + return cursor.fetchone()[0] + + +def add_group_privileges_to_sysadmin_role(conn): + """Add group-management privileges to sysadmin role.""" + with contextlib.closing(conn.cursor()) as cursor: + sysadminroleid = system_administrator_role_id(cursor) + cursor.executemany( + "INSERT INTO role_privileges(role_id, privilege_id) VALUES (?, ?)", + tuple(itertools.product( + (sysadminroleid,), + ('system:group:add-group-member', + 'system:group:remove-group-member', + 'system:group:assign-group-leader', + 'system:group:revoke-group-leader')))) + + +def remove_group_privileges_to_sysadmin_role(conn): + """Remove group-management privileges from sysadmin role.""" + with contextlib.closing(conn.cursor()) as cursor: + sysadminroleid = system_administrator_role_id(cursor) + cursor.executemany( + "DELETE FROM role_privileges WHERE role_id=? AND privilege_id=?", + tuple(itertools.product( + (sysadminroleid,), + ('system:group:add-group-member', + 'system:group:remove-group-member', + 'system:group:assign-group-leader', + 'system:group:revoke-group-leader')))) + + +steps = [ + step( + """ + INSERT INTO privileges(privilege_id, privilege_description) + VALUES + ('system:group:add-group-member', + 'Make an existing user a member of a group.'), + ('system:group:remove-group-member', + 'Remove a member user from a group.'), + ('system:group:assign-group-leader', + 'Assign an existing group member the group-leader role'), + ('system:group:revoke-group-leader', + 'Revoke the group-leader role from a group member with the role.') + """, + """ + DELETE FROM privileges WHERE privilege_id IN + ('system:group:add-group-member', + 'system:group:remove-group-member', + 'system:group:assign-group-leader', + 'system:group:revoke-group-leader') + """), + step(add_group_privileges_to_sysadmin_role, + remove_group_privileges_to_sysadmin_role) +] diff --git a/gn_auth/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py b/gn_auth/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py new file mode 100644 index 0000000..63e807a --- /dev/null +++ b/gn_auth/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/gn_auth/migrations/auth/20260311_01_TfRlV-add-privilege-for-gn-docs-documentation-editing.py b/gn_auth/migrations/auth/20260311_01_TfRlV-add-privilege-for-gn-docs-documentation-editing.py new file mode 100644 index 0000000..d618f14 --- /dev/null +++ b/gn_auth/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/gn_auth/migrations/auth/20260311_02_v3EFQ-assign-systemwide-docs-editor-role-to-sysadmins.py b/gn_auth/migrations/auth/20260311_02_v3EFQ-assign-systemwide-docs-editor-role-to-sysadmins.py new file mode 100644 index 0000000..e79ef6a --- /dev/null +++ b/gn_auth/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/gn_auth/migrations/auth/20260311_03_vxBCX-restrict-access-to-resources-make-public-feature.py b/gn_auth/migrations/auth/20260311_03_vxBCX-restrict-access-to-resources-make-public-feature.py new file mode 100644 index 0000000..bdf8a56 --- /dev/null +++ b/gn_auth/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/gn_auth/migrations/auth/20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator.py b/gn_auth/migrations/auth/20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator.py new file mode 100644 index 0000000..22863ae --- /dev/null +++ b/gn_auth/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/gn_auth/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py b/gn_auth/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py new file mode 100644 index 0000000..702c418 --- /dev/null +++ b/gn_auth/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/gn_auth/migrations/auth/20260428_01_Tak6O-new-privilege-system-system-wide-data-view.py b/gn_auth/migrations/auth/20260428_01_Tak6O-new-privilege-system-system-wide-data-view.py new file mode 100644 index 0000000..2dddc56 --- /dev/null +++ b/gn_auth/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/gn_auth/migrations/auth/20260428_02_L6zIV-add-privileges-to-batch-editors-role.py b/gn_auth/migrations/auth/20260428_02_L6zIV-add-privileges-to-batch-editors-role.py new file mode 100644 index 0000000..537bf9b --- /dev/null +++ b/gn_auth/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/gn_auth/migrations/auth/__init__.py b/gn_auth/migrations/auth/__init__.py new file mode 100644 index 0000000..1358c9a --- /dev/null +++ b/gn_auth/migrations/auth/__init__.py @@ -0,0 +1 @@ +"Auth(entic|oris)ation package." diff --git a/gn_auth/scripts/__init__.py b/gn_auth/scripts/__init__.py new file mode 100644 index 0000000..5be56d8 --- /dev/null +++ b/gn_auth/scripts/__init__.py @@ -0,0 +1 @@ +"""These are command-line scripts to be run manually or in the background.""" diff --git a/gn_auth/scripts/assign_data_to_default_admin.py b/gn_auth/scripts/assign_data_to_default_admin.py new file mode 100644 index 0000000..69fc50c --- /dev/null +++ b/gn_auth/scripts/assign_data_to_default_admin.py @@ -0,0 +1,434 @@ +""" +Assign any existing data (that is not currently assigned to any group) to the +default sys-admin group for accessibility purposes. +""" +import sys +import json +import time +import random +import logging +from pathlib import Path +from uuid import UUID, uuid4 + +import click +from gn_libs import mysqldb as biodb +from MySQLdb.cursors import DictCursor + +import gn_auth.auth.db.sqlite3 as authdb +from gn_auth.auth.authentication.users import User +from gn_auth.auth.authorisation.roles.models import ( + revoke_user_role_by_name, assign_user_role_by_name) + +from gn_auth.auth.authorisation.resources.groups.models import ( + Group, save_group, add_resources_to_group) +from gn_auth.auth.authorisation.resources.common import assign_resource_owner_role +from gn_auth.auth.authorisation.resources.models import Resource, ResourceCategory + + +class DataNotFound(Exception): + """Raise if no admin user exists.""" + + +def sys_admins(conn: authdb.DbConnection) -> tuple[User, ...]: + """Retrieve all the existing system admins.""" + with authdb.cursor(conn) as cursor: + cursor.execute( + "SELECT u.* FROM users AS u " + "INNER JOIN user_roles AS ur ON u.user_id=ur.user_id " + "INNER JOIN roles AS r ON ur.role_id=r.role_id " + "WHERE r.role_name='system-administrator'") + return tuple(User.from_sqlite3_row(row) for row in cursor.fetchall()) + return tuple() + + +def choose_admin(enum_admins: dict[int, User]) -> int: + """Prompt and read user choice.""" + while True: + try: + print("\n===========================\n") + print("We found the following system administrators:") + for idx, admin in enum_admins.items(): + print(f"\t{idx}: {admin.name} ({admin.email})") + choice = input(f"Choose [1 .. {len(enum_admins)}]: ") + return int(choice) + except ValueError as _verr: + if choice.lower() == "quit": + print("Goodbye!") + sys.exit(0) + print(f"\nERROR: Invalid choice '{choice}'!") + + +def select_sys_admin(admins: tuple[User, ...]) -> User: + """Pick one admin out of list.""" + if len(admins) > 0: + if len(admins) == 1: + print(f"-> Found Admin: {admins[0].name} ({admins[0].email})") + return admins[0] + enum_admins = dict(enumerate(admins, start=1)) + chosen = enum_admins[choose_admin(enum_admins)] + print(f"-> Chosen Admin: {chosen.name} ({chosen.email})") + return chosen + raise DataNotFound( + "No administrator user found. Create an administrator user first.") + + +def admin_group(conn: authdb.DbConnection, admin: User) -> Group: + """Retrieve the admin's user group. If none exist, create one.""" + with authdb.cursor(conn) as cursor: + cursor.execute( + "SELECT g.* FROM users AS u " + "INNER JOIN group_users AS gu ON u.user_id=gu.user_id " + "INNER JOIN groups AS g on gu.group_id=g.group_id " + "WHERE u.user_id = ?", + (str(admin.user_id),)) + row = cursor.fetchone() + if row: + return Group(UUID(row["group_id"]), + row["group_name"], + json.loads(row["group_metadata"])) + new_group = save_group(cursor, "AutoAdminGroup", { + "group_description": ( + "Created by script for existing data visibility. " + "Existing data was migrated into this group and assigned " + "to publicly visible resources according to type.") + }) + + cursor.execute( + "SELECT * FROM resource_categories WHERE " + "resource_category_key='group'") + res_cat_id = cursor.fetchone()["resource_category_id"] + grp_res = { + "group_id": str(new_group.group_id), + "resource_id": str(uuid4()), + "resource_name": new_group.group_name, + "resource_category_id": res_cat_id, + "public": 0 + } + cursor.execute( + "INSERT INTO resources VALUES " + "(:resource_id, :resource_name, :resource_category_id, :public)", + grp_res) + cursor.execute( + "INSERT INTO group_resources(resource_id, group_id) " + "VALUES(:resource_id, :group_id)", + grp_res) + cursor.execute("INSERT INTO group_users VALUES (?, ?)", + (str(new_group.group_id), str(admin.user_id))) + revoke_user_role_by_name(cursor, admin, "group-creator") + assign_user_role_by_name( + cursor, admin, UUID(grp_res["resource_id"]), "group-leader") + return new_group + + +def __resource_category_by_key__( + cursor: authdb.DbCursor, category_key: str) -> ResourceCategory: + """Retrieve a resource category by its ID.""" + cursor.execute( + "SELECT * FROM resource_categories WHERE resource_category_key = ?", + (category_key,)) + row = cursor.fetchone() + if not bool(row): + raise DataNotFound( + f"Could not find resource category with key {category_key}") + return ResourceCategory(UUID(row["resource_category_id"]), + row["resource_category_key"], + row["resource_category_description"]) + + +def __create_resources__(cursor: authdb.DbCursor) -> tuple[Resource, ...]: + """Create default resources.""" + resources = tuple(Resource( + uuid4(), name, __resource_category_by_key__(cursor, catkey), + True, tuple() + ) for name, catkey in ( + ("mRNA-euhrin", "mrna"), + ("pheno-xboecp", "phenotype"), + ("geno-welphd", "genotype"))) + cursor.executemany( + "INSERT INTO resources VALUES (:rid, :rname, :rcid, :pub)", + tuple({ + "rid": str(res.resource_id), + "rname": res.resource_name, + "rcid": str(res.resource_category.resource_category_id), + "pub": 1 + } for res in resources)) + return resources + + +def default_resources(conn: authdb.DbConnection, group: Group) -> tuple[ + Resource, ...]: + """Create default resources, or return them if they exist.""" + with authdb.cursor(conn) as cursor: + cursor.execute( + "SELECT r.resource_id, r.resource_name, r.public, rc.* " + "FROM resource_ownership AS ro INNER JOIN resources AS r " + "ON ro.resource_id=r.resource_id " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE ro.group_id=? AND r.resource_name IN " + "('mRNA-euhrin', 'pheno-xboecp', 'geno-welphd')", + (str(group.group_id),)) + rows = cursor.fetchall() + if len(rows) == 0: + return __create_resources__(cursor) + + return tuple(Resource( + UUID(row["resource_id"]), + row["resource_name"], + ResourceCategory( + UUID(row["resource_category_id"]), + row["resource_category_key"], + row["resource_category_description"]), + bool(row["public"]), + tuple() + ) for row in rows) + + +def delay(): + """Delay a while: anything from 2 seconds to 15 seconds.""" + time.sleep(random.choice(range(2,16))) + + +def __assigned_mrna__(authconn): + """Retrieve assigned mRNA items.""" + with authdb.cursor(authconn) as cursor: + cursor.execute( + "SELECT SpeciesId, InbredSetId, ProbeFreezeId, ProbeSetFreezeId " + "FROM linked_mrna_data") + return tuple( + (row["SpeciesId"], row["InbredSetId"], row["ProbeFreezeId"], + row["ProbeSetFreezeId"]) for row in cursor.fetchall()) + + +def __unassigned_mrna__(bioconn, assigned): + """Retrieve unassigned mRNA data items.""" + query = ( + "SELECT s.SpeciesId, iset.InbredSetId, pf.ProbeFreezeId, " + "psf.Id AS ProbeSetFreezeId, psf.Name AS dataset_name, " + "psf.FullName AS dataset_fullname, psf.ShortName AS dataset_shortname " + "FROM Species AS s INNER JOIN InbredSet AS iset " + "ON s.SpeciesId=iset.SpeciesId INNER JOIN ProbeFreeze AS pf " + "ON iset.InbredSetId=pf.InbredSetId INNER JOIN ProbeSetFreeze AS psf " + "ON pf.ProbeFreezeId=psf.ProbeFreezeId " + "WHERE s.Name != 'human' ") + if len(assigned) > 0: + paramstr = ", ".join(["(%s, %s, %s, %s)"] * len(assigned)) + query = query + ( + "AND (s.SpeciesId, iset.InbredSetId, pf.ProbeFreezeId, psf.Id) " + f"NOT IN ({paramstr}) ") + + query = query + "LIMIT 100000" + with bioconn.cursor(DictCursor) as cursor: + cursor.execute(query, tuple(item for row in assigned for item in row)) + return (row for row in cursor.fetchall()) + + +def __assign_mrna__(authconn, bioconn, resource, group): + "Assign any unassigned mRNA data to resource." + while True: + unassigned = tuple({ + "data_link_id": str(uuid4()), + "group_id": str(group.group_id), + "resource_id": str(resource.resource_id), + **row + } for row in __unassigned_mrna__( + bioconn, __assigned_mrna__(authconn))) + + if len(unassigned) <= 0: + print("-> mRNA: Completed!") + break + with authdb.cursor(authconn) as cursor: + cursor.executemany( + "INSERT INTO linked_mrna_data VALUES " + "(:data_link_id, :group_id, :SpeciesId, :InbredSetId, " + ":ProbeFreezeId, :ProbeSetFreezeId, :dataset_name, " + ":dataset_fullname, :dataset_shortname)", + unassigned) + cursor.executemany( + "INSERT INTO mrna_resources VALUES " + "(:resource_id, :data_link_id)", + unassigned) + print(f"-> mRNA: Linked {len(unassigned)}") + delay() + + +def __assigned_geno__(authconn): + """Retrieve assigned genotype data.""" + with authdb.cursor(authconn) as cursor: + cursor.execute( + "SELECT SpeciesId, InbredSetId, GenoFreezeId " + "FROM linked_genotype_data") + return tuple((row["SpeciesId"], row["InbredSetId"], row["GenoFreezeId"]) + for row in cursor.fetchall()) + +def __unassigned_geno__(bioconn, assigned): + """Fetch unassigned genotype data.""" + query = ( + "SELECT s.SpeciesId, iset.InbredSetId, iset.InbredSetName, " + "gf.Id AS GenoFreezeId, gf.Name AS dataset_name, " + "gf.FullName AS dataset_fullname, " + "gf.ShortName AS dataset_shortname " + "FROM Species AS s INNER JOIN InbredSet AS iset " + "ON s.SpeciesId=iset.SpeciesId INNER JOIN GenoFreeze AS gf " + "ON iset.InbredSetId=gf.InbredSetId " + "WHERE s.Name != 'human' ") + if len(assigned) > 0: + paramstr = ", ".join(["(%s, %s, %s)"] * len(assigned)) + query = query + ( + "AND (s.SpeciesId, iset.InbredSetId, gf.Id) " + f"NOT IN ({paramstr}) ") + + query = query + "LIMIT 100000" + with bioconn.cursor(DictCursor) as cursor: + cursor.execute(query, tuple(item for row in assigned for item in row)) + return (row for row in cursor.fetchall()) + + +def __assign_geno__(authconn, bioconn, resource, group): + "Assign any unassigned Genotype data to resource." + while True: + unassigned = tuple({ + "data_link_id": str(uuid4()), + "group_id": str(group.group_id), + "resource_id": str(resource.resource_id), + **row + } for row in __unassigned_geno__( + bioconn, __assigned_geno__(authconn))) + + if len(unassigned) <= 0: + print("-> Genotype: Completed!") + break + with authdb.cursor(authconn) as cursor: + cursor.executemany( + "INSERT INTO linked_genotype_data VALUES " + "(:data_link_id, :group_id, :SpeciesId, :InbredSetId, " + ":GenoFreezeId, :dataset_name, :dataset_fullname, " + ":dataset_shortname)", + unassigned) + cursor.executemany( + "INSERT INTO genotype_resources VALUES " + "(:resource_id, :data_link_id)", + unassigned) + print(f"-> Genotype: Linked {len(unassigned)}") + delay() + + +def __assigned_pheno__(authconn): + """Retrieve assigned phenotype data.""" + with authdb.cursor(authconn) as cursor: + cursor.execute( + "SELECT SpeciesId, InbredSetId, PublishFreezeId, PublishXRefId " + "FROM linked_phenotype_data") + return tuple(( + row["SpeciesId"], row["InbredSetId"], row["PublishFreezeId"], + row["PublishXRefId"]) for row in cursor.fetchall()) + + +def __unassigned_pheno__(bioconn, assigned): + """Retrieve all unassigned Phenotype data.""" + query = ( + "SELECT spc.SpeciesId, iset.InbredSetId, " + "pf.Id AS PublishFreezeId, pf.Name AS dataset_name, " + "pf.FullName AS dataset_fullname, " + "pf.ShortName AS dataset_shortname, pxr.Id AS PublishXRefId " + "FROM " + "Species AS spc " + "INNER JOIN InbredSet AS iset " + "ON spc.SpeciesId=iset.SpeciesId " + "INNER JOIN PublishFreeze AS pf " + "ON iset.InbredSetId=pf.InbredSetId " + "INNER JOIN PublishXRef AS pxr " + "ON pf.InbredSetId=pxr.InbredSetId " + "WHERE spc.Name != 'human' ") + if len(assigned) > 0: + paramstr = ", ".join(["(%s, %s, %s, %s)"] * len(assigned)) + query = query + ( + "AND (spc.SpeciesId, iset.InbredSetId, pf.Id, pxr.Id) " + f"NOT IN ({paramstr}) ") + + query = query + "LIMIT 100000" + with bioconn.cursor(DictCursor) as cursor: + cursor.execute(query, tuple(item for row in assigned for item in row)) + return (row for row in cursor.fetchall()) + + +def __assign_pheno__(authconn, bioconn, resource, group): + """Assign any unassigned Phenotype data to resource.""" + while True: + unassigned = tuple({ + "data_link_id": str(uuid4()), + "group_id": str(group.group_id), + "resource_id": str(resource.resource_id), + **row + } for row in __unassigned_pheno__( + bioconn, __assigned_pheno__(authconn))) + + if len(unassigned) <= 0: + print("-> Phenotype: Completed!") + break + with authdb.cursor(authconn) as cursor: + cursor.executemany( + "INSERT INTO linked_phenotype_data VALUES " + "(:data_link_id, :group_id, :SpeciesId, :InbredSetId, " + ":PublishFreezeId, :dataset_name, :dataset_fullname, " + ":dataset_shortname, :PublishXRefId)", + unassigned) + cursor.executemany( + "INSERT INTO phenotype_resources VALUES " + "(:resource_id, :data_link_id)", + unassigned) + print(f"-> Phenotype: Linked {len(unassigned)}") + delay() + + +def assign_data_to_resource( + authconn, bioconn, resource: Resource, group: Group): + """Assign existing data, not linked to any group to the resource.""" + assigner_fns = { + "mrna": __assign_mrna__, + "genotype": __assign_geno__, + "phenotype": __assign_pheno__ + } + return assigner_fns[resource.resource_category.resource_category_key]( + authconn, bioconn, resource, group) + + +def entry(authdbpath, mysqldburi): + """Entry-point for data migration.""" + if not Path(authdbpath).exists(): + print( + f"ERROR: Auth db file `{authdbpath}` does not exist.", + file=sys.stderr) + sys.exit(2) + try: + with (authdb.connection(authdbpath) as authconn, + biodb.database_connection(mysqldburi) as bioconn): + admin = select_sys_admin(sys_admins(authconn)) + the_admin_group = admin_group(authconn, admin) + resources = default_resources(authconn, the_admin_group) + add_resources_to_group(authconn, resources, the_admin_group) + for resource in resources: + assign_data_to_resource( + authconn, bioconn, resource, the_admin_group) + with authdb.cursor(authconn) as cursor: + assign_resource_owner_role( + cursor, resource.resource_id, admin.user_id) + except DataNotFound as dnf: + print(dnf.args[0], file=sys.stderr) + sys.exit(1) + + +@click.command() +@click.argument("authdbpath") # "Path to the Auth(entic|oris)ation database" +@click.argument("mysqldburi") # "URI to the MySQL database with the biology data" +@click.option("--loglevel", default="WARNING", show_default=True, + type=click.Choice(["CRITICAL", "ERROR", "WARNING", "INFO", "DEBUG"])) +def run(authdbpath, mysqldburi, loglevel): + """Setup command-line arguments.""" + globallogger = logging.getLogger() + globallogger.setLevel(loglevel) + entry(authdbpath, mysqldburi) + + +if __name__ == "__main__": + run() # pylint: disable=[no-value-for-parameter] diff --git a/gn_auth/scripts/batch_assign_data_to_default_admin.py b/gn_auth/scripts/batch_assign_data_to_default_admin.py new file mode 100644 index 0000000..95d9794 --- /dev/null +++ b/gn_auth/scripts/batch_assign_data_to_default_admin.py @@ -0,0 +1,86 @@ +""" +Similar to the 'assign_data_to_default_admin' script but without user +interaction. +""" +import sys +import logging +from pathlib import Path + +import click +from gn_libs import mysqldb as biodb +from pymonad.maybe import Just, Maybe, Nothing +from pymonad.tools import monad_from_none_or_value + +from gn_auth.auth.db import sqlite3 as authdb +from gn_auth.auth.authentication.users import User +from gn_auth.auth.authorisation.resources.groups.models import ( + Group, db_row_to_group) +from gn_auth.scripts.assign_data_to_default_admin import ( + default_resources, assign_data_to_resource) + + +def resources_group(conn: authdb.DbConnection) -> Maybe: + """Retrieve resources' group""" + with authdb.cursor(conn) as cursor: + cursor.execute( + "SELECT g.* FROM resources AS r " + "INNER JOIN resource_ownership AS ro " + "ON r.resource_id=ro.resource_id " + "INNER JOIN groups AS g ON ro.group_id=g.group_id " + "WHERE resource_name='mRNA-euhrin'") + return monad_from_none_or_value( + Nothing, Just, cursor.fetchone()).then( + db_row_to_group) + + +def resource_owner(conn: authdb.DbConnection) -> Maybe: + """Retrieve the resource owner.""" + with authdb.cursor(conn) as cursor: + cursor.execute( + "SELECT u.* FROM users AS u WHERE u.user_id IN " + "(SELECT ur.user_id FROM resources 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='mRNA-euhrin' " + "AND r.role_name='resource-owner')") + return monad_from_none_or_value( + Nothing, Just, cursor.fetchone()).then( + User.from_sqlite3_row) + + +def assign_data(authconn: authdb.DbConnection, bioconn, group: Group): + """Do actual data assignments.""" + try: + for resource in default_resources(authconn, group): + assign_data_to_resource(authconn, bioconn, resource, group) + + return 1 + except Exception as _exc:# pylint: disable=[broad-except] + logging.error("Failed to assign some data!", exc_info=True) + return 1 + + +if __name__ == "__main__": + @click.command() + @click.argument("authdbpath") # "Path to the Auth(entic|oris)ation database" + @click.argument("mysqldburi") # "URI to the MySQL database with the biology data" + @click.option("--loglevel", + default="WARNING", + show_default=True, + type=click.Choice([ + "CRITICAL", "ERROR", "WARNING", "INFO", "DEBUG"])) + def run(authdbpath, mysqldburi, loglevel): + """Script entry point.""" + _logger = logging.getLogger() + _logger.setLevel(loglevel) + if Path(authdbpath).exists(): + with (authdb.connection(authdbpath) as authconn, + biodb.database_connection(mysqldburi) as bioconn): + return resources_group(authconn).maybe( + 1, + lambda group: assign_data(authconn, bioconn, group)) + + logging.error("There is no such SQLite3 database file.") + return 1 + + sys.exit(run()) # pylint: disable=[no-value-for-parameter] diff --git a/gn_auth/scripts/link_inbredsets.py b/gn_auth/scripts/link_inbredsets.py new file mode 100644 index 0000000..ad743f5 --- /dev/null +++ b/gn_auth/scripts/link_inbredsets.py @@ -0,0 +1,122 @@ +""" +Link any unlinked InbredSet groups. +""" +import sys +import uuid +from pathlib import Path + +import click +from gn_libs import mysqldb as biodb + +import gn_auth.auth.db.sqlite3 as authdb + +from gn_auth.scripts.assign_data_to_default_admin import ( + sys_admins, admin_group, select_sys_admin) + +def linked_inbredsets(conn): + """Fetch all inbredset groups that are linked to the auth system.""" + with authdb.cursor(conn) as cursor: + cursor.execute( + "SELECT SpeciesId, InbredSetId FROM linked_inbredset_groups") + return tuple((row["SpeciesId"], row["InbredSetId"]) + for row in cursor.fetchall()) + +def unlinked_inbredsets(conn, linked): + """Fetch any inbredset groups that are not linked to the auth system.""" + with conn.cursor() as cursor: + where_clause = "" + query = "SELECT SpeciesId, InbredSetId, InbredSetName, FullName FROM InbredSet" + if len(linked) > 0: + pholders = ["(%s, %s)"] * len(linked) + where_clause = (f" WHERE (SpeciesId, InbredSetId) " + f"NOT IN ({pholders})") + cursor.execute(query + where_clause, + tuple(arg for sublist in linked for arg in sublist)) + return cursor.fetchall() + + cursor.execute(query) + return cursor.fetchall() + +def link_unlinked(conn, unlinked): + """Link the unlinked inbredset groups to the auth system.""" + params = tuple((str(uuid.uuid4()),) + row for row in unlinked) + with authdb.cursor(conn) as cursor: + cursor.executemany( + "INSERT INTO linked_inbredset_groups VALUES (?, ?, ?, ?, ?)", + params) + + return params + +def build_resources(conn, new_linked): + """Build resources for newly linked inbredsets.""" + with authdb.cursor(conn) as cursor: + cursor.execute( + "SELECT resource_category_id FROM resource_categories " + "WHERE resource_category_key='inbredset-group'") + category_id = cursor.fetchone()["resource_category_id"] + resources = tuple({ + "resource_id": str(uuid.uuid4()), + "resource_name": f"InbredSet: {name}", + "resource_category_id": category_id, + "public": 1, + "data_link_id": datalinkid + } for datalinkid, _sid, _isetid, name, _name in new_linked) + cursor.executemany( + "INSERT INTO resources VALUES " + "(:resource_id, :resource_name, :resource_category_id, :public)", + resources) + cursor.executemany( + "INSERT INTO inbredset_group_resources VALUES " + "(:resource_id, :data_link_id)", + resources) + return resources + +def own_resources(conn, group, resources): + """Link new resources to admin group.""" + with authdb.cursor(conn) as cursor: + params = tuple({ + "group_id": str(group.group_id), + **resource + } for resource in resources) + cursor.executemany( + "INSERT INTO resource_ownership VALUES " + "(:group_id, :resource_id)", + params) + return params + +def assign_role_for_admin(conn, user, resources): + """Assign basic role to admin on the inbredset-group resources.""" + with authdb.cursor(conn) as cursor: + cursor.execute( + "SELECT * FROM roles WHERE role_name='inbredset-group-owner'") + role_id = cursor.fetchone()["role_id"] + cursor.executemany( + "INSERT INTO user_roles(user_id, role_id, resource_id) " + "VALUES (:user_id, :role_id, :resource_id)", + tuple({**rsc, "user_id": str(user.user_id), "role_id": role_id} + for rsc in resources)) + +@click.command() +@click.argument("authdbpath") # "Path to the Auth(entic|oris)ation database" +@click.argument("mysqldburi") # "URI to the MySQL database with the biology data" +def run(authdbpath, mysqldburi): + """Setup command-line arguments.""" + if not Path(authdbpath).exists(): + print( + f"ERROR: Auth db file `{authdbpath}` does not exist.", + file=sys.stderr) + sys.exit(2) + + with (authdb.connection(authdbpath) as authconn, + biodb.database_connection(mysqldburi) as bioconn): + admin = select_sys_admin(sys_admins(authconn)) + assign_role_for_admin(authconn, admin, own_resources( + authconn, + admin_group(authconn, admin), + build_resources( + authconn, link_unlinked( + authconn, + unlinked_inbredsets(bioconn, linked_inbredsets(authconn)))))) + +if __name__ == "__main__": + run() # pylint: disable=[no-value-for-parameter] diff --git a/gn_auth/scripts/register_sys_admin.py b/gn_auth/scripts/register_sys_admin.py new file mode 100644 index 0000000..06aa845 --- /dev/null +++ b/gn_auth/scripts/register_sys_admin.py @@ -0,0 +1,68 @@ +"""Script to register and mark a user account as sysadmin.""" +import sys +import getpass +from pathlib import Path + +import click +from email_validator import validate_email, EmailNotValidError + +from gn_auth.auth.db import sqlite3 as db +from gn_auth.auth.authorisation.users.admin.models import make_sys_admin +from gn_auth.auth.authentication.users import save_user, set_user_password + +def fetch_email() -> str: + """Prompt user for email.""" + while True: + try: + user_input = input("Enter the administrator's email: ") + email = validate_email(user_input.strip(), check_deliverability=True) + return email["email"] # type: ignore + except EmailNotValidError as _enve: + print("You did not provide a valid email address. Try again...", + file=sys.stderr) + +def fetch_password() -> str: + """Prompt user for password.""" + while True: + passwd = getpass.getpass(prompt="Enter password: ").strip() + passwd2 = getpass.getpass(prompt="Confirm password: ").strip() + if passwd != "" and passwd == passwd2: + return passwd + if passwd == "": + print("Empty password not accepted", file=sys.stderr) + continue + if passwd != passwd2: + print("Passwords *MUST* match", file=sys.stderr) + continue + +def fetch_name() -> str: + """Prompt user for name""" + while True: + name = input("Enter the user's name: ").strip() + if name == "": + print("Invalid name.") + continue + return name + +def save_admin(conn: db.DbConnection, name: str, email: str, passwd: str): + """Save the details to the database and assign the new user as admin.""" + with db.cursor(conn) as cursor: + usr, _hpasswd = set_user_password( + cursor, save_user(cursor, email, name), passwd) + make_sys_admin(cursor, usr) + return 0 + +def register_admin(authdbpath: Path): + """Register a user as a system admin.""" + assert authdbpath.exists(), "Could not find database file." + with db.connection(str(authdbpath)) as conn: + return save_admin(conn, fetch_name(), fetch_email(), fetch_password()) + +if __name__ == "__main__": + @click.command() + @click.argument("authdbpath") + def run(authdbpath): + """Entry-point for when script is run directly""" + return register_admin(Path(authdbpath).absolute()) + + run()# pylint: disable=[no-value-for-parameter] diff --git a/gn_auth/scripts/search_phenotypes.py b/gn_auth/scripts/search_phenotypes.py new file mode 100644 index 0000000..eee112d --- /dev/null +++ b/gn_auth/scripts/search_phenotypes.py @@ -0,0 +1,125 @@ +""" +A script to do search for phenotype traits using the Xapian Search endpoint. +""" +import uuid +import json +import traceback +from urllib.parse import urljoin +from typing import Any, Iterable +from datetime import datetime, timedelta + +import click +import redis +import requests +from gn_libs import mysqldb as gn3db + +from gn_auth import jobs +from gn_auth.auth.db import sqlite3 as authdb +from gn_auth.settings import SQL_URI, AUTH_DB +from gn_auth.auth.authorisation.data.phenotypes import linked_phenotype_data + +class NoSearchResults(Exception): + """Raise when there are no results for a search.""" + +def do_search( + host: str, query: str, per_page: int, page: int = 1) -> Iterable[dict[str, Any]]: + """Do the search and return the results""" + search_uri = urljoin(host, (f"search/?page={page}&per_page={per_page}" + f"&type=phenotype&query={query}")) + response = requests.get(search_uri, timeout=300) + results = response.json() + if len(results) > 0: + return (item for item in results) + raise NoSearchResults(f"No results for search '{query}'") + +def __filter_object__(search_item): + return (search_item["species"], search_item["group"], + search_item["dataset"], search_item["name"]) + +def remove_selected(search_results, selected: tuple): + """Remove any item that the user has selected.""" + return (item for item in search_results if __filter_object__(item) not in selected) + +def remove_linked(search_results, linked: tuple): + """Remove any item that has been already linked to a user group.""" + return (item for item in search_results if __filter_object__(item) not in linked) + +def update_status(redisconn: redis.Redis, redisname, status: str): + """Update the status of the search.""" + redisconn.hset(redisname, "status", json.dumps(status)) + +def update_search_results(redisconn: redis.Redis, redisname: str, + results: tuple[dict[str, Any], ...]): + """Save the results to redis db.""" + key = "search_results" + prev_results = tuple(json.loads(redisconn.hget(redisname, key) or "[]")) # type: ignore + redisconn.hset(redisname, key, json.dumps(prev_results + results)) + +def expire_redis_results(redisconn: redis.Redis, redisname: str): + """Expire the results after a while to ensure they are cleaned up.""" + redisconn.expireat(redisname, datetime.now() + timedelta(minutes=30)) + +@click.command() +@click.argument("species") +@click.argument("query") +@click.argument("job-id", type=click.UUID) +@click.option( + "--host", default="http://localhost:8080/api/", help="The URI to GN3.") +@click.option("--per-page", default=10000, help="Number of results per page.") +@click.option("--selected", default="[]", help="Selected traits.") +@click.option( + "--auth-db-uri", default=AUTH_DB, help="The SQL URI to the auth database.") +@click.option( + "--gn3-db-uri", default=SQL_URI, + help="The SQL URI to the main GN3 database.") +@click.option( + "--redis-uri", default="redis://:@localhost:6379/0", + help="The URI to the redis server.") +def search(# pylint: disable=[too-many-arguments, too-many-positional-arguments, too-many-locals] + species: str, query: str, job_id: uuid.UUID, host: str, per_page: int, + selected: str, auth_db_uri: str, gn3_db_uri: str, redis_uri: str): + """ + Search for phenotype traits, filtering out any linked and selected traits, + loading more and more pages until the `per_page` quota is fulfilled or the + search runs out of pages. + """ + redisname = jobs.job_key(job_id) + with (authdb.connection(auth_db_uri) as authconn, + gn3db.database_connection(gn3_db_uri) as gn3conn, + redis.Redis.from_url(redis_uri, decode_responses=True) as redisconn): + update_status(redisconn, redisname, "started") + update_search_results(redisconn, redisname, tuple()) # init search results + try: + search_query = f"species:{species}" + ( + f" AND ({query})" if bool(query) else "") + selected_traits = tuple( + (item["species"], item["group"], item["dataset"], item["name"]) + for item in json.loads(selected)) + linked = tuple( + (row["SpeciesName"], row["InbredSetName"], row["dataset_name"], + str(row["PublishXRefId"])) + for row in linked_phenotype_data(authconn, gn3conn, species)) + page = 1 + count = 0 + while count < per_page: + results = tuple(remove_linked( + remove_selected( + do_search(host, search_query, per_page, page), + selected_traits), + linked))[0:per_page-count] + count = count + len(results) + page = page + 1 + update_search_results(redisconn, redisname, results) + except NoSearchResults as _nsr: + pass + except Exception as _exc: # pylint: disable=[broad-except] + update_status(redisconn, redisname, "failed") + redisconn.hset(redisname, "exception", json.dumps(traceback.format_exc())) + expire_redis_results(redisconn, redisname) + return 1 + update_status(redisconn, redisname, "completed") + expire_redis_results(redisconn, redisname) + return 0 + +if __name__ == "__main__": + search() # pylint: disable=[no-value-for-parameter] diff --git a/gn_auth/scripts/worker.py b/gn_auth/scripts/worker.py new file mode 100644 index 0000000..0a77d41 --- /dev/null +++ b/gn_auth/scripts/worker.py @@ -0,0 +1,83 @@ +"""Daemon that processes commands""" +import os +import sys +import time +import argparse + +import redis +import redis.connection + +from gn_auth.commands import run_cmd + +# Enable importing from one dir up: put as first to override any other globally +# accessible GN3 +sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))) + +def update_status(conn, cmd_id, status): + """Helper to update command status""" + conn.hset(name=f"{cmd_id}", key="status", value=f"{status}") + +def make_incremental_backoff(init_val: float=0.1, maximum: int=420): + """ + Returns a closure that can be used to increment the returned value up to + `maximum` or reset it to `init_val`. + """ + current = init_val + + def __increment_or_reset__(command: str, value: float=0.1): + nonlocal current + if command == "reset": + current = init_val + return current + + if command == "increment": + current = min(current + abs(value), maximum) + return current + + return current + + return __increment_or_reset__ + +def run_jobs(conn, queue_name: str): + """Process the redis using a redis connection, CONN""" + # pylint: disable=E0401, C0415 + cmd_id = (conn.lpop(queue_name) or b'').decode("utf-8") + if bool(cmd_id): + cmd = conn.hget(name=cmd_id, key="cmd") + if cmd and (conn.hget(cmd_id, "status") == b"queued"): + update_status(conn, cmd_id, "running") + result = run_cmd( + cmd.decode("utf-8"), env=conn.hget(name=cmd_id, key="env")) + conn.hset(name=cmd_id, key="result", value=result.get("output")) + if result.get("code") == 0: # Success + update_status(conn, cmd_id, "success") + else: + update_status(conn, cmd_id, "error") + conn.hset(cmd_id, "stderr", result.get("output")) + return cmd_id + return None + +def parse_cli_arguments(): + """Parse the command-line arguments.""" + parser = argparse.ArgumentParser( + description="Run asynchronous (service) commands.") + parser.add_argument("queue_name", help="Queue to check in redis") + parser.add_argument( + "--daemon", default=False, action="store_true", + help=( + "Run process as a daemon instead of the default 'one-shot' " + "process")) + return parser.parse_args() + +if __name__ == "__main__": + args = parse_cli_arguments() + with redis.Redis() as redis_conn: + if not args.daemon: + run_jobs(redis_conn, args.queue_name) + else: + sleep_time = make_incremental_backoff() + while True: # Daemon that keeps running forever: + if run_jobs(redis_conn, args.queue_name): + time.sleep(sleep_time("reset")) + continue + time.sleep(sleep_time("increment", sleep_time("return_current"))) diff --git a/gn_auth/settings.py b/gn_auth/settings.py index d59e997..f903553 100644 --- a/gn_auth/settings.py +++ b/gn_auth/settings.py @@ -14,7 +14,7 @@ SESSION_EXPIRY_MINUTES = 10 # Database settings SQL_URI = "mysql://webqtlout:webqtlout@localhost/db_webqtl" AUTH_DB = f"{os.environ.get('HOME')}/genenetwork/gn3_files/db/auth.db" -AUTH_MIGRATIONS = "migrations/auth" +AUTH_MIGRATIONS = "gn_auth/migrations/auth" # Redis settings REDIS_URI = "redis://localhost:6379/0" @@ -49,3 +49,5 @@ EMAIL_ADDRESS = "no-reply@uthsc.edu" ## Variable settings for various emails going out to users AUTH_EMAILS_EXPIRY_MINUTES = 15 + +LOGGABLE_MODULES = ["gn_auth"] diff --git a/gn_auth/wsgi.py b/gn_auth/wsgi.py index e05ef0d..a5af37e 100644 --- a/gn_auth/wsgi.py +++ b/gn_auth/wsgi.py @@ -1,10 +1,13 @@ """Main entry point for project""" +import os +import re +import secrets import sys import uuid import json from math import ceil from pathlib import Path -from datetime import datetime +from datetime import datetime, timezone import click from yoyo import get_backend, read_migrations @@ -14,10 +17,16 @@ from gn_auth import create_app from gn_auth.auth.db import sqlite3 as db from gn_auth.auth.errors import NotFoundError -from gn_auth.auth.authentication.users import user_by_id, hash_password -from gn_auth.auth.authorisation.users.admin.models import make_sys_admin - -from scripts import register_sys_admin as rsysadm# type: ignore[import] +from gn_auth.auth.authentication.users import ( + user_by_id, hash_password, save_user, set_user_password) +from gn_auth.auth.authorisation.roles.models import assign_default_roles +from gn_auth.auth.authorisation.users.admin.models import ( + make_sys_admin, grant_sysadmin_role) +from gn_auth.auth.authorisation.users.models import delete_users_by_id +from gn_auth.auth.authentication.oauth2.models.oauth2client import ( + OAuth2Client, save_client, delete_client, + client as oauth2_client_by_id) +from gn_auth.scripts import register_sys_admin as rsysadm# type: ignore[import] app = create_app() @@ -127,6 +136,364 @@ def register_admin(): """Register the administrator.""" rsysadm.register_admin(Path(app.config["AUTH_DB"])) + +_VALID_ROLES_ = ("system-admin", "none") + +_TEST_EMAIL_DOMAIN_ = "regression-tests.genenetwork.org" + + +def __normalise_name_for_email__(name: str) -> str: + """Lowercase and strip non-alphanumeric characters for use in an email.""" + return re.sub(r"[^a-z0-9]", "", name.lower()) + + +def __create_one_user__(cursor, name: str, email: str, password: str, role: str) -> dict: + """Create a single user in the DB and return their credential record.""" + user = save_user(cursor, email, name, verified=True) + set_user_password(cursor, user, password) + assign_default_roles(cursor, user) + if role == "system-admin": + grant_sysadmin_role(cursor, user) + return { + "user_id": str(user.user_id), + "name": user.name, + "email": user.email, + "password": password, + "role": role, + } + + +def __parse_user_spec__(spec: str) -> dict: + """Parse 'key=value,key=value,...' into a dict.""" + result = {} + for part in spec.split(","): + key, _, value = part.partition("=") + if key.strip(): + result[key.strip()] = value.strip() + return result + + +def __write_output__(data: dict, output_path) -> None: + """Write JSON data to a file with 0600 permissions, or stdout.""" + text = json.dumps(data, indent=2) + if output_path is None: + print(text) + return + fd = os.open(output_path, os.O_WRONLY | os.O_CREAT | os.O_TRUNC, 0o600) + with os.fdopen(fd, "w") as outfile: + outfile.write(text) + + +@app.cli.command() +@click.option("--user", "user_specs", multiple=True, + help='User spec: "name=...,email=...,password=...,role=..."') +@click.option("--output", "output_path", type=click.Path(), default=None, + help="Write credentials as JSON to this file (default: stdout)") +def create_users(user_specs, output_path): + """Create one or more users with specified credentials and roles. + + Each --user option takes a comma-separated key=value string with the + following keys: name, email, password, role. + + Valid roles: system-admin, none. + """ + if not user_specs: + print("No users specified.", file=sys.stderr) + sys.exit(1) + + records = [] + with db.connection(app.config["AUTH_DB"]) as conn, db.cursor(conn) as cursor: + for spec_str in user_specs: + spec = __parse_user_spec__(spec_str) + name = spec.get("name", "").strip() + email = spec.get("email", "").strip() + password = spec.get("password", "").strip() + role = spec.get("role", "none").strip() + + if not name: + print(f"Missing 'name' in user spec: {spec_str!r}", file=sys.stderr) + sys.exit(1) + if not email: + print(f"Missing 'email' in user spec: {spec_str!r}", file=sys.stderr) + sys.exit(1) + if not password: + print(f"Missing 'password' in user spec: {spec_str!r}", file=sys.stderr) + sys.exit(1) + if role not in _VALID_ROLES_: + print( + f"Invalid role {role!r} in spec: {spec_str!r}. " + f"Valid roles: {_VALID_ROLES_}", + file=sys.stderr) + sys.exit(1) + + records.append( + __create_one_user__(cursor, name, email, password, role)) + + __write_output__({"users": records}, output_path) + + +@app.cli.command() +@click.option("--user-id", "user_ids", multiple=True, type=click.UUID, + help="UUID of a user to delete (repeatable)") +def delete_users(user_ids): + """Delete one or more users by ID, bypassing policy checks. + + Removes users unconditionally regardless of their roles or group + memberships. Use with care — intended for test teardown and administration. + """ + if not user_ids: + print("No user IDs specified.", file=sys.stderr) + sys.exit(1) + + with db.connection(app.config["AUTH_DB"]) as conn: + deleted = delete_users_by_id(conn, tuple(user_ids)) + print(f"Deleted {deleted} user(s).") + + +@app.cli.command() +@click.option("--session-timestamp", required=True, + help="Compact ISO 8601 UTC timestamp (e.g. 20260602T122700Z)") +@click.option("--user", "user_specs", multiple=True, + help='User spec: "name=...,role=..."') +@click.option("--output", "output_path", required=True, type=click.Path(), + help="Write credentials as JSON to this file (0600 permissions)") +def create_test_users(session_timestamp, user_specs, output_path): + """Create ephemeral test users with auto-generated email and password. + + Each --user option takes a comma-separated key=value string with the + following keys: name, role. + + Email: <normalised-name><timestamp>@regression-tests.genenetwork.org + Password: randomly generated. + + Output is written with 0600 permissions. Valid roles: system-admin, none. + """ + if not user_specs: + print("No users specified.", file=sys.stderr) + sys.exit(1) + + records = [] + with db.connection(app.config["AUTH_DB"]) as conn, db.cursor(conn) as cursor: + for spec_str in user_specs: + spec = __parse_user_spec__(spec_str) + name = spec.get("name", "").strip() + role = spec.get("role", "none").strip() + + if not name: + print(f"Missing 'name' in user spec: {spec_str!r}", file=sys.stderr) + sys.exit(1) + if role not in _VALID_ROLES_: + print( + f"Invalid role {role!r} in spec: {spec_str!r}. " + f"Valid roles: {_VALID_ROLES_}", + file=sys.stderr) + sys.exit(1) + + email = (f"{__normalise_name_for_email__(name)}" + f"{session_timestamp}@{_TEST_EMAIL_DOMAIN_}") + password = secrets.token_urlsafe(32) + + records.append( + __create_one_user__(cursor, name, email, password, role)) + + __write_output__( + {"session_timestamp": session_timestamp, "users": records}, + output_path) + + +_DEFAULT_GRANT_TYPES_ = ( + "password", + "authorization_code", + "refresh_token", + "urn:ietf:params:oauth:grant-type:jwt-bearer", +) + +_DEFAULT_SCOPES_ = ( + "profile", "group", "role", "resource", + "register-client", "user", "masquerade", + "migrate-data", "introspect", +) + + +def __create_one_client__(# pylint: disable=[too-many-arguments, too-many-positional-arguments] + conn, + client_name: str, + owner_user, + redirect_uris: tuple, + scopes: tuple = _DEFAULT_SCOPES_, + grant_types: tuple = _DEFAULT_GRANT_TYPES_, + jwks_uri: str = "", +) -> dict: + """Create a single OAuth2 client and return its credential record.""" + raw_secret = secrets.token_urlsafe(32) + the_client = OAuth2Client( + client_id=uuid.uuid4(), + client_secret=hash_password(raw_secret), + client_id_issued_at=datetime.now(tz=timezone.utc), + client_secret_expires_at=datetime.fromtimestamp(0), + client_metadata={ + "client_name": client_name, + "token_endpoint_auth_method": [ + "client_secret_post", "client_secret_basic"], + "client_type": "confidential", + "grant_types": list(grant_types), + "default_redirect_uri": redirect_uris[0] if redirect_uris else "", + "redirect_uris": list(redirect_uris), + "response_type": ["code", "token"], + "scope": list(scopes), + "public-jwks-uri": jwks_uri, + }, + user=owner_user) + save_client(conn, the_client) + return { + "client_id": str(the_client.client_id), + "client_secret": raw_secret, + "client_name": client_name, + } + + +@app.cli.command() +@click.option("--name", "client_name", required=True, + help="Human-readable name for the OAuth2 client") +@click.option("--owner-id", required=True, type=click.UUID, + help="UUID of the user who owns this client") +@click.option("--redirect-uri", "redirect_uris", multiple=True, + help="Allowed redirect URI (repeatable)") +@click.option("--scope", "scopes", multiple=True, + default=_DEFAULT_SCOPES_, show_default=False, + help="OAuth2 scope (repeatable; defaults to full scope set)") +@click.option("--grant-type", "grant_types", multiple=True, + default=_DEFAULT_GRANT_TYPES_, show_default=False, + help="Grant type (repeatable; defaults to all standard types)") +@click.option("--jwks-uri", default="", + help="URI to the client's public JWKS (optional)") +@click.option("--output", "output_path", type=click.Path(), default=None, + help="Write credentials as JSON to this file (default: stdout)") +def create_oauth2_client(# pylint: disable=[too-many-arguments, too-many-positional-arguments] + client_name, + owner_id, + redirect_uris, + scopes, + grant_types, + jwks_uri, + output_path +): + """Create an OAuth2 client with specified parameters. + + Scopes and grant types default to the full standard set if not provided. + """ + with db.connection(app.config["AUTH_DB"]) as conn: + try: + owner = user_by_id(conn, owner_id) + except NotFoundError: + print(f"No user found with ID {owner_id}", file=sys.stderr) + sys.exit(1) + record = __create_one_client__( + conn, client_name, owner, redirect_uris, scopes, grant_types, + jwks_uri) + + __write_output__({"client": record}, output_path) + + +@app.cli.command() +@click.option("--session-timestamp", required=True, + help="Compact ISO 8601 UTC timestamp (e.g. 20260602T122700Z)") +@click.option("--users-file", required=True, type=click.Path(exists=True), + help="Credentials file produced by create-test-users") +@click.option("--owner-role", default="system-admin", show_default=True, + help="Role of the user in users-file to assign as client owner") +@click.option("--output", "output_path", required=True, type=click.Path(), + help="Write credentials as JSON to this file (0600 permissions)") +def create_test_oauth2_client(session_timestamp, users_file, owner_role, + output_path): + """Create an ephemeral OAuth2 client for a test session. + + Reads the credentials file produced by create-test-users to find the + owner. Client name and secret are auto-generated using the session + timestamp. Output is written with 0600 permissions. + """ + with open(users_file, encoding="utf8") as f: + users_data = json.load(f) + + owner_record = next( + (u for u in users_data.get("users", []) if u["role"] == owner_role), + None) + if owner_record is None: + print( + f"No user with role {owner_role!r} found in {users_file}", + file=sys.stderr) + sys.exit(1) + + client_name = f"gn-test-client-{session_timestamp}" + + with db.connection(app.config["AUTH_DB"]) as conn: + try: + owner = user_by_id(conn, uuid.UUID(owner_record["user_id"])) + except NotFoundError: + print( + f"Owner user {owner_record['user_id']!r} not found in DB", + file=sys.stderr) + sys.exit(1) + record = __create_one_client__(conn, client_name, owner, tuple()) + + __write_output__( + {"session_timestamp": session_timestamp, "client": record}, + output_path) + + +@app.cli.command() +@click.option("--credentials", "credentials_path", required=True, + type=click.Path(exists=True), + help="Credentials file produced by create-oauth2-client or " + "create-test-oauth2-client") +def delete_oauth2_client(credentials_path): + """Delete an OAuth2 client using a credentials file. + + Reads the client_id from the given credentials file and removes the + client and all associated tokens from the database. + """ + with open(credentials_path, encoding="utf8") as f: + data = json.load(f) + + client_id_str = data.get("client", {}).get("client_id") + if not client_id_str: + print("No client_id found in credentials file.", file=sys.stderr) + sys.exit(1) + + client_id = uuid.UUID(client_id_str) + with db.connection(app.config["AUTH_DB"]) as conn: + the_client = oauth2_client_by_id(conn, client_id) + if the_client.is_nothing(): + print(f"No client found with ID {client_id}", file=sys.stderr) + sys.exit(1) + delete_client(conn, the_client.value) + print(f"Deleted OAuth2 client {client_id}.") + + +@app.cli.command() +@click.option("--credentials", "credentials_path", required=True, + type=click.Path(exists=True), + help="Credentials file produced by create-test-users") +def delete_test_users(credentials_path): + """Delete ephemeral test users using a credentials file. + + Reads the credentials file produced by create-test-users and deletes + all listed users unconditionally, bypassing policy checks. Intended + for CI test teardown. + """ + with open(credentials_path, encoding="utf8") as f: + data = json.load(f) + + user_ids = tuple( + uuid.UUID(u["user_id"]) for u in data.get("users", [])) + if not user_ids: + print("No users found in credentials file.", file=sys.stderr) + sys.exit(1) + + with db.connection(app.config["AUTH_DB"]) as conn: + deleted = delete_users_by_id(conn, user_ids) + print(f"Deleted {deleted} user(s).") + ##### END: CLI Commands ##### if __name__ == '__main__': |