aboutsummaryrefslogtreecommitdiff
path: root/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2023-08-04 10:10:28 +0300
committerFrederick Muriuki Muriithi2023-08-04 10:20:09 +0300
commit8b7c598407a5fea9a3d78473e72df87606998cd4 (patch)
tree8526433a17eca6b511feb082a0574f9b15cb9469 /migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py
parentf7fcbbcc014686ac597b783a8dcb38b43024b9d6 (diff)
downloadgn-auth-8b7c598407a5fea9a3d78473e72df87606998cd4.tar.gz
Copy over files from GN3 repository.
Diffstat (limited to 'migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py')
-rw-r--r--migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py111
1 files changed, 111 insertions, 0 deletions
diff --git a/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py b/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py
new file mode 100644
index 0000000..1ef5ab0
--- /dev/null
+++ b/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py
@@ -0,0 +1,111 @@
+"""
+rework privileges schema
+"""
+import contextlib
+
+from yoyo import step
+
+__depends__ = {'20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader'}
+
+privileges = ( # format: (original_id, original_name, new_id, category)
+ ("13ec2a94-4f1a-442d-aad2-936ad6dd5c57", "delete-group",
+ "system:group:delete-group", "group-management"),
+ ("1c59eff5-9336-4ed2-a166-8f70d4cb012e", "delete-role",
+ "group:role:delete-role", "role-management"),
+ ("1fe61370-cae9-4983-bd6c-ce61050c510f", "delete-any-user",
+ "system:user:delete-user", "user-management"),
+ ("221660b1-df05-4be1-b639-f010269dbda9", "create-role",
+ "group:role:create-role", "role-management"),
+ ("2f980855-959b-4339-b80e-25d1ec286e21", "edit-resource",
+ "group:resource:edit-resource", "resource-management"),
+ ("3ebfe79c-d159-4629-8b38-772cf4bc2261", "view-group",
+ "system:group:view-group", "group-management"),
+ ("4842e2aa-38b9-4349-805e-0a99a9cf8bff", "create-group",
+ "system:group:create-group", "group-management"),
+ ("5103cc68-96f8-4ebb-83a4-a31692402c9b", "assign-role",
+ "group:user:assign-role", "role-management"),
+ ("519db546-d44e-4fdc-9e4e-25aa67548ab3", "masquerade",
+ "system:user:masquerade", "system-admin"),
+ ("52576370-b3c7-4e6a-9f7e-90e9dbe24d8f", "edit-group",
+ "system:group:edit-group", "group-management"),
+ ("7bcca363-cba9-4169-9e31-26bdc6179b28", "edit-role",
+ "group:role:edit-role", "role-management"),
+ ("7f261757-3211-4f28-a43f-a09b800b164d", "view-resource",
+ "group:resource:view-resource", "resource-management"),
+ ("80f11285-5079-4ec0-907c-06509f88a364", "assign-group-leader",
+ "system:user:assign-group-leader", "group-management"),
+ ("aa25b32a-bff2-418d-b0a2-e26b4a8f089b", "create-resource",
+ "group:resource:create-resource", "resource-management"),
+ ("ae4add8c-789a-4d11-a6e9-a306470d83d9", "add-group-member",
+ "group:user:add-group-member", "group-management"),
+ ("d2a070fd-e031-42fb-ba41-d60cf19e5d6d", "delete-resource",
+ "group:resource:delete-resource", "resource-management"),
+ ("d4afe2b3-4ca0-4edd-b37d-966535b5e5bd", "transfer-group-leadership",
+ "system:group:transfer-group-leader", "group-management"),
+ ("e7252301-6ee0-43ba-93ef-73b607cf06f6", "reset-any-password",
+ "system:user:reset-password", "user-management"),
+ ("f1bd3f42-567e-4965-9643-6d1a52ddee64", "remove-group-member",
+ "group:user:remove-group-member", "group-management"))
+
+def rework_privileges_table(cursor):
+ "rework the schema"
+ cursor.executemany(
+ ("UPDATE privileges SET privilege_id=:id "
+ "WHERE privilege_id=:old_id"),
+ ({"id": row[2], "old_id": row[0]} for row in privileges))
+ cursor.execute("ALTER TABLE privileges DROP COLUMN privilege_category")
+ cursor.execute("ALTER TABLE privileges DROP COLUMN privilege_name")
+
+def restore_privileges_table(cursor):
+ "restore the schema"
+ cursor.execute((
+ "CREATE TABLE privileges_restore ("
+ " privilege_id TEXT PRIMARY KEY,"
+ " privilege_name TEXT NOT NULL,"
+ " privilege_category TEXT NOT NULL DEFAULT 'common',"
+ " privilege_description TEXT"
+ ")"))
+ id_dict = {row[2]: {"id": row[0], "name": row[1], "cat": row[3]}
+ for row in privileges}
+ cursor.execute(
+ "SELECT privilege_id, privilege_description FROM privileges")
+ params = ({**id_dict[row[0]], "desc": row[1]} for row in cursor.fetchall())
+ cursor.executemany(
+ "INSERT INTO privileges_restore VALUES (:id, :name, :cat, :desc)",
+ params)
+ cursor.execute("DROP TABLE privileges")
+ cursor.execute("ALTER TABLE privileges_restore RENAME TO privileges")
+
+def update_privilege_ids_in_role_privileges(cursor):
+ """Update the ids to new form."""
+ cursor.executemany(
+ ("UPDATE role_privileges SET privilege_id=:new_id "
+ "WHERE privilege_id=:old_id"),
+ ({"new_id": row[2], "old_id": row[0]} for row in privileges))
+
+def restore_privilege_ids_in_role_privileges(cursor):
+ """Restore original ids"""
+ cursor.executemany(
+ ("UPDATE role_privileges SET privilege_id=:old_id "
+ "WHERE privilege_id=:new_id"),
+ ({"new_id": row[2], "old_id": row[0]} for row in privileges))
+
+def change_schema(conn):
+ """Change the privileges schema and IDs"""
+ with contextlib.closing(conn.cursor()) as cursor:
+ cursor.execute("PRAGMA foreign_keys=OFF")
+ rework_privileges_table(cursor)
+ update_privilege_ids_in_role_privileges(cursor)
+ cursor.execute("PRAGMA foreign_keys=ON")
+
+def restore_schema(conn):
+ """Change the privileges schema and IDs"""
+ with contextlib.closing(conn.cursor()) as cursor:
+ cursor.execute("PRAGMA foreign_keys=OFF")
+ restore_privilege_ids_in_role_privileges(cursor)
+ restore_privileges_table(cursor)
+ cursor.execute("PRAGMA foreign_keys=ON")
+
+steps = [
+ step(change_schema, restore_schema)
+]