1 files changed, 70 insertions, 0 deletions
diff --git a/migrations/auth/20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members.py b/migrations/auth/20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members.py
new file mode 100644
index 0000000..95a6fbb
--- /dev/null
+++ b/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)
+]