auth

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

169:37a42585660e Go to Latest

auth/sql/postgres_init.sql

Create interfaces for login verification flow. We needed an interface that we could use to say "send the email to verify the user's login" so that we could verify the emails we have are actually valid. This implements an NSQ version that sends an email_verification event. We'll get listener implementations that pull these messages off NSQ and actually send the emails. This also implements, for testing purposes, a version that just echoes the Login Value and the Verification code to stdout.

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