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.

1 files changed, 61 insertions, 0 deletions

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)
+]