auth

Paddy 2015-04-11 Parent:2809016184f6 Child:849f3820b164

157:202e991accc2 Go to Latest

auth/sql/postgres_init.sql

Wire up the postgres database for authd. Have authd use the AUTH_PG_DB environment variable to detect support for the postgres *Stores, and if postgres is supported, use it. If postgres isn't supported, fall back on the in-memory store. Also create-if-not-exists the test scopes, instead of panicking when the scope already exists.

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@149 13 last_seen TIMESTAMPTZ NOT NULL,
paddy@149 14 deleted BOOLEAN NOT NULL
paddy@149 15 );
paddy@149 16
paddy@149 17 CREATE TABLE IF NOT EXISTS logins (
paddy@149 18 type VARCHAR(16) NOT NULL,
paddy@149 19 value VARCHAR(64) PRIMARY KEY,
paddy@149 20 profile_id VARCHAR(36) NOT NULL,
paddy@149 21 created TIMESTAMPTZ NOT NULL,
paddy@149 22 last_used TIMESTAMPTZ NOT NULL
paddy@149 23 );
paddy@151 24
paddy@151 25 CREATE TABLE IF NOT EXISTS clients (
paddy@151 26 id VARCHAR(36) PRIMARY KEY,
paddy@151 27 secret VARCHAR(64) NOT NULL,
paddy@151 28 owner_id VARCHAR(36) NOT NULL,
paddy@151 29 name VARCHAR(32) NOT NULL,
paddy@151 30 logo VARCHAR(512) NOT NULL,
paddy@151 31 website VARCHAR(140) NOT NULL,
paddy@151 32 type VARCHAR(16) NOT NULL,
paddy@151 33 deleted BOOLEAN NOT NULL
paddy@151 34 );
paddy@151 35
paddy@151 36 CREATE TABLE IF NOT EXISTS endpoints (
paddy@151 37 id VARCHAR(36) PRIMARY KEY,
paddy@151 38 client_id VARCHAR(36) NOT NULL,
paddy@151 39 uri VARCHAR(512) NOT NULL,
paddy@151 40 normalized_uri VARCHAR(512) NOT NULL,
paddy@151 41 added TIMESTAMPTZ NOT NULL
paddy@151 42 );
paddy@152 43
paddy@152 44 CREATE TABLE IF NOT EXISTS scopes (
paddy@152 45 id VARCHAR(64) PRIMARY KEY,
paddy@152 46 name VARCHAR(64) NOT NULL,
paddy@152 47 description TEXT NOT NULL
paddy@152 48 );
paddy@154 49
paddy@154 50 CREATE TABLE IF NOT EXISTS sessions (
paddy@154 51 id VARCHAR(72) PRIMARY KEY,
paddy@154 52 ip VARCHAR(32) NOT NULL,
paddy@154 53 user_agent TEXT NOT NULL,
paddy@154 54 profile_id VARCHAR(36) NOT NULL,
paddy@154 55 login VARCHAR(64) NOT NULL,
paddy@154 56 created TIMESTAMPTZ NOT NULL,
paddy@154 57 expires TIMESTAMPTZ NOT NULL,
paddy@154 58 active BOOLEAN NOT NULL,
paddy@154 59 csrftoken VARCHAR(72) NOT NULL
paddy@154 60 );
paddy@155 61
paddy@155 62 CREATE TABLE IF NOT EXISTS tokens (
paddy@155 63 access_token VARCHAR(36) PRIMARY KEY,
paddy@155 64 refresh_token VARCHAR(36) UNIQUE NOT NULL,
paddy@155 65 created TIMESTAMPTZ NOT NULL,
paddy@155 66 created_from VARCHAR(128) NOT NULL,
paddy@155 67 expires_in INTEGER NOT NULL,
paddy@155 68 token_type VARCHAR(64) NOT NULL,
paddy@155 69 profile_id VARCHAR(36) NOT NULL,
paddy@155 70 client_id VARCHAR(36) NOT NULL,
paddy@155 71 revoked BOOLEAN NOT NULL,
paddy@155 72 refresh_revoked BOOLEAN NOT NULL
paddy@155 73 );
paddy@155 74
paddy@155 75 CREATE TABLE IF NOT EXISTS scopes_tokens (
paddy@155 76 token VARCHAR(36) NOT NULL,
paddy@155 77 scope VARCHAR(64) NOT NULL,
paddy@155 78 PRIMARY KEY(token, scope)
paddy@155 79 );
paddy@156 80
paddy@156 81 CREATE TABLE IF NOT EXISTS authorization_codes (
paddy@156 82 code VARCHAR(36) PRIMARY KEY,
paddy@156 83 created TIMESTAMPTZ NOT NULL,
paddy@156 84 expires_in INTEGER NOT NULL,
paddy@156 85 client_id VARCHAR(36) NOT NULL,
paddy@156 86 redirect_uri TEXT NOT NULL,
paddy@156 87 state TEXT NOT NULL,
paddy@156 88 profile_id VARCHAR(36) NOT NULL,
paddy@156 89 used BOOLEAN NOT NULL
paddy@156 90 );
paddy@156 91
paddy@156 92 CREATE TABLE IF NOT EXISTS authorization_codes_scopes (
paddy@156 93 code VARCHAR(36) NOT NULL,
paddy@156 94 scope VARCHAR(64) NOT NULL,
paddy@156 95 PRIMARY KEY(code, scope)
paddy@156 96 );