1 files changed, 79 insertions, 0 deletions
diff --git a/gn_auth/migrations/auth/20260901_01_asMmm-revoke-system-administration-role-on-non-system-resources.py b/gn_auth/migrations/auth/20260901_01_asMmm-revoke-system-administration-role-on-non-system-resources.py
new file mode 100644
index 0000000..cb4b9bc
--- /dev/null
+++ b/gn_auth/migrations/auth/20260901_01_asMmm-revoke-system-administration-role-on-non-system-resources.py
@@ -0,0 +1,79 @@
+"""
+Revoke system-administration role on non-system resources.
+
+Migration `20250729_03_oCvvq-grant-role-to-all-resources-to-sys-admin-users.py`
+assigns the "system-administrator" role on all resources to the system-admin
+users. This is not the correct way of things.
+
+The correct way is that the "system-administrator" role can only ever be applied
+against the system itself, and the sys-admin can masquerade as whatever user
+they need to in order to accomplish their goal.
+"""
+import itertools
+import contextlib
+
+from yoyo import step
+
+__depends__ = {'20260831_01_VZp52-assign-resource-user-assign-role-to-the-system-administrator-role'}
+
+
+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 revoke_sys_admin_role_on_non_system_resources(conn):
+    """Revoke the 'system-administration' role from non-system resources."""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "DELETE FROM user_roles WHERE role_id=? AND resource_id != ?",
+            (system_administrator_role_id(cursor), system_resource_id(cursor)))
+
+
+def restore_sys_admin_role_on_non_system_resources(conn):
+    """..."""
+    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))))
+
+
+steps = [
+    step(revoke_sys_admin_role_on_non_system_resources,
+         restore_sys_admin_role_on_non_system_resources)
+]