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" (

Impressum / About Us