Add `public-view` role. Assign it to users.

Add a new `public-view` role to be assigned to all users on all
resources that are defined as publicly viewable.

Update code to make assign `public-view` role to a newly registered
user for all publicly viewable roles.

Update the code to assign/revoke the `public-view` role to/from users
whenever the resource is toggled to and from being publicly viewable.

Ensure that `public-view` is not revoked from system-administrators.

Ensure that `public-view` is not revoked from the group administrators
of the group that owns the resource.

3 files changed, 159 insertions, 16 deletions

diff --git a/gn_auth/auth/authorisation/resources/views.py b/gn_auth/auth/authorisation/resources/views.py
index 0e44df5..f609303 100644
--- a/gn_auth/auth/authorisation/resources/views.py
+++ b/gn_auth/auth/authorisation/resources/views.py
@@ -18,9 +18,9 @@ from gn_auth.auth.authentication.users import User, user_by_id, user_by_email
 
 from .checks import authorised_for
 from .models import (
-    Resource, save_resource, resource_data, resource_by_id,
-    resource_categories, assign_resource_user, link_data_to_resource,
-    unassign_resource_user, resource_category_by_id, unlink_data_from_resource,
+    Resource, resource_data, resource_by_id, resource_categories,
+    assign_resource_user, link_data_to_resource, unassign_resource_user,
+    resource_category_by_id, unlink_data_from_resource,
     create_resource as _create_resource)
 from .groups.models import Group, GroupRole, resource_owner, group_role_by_id
 
@@ -253,6 +253,54 @@ def unassign_role_to_user(resource_id: uuid.UUID) -> Response:
 
         return jsonify(with_db_connection(__assign__))
 
+def __public_view_params__(cursor, user_id, resource_id):
+    ignore = (str(user_id),)
+    # sys admins
+    cursor.execute(
+        "SELECT ur.user_id FROM user_roles AS ur INNER JOIN roles AS r "
+        "ON ur.role_id=r.role_id WHERE r.role_name='system-administrator'")
+    ignore = ignore + tuple(
+        row["user_id"] for row in cursor.fetchall())
+    # group admins
+    cursor.execute(
+        "SELECT DISTINCT gu.user_id FROM resource_ownership AS ro "
+        "INNER JOIN groups AS g ON ro.group_id=g.group_id "
+        "INNER JOIN group_users AS gu ON g.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 ro.resource_id=? AND r.role_name='group-leader'",
+        (str(resource_id),))
+    ignore = tuple(set(
+        ignore + tuple(row["user_id"] for row in cursor.fetchall())))
+
+    cursor.execute(
+        "SELECT user_id FROM users WHERE user_id NOT IN "
+        f"({', '.join(['?'] * len(ignore))})",
+        ignore)
+    user_ids = tuple(row["user_id"] for row in cursor.fetchall())
+    cursor.execute(
+        "SELECT role_id FROM roles WHERE role_name='public-view'")
+    role_id = cursor.fetchone()["role_id"]
+    return tuple({
+        "user_id": user_id,
+        "role_id": role_id,
+        "resource_id": str(resource_id)
+    } for user_id in user_ids)
+
+def __assign_revoke_public_view__(cursor, user_id, resource_id, public):
+    if public:
+        cursor.executemany(
+            "INSERT INTO user_roles(user_id, role_id, resource_id) "
+            "VALUES(:user_id, :role_id, :resource_id) "
+            "ON CONFLICT (user_id, role_id, resource_id) "
+            "DO NOTHING",
+            __public_view_params__(cursor, user_id, resource_id))
+        return
+    cursor.executemany(
+        "DELETE FROM user_roles WHERE user_id=:user_id "
+        "AND role_id=:role_id AND resource_id=:resource_id",
+        __public_view_params__(cursor, user_id, resource_id))
+
 @resources.route("<uuid:resource_id>/toggle-public", methods=["POST"])
 @require_oauth("profile group resource role")
 def toggle_public(resource_id: uuid.UUID) -> Response:
@@ -260,11 +308,23 @@ def toggle_public(resource_id: uuid.UUID) -> Response:
     with require_oauth.acquire("profile group resource") as the_token:
         def __toggle__(conn: db.DbConnection) -> Resource:
             old_rsc = resource_by_id(conn, the_token.user, resource_id)
-            return save_resource(
-                conn, the_token.user, Resource(
-                    old_rsc.resource_id, old_rsc.resource_name,
-                    old_rsc.resource_category, not old_rsc.public,
-                    old_rsc.resource_data))
+            public = not old_rsc.public
+            new_resource = Resource(
+                old_rsc.resource_id, old_rsc.resource_name,
+                old_rsc.resource_category, public,
+                old_rsc.resource_data)
+            with db.cursor(conn) as cursor:
+                cursor.execute(
+                    "UPDATE resources SET public=:public "
+                    "WHERE resource_id=:resource_id",
+                    {
+                        "public": 1 if public else 0,
+                        "resource_id": str(resource_id)
+                    })
+                __assign_revoke_public_view__(
+                    cursor, the_token.user.user_id, resource_id, public)
+                return new_resource
+            return new_resource
 
         resource = with_db_connection(__toggle__)
         return jsonify({
diff --git a/gn_auth/auth/authorisation/roles/models.py b/gn_auth/auth/authorisation/roles/models.py
index 4281446..7d78eac 100644
--- a/gn_auth/auth/authorisation/roles/models.py
+++ b/gn_auth/auth/authorisation/roles/models.py
@@ -133,18 +133,40 @@ def user_role(conn: db.DbConnection, user: User, role_id: UUID) -> Either:
         return Left(NotFoundError(
             f"Could not find role with id '{role_id}'",))
 
-def assign_default_roles(cursor: db.DbCursor, user: User):
-    """Assign `user` some default roles."""
+def __assign_group_creator_role__(cursor: db.DbCursor, user: User):
     cursor.execute(
         'SELECT role_id FROM roles WHERE role_name IN '
         '("group-creator")')
-    role_ids = cursor.fetchall()
-    str_user_id = str(user.user_id)
-    params = tuple(
-        {"user_id": str_user_id, "role_id": row["role_id"]} for row in role_ids)
+    role_id = cursor.fetchone()["role_id"]
+    cursor.execute(
+        "SELECT 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'")
+    resource_id = cursor.fetchone()["resource_id"]
+    cursor.execute(
+        ("INSERT INTO user_roles VALUES (:user_id, :role_id, :resource_id)"),
+        {"user_id": str(user.user_id), "role_id": role_id,
+         "resource_id": resource_id})
+
+def __assign_public_view_role__(cursor: db.DbCursor, user: User):
+    cursor.execute("SELECT resource_id FROM resources WHERE public=1")
+    public_resources = tuple(row["resource_id"] for row in cursor.fetchall())
+    cursor.execute("SELECT role_id FROM roles WHERE role_name='public-view'")
+    role_id = cursor.fetchone()["role_id"]
     cursor.executemany(
-        ("INSERT INTO user_roles VALUES (:user_id, :role_id)"),
-        params)
+        "INSERT INTO user_roles(user_id, role_id, resource_id "
+        "VALUES(:user_id, :role_id, :resource_id)",
+        tuple({
+            "user_id": str(user.user_id),
+            "role_id": role_id,
+            "resource_id": resource_id
+        } for resource_id in public_resources))
+
+def assign_default_roles(cursor: db.DbCursor, user: User):
+    """Assign `user` some default roles."""
+    __assign_group_creator_role__(cursor, user)
+    __assign_public_view_role__(cursor, user)
 
 def revoke_user_role_by_name(cursor: db.DbCursor, user: User, role_name: str):
     """Revoke a role from `user` by the role's name"""
diff --git a/migrations/auth/20230925_01_TWJuR-add-new-public-view-role.py b/migrations/auth/20230925_01_TWJuR-add-new-public-view-role.py
new file mode 100644
index 0000000..1172034
--- /dev/null
+++ b/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)
+]