Migrations: Migration for initial privileges

* migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py: new
  migration.
* tests/unit/auth/test_migrations_insert_data_into_empty_table.py: test new
  migration.

2 files changed, 142 insertions, 0 deletions

diff --git a/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py b/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py
new file mode 100644
index 0000000..072f226
--- /dev/null
+++ b/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py
@@ -0,0 +1,66 @@
+"""
+Enumerate initial privileges
+"""
+
+from yoyo import step
+
+__depends__ = {'20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table'}
+
+steps = [
+    step(
+        """
+        INSERT INTO
+            privileges(privilege_id, privilege_name, privilege_category,
+                       privilege_description)
+        VALUES
+            -- group-management privileges
+            ('4842e2aa-38b9-4349-805e-0a99a9cf8bff', 'create-group',
+             'group-management', 'Create a group'),
+            ('3ebfe79c-d159-4629-8b38-772cf4bc2261', 'view-group',
+             'group-management', 'View the details of a group'),
+            ('52576370-b3c7-4e6a-9f7e-90e9dbe24d8f', 'edit-group',
+             'group-management', 'Edit the details of a group'),
+            ('13ec2a94-4f1a-442d-aad2-936ad6dd5c57', 'delete-group',
+             'group-management', 'Delete a group'),
+            ('ae4add8c-789a-4d11-a6e9-a306470d83d9', 'add-group-member',
+             'group-management', 'Add a user to a group'),
+            ('f1bd3f42-567e-4965-9643-6d1a52ddee64', 'remove-group-member',
+             'group-management', 'Remove a user from a group'),
+            ('80f11285-5079-4ec0-907c-06509f88a364', 'assign-group-leader',
+             'group-management', 'Assign user group-leader privileges'),
+            ('d4afe2b3-4ca0-4edd-b37d-966535b5e5bd',
+             'transfer-group-leadership', 'group-management',
+             'Transfer leadership of the group to some other member'),
+
+            -- resource-management privileges
+            ('aa25b32a-bff2-418d-b0a2-e26b4a8f089b', 'create-resource',
+             'resource-management', 'Create a resource object'),
+            ('7f261757-3211-4f28-a43f-a09b800b164d', 'view-resource',
+             'resource-management', 'view a resource and use it in computations'),
+            ('2f980855-959b-4339-b80e-25d1ec286e21', 'edit-resource',
+             'resource-management', 'edit/update a resource'),
+            ('d2a070fd-e031-42fb-ba41-d60cf19e5d6d', 'delete-resource',
+             'resource-management', 'Delete a resource'),
+
+            -- role-management privileges
+            ('221660b1-df05-4be1-b639-f010269dbda9', 'create-role',
+             'role-management', 'Create a new role'),
+            ('7bcca363-cba9-4169-9e31-26bdc6179b28', 'edit-role',
+             'role-management', 'edit/update an existing role'),
+            ('5103cc68-96f8-4ebb-83a4-a31692402c9b', 'assign-role',
+             'role-management', 'Assign a role to an existing user'),
+            ('1c59eff5-9336-4ed2-a166-8f70d4cb012e', 'delete-role',
+             'role-management', 'Delete an existing role'),
+
+            -- user-management privileges
+            ('e7252301-6ee0-43ba-93ef-73b607cf06f6', 'reset-any-password',
+             'user-management', 'Reset the password for any user'),
+            ('1fe61370-cae9-4983-bd6c-ce61050c510f', 'delete-any-user',
+             'user-management', 'Delete any user from the system'),
+
+            -- sytem-admin privileges
+            ('519db546-d44e-4fdc-9e4e-25aa67548ab3', 'masquerade',
+             'system-admin', 'Masquerade as some other user')
+        """,
+        "DELETE FROM privileges")
+]
diff --git a/tests/unit/auth/test_migrations_insert_data_into_empty_table.py b/tests/unit/auth/test_migrations_insert_data_into_empty_table.py
new file mode 100644
index 0000000..c89884f
--- /dev/null
+++ b/tests/unit/auth/test_migrations_insert_data_into_empty_table.py
@@ -0,0 +1,76 @@
+"""Test data insertion when migrations are run."""
+
+from contextlib import closing
+
+import pytest
+import sqlite3
+
+from gn3.migrations import get_migration, apply_migrations, rollback_migrations
+from tests.unit.auth.conftest import (
+    apply_single_migration, rollback_single_migration, migrations_up_to)
+
+test_params = (
+    ("20221113_01_7M0hv-enumerate-initial-privileges.py", "privileges", 19),)
+
+@pytest.mark.unit_test
+@pytest.mark.parametrize(
+    "migration_file,table,row_count", test_params)
+def test_apply_insert(
+        auth_testdb_path, auth_migrations_dir, backend, migration_file,
+        table, row_count):
+    """
+    GIVEN: A database migration
+    WHEN: The migration is applied
+    THEN: Ensure the given number of rows are inserted into the table
+    """
+    migration_path=f"{auth_migrations_dir}/{migration_file}"
+    older_migrations = migrations_up_to(migration_path, auth_migrations_dir)
+    the_migration = get_migration(migration_path)
+    apply_migrations(backend, older_migrations)
+    with closing(sqlite3.connect(auth_testdb_path)) as conn, closing(conn.cursor()) as cursor:
+        query = f"SELECT COUNT(*) FROM {table}"
+        cursor.execute(query)
+        result_before_migration = cursor.fetchall()
+        apply_single_migration(backend, the_migration)
+        cursor.execute(query)
+        result_after_migration = cursor.fetchall()
+
+    rollback_migrations(backend, older_migrations+[the_migration])
+    assert result_before_migration[0][0] == 0, (
+        "Expected empty table before initialisation")
+    assert result_after_migration[0][0] == row_count, (
+        f"Expected {row_count} rows")
+
+@pytest.mark.unit_test
+@pytest.mark.parametrize(
+    "migration_file,table,row_count", test_params)
+def test_rollback_insert(
+        auth_testdb_path, auth_migrations_dir, backend, migration_file,
+        table, row_count):
+    """
+    GIVEN: A database migration
+    WHEN: The migration is applied
+    THEN: Ensure the given number of rows are inserted into the table
+    """
+    migration_path=f"{auth_migrations_dir}/{migration_file}"
+    older_migrations = migrations_up_to(migration_path, auth_migrations_dir)
+    the_migration = get_migration(migration_path)
+    apply_migrations(backend, older_migrations)
+    with closing(sqlite3.connect(auth_testdb_path)) as conn, closing(conn.cursor()) as cursor:
+        query = f"SELECT COUNT(*) FROM {table}"
+        cursor.execute(query)
+        result_before_migration = cursor.fetchall()
+        apply_single_migration(backend, the_migration)
+        cursor.execute(query)
+        result_after_migration = cursor.fetchall()
+        rollback_single_migration(backend, the_migration)
+        cursor.execute(query)
+        result_after_rollback = cursor.fetchall()
+
+    rollback_migrations(backend, older_migrations)
+    assert result_before_migration[0][0] == 0, (
+        "Expected empty table before initialisation")
+    assert result_after_migration[0][0] == row_count, (
+        f"Expected {row_count} rows")
+    assert result_after_rollback[0][0] == 0, (
+        f"Expected empty table after rollback")