auth

Paddy 2015-04-06 Parent:de5e09680f6b Child:762953f6a7f2

154:5f670aba87b4 Go to Latest

auth/sql/postgres_init.sql

Implement a session store in postgres. Write the postgres implementation of our sessionStore type. Write the SQL statements to initialize the database for us. Include the postgres implementation of our sessionStore type in our sessionStore tests when the appropriate environment variable is passed.

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