auth

Paddy 2015-04-07 Parent:762953f6a7f2 Child:849f3820b164

156:2809016184f6 Go to Latest

auth/sql/postgres_init.sql

Implement postgres version of authCodeStore. Create an authCodeStore that keeps data in Postgres. Again, we run into the problem where Scopes can't be stored in Postgres arrays, as discussed in 762953f6a7f2. I wish we could do better, but for now, it will suffice. We also added the postgres authCodeStore to our slice of authCodeStores to test when the correct environment variables are present. Wrote initialization SQL for the tables required by the postgres authCodeStore. Added SQL to the SQL script that empties our database, to properly empty our new tables.

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 );