# HG changeset patch # User jbe # Date 1344463861 -7200 # Node ID 02a72de117e9d83785de52379ab776d0dbf59152 # Parent b555a544c7247bdfa7a575dcca59e7e12834add3 Work on OAuth2 diff -r b555a544c724 -r 02a72de117e9 core.sql --- a/core.sql Wed Aug 08 18:48:13 2012 +0200 +++ b/core.sql Thu Aug 09 00:11:01 2012 +0200 @@ -182,31 +182,40 @@ ON DELETE CASCADE ON UPDATE CASCADE, "client_identifier" TEXT NOT NULL, "client_secret" TEXT, - "member_authorization" BOOLEAN NOT NULL, - "public_access_level" "api_access_level", - "access_level" "api_access_level" NOT NULL, - "validity_period" INTERVAL NOT NULL, + "code_grant" BOOLEAN NOT NULL, + "token_grant" BOOLEAN NOT NULL, + "client_grant" BOOLEAN NOT NULL, + "code_grant_validity_period" INTERVAL, + "access_level" "api_access_level", + "client_grant_access_level" "api_access_level", "last_usage" TIMESTAMPTZ NOT NULL, CONSTRAINT "system_clients_require_name" CHECK ("name" NOTNULL OR "member_id" ISNULL), - CONSTRAINT "public_access_level_set_if_and_only_if_system_client" - CHECK ("member_id" ISNULL = "public_access_level" NOTNULL) ); + CONSTRAINT "code_grant_requires_validity_period" + CHECK ("code_grant"=FALSE OR "code_grant_validity_period" NOTNULL), + CONSTRAINT "code_or_token_grant_requires_access_level" + CHECK (("code_grant"=FALSE AND "token_grant"=FALSE) OR "access_level" NOTNULL), + CONSTRAINT "client_grant_requires_client_grant_access_level" + CHECK ("client_grant"=FALSE OR "client_grant_access_level" NOTNULL) ); CREATE UNIQUE INDEX "api_client_non_member_client_identifier_idx" ON "api_client" ("client_identifier") WHERE "member_id" ISNULL; COMMENT ON TABLE "api_client" IS 'Registered OAuth2 client for a member'; -COMMENT ON COLUMN "api_client"."member_id" IS 'Member, who registered the client for him/herself, or NULL for clients registered by administrator'; -COMMENT ON COLUMN "api_client"."name" IS 'Name of the client as chosen by member or administrator, NULL means unnamed'; -COMMENT ON COLUMN "api_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint if "member_authorization" is set to TRUE'; -COMMENT ON COLUMN "api_client"."client_secret" IS 'Secret for client authentication, enables OAuth2 Client Credentials Grant when set'; -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'; -COMMENT ON COLUMN "api_client"."public_access_level" IS 'Access level for OAuth2 Client Credentials Grant'; -COMMENT ON COLUMN "api_client"."access_level" IS 'Access level for OAuth2 Authorization Code Grant and Implicit Grant'; -COMMENT ON COLUMN "api_client"."validity_period" IS 'Period after which an entry in the "api_access" table expires'; - - -CREATE TABLE "api_access" ( +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'; +COMMENT ON COLUMN "api_client"."member_id" IS 'Member, who registered the client for him/herself, or NULL for clients registered by administrator'; +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'; +COMMENT ON COLUMN "api_client"."client_secret" IS 'Secret for client authentication'; +COMMENT ON COLUMN "api_client"."code_grant" IS 'Enable OAuth2 Authorization Code Grant'; +COMMENT ON COLUMN "api_client"."token_grant" IS 'Enable OAuth2 Implicit Grant'; +COMMENT ON COLUMN "api_client"."client_grant" IS 'Enable OAuth2 Client Credentials Grant'; +COMMENT ON COLUMN "api_client"."code_grant_validity_period" IS 'Validity period of OAuth2 Authorization Code Grant, after which no more refresh is possible'; +COMMENT ON COLUMN "api_client"."access_level" IS 'Access level for OAuth2 Authorization Code Grant and Implicit Grant'; +COMMENT ON COLUMN "api_client"."client_grant_access_level" IS 'Access level for OAuth2 Authorization Code Grant and Implicit Grant'; +COMMENT ON COLUMN "api_client"."last_usage" IS 'Date/time when this client registration was last used'; + + +CREATE TABLE "api_code_grant" ( "id" SERIAL8 PRIMARY KEY, "api_client_id" INT8 NOT NULL REFERENCES "api_client" ("id") ON DELETE CASCADE ON UPDATE CASCADE, @@ -226,13 +235,13 @@ CONSTRAINT "old_refresh_token_requires_current_refresh_token" CHECK ("refresh_token" NOTNULL OR "old_refresh_token" ISNULL) ); -COMMENT ON TABLE "api_access" IS 'Issued OAuth2 authorization codes and refresh tokens'; - -COMMENT ON COLUMN "api_client"."validity_period" IS 'Period after which an entry in the "api_access" table expires'; -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'; -COMMENT ON COLUMN "api_access"."authorization_code" IS 'OAuth2 authorization code (only valid for a very short time after it has been created)'; -COMMENT ON COLUMN "api_access"."refreshed" IS 'Date/time of last refresh'; -COMMENT ON COLUMN "api_access"."refresh_token" IS 'OAuth2 refresh token'; +COMMENT ON TABLE "api_code_grant" IS 'Issued OAuth2 authorization codes and refresh tokens'; + +COMMENT ON COLUMN "api_code_grant"."validity_period" IS 'Period after which no more refreshing is possible'; +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'; +COMMENT ON COLUMN "api_code_grant"."authorization_code" IS 'OAuth2 authorization code (only valid for a very short time after it has been created)'; +COMMENT ON COLUMN "api_code_grant"."refreshed" IS 'Date/time of last refresh'; +COMMENT ON COLUMN "api_code_grant"."refresh_token" IS 'OAuth2 refresh token'; CREATE TABLE "member_history" (