liquid_feedback_core
changeset 279:03897de17853
Work on tables for OAuth2
author | jbe |
---|---|
date | Wed Aug 15 01:13:44 2012 +0200 (2012-08-15) |
parents | 0a42a2600a2b |
children | 5a32f862b2dd |
files | core.sql |
line diff
1.1 --- a/core.sql Fri Aug 10 21:39:50 2012 +0200 1.2 +++ b/core.sql Wed Aug 15 01:13:44 2012 +0200 1.3 @@ -170,6 +170,8 @@ 1.4 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile'; 1.5 1.6 1.7 +-- DEPRECATED API TABLES -- 1.8 + 1.9 CREATE TYPE "application_access_level" AS ENUM 1.10 ('member', 'full', 'pseudonymous', 'anonymous'); 1.11 1.12 @@ -189,6 +191,10 @@ 1.13 1.14 COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API'; 1.15 1.16 +-- END OF DEPRECARED API TABLES -- 1.17 + 1.18 + 1.19 +-- NEW PRELIMINARY API TABLES -- 1.20 1.21 CREATE TYPE "api_access_level" AS ENUM ( 1.22 'none', 'anonymous', 'authors_pseudonymous', 'all_pseudonymous', 'everything', 'member' ); 1.23 @@ -196,57 +202,75 @@ 1.24 COMMENT ON TYPE "api_access_level" IS 'PRELIMINARY, SUBJECT TO CHANGE! Access scope for API consumers.'; 1.25 1.26 1.27 -CREATE TABLE "api_client" ( 1.28 +CREATE TABLE "registered_client" ( 1.29 + UNIQUE ("client_identifier", "id"), -- index needed for foreign-key on table "authorized_client" 1.30 "id" SERIAL8 PRIMARY KEY, 1.31 - "name" TEXT, 1.32 - UNIQUE ("member_id", "client_identifier"), 1.33 - "member_id" INT4 REFERENCES "member" ("id") 1.34 - ON DELETE CASCADE ON UPDATE CASCADE, 1.35 + "name" TEXT NOT NULL, 1.36 "client_identifier" TEXT NOT NULL, 1.37 "client_secret" TEXT, 1.38 "code_grant" BOOLEAN NOT NULL, 1.39 "implicit_grant" BOOLEAN NOT NULL, 1.40 "client_grant" BOOLEAN NOT NULL, 1.41 - "code_grant_validity_period" INTERVAL, 1.42 - "code_grant_multiple" BOOLEAN, 1.43 - "access_level" "api_access_level", 1.44 - "client_grant_access_level" "api_access_level", 1.45 - "last_usage" TIMESTAMPTZ NOT NULL, 1.46 - CONSTRAINT "system_clients_require_name" 1.47 - CHECK ("name" NOTNULL OR "member_id" ISNULL), 1.48 - CONSTRAINT "code_grant_requires_validity_period" 1.49 - CHECK ("code_grant"=FALSE OR "code_grant_validity_period" NOTNULL), 1.50 + "access_level" "api_access_level", 1.51 + "client_grant_access_level" "api_access_level", 1.52 + "single_token" BOOLEAN NOT NULL DEFAULT FALSE, 1.53 + "always_authorized" BOOLEAN NOT NULL DEFAULT FALSE, 1.54 + "auth_duration" INTERVAL, 1.55 + "login_duration" INTERVAL, 1.56 + "refresh_duration" INTERVAL, 1.57 + "access_duration" INTERVAL, 1.58 CONSTRAINT "code_or_implicit_grant_requires_access_level" 1.59 CHECK (("code_grant"=FALSE AND "implicit_grant"=FALSE) OR "access_level" NOTNULL), 1.60 CONSTRAINT "client_grant_requires_client_grant_access_level" 1.61 CHECK ("client_grant"=FALSE OR "client_grant_access_level" NOTNULL) ); 1.62 -CREATE UNIQUE INDEX "api_client_non_member_client_identifier_idx" 1.63 - ON "api_client" ("client_identifier") WHERE "member_id" ISNULL; 1.64 - 1.65 -COMMENT ON TABLE "api_client" IS 'PRELIMINARY, SUBJECT TO CHANGE! Registered OAuth2 client for a member'; 1.66 - 1.67 -COMMENT ON COLUMN "api_client"."name" IS 'Name of the client as chosen by member or administrator, NULL is allowed for unnamed member-registered clients'; 1.68 -COMMENT ON COLUMN "api_client"."member_id" IS 'Member, who registered the client for him/herself, or NULL for clients registered by administrator'; 1.69 -COMMENT ON COLUMN "api_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint if "code_grant" or "implicit_grant" is set to TRUE'; 1.70 -COMMENT ON COLUMN "api_client"."client_secret" IS 'Secret for client authentication'; 1.71 -COMMENT ON COLUMN "api_client"."code_grant" IS 'Enable OAuth2 Authorization Code Grant'; 1.72 -COMMENT ON COLUMN "api_client"."implicit_grant" IS 'Enable OAuth2 Implicit Grant'; 1.73 -COMMENT ON COLUMN "api_client"."client_grant" IS 'Enable OAuth2 Client Credentials Grant'; 1.74 -COMMENT ON COLUMN "api_client"."code_grant_validity_period" IS 'Maximum validity period of OAuth2 Authorization Code Grant, after which no more refresh is possible'; 1.75 -COMMENT ON COLUMN "api_client"."code_grant_multiple" IS 'If set to FALSE, invalidates previously issued OAuth2 Refresh Tokens during authorization; If set to NULL, the member is asked interactively during authorization, whether previously issued authorizations shall be revoked'; 1.76 -COMMENT ON COLUMN "api_client"."access_level" IS 'Maximum access level for OAuth2 Authorization Code Grant and Implicit Grant'; 1.77 -COMMENT ON COLUMN "api_client"."client_grant_access_level" IS 'Maximum access level for OAuth2 Authorization Code Grant and Implicit Grant'; 1.78 -COMMENT ON COLUMN "api_client"."last_usage" IS 'Date/time when this client registration was last used'; 1.79 - 1.80 - 1.81 -CREATE TABLE "api_code_grant" ( 1.82 + 1.83 +COMMENT ON TABLE "registered_client" IS 'PRELIMINARY, SUBJECT TO CHANGE! OAuth2 client registered by administrator'; 1.84 + 1.85 +COMMENT ON COLUMN "registered_client"."name" IS 'Name of the registered client'; 1.86 +COMMENT ON COLUMN "registered_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint if "code_grant" or "implicit_grant" is set to TRUE'; 1.87 +COMMENT ON COLUMN "registered_client"."client_secret" IS 'Secret for client authentication'; 1.88 +COMMENT ON COLUMN "registered_client"."code_grant" IS 'Enable OAuth2 Authorization Code Grant'; 1.89 +COMMENT ON COLUMN "registered_client"."implicit_grant" IS 'Enable OAuth2 Implicit Grant'; 1.90 +COMMENT ON COLUMN "registered_client"."client_grant" IS 'Enable OAuth2 Client Credentials Grant'; 1.91 +COMMENT ON COLUMN "registered_client"."access_level" IS 'Maximum access level for OAuth2 Authorization Code Grant and Implicit Grant'; 1.92 +COMMENT ON COLUMN "registered_client"."client_grant_access_level" IS 'Maximum access level for OAuth2 Client Credentials Grant'; 1.93 +COMMENT ON COLUMN "registered_client"."single_token" IS 'Allow only one valid refresh token'; 1.94 +COMMENT ON COLUMN "registered_client"."always_authorized" IS 'Members do not need to authorize the client'; 1.95 +COMMENT ON COLUMN "registered_client"."auth_duration" IS 'Duration of authorization by member'; 1.96 +COMMENT ON COLUMN "registered_client"."login_duration" IS 'Life time of refresh code chain'; 1.97 +COMMENT ON COLUMN "registered_client"."refresh_duration" IS 'Life time of a refresh code'; 1.98 +COMMENT ON COLUMN "registered_client"."access_duration" IS 'Life time of an access code'; 1.99 + 1.100 + 1.101 +CREATE TABLE "authorized_client" ( 1.102 "id" SERIAL8 PRIMARY KEY, 1.103 - "api_client_id" INT8 NOT NULL REFERENCES "api_client" ("id") 1.104 + "registered_client_id" INT8 REFERENCES "registered_client" ("id") 1.105 ON DELETE CASCADE ON UPDATE CASCADE, 1.106 - "member_id" INT4 REFERENCES "member" ("id") 1.107 + "client_identifier" TEXT NOT NULL, 1.108 + FOREIGN KEY ("registered_client_id", "client_identifier") 1.109 + REFERENCES "registered_client" ("id", "client_identifier") 1.110 + ON DELETE CASCADE ON UPDATE CASCADE, 1.111 + "member_id" INT4 NOT NULL REFERENCES "member" ("id") 1.112 ON DELETE CASCADE ON UPDATE CASCADE, 1.113 "access_level" "api_access_level" NOT NULL, 1.114 - "validity_period" INTERVAL NOT NULL, 1.115 + "first_auth" TIMESTAMPTZ NOT NULL, 1.116 + "last_auth" TIMESTAMPTZ NOT NULL, 1.117 + UNIQUE ("client_identifier", "member_id") ); 1.118 + 1.119 +COMMENT ON TABLE "authorized_client" IS 'PRELIMINARY, SUBJECT TO CHANGE! OAuth2 client authorized by member, or automatically authorized for a member if "registered_client"."always_authorized" is set'; 1.120 + 1.121 +COMMENT ON COLUMN "authorized_client"."registered_client_id" IS 'Set, if client is a registered client'; 1.122 +COMMENT ON COLUMN "authorized_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint'; 1.123 +COMMENT ON COLUMN "authorized_client"."member_id" IS 'Member who authorized the client'; 1.124 +COMMENT ON COLUMN "authorized_client"."access_level" IS 'Authorized access level'; 1.125 +COMMENT ON COLUMN "authorized_client"."first_auth" IS 'Date/time of initial authorization'; 1.126 +COMMENT ON COLUMN "authorized_client"."last_auth" IS 'Date/time of last authorization refresh'; 1.127 + 1.128 + 1.129 +CREATE TABLE "authorized_client_token" ( 1.130 + "id" SERIAL8 PRIMARY KEY, 1.131 + "authorized_client_id" INT8 NOT NULL REFERENCES "authorized_client" 1.132 + ON DELETE CASCADE ON UPDATE CASCADE, 1.133 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.134 "authorization_code" TEXT, 1.135 "refreshed" TIMESTAMPTZ, 1.136 @@ -259,13 +283,14 @@ 1.137 CONSTRAINT "old_refresh_token_requires_current_refresh_token" 1.138 CHECK ("refresh_token" NOTNULL OR "old_refresh_token" ISNULL) ); 1.139 1.140 -COMMENT ON TABLE "api_code_grant" IS 'PRELIMINARY, SUBJECT TO CHANGE! Issued OAuth2 authorization codes and refresh tokens'; 1.141 - 1.142 -COMMENT ON COLUMN "api_code_grant"."validity_period" IS 'Period after which no more refreshing is possible'; 1.143 -COMMENT ON COLUMN "api_code_grant"."created" IS 'Date/time when authorization code (or first refresh token when there is no authorization code) has been created'; 1.144 -COMMENT ON COLUMN "api_code_grant"."authorization_code" IS 'OAuth2 authorization code (only valid for a very short time after it has been created)'; 1.145 -COMMENT ON COLUMN "api_code_grant"."refreshed" IS 'Date/time of last refresh'; 1.146 -COMMENT ON COLUMN "api_code_grant"."refresh_token" IS 'OAuth2 refresh token'; 1.147 +COMMENT ON TABLE "authorized_client_token" IS 'PRELIMINARY, SUBJECT TO CHANGE! Issued OAuth2 authorization codes and refresh tokens'; 1.148 + 1.149 +COMMENT ON COLUMN "authorized_client_token"."created" IS 'Date/time when authorization code (or first refresh token when there is no authorization code) has been created'; 1.150 +COMMENT ON COLUMN "authorized_client_token"."authorization_code" IS 'OAuth2 authorization code (only valid for a very short time after it has been created)'; 1.151 +COMMENT ON COLUMN "authorized_client_token"."refreshed" IS 'Date/time of last refresh'; 1.152 +COMMENT ON COLUMN "authorized_client_token"."refresh_token" IS 'OAuth2 refresh token'; 1.153 + 1.154 +-- END OF NEW PRELIMINARY API TABLES -- 1.155 1.156 1.157 CREATE TABLE "member_history" (