auth: Allow non-member users to access group resources

Allow users that are not members of a particular group to be granted access to
that group's resources via an explicit role assignment. This is accomplished
by removing the `FOREIGN KEY(group_id, user_id)` constraint.

1 files changed, 6 insertions, 3 deletions

diff --git a/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py b/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py
index b683b03..9aa3667 100644
--- a/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py
+++ b/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py
@@ -15,12 +15,15 @@ steps = [
             role_id TEXT NOT NULL,
             resource_id TEXT NOT NULL,
             PRIMARY KEY (group_id, user_id, role_id, resource_id),
-            FOREIGN KEY (group_id, user_id)
-              REFERENCES group_users(group_id, user_id),
+            FOREIGN KEY (user_id)
+              REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY (group_id, role_id)
-              REFERENCES group_roles(group_id, role_id),
+              REFERENCES group_roles(group_id, role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY (group_id, resource_id)
               REFERENCES resources(group_id, resource_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS group_user_roles_on_resources"),