github.com/glennzw/gophish@v0.8.1-0.20190824020715-24fe998a3aa0/db/db_sqlite3/migrations/20190105192341_0.8.0_rbac.sql (about)

     1  
     2  -- +goose Up
     3  -- SQL in section 'Up' is executed when this migration is applied
     4  CREATE TABLE "roles" (
     5      "id"          INTEGER PRIMARY KEY AUTOINCREMENT,
     6      "slug"        VARCHAR(255) NOT NULL UNIQUE,
     7      "name"        VARCHAR(255) NOT NULL UNIQUE,
     8      "description" VARCHAR(255)
     9  );
    10  
    11  ALTER TABLE "users" ADD COLUMN "role_id" INTEGER;
    12  
    13  CREATE TABLE "permissions" (
    14      "id"          INTEGER PRIMARY KEY AUTOINCREMENT,
    15      "slug"        VARCHAR(255) NOT NULL UNIQUE,
    16      "name"        VARCHAR(255) NOT NULL UNIQUE,
    17      "description" VARCHAR(255)
    18  );
    19  
    20  
    21  CREATE TABLE "role_permissions" (
    22      "role_id"       INTEGER NOT NULL,
    23      "permission_id" INTEGER NOT NULL
    24  );
    25  
    26  INSERT INTO "roles" ("slug", "name", "description")
    27  VALUES
    28      ("admin", "Admin", "System administrator with full permissions"),
    29      ("user", "User", "User role with edit access to objects and campaigns");
    30  
    31  INSERT INTO "permissions" ("slug", "name", "description")
    32  VALUES
    33      ("view_objects", "View Objects", "View objects in Gophish"),
    34      ("modify_objects", "Modify Objects", "Create and edit objects in Gophish"),
    35      ("modify_system", "Modify System", "Manage system-wide configuration");
    36  
    37  -- Our rules for generating the admin user are:
    38  -- * The user with the name "admin"
    39  -- * OR the first user, if no "admin" user exists
    40  UPDATE "users" SET "role_id"=(
    41      SELECT "id" FROM "roles" WHERE "slug"="admin")
    42  WHERE "id"=(
    43      SELECT "id" FROM "users" WHERE "username"="admin" OR "id"=(SELECT MIN("id") FROM "users") LIMIT 1);
    44  
    45  -- Every other user will be considered a standard user account. The admin user
    46  -- will be able to change the role of any other user at any time.
    47  UPDATE "users" SET "role_id"=(
    48      SELECT "id" FROM "roles" WHERE "slug"="user")
    49  WHERE role_id IS NULL;
    50  
    51  -- Our default permission set will:
    52  -- * Allow admins the ability to do anything
    53  -- * Allow users to modify objects
    54  
    55  -- Allow any user to view objects
    56  INSERT INTO "role_permissions" ("role_id", "permission_id")
    57  SELECT r.id, p.id FROM roles AS r, "permissions" AS p
    58  WHERE r.id IN (SELECT "id" FROM roles WHERE "slug"="admin" OR "slug"="user")
    59  AND p.id=(SELECT "id" FROM "permissions" WHERE "slug"="view_objects");
    60  
    61  -- Allow admins and users to modify objects
    62  INSERT INTO "role_permissions" ("role_id", "permission_id")
    63  SELECT r.id, p.id FROM roles AS r, "permissions" AS p
    64  WHERE r.id IN (SELECT "id" FROM roles WHERE "slug"="admin" OR "slug"="user")
    65  AND p.id=(SELECT "id" FROM "permissions" WHERE "slug"="modify_objects");
    66  
    67  -- Allow admins to modify system level configuration
    68  INSERT INTO "role_permissions" ("role_id", "permission_id")
    69  SELECT r.id, p.id FROM roles AS r, "permissions" AS p
    70  WHERE r.id IN (SELECT "id" FROM roles WHERE "slug"="admin")
    71  AND p.id=(SELECT "id" FROM "permissions" WHERE "slug"="modify_system");
    72  
    73  -- +goose Down
    74  -- SQL section 'Down' is executed when this migration is rolled back
    75  DROP TABLE "roles"
    76  DROP TABLE "user_roles"
    77  DROP TABLE "permissions"