auth

Paddy 2015-05-17 Parent:581c60f8dd23 Child:8ecb60d29b0d

171:807d20a0b197 Go to Latest

auth/sql/postgres_init.sql

Add login verification to Config. Keep track of how we're going to verify logins using the Config struct.

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@168 62 access_token TEXT 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 scopes varchar(64)[] NOT NULL
paddy@155 72 );
paddy@156 73
paddy@156 74 CREATE TABLE IF NOT EXISTS authorization_codes (
paddy@156 75 code VARCHAR(36) PRIMARY KEY,
paddy@156 76 created TIMESTAMPTZ NOT NULL,
paddy@156 77 expires_in INTEGER NOT NULL,
paddy@156 78 client_id VARCHAR(36) NOT NULL,
paddy@156 79 redirect_uri TEXT NOT NULL,
paddy@156 80 state TEXT NOT NULL,
paddy@156 81 profile_id VARCHAR(36) NOT NULL,
paddy@163 82 used BOOLEAN NOT NULL,
paddy@163 83 scopes varchar(64)[] NOT NULL
paddy@156 84 );