diff options
Diffstat (limited to 'migrations/auth')
| -rw-r--r-- | migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py | 111 |
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) +] |