liquid_feedback_core
changeset 440:9055fd4de232
Added data structures to support externally managed accounts (e.g. LDAP)
author | jbe |
---|---|
date | Tue Jul 22 18:46:25 2014 +0200 (2014-07-22) |
parents | fc708fb5684f |
children | f5c78b0590c6 |
files | core.sql update/core-update.v3.0.3-v3.0.4.sql |
line diff
1.1 --- a/core.sql Wed Jul 16 16:54:40 2014 +0200 1.2 +++ b/core.sql Tue Jul 22 18:46:25 2014 +0200 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('3.0.3', 3, 0, 3)) 1.8 + SELECT * FROM (VALUES ('3.0.4', 3, 0, 4)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -107,6 +107,9 @@ 1.13 "last_delegation_check" TIMESTAMPTZ, 1.14 "login" TEXT UNIQUE, 1.15 "password" TEXT, 1.16 + "authority" TEXT, 1.17 + "authority_uid" TEXT, 1.18 + "authority_login" TEXT, 1.19 "locked" BOOLEAN NOT NULL DEFAULT FALSE, 1.20 "active" BOOLEAN NOT NULL DEFAULT FALSE, 1.21 "admin" BOOLEAN NOT NULL DEFAULT FALSE, 1.22 @@ -141,8 +144,15 @@ 1.23 "text_search_data" TSVECTOR, 1.24 CONSTRAINT "active_requires_activated_and_last_activity" 1.25 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)), 1.26 + CONSTRAINT "authority_requires_uid_and_vice_versa" 1.27 + CHECK ("authority" NOTNULL = "authority_uid" NOTNULL), 1.28 + CONSTRAINT "authority_uid_unique_per_authority" 1.29 + UNIQUE ("authority", "authority_uid"), 1.30 + CONSTRAINT "authority_login_requires_authority" 1.31 + CHECK ("authority" NOTNULL OR "authority_login" ISNULL), 1.32 CONSTRAINT "name_not_null_if_activated" 1.33 CHECK ("activated" ISNULL OR "name" NOTNULL) ); 1.34 +CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login"); 1.35 CREATE INDEX "member_active_idx" ON "member" ("active"); 1.36 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data"); 1.37 CREATE TRIGGER "update_text_search_data" 1.38 @@ -164,6 +174,9 @@ 1.39 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)'; 1.40 COMMENT ON COLUMN "member"."login" IS 'Login name'; 1.41 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; 1.42 +COMMENT ON COLUMN "member"."authority" IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)'; 1.43 +COMMENT ON COLUMN "member"."authority_uid" IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)'; 1.44 +COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)'; 1.45 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.'; 1.46 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".'; 1.47 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas'; 1.48 @@ -335,6 +348,9 @@ 1.49 "additional_secret" TEXT, 1.50 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours', 1.51 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL, 1.52 + "authority" TEXT, 1.53 + "authority_uid" TEXT, 1.54 + "authority_login" TEXT, 1.55 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE, 1.56 "lang" TEXT ); 1.57 CREATE INDEX "session_expiry_idx" ON "session" ("expiry"); 1.58 @@ -344,6 +360,9 @@ 1.59 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)'; 1.60 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks'; 1.61 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in'; 1.62 +COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation'; 1.63 +COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation'; 1.64 +COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation'; 1.65 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table'; 1.66 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language'; 1.67
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v3.0.3-v3.0.4.sql Tue Jul 22 18:46:25 2014 +0200 2.3 @@ -0,0 +1,34 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('3.0.4', 3, 0, 4)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +ALTER TABLE "member" ADD COLUMN "authority" TEXT; 2.11 +ALTER TABLE "member" ADD COLUMN "authority_uid" TEXT; 2.12 +ALTER TABLE "member" ADD COLUMN "authority_login" TEXT; 2.13 + 2.14 +COMMENT ON COLUMN "member"."authority" IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)'; 2.15 +COMMENT ON COLUMN "member"."authority_uid" IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)'; 2.16 +COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)'; 2.17 + 2.18 +ALTER TABLE "member" ADD CONSTRAINT "authority_requires_uid_and_vice_versa" 2.19 + CHECK ("authority" NOTNULL = "authority_uid" NOTNULL); 2.20 + 2.21 +ALTER TABLE "member" ADD CONSTRAINT "authority_uid_unique_per_authority" 2.22 + UNIQUE ("authority", "authority_uid"); 2.23 + 2.24 +ALTER TABLE "member" ADD CONSTRAINT "authority_login_requires_authority" 2.25 + CHECK ("authority" NOTNULL OR "authority_login" ISNULL); 2.26 + 2.27 +CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login"); 2.28 + 2.29 +ALTER TABLE "session" ADD COLUMN "authority" TEXT; 2.30 +ALTER TABLE "session" ADD COLUMN "authority_uid" TEXT; 2.31 +ALTER TABLE "session" ADD COLUMN "authority_login" TEXT; 2.32 + 2.33 +COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation'; 2.34 +COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation'; 2.35 +COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation'; 2.36 + 2.37 +COMMIT;