liquid_feedback_core
changeset 266:d1d5dca15491
API system clients and redundancy in API access table
author | jbe |
---|---|
date | Wed Aug 08 14:10:14 2012 +0200 (2012-08-08) |
parents | e3c9f737a096 |
children | 5bf2c973ec3c |
files | core.sql |
line diff
1.1 --- a/core.sql Wed Aug 08 05:35:49 2012 +0200 1.2 +++ b/core.sql Wed Aug 08 14:10:14 2012 +0200 1.3 @@ -170,6 +170,31 @@ 1.4 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile'; 1.5 1.6 1.7 +CREATE TYPE "api_access_level" AS ENUM ( 1.8 + 'none', 'anonymous', 'authors_pseudonymous', 'all_pseudonymous', 'everything', 'member' ); 1.9 + 1.10 + 1.11 +CREATE TABLE "system_api_client" ( 1.12 + "id" INT4 PRIMARY KEY, 1.13 + "name" TEXT NOT NULL UNIQUE, 1.14 + "client_identifier" TEXT NOT NULL UNIQUE, 1.15 + "client_secret" TEXT, 1.16 + "redirection_endpoint" TEXT, 1.17 + "access_level" "api_access_level" NOT NULL, 1.18 + "multi_access" BOOLEAN NOT NULL, 1.19 + "validity_period" INTERVAL NOT NULL, 1.20 + "last_usage" TIMESTAMPTZ NOT NULL ); 1.21 + 1.22 +COMMENT ON TABLE "system_api_client" IS 'Registered OAuth2 clients by the system administrator'; 1.23 + 1.24 +COMMENT ON COLUMN "system_api_client"."name" IS 'Name of the client as chosen by member'; 1.25 +COMMENT ON COLUMN "system_api_client"."client_identifier" IS 'OAuth2 client id'; 1.26 +COMMENT ON COLUMN "system_api_client"."client_secret" IS 'Secret for client authentication, enables OAuth2 Client Credentials Grant when set'; 1.27 +COMMENT ON COLUMN "system_api_client"."redirection_endpoint" IS 'OAuth2 redirection endpoint, enables OAuth2 Authorization Code Grant and Implicit Grant when set'; 1.28 +COMMENT ON COLUMN "system_api_client"."multi_access" IS 'TRUE = Allow to issue more than one access token simultanously'; 1.29 +COMMENT ON COLUMN "system_api_client"."validity_period" IS 'Life time of an OAuth2 access token'; 1.30 + 1.31 + 1.32 CREATE TABLE "member_api_client" ( 1.33 "id" SERIAL8 PRIMARY KEY, 1.34 UNIQUE ("member_id", "name"), 1.35 @@ -179,6 +204,7 @@ 1.36 "name" TEXT NOT NULL, 1.37 "client_identifier" TEXT NOT NULL, 1.38 "redirection_endpoint" TEXT NOT NULL, 1.39 + "access_level" "api_access_level" NOT NULL, 1.40 "multi_access" BOOLEAN NOT NULL, 1.41 "validity_period" INTERVAL NOT NULL, 1.42 "last_usage" TIMESTAMPTZ NOT NULL ); 1.43 @@ -192,21 +218,28 @@ 1.44 COMMENT ON COLUMN "member_api_client"."validity_period" IS 'Life time of an OAuth2 access token'; 1.45 1.46 1.47 -CREATE TABLE "member_api_access" ( 1.48 +CREATE TABLE "api_access" ( 1.49 "id" SERIAL8 PRIMARY KEY, 1.50 - "member_api_client_id" INT8 NOT NULL REFERENCES "member_api_client" ("id") 1.51 + "client_identifier" TEXT NOT NULL, 1.52 + "redirection_endpoint" TEXT, 1.53 + "access_level" "api_access_level" NOT NULL, 1.54 + "member_id" INT4 REFERENCES "member" ("id") 1.55 ON DELETE CASCADE ON UPDATE CASCADE, 1.56 "expiry" TIMESTAMPTZ NOT NULL, 1.57 "authorization_code" TEXT, 1.58 "access_token" TEXT, 1.59 + CONSTRAINT "authorization_code_requires_redirection_endpoint" 1.60 + CHECK ("authorization_code" ISNULL OR "redirection_endpoint" NOTNULL), 1.61 CONSTRAINT "one_of_authorization_code_and_access_token_set" 1.62 CHECK ("authorization_code" NOTNULL OR "access_token" NOTNULL) ); 1.63 1.64 -COMMENT ON TABLE "member_api_access" IS 'Issued OAuth2 authorization codes and access tokens'; 1.65 - 1.66 -COMMENT ON COLUMN "member_api_access"."expiry" IS 'Expiry of access token, or expiry of authorization code when access token is NULL'; 1.67 -COMMENT ON COLUMN "member_api_access"."authorization_code" IS 'OAuth2 authorization code'; 1.68 -COMMENT ON COLUMN "member_api_access"."access_token" IS 'OAuth2 access token'; 1.69 +COMMENT ON TABLE "api_access" IS 'Issued OAuth2 authorization codes and access tokens'; 1.70 + 1.71 +COMMENT ON COLUMN "api_access"."client_identifier" IS 'OAuth2 client id'; 1.72 +COMMENT ON COLUMN "api_access"."redirection_endpoint" IS 'OAuth2 redirection endpoint'; 1.73 +COMMENT ON COLUMN "api_access"."expiry" IS 'Expiry of access token, or expiry of authorization code when access token is NULL'; 1.74 +COMMENT ON COLUMN "api_access"."authorization_code" IS 'OAuth2 authorization code'; 1.75 +COMMENT ON COLUMN "api_access"."access_token" IS 'OAuth2 access token'; 1.76 1.77 1.78 CREATE TABLE "member_history" (