liquid_feedback_core
changeset 267:5bf2c973ec3c
Work on OAuth 2.0 authorization support
author | jbe |
---|---|
date | Wed Aug 08 15:20:34 2012 +0200 (2012-08-08) |
parents | d1d5dca15491 |
children | 739ed2d3d372 |
files | core.sql |
line diff
1.1 --- a/core.sql Wed Aug 08 14:10:14 2012 +0200 1.2 +++ b/core.sql Wed Aug 08 15:20:34 2012 +0200 1.3 @@ -174,72 +174,61 @@ 1.4 'none', 'anonymous', 'authors_pseudonymous', 'all_pseudonymous', 'everything', 'member' ); 1.5 1.6 1.7 -CREATE TABLE "system_api_client" ( 1.8 - "id" INT4 PRIMARY KEY, 1.9 - "name" TEXT NOT NULL UNIQUE, 1.10 - "client_identifier" TEXT NOT NULL UNIQUE, 1.11 - "client_secret" TEXT, 1.12 - "redirection_endpoint" TEXT, 1.13 - "access_level" "api_access_level" NOT NULL, 1.14 - "multi_access" BOOLEAN NOT NULL, 1.15 - "validity_period" INTERVAL NOT NULL, 1.16 - "last_usage" TIMESTAMPTZ NOT NULL ); 1.17 - 1.18 -COMMENT ON TABLE "system_api_client" IS 'Registered OAuth2 clients by the system administrator'; 1.19 - 1.20 -COMMENT ON COLUMN "system_api_client"."name" IS 'Name of the client as chosen by member'; 1.21 -COMMENT ON COLUMN "system_api_client"."client_identifier" IS 'OAuth2 client id'; 1.22 -COMMENT ON COLUMN "system_api_client"."client_secret" IS 'Secret for client authentication, enables OAuth2 Client Credentials Grant when set'; 1.23 -COMMENT ON COLUMN "system_api_client"."redirection_endpoint" IS 'OAuth2 redirection endpoint, enables OAuth2 Authorization Code Grant and Implicit Grant when set'; 1.24 -COMMENT ON COLUMN "system_api_client"."multi_access" IS 'TRUE = Allow to issue more than one access token simultanously'; 1.25 -COMMENT ON COLUMN "system_api_client"."validity_period" IS 'Life time of an OAuth2 access token'; 1.26 - 1.27 - 1.28 -CREATE TABLE "member_api_client" ( 1.29 +CREATE TABLE "api_client" ( 1.30 "id" SERIAL8 PRIMARY KEY, 1.31 UNIQUE ("member_id", "name"), 1.32 UNIQUE ("member_id", "client_identifier"), 1.33 - "member_id" INT4 NOT NULL REFERENCES "member" ("id") 1.34 + "member_id" INT4 REFERENCES "member" ("id") 1.35 ON DELETE CASCADE ON UPDATE CASCADE, 1.36 "name" TEXT NOT NULL, 1.37 "client_identifier" TEXT NOT NULL, 1.38 - "redirection_endpoint" TEXT NOT NULL, 1.39 + "client_secret" TEXT, 1.40 + "redirection_endpoint" TEXT, 1.41 + "public_access_level" "api_access_level", 1.42 "access_level" "api_access_level" NOT NULL, 1.43 - "multi_access" BOOLEAN NOT NULL, 1.44 "validity_period" INTERVAL NOT NULL, 1.45 - "last_usage" TIMESTAMPTZ NOT NULL ); 1.46 - 1.47 -COMMENT ON TABLE "member_api_client" IS 'Registered OAuth2 client for a member'; 1.48 - 1.49 -COMMENT ON COLUMN "member_api_client"."name" IS 'Name of the client as chosen by member'; 1.50 -COMMENT ON COLUMN "member_api_client"."client_identifier" IS 'OAuth2 client id'; 1.51 -COMMENT ON COLUMN "member_api_client"."redirection_endpoint" IS 'OAuth2 redirection endpoint'; 1.52 -COMMENT ON COLUMN "member_api_client"."multi_access" IS 'TRUE = Allow to issue more than one access token simultanously'; 1.53 -COMMENT ON COLUMN "member_api_client"."validity_period" IS 'Life time of an OAuth2 access token'; 1.54 + "last_usage" TIMESTAMPTZ NOT NULL, 1.55 + CONSTRAINT "public_access_level_set_if_and_only_if_system_client" 1.56 + CHECK ("member_id" ISNULL OR "client_secret" NOTNULL OR "access_level"='none'), 1.57 + CONSTRAINT "system_client_with_public_access_requires_secret" 1.58 + CHECK ("member_id" ISNULL OR "client_secret" NOTNULL OR "public_access_level"='none'), 1.59 + CONSTRAINT "member_client_requires_redirection_endpoint" 1.60 + CHECK ("member_id" ISNULL OR "redirection_endpoint" NOTNULL) ); 1.61 +CREATE UNIQUE INDEX "api_client_non_member_name_idx" ON "api_client" ("name") WHERE "member_id" ISNULL; 1.62 +CREATE UNIQUE INDEX "api_client_non_member_client_identifier_idx" ON "api_client" ("client_identifier") WHERE "member_id" ISNULL; 1.63 + 1.64 +COMMENT ON TABLE "api_client" IS 'Registered OAuth2 client for a member'; 1.65 + 1.66 +COMMENT ON COLUMN "api_client"."member_id" IS 'Member, who registered the client for him/herself, or NULL for clients registered by administrator'; 1.67 +COMMENT ON COLUMN "api_client"."name" IS 'Name of the client as chosen by member or administrator'; 1.68 +COMMENT ON COLUMN "api_client"."client_identifier" IS 'OAuth2 client id'; 1.69 +COMMENT ON COLUMN "api_client"."client_secret" IS 'Secret for client authentication, enables OAuth2 Client Credentials Grant when set'; 1.70 +COMMENT ON COLUMN "api_client"."redirection_endpoint" IS 'OAuth2 redirection endpoint, must be set for clients registered by members'; 1.71 +COMMENT ON COLUMN "api_client"."access_level" IS 'For clients registered by administrator: access level for OAuth2 Client Credentials Grant; For clients registered by member: access level for OAuth '; 1.72 +COMMENT ON COLUMN "api_client"."validity_period" IS 'Life time of an OAuth2 access token'; 1.73 1.74 1.75 CREATE TABLE "api_access" ( 1.76 "id" SERIAL8 PRIMARY KEY, 1.77 - "client_identifier" TEXT NOT NULL, 1.78 - "redirection_endpoint" TEXT, 1.79 - "access_level" "api_access_level" NOT NULL, 1.80 + "api_client_id" INT8 NOT NULL REFERENCES "api_client" ("id") 1.81 + ON DELETE CASCADE ON UPDATE CASCADE, 1.82 "member_id" INT4 REFERENCES "member" ("id") 1.83 ON DELETE CASCADE ON UPDATE CASCADE, 1.84 - "expiry" TIMESTAMPTZ NOT NULL, 1.85 + "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.86 "authorization_code" TEXT, 1.87 - "access_token" TEXT, 1.88 - CONSTRAINT "authorization_code_requires_redirection_endpoint" 1.89 - CHECK ("authorization_code" ISNULL OR "redirection_endpoint" NOTNULL), 1.90 - CONSTRAINT "one_of_authorization_code_and_access_token_set" 1.91 - CHECK ("authorization_code" NOTNULL OR "access_token" NOTNULL) ); 1.92 - 1.93 -COMMENT ON TABLE "api_access" IS 'Issued OAuth2 authorization codes and access tokens'; 1.94 - 1.95 -COMMENT ON COLUMN "api_access"."client_identifier" IS 'OAuth2 client id'; 1.96 -COMMENT ON COLUMN "api_access"."redirection_endpoint" IS 'OAuth2 redirection endpoint'; 1.97 -COMMENT ON COLUMN "api_access"."expiry" IS 'Expiry of access token, or expiry of authorization code when access token is NULL'; 1.98 + "refreshed" TIMESTAMPTZ, 1.99 + "refresh_token" TEXT, 1.100 + CONSTRAINT "one_of_authorization_code_and_refresh_token_set" 1.101 + CHECK ("authorization_code" NOTNULL OR "refresh_token" NOTNULL), 1.102 + CONSTRAINT "refresh_token_if_and_only_if_refreshed" 1.103 + CHECK ("refreshed" NOTNULL = "refresh_token" NOTNULL) ); 1.104 + 1.105 +COMMENT ON TABLE "api_access" IS 'Issued OAuth2 authorization codes and refresh tokens'; 1.106 + 1.107 +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.108 COMMENT ON COLUMN "api_access"."authorization_code" IS 'OAuth2 authorization code'; 1.109 -COMMENT ON COLUMN "api_access"."access_token" IS 'OAuth2 access token'; 1.110 +COMMENT ON COLUMN "api_access"."refreshed" IS 'Date/time of last refresh'; 1.111 +COMMENT ON COLUMN "api_access"."refresh_token" IS 'OAuth2 refresh token'; 1.112 1.113 1.114 CREATE TABLE "member_history" (