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
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 );
15
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 );
23
24 CREATE TABLE IF NOT EXISTS clients (
25 id VARCHAR(36) PRIMARY KEY,
26 secret VARCHAR(64) NOT NULL,
27 owner_id VARCHAR(36) NOT NULL,
28 name VARCHAR(32) NOT NULL,
29 logo VARCHAR(512) NOT NULL,
30 website VARCHAR(140) NOT NULL,
31 type VARCHAR(16) NOT NULL,
32 deleted BOOLEAN NOT NULL
33 );
34
35 CREATE TABLE IF NOT EXISTS endpoints (
36 id VARCHAR(36) PRIMARY KEY,
37 client_id VARCHAR(36) NOT NULL,
38 uri VARCHAR(512) NOT NULL,
39 normalized_uri VARCHAR(512) NOT NULL,
40 added TIMESTAMPTZ NOT NULL
41 );
42
43 CREATE TABLE IF NOT EXISTS scopes (
44 id VARCHAR(64) PRIMARY KEY,
45 name VARCHAR(64) NOT NULL,
46 description TEXT NOT NULL
47 );
48
49 CREATE TABLE IF NOT EXISTS sessions (
50 id VARCHAR(72) PRIMARY KEY,
51 ip VARCHAR(32) NOT NULL,
52 user_agent TEXT NOT NULL,
53 profile_id VARCHAR(36) NOT NULL,
54 login VARCHAR(64) NOT NULL,
55 created TIMESTAMPTZ NOT NULL,
56 expires TIMESTAMPTZ NOT NULL,
57 active BOOLEAN NOT NULL,
58 csrftoken VARCHAR(72) NOT NULL
59 );
60
61 CREATE TABLE IF NOT EXISTS tokens (
62 access_token TEXT PRIMARY KEY,
63 refresh_token VARCHAR(36) UNIQUE NOT NULL,
64 created TIMESTAMPTZ NOT NULL,
65 created_from VARCHAR(128) NOT NULL,
66 expires_in INTEGER NOT NULL,
67 token_type VARCHAR(64) NOT NULL,
68 profile_id VARCHAR(36) NOT NULL,
69 client_id VARCHAR(36) NOT NULL,
70 revoked BOOLEAN NOT NULL,
71 scopes varchar(64)[] NOT NULL
72 );
73
74 CREATE TABLE IF NOT EXISTS authorization_codes (
75 code VARCHAR(36) PRIMARY KEY,
76 created TIMESTAMPTZ NOT NULL,
77 expires_in INTEGER NOT NULL,
78 client_id VARCHAR(36) NOT NULL,
79 redirect_uri TEXT NOT NULL,
80 state TEXT NOT NULL,
81 profile_id VARCHAR(36) NOT NULL,
82 used BOOLEAN NOT NULL,
83 scopes varchar(64)[] NOT NULL
84 );