Migration: Assign 'resource:user:assign-role' privilege to sysadmins.

Allow system administrators to assign roles against the system itself.

1 files changed, 39 insertions, 0 deletions

diff --git a/gn_auth/migrations/auth/20260831_01_VZp52-assign-resource-user-assign-role-to-the-system-administrator-role.py b/gn_auth/migrations/auth/20260831_01_VZp52-assign-resource-user-assign-role-to-the-system-administrator-role.py
new file mode 100644
index 0000000..4dd3f68
--- /dev/null
+++ b/gn_auth/migrations/auth/20260831_01_VZp52-assign-resource-user-assign-role-to-the-system-administrator-role.py
@@ -0,0 +1,39 @@
+"""
+Assign 'resource:user:assign-role' to the 'system-administrator' role
+"""
+import contextlib
+
+from yoyo import step
+
+__depends__ = {'20260826_01_6Hzl8-add-system-user-create-user-privilege-to-system-administrator-role'}
+
+
+def fetch_system_admin_role_id(cursor):
+    cursor.execute("SELECT role_id FROM roles WHERE role_name='system-administrator'")
+    return cursor.fetchone()[0]
+
+
+def assign_assign_role_to_sys_admin(conn):
+    """Assign 'resource:user:assign-role' privilege to the
+    'system-administrator' role."""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "INSERT INTO role_privileges(role_id, privilege_id) "
+            "VALUES (?, ?) "
+            "ON CONFLICT (role_id, privilege_id) DO NOTHING",
+            (fetch_system_admin_role_id(cursor), "resource:user:assign-role"))
+
+
+def revoke_assign_role_from_sys_admin(conn):
+    """Revoke 'resource:user:assign-role' privilege from the
+    'system-administrator' role."""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "DELETE FROM role_privileges "
+            "WHERE role_id=? AND privilege_id=?",
+            (fetch_system_admin_role_id(cursor), "resource:user:assign-role"))
+
+
+steps = [
+    step(assign_assign_role_to_sys_admin, revoke_assign_role_from_sys_admin)
+]