auth
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.
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 deleted BOOLEAN NOT NULL
15 );
17 CREATE TABLE IF NOT EXISTS logins (
18 type VARCHAR(16) NOT NULL,
19 value VARCHAR(64) PRIMARY KEY,
20 profile_id VARCHAR(36) NOT NULL,
21 created TIMESTAMPTZ NOT NULL,
22 last_used TIMESTAMPTZ NOT NULL
23 );
25 CREATE TABLE IF NOT EXISTS clients (
26 id VARCHAR(36) PRIMARY KEY,
27 secret VARCHAR(64) NOT NULL,
28 owner_id VARCHAR(36) NOT NULL,
29 name VARCHAR(32) NOT NULL,
30 logo VARCHAR(512) NOT NULL,
31 website VARCHAR(140) NOT NULL,
32 type VARCHAR(16) NOT NULL,
33 deleted BOOLEAN NOT NULL
34 );
36 CREATE TABLE IF NOT EXISTS endpoints (
37 id VARCHAR(36) PRIMARY KEY,
38 client_id VARCHAR(36) NOT NULL,
39 uri VARCHAR(512) NOT NULL,
40 normalized_uri VARCHAR(512) NOT NULL,
41 added TIMESTAMPTZ NOT NULL
42 );
44 CREATE TABLE IF NOT EXISTS scopes (
45 id VARCHAR(64) PRIMARY KEY,
46 name VARCHAR(64) NOT NULL,
47 description TEXT NOT NULL
48 );
50 CREATE TABLE IF NOT EXISTS sessions (
51 id VARCHAR(72) PRIMARY KEY,
52 ip VARCHAR(32) NOT NULL,
53 user_agent TEXT NOT NULL,
54 profile_id VARCHAR(36) NOT NULL,
55 login VARCHAR(64) NOT NULL,
56 created TIMESTAMPTZ NOT NULL,
57 expires TIMESTAMPTZ NOT NULL,
58 active BOOLEAN NOT NULL,
59 csrftoken VARCHAR(72) NOT NULL
60 );