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

Impressum / About Us