auth

Paddy 2015-04-19 Parent:849f3820b164 Child:581c60f8dd23

163:73e12d5a1124 Go to Latest

auth/sql/postgres_init.sql

Use postgres arrays for scope associations. Use the new pqarrays library I wrote to store Scope associations for Tokens and AuthorizationCodes, instead of using our hacky and abstraction-breaking many-to-many code. We also created the authStore.deleteAuthorizationCodesByProfileID method, to clear out the AuthorizationCodes that belong to a Profile (used when the Profile is deleted). So we added the implementation for memstore and for our postgres store. Call Context.DeleteAuthorizationCodesByProfileID when deleting a Profile to clean up after it. Rename sortedScopes to Scopes, which we use pqarrays.StringArray's methods on to fulfill the sql.Scanner and driver.Valuer interfaces. This lets us store Scopes in postgres arrays. Create a stringsToScopes helper function that creates Scope objects, with their IDs filled by the strings specified. Update our GrantType.Validate function signature to return Scopes instead of []string. Create a Scopes.Strings() helper method that returns a []string of the IDs of the Scopes. Update our SQL init file to use the new postgres array definition, instead of the many-to-many definition.

Download raw file
View source Diff to previous Annotate
History
paddy@149 1 CREATE TABLE IF NOT EXISTS profiles (
paddy@149 2 id VARCHAR(36) PRIMARY KEY,
paddy@149 3 name VARCHAR(64) NOT NULL,
paddy@149 4 passphrase VARCHAR(64) NOT NULL,
paddy@149 5 iterations INTEGER NOT NULL,
paddy@149 6 salt VARCHAR(64) NOT NULL,
paddy@149 7 passphrase_scheme INTEGER NOT NULL,
paddy@149 8 compromised BOOLEAN NOT NULL,
paddy@149 9 locked_until TIMESTAMPTZ NOT NULL,
paddy@149 10 passphrase_reset VARCHAR(64) NOT NULL,
paddy@149 11 passphrase_reset_created TIMESTAMPTZ NOT NULL,
paddy@149 12 created TIMESTAMPTZ NOT NULL,
paddy@161 13 last_seen TIMESTAMPTZ NOT NULL
paddy@149 14 );
paddy@149 15
paddy@149 16 CREATE TABLE IF NOT EXISTS logins (
paddy@149 17 type VARCHAR(16) NOT NULL,
paddy@149 18 value VARCHAR(64) PRIMARY KEY,
paddy@149 19 profile_id VARCHAR(36) NOT NULL,
paddy@149 20 created TIMESTAMPTZ NOT NULL,
paddy@149 21 last_used TIMESTAMPTZ NOT NULL
paddy@149 22 );
paddy@151 23
paddy@151 24 CREATE TABLE IF NOT EXISTS clients (
paddy@151 25 id VARCHAR(36) PRIMARY KEY,
paddy@151 26 secret VARCHAR(64) NOT NULL,
paddy@151 27 owner_id VARCHAR(36) NOT NULL,
paddy@151 28 name VARCHAR(32) NOT NULL,
paddy@151 29 logo VARCHAR(512) NOT NULL,
paddy@151 30 website VARCHAR(140) NOT NULL,
paddy@151 31 type VARCHAR(16) NOT NULL,
paddy@151 32 deleted BOOLEAN NOT NULL
paddy@151 33 );
paddy@151 34
paddy@151 35 CREATE TABLE IF NOT EXISTS endpoints (
paddy@151 36 id VARCHAR(36) PRIMARY KEY,
paddy@151 37 client_id VARCHAR(36) NOT NULL,
paddy@151 38 uri VARCHAR(512) NOT NULL,
paddy@151 39 normalized_uri VARCHAR(512) NOT NULL,
paddy@151 40 added TIMESTAMPTZ NOT NULL
paddy@151 41 );
paddy@152 42
paddy@152 43 CREATE TABLE IF NOT EXISTS scopes (
paddy@152 44 id VARCHAR(64) PRIMARY KEY,
paddy@152 45 name VARCHAR(64) NOT NULL,
paddy@152 46 description TEXT NOT NULL
paddy@152 47 );
paddy@154 48
paddy@154 49 CREATE TABLE IF NOT EXISTS sessions (
paddy@154 50 id VARCHAR(72) PRIMARY KEY,
paddy@154 51 ip VARCHAR(32) NOT NULL,
paddy@154 52 user_agent TEXT NOT NULL,
paddy@154 53 profile_id VARCHAR(36) NOT NULL,
paddy@154 54 login VARCHAR(64) NOT NULL,
paddy@154 55 created TIMESTAMPTZ NOT NULL,
paddy@154 56 expires TIMESTAMPTZ NOT NULL,
paddy@154 57 active BOOLEAN NOT NULL,
paddy@154 58 csrftoken VARCHAR(72) NOT NULL
paddy@154 59 );
paddy@155 60
paddy@155 61 CREATE TABLE IF NOT EXISTS tokens (
paddy@155 62 access_token VARCHAR(36) PRIMARY KEY,
paddy@155 63 refresh_token VARCHAR(36) UNIQUE NOT NULL,
paddy@155 64 created TIMESTAMPTZ NOT NULL,
paddy@155 65 created_from VARCHAR(128) NOT NULL,
paddy@155 66 expires_in INTEGER NOT NULL,
paddy@155 67 token_type VARCHAR(64) NOT NULL,
paddy@155 68 profile_id VARCHAR(36) NOT NULL,
paddy@155 69 client_id VARCHAR(36) NOT NULL,
paddy@155 70 revoked BOOLEAN NOT NULL,
paddy@163 71 refresh_revoked BOOLEAN NOT NULL,
paddy@163 72 scopes varchar(64)[] NOT NULL
paddy@155 73 );
paddy@156 74
paddy@156 75 CREATE TABLE IF NOT EXISTS authorization_codes (
paddy@156 76 code VARCHAR(36) PRIMARY KEY,
paddy@156 77 created TIMESTAMPTZ NOT NULL,
paddy@156 78 expires_in INTEGER NOT NULL,
paddy@156 79 client_id VARCHAR(36) NOT NULL,
paddy@156 80 redirect_uri TEXT NOT NULL,
paddy@156 81 state TEXT NOT NULL,
paddy@156 82 profile_id VARCHAR(36) NOT NULL,
paddy@163 83 used BOOLEAN NOT NULL,
paddy@163 84 scopes varchar(64)[] NOT NULL
paddy@156 85 );