auth
2015-07-18
Parent:8ecb60d29b0d
auth/sql/postgres_init.sql
Send events when logins are verified. Add an ActionLoginVerified constant to use as the action when a login has been verified. On second thought, this should probably just be "verified", huh? Then we can reuse it across models. Oops. We also added a call to send a login verified event to NSQ when the login is verified.
1 CREATE TABLE IF NOT EXISTS profiles (
2 id VARCHAR(36) PRIMARY KEY,
3 name VARCHAR(64) NOT NULL,
4 passphrase VARCHAR(64) NOT NULL,
5 iterations INTEGER NOT NULL,
6 salt VARCHAR(64) NOT NULL,
7 passphrase_scheme INTEGER NOT NULL,
8 compromised BOOLEAN NOT NULL,
9 locked_until TIMESTAMPTZ NOT NULL,
10 passphrase_reset VARCHAR(64) NOT NULL,
11 passphrase_reset_created TIMESTAMPTZ NOT NULL,
12 created TIMESTAMPTZ NOT NULL,
13 last_seen TIMESTAMPTZ NOT NULL
14 );
16 CREATE TABLE IF NOT EXISTS logins (
17 type VARCHAR(16) NOT NULL,
18 value VARCHAR(64) PRIMARY KEY,
19 profile_id VARCHAR(36) NOT NULL,
20 created TIMESTAMPTZ NOT NULL,
21 last_used TIMESTAMPTZ NOT NULL,
22 verification VARCHAR(36) NOT NULL,
23 verified BOOLEAN NOT NULL
24 );
26 CREATE TABLE IF NOT EXISTS clients (
27 id VARCHAR(36) PRIMARY KEY,
28 secret VARCHAR(64) NOT NULL,
29 owner_id VARCHAR(36) NOT NULL,
30 name VARCHAR(32) NOT NULL,
31 logo VARCHAR(512) NOT NULL,
32 website VARCHAR(140) NOT NULL,
33 type VARCHAR(16) NOT NULL,
34 deleted BOOLEAN NOT NULL
35 );
37 CREATE TABLE IF NOT EXISTS endpoints (
38 id VARCHAR(36) PRIMARY KEY,
39 client_id VARCHAR(36) NOT NULL,
40 uri VARCHAR(512) NOT NULL,
41 normalized_uri VARCHAR(512) NOT NULL,
42 added TIMESTAMPTZ NOT NULL
43 );
45 CREATE TABLE IF NOT EXISTS scopes (
46 id VARCHAR(64) PRIMARY KEY,
47 name VARCHAR(64) NOT NULL,
48 description TEXT NOT NULL
49 );
51 CREATE TABLE IF NOT EXISTS sessions (
52 id VARCHAR(72) PRIMARY KEY,
53 ip VARCHAR(32) NOT NULL,
54 user_agent TEXT NOT NULL,
55 profile_id VARCHAR(36) NOT NULL,
56 login VARCHAR(64) NOT NULL,
57 created TIMESTAMPTZ NOT NULL,
58 expires TIMESTAMPTZ NOT NULL,
59 active BOOLEAN NOT NULL,
60 csrftoken VARCHAR(72) NOT NULL
61 );
63 CREATE TABLE IF NOT EXISTS tokens (
64 access_token TEXT PRIMARY KEY,
65 refresh_token VARCHAR(36) UNIQUE NOT NULL,
66 created TIMESTAMPTZ NOT NULL,
67 created_from VARCHAR(128) NOT NULL,
68 expires_in INTEGER NOT NULL,
69 token_type VARCHAR(64) NOT NULL,
70 profile_id VARCHAR(36) NOT NULL,
71 client_id VARCHAR(36) NOT NULL,
72 revoked BOOLEAN NOT NULL,
73 scopes varchar(64)[] NOT NULL
74 );
76 CREATE TABLE IF NOT EXISTS authorization_codes (
77 code VARCHAR(36) PRIMARY KEY,
78 created TIMESTAMPTZ NOT NULL,
79 expires_in INTEGER NOT NULL,
80 client_id VARCHAR(36) NOT NULL,
81 redirect_uri TEXT NOT NULL,
82 state TEXT NOT NULL,
83 profile_id VARCHAR(36) NOT NULL,
84 used BOOLEAN NOT NULL,
85 scopes varchar(64)[] NOT NULL
86 );