liquid_feedback_core

changeset 580:78f6833f5f19

Further work on role accounts (role_verification, real_member_id)
author jbe
date Sun Mar 04 17:59:11 2018 +0100 (2018-03-04)
parents f6b84db4dd0c
children 1cb6710fc429
files core.sql update/core-update.v4.0.0-v4.0.1.sql
line diff
     1.1 --- a/core.sql	Fri Mar 02 16:52:32 2018 +0100
     1.2 +++ b/core.sql	Sun Mar 04 17:59:11 2018 +0100
     1.3 @@ -254,6 +254,36 @@
     1.4  COMMENT ON COLUMN "verification"."comment"              IS 'Administrative comment';
     1.5  
     1.6  
     1.7 +-- TODO: merge tables "verification" and "role_verification"
     1.8 +
     1.9 +CREATE TABLE "role_verification" (
    1.10 +        "id"                    SERIAL8         PRIMARY KEY,
    1.11 +        "requested"             TIMESTAMPTZ,
    1.12 +        "request_origin"        JSONB,
    1.13 +        "request_data"          JSONB,
    1.14 +        "requesting_member_id"  INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.15 +        "requesting_real_member_id"  INT4       REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.16 +        "verifying_member_id"   INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.17 +        "verified_member_id"    INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.18 +        "verified"              TIMESTAMPTZ,
    1.19 +        "verification_data"     JSONB,
    1.20 +        "denied"                TIMESTAMPTZ,
    1.21 +        "comment"               TEXT,
    1.22 +        CONSTRAINT "verified_and_denied_conflict" CHECK (
    1.23 +          "verified" ISNULL OR "denied" ISNULL ) );
    1.24 +CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested");
    1.25 +CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;
    1.26 +CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id");
    1.27 +CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id");
    1.28 +CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified");
    1.29 +CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied");
    1.30 +
    1.31 +COMMENT ON TABLE "role_verification" IS 'Request to verify a role account (see table "verification" for documentation of columns not documented for this table)';
    1.32 +
    1.33 +COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify';
    1.34 +COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification';
    1.35 +
    1.36 +
    1.37  CREATE TABLE "member_settings" (
    1.38          "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.39          "settings"              JSONB           NOT NULL CHECK (jsonb_typeof("settings") = 'object') );
    1.40 @@ -366,6 +396,7 @@
    1.41          "logout_token"          TEXT,
    1.42          "expiry"                TIMESTAMPTZ     NOT NULL DEFAULT now() + '24 hours',
    1.43          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE SET NULL,
    1.44 +        "real_member_id"        INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.45          "authority"             TEXT,
    1.46          "authority_uid"         TEXT,
    1.47          "authority_login"       TEXT,
    1.48 @@ -378,7 +409,8 @@
    1.49  COMMENT ON COLUMN "session"."ident"             IS 'Secret session identifier (i.e. random string)';
    1.50  COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
    1.51  COMMENT ON COLUMN "session"."logout_token"      IS 'Optional token to authorize logout through external component';
    1.52 -COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in';
    1.53 +COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in, or role account in use';
    1.54 +COMMENT ON COLUMN "session"."real_member_id"    IS 'Reference to member, who is really logged in (real person rather than role account)';
    1.55  COMMENT ON COLUMN "session"."authority"         IS 'Temporary store for "member"."authority" during member account creation';
    1.56  COMMENT ON COLUMN "session"."authority_uid"     IS 'Temporary store for "member"."authority_uid" during member account creation';
    1.57  COMMENT ON COLUMN "session"."authority_login"   IS 'Temporary store for "member"."authority_login" during member account creation';
     2.1 --- a/update/core-update.v4.0.0-v4.0.1.sql	Fri Mar 02 16:52:32 2018 +0100
     2.2 +++ b/update/core-update.v4.0.0-v4.0.1.sql	Sun Mar 04 17:59:11 2018 +0100
     2.3 @@ -16,6 +16,38 @@
     2.4  
     2.5  COMMENT ON TABLE "agent" IS 'Privileges for role accounts';
     2.6  
     2.7 +ALTER TABLE "session" ADD COLUMN "real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
     2.8 +
     2.9 +COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in, or role account in use';
    2.10 +COMMENT ON COLUMN "session"."real_member_id"    IS 'Reference to member, who is really logged in (real person rather than role account)';
    2.11 +
    2.12 +CREATE TABLE "role_verification" (
    2.13 +        "id"                    SERIAL8         PRIMARY KEY,
    2.14 +        "requested"             TIMESTAMPTZ,
    2.15 +        "request_origin"        JSONB,
    2.16 +        "request_data"          JSONB,
    2.17 +        "requesting_member_id"  INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    2.18 +        "requesting_real_member_id"  INT4       REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    2.19 +        "verifying_member_id"   INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    2.20 +        "verified_member_id"    INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    2.21 +        "verified"              TIMESTAMPTZ,
    2.22 +        "verification_data"     JSONB,
    2.23 +        "denied"                TIMESTAMPTZ,
    2.24 +        "comment"               TEXT,
    2.25 +        CONSTRAINT "verified_and_denied_conflict" CHECK (
    2.26 +          "verified" ISNULL OR "denied" ISNULL ) );
    2.27 +CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested");
    2.28 +CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;
    2.29 +CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id");
    2.30 +CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id");
    2.31 +CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified");
    2.32 +CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied");
    2.33 +
    2.34 +COMMENT ON TABLE "role_verification" IS 'Request to verify a role account (see table "verification" for documentation of columns not documented for this table)';
    2.35 +
    2.36 +COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify';
    2.37 +COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification';
    2.38 +
    2.39  ALTER TABLE "ignored_area" DROP CONSTRAINT "ignored_area_member_id_fkey";
    2.40  ALTER TABLE "ignored_area" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
    2.41  

Impressum / About Us