liquid_feedback_core
changeset 271:02a72de117e9
Work on OAuth2
author | jbe |
---|---|
date | Thu Aug 09 00:11:01 2012 +0200 (2012-08-09) |
parents | b555a544c724 |
children | f9c324f0cfe5 |
files | core.sql |
line diff
1.1 --- a/core.sql Wed Aug 08 18:48:13 2012 +0200 1.2 +++ b/core.sql Thu Aug 09 00:11:01 2012 +0200 1.3 @@ -182,31 +182,40 @@ 1.4 ON DELETE CASCADE ON UPDATE CASCADE, 1.5 "client_identifier" TEXT NOT NULL, 1.6 "client_secret" TEXT, 1.7 - "member_authorization" BOOLEAN NOT NULL, 1.8 - "public_access_level" "api_access_level", 1.9 - "access_level" "api_access_level" NOT NULL, 1.10 - "validity_period" INTERVAL NOT NULL, 1.11 + "code_grant" BOOLEAN NOT NULL, 1.12 + "token_grant" BOOLEAN NOT NULL, 1.13 + "client_grant" BOOLEAN NOT NULL, 1.14 + "code_grant_validity_period" INTERVAL, 1.15 + "access_level" "api_access_level", 1.16 + "client_grant_access_level" "api_access_level", 1.17 "last_usage" TIMESTAMPTZ NOT NULL, 1.18 CONSTRAINT "system_clients_require_name" 1.19 CHECK ("name" NOTNULL OR "member_id" ISNULL), 1.20 - CONSTRAINT "public_access_level_set_if_and_only_if_system_client" 1.21 - CHECK ("member_id" ISNULL = "public_access_level" NOTNULL) ); 1.22 + CONSTRAINT "code_grant_requires_validity_period" 1.23 + CHECK ("code_grant"=FALSE OR "code_grant_validity_period" NOTNULL), 1.24 + CONSTRAINT "code_or_token_grant_requires_access_level" 1.25 + CHECK (("code_grant"=FALSE AND "token_grant"=FALSE) OR "access_level" NOTNULL), 1.26 + CONSTRAINT "client_grant_requires_client_grant_access_level" 1.27 + CHECK ("client_grant"=FALSE OR "client_grant_access_level" NOTNULL) ); 1.28 CREATE UNIQUE INDEX "api_client_non_member_client_identifier_idx" 1.29 ON "api_client" ("client_identifier") WHERE "member_id" ISNULL; 1.30 1.31 COMMENT ON TABLE "api_client" IS 'Registered OAuth2 client for a member'; 1.32 1.33 -COMMENT ON COLUMN "api_client"."member_id" IS 'Member, who registered the client for him/herself, or NULL for clients registered by administrator'; 1.34 -COMMENT ON COLUMN "api_client"."name" IS 'Name of the client as chosen by member or administrator, NULL means unnamed'; 1.35 -COMMENT ON COLUMN "api_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint if "member_authorization" is set to TRUE'; 1.36 -COMMENT ON COLUMN "api_client"."client_secret" IS 'Secret for client authentication, enables OAuth2 Client Credentials Grant when set'; 1.37 -COMMENT ON COLUMN "api_client"."member_authorization" IS 'Allow OAuth2 Authorization Code Grant and Implicit Grant, in which case the "client_identifier" is used as the redirection endpoint'; 1.38 -COMMENT ON COLUMN "api_client"."public_access_level" IS 'Access level for OAuth2 Client Credentials Grant'; 1.39 -COMMENT ON COLUMN "api_client"."access_level" IS 'Access level for OAuth2 Authorization Code Grant and Implicit Grant'; 1.40 -COMMENT ON COLUMN "api_client"."validity_period" IS 'Period after which an entry in the "api_access" table expires'; 1.41 - 1.42 - 1.43 -CREATE TABLE "api_access" ( 1.44 +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.45 +COMMENT ON COLUMN "api_client"."member_id" IS 'Member, who registered the client for him/herself, or NULL for clients registered by administrator'; 1.46 +COMMENT ON COLUMN "api_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint if "authorization_code_grant" or "implicit_grant" is set to TRUE'; 1.47 +COMMENT ON COLUMN "api_client"."client_secret" IS 'Secret for client authentication'; 1.48 +COMMENT ON COLUMN "api_client"."code_grant" IS 'Enable OAuth2 Authorization Code Grant'; 1.49 +COMMENT ON COLUMN "api_client"."token_grant" IS 'Enable OAuth2 Implicit Grant'; 1.50 +COMMENT ON COLUMN "api_client"."client_grant" IS 'Enable OAuth2 Client Credentials Grant'; 1.51 +COMMENT ON COLUMN "api_client"."code_grant_validity_period" IS 'Validity period of OAuth2 Authorization Code Grant, after which no more refresh is possible'; 1.52 +COMMENT ON COLUMN "api_client"."access_level" IS 'Access level for OAuth2 Authorization Code Grant and Implicit Grant'; 1.53 +COMMENT ON COLUMN "api_client"."client_grant_access_level" IS 'Access level for OAuth2 Authorization Code Grant and Implicit Grant'; 1.54 +COMMENT ON COLUMN "api_client"."last_usage" IS 'Date/time when this client registration was last used'; 1.55 + 1.56 + 1.57 +CREATE TABLE "api_code_grant" ( 1.58 "id" SERIAL8 PRIMARY KEY, 1.59 "api_client_id" INT8 NOT NULL REFERENCES "api_client" ("id") 1.60 ON DELETE CASCADE ON UPDATE CASCADE, 1.61 @@ -226,13 +235,13 @@ 1.62 CONSTRAINT "old_refresh_token_requires_current_refresh_token" 1.63 CHECK ("refresh_token" NOTNULL OR "old_refresh_token" ISNULL) ); 1.64 1.65 -COMMENT ON TABLE "api_access" IS 'Issued OAuth2 authorization codes and refresh tokens'; 1.66 - 1.67 -COMMENT ON COLUMN "api_client"."validity_period" IS 'Period after which an entry in the "api_access" table expires'; 1.68 -COMMENT ON COLUMN "api_access"."created" IS 'Date/time when authorization code (or first refresh token when there is no authorization code) has been created'; 1.69 -COMMENT ON COLUMN "api_access"."authorization_code" IS 'OAuth2 authorization code (only valid for a very short time after it has been created)'; 1.70 -COMMENT ON COLUMN "api_access"."refreshed" IS 'Date/time of last refresh'; 1.71 -COMMENT ON COLUMN "api_access"."refresh_token" IS 'OAuth2 refresh token'; 1.72 +COMMENT ON TABLE "api_code_grant" IS 'Issued OAuth2 authorization codes and refresh tokens'; 1.73 + 1.74 +COMMENT ON COLUMN "api_code_grant"."validity_period" IS 'Period after which no more refreshing is possible'; 1.75 +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.76 +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.77 +COMMENT ON COLUMN "api_code_grant"."refreshed" IS 'Date/time of last refresh'; 1.78 +COMMENT ON COLUMN "api_code_grant"."refresh_token" IS 'OAuth2 refresh token'; 1.79 1.80 1.81 CREATE TABLE "member_history" (