1.1 --- a/core.sql	Thu Feb 22 11:24:55 2018 +0100
     1.2 +++ b/core.sql	Thu Feb 22 11:50:20 2018 +0100
     1.3 @@ -127,6 +127,7 @@
     1.4          "name"                  TEXT            UNIQUE,
     1.5          "identification"        TEXT            UNIQUE,
     1.6          "authentication"        TEXT,
     1.7 +        "role"                  BOOLEAN         NOT NULL DEFAULT FALSE,
     1.8          "location"              JSONB,
     1.9          "text_search_data"      TSVECTOR,
    1.10          CONSTRAINT "deleted_requires_locked"
    1.11 @@ -207,6 +208,17 @@
    1.12  COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
    1.13  
    1.14  
    1.15 +CREATE TABLE "agent" (
    1.16 +        PRIMARY KEY ("controlled_id", "controller_id"),
    1.17 +        "controlled_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.18 +        "controller_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.19 +        CONSTRAINT "controlled_id_and_controller_id_differ" CHECK (
    1.20 +            "controlled_id" != "controller_id" ) );
    1.21 +CREATE INDEX "agent_controller_id_idx" ON "agent" ("controller_id");
    1.22 +
    1.23 +COMMENT ON TABLE "agent" IS 'Privileges for role accounts';
    1.24 +
    1.25 +
    1.26  CREATE TABLE "verification" (
    1.27          "id"                    SERIAL8         PRIMARY KEY,
    1.28          "requested"             TIMESTAMPTZ,

     2.1 --- a/update/core-update.v4.0.0-v4.0.1.sql	Thu Feb 22 11:24:55 2018 +0100
     2.2 +++ b/update/core-update.v4.0.0-v4.0.1.sql	Thu Feb 22 11:50:20 2018 +0100
     2.3 @@ -4,6 +4,18 @@
     2.4    SELECT * FROM (VALUES ('4.0.1', 4, 0, 1))
     2.5    AS "subquery"("string", "major", "minor", "revision");
     2.6  
     2.7 +ALTER TABLE "member" ADD COLUMN "role" BOOLEAN NOT NULL DEFAULT FALSE;
     2.8 +
     2.9 +CREATE TABLE "agent" (
    2.10 +        PRIMARY KEY ("controlled_id", "controller_id"),
    2.11 +        "controlled_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.12 +        "controller_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.13 +        CONSTRAINT "controlled_id_and_controller_id_differ" CHECK (
    2.14 +            "controlled_id" != "controller_id" ) );
    2.15 +CREATE INDEX "agent_controller_id_idx" ON "agent" ("controller_id");
    2.16 +
    2.17 +COMMENT ON TABLE "agent" IS 'Privileges for role accounts';
    2.18 +
    2.19  CREATE OR REPLACE VIEW "expired_token" AS
    2.20    SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
    2.21      "token_type" = 'authorization' AND "used" AND EXISTS (