auth
auth/sql/postgres_init.sql
Wire up the postgres database for authd. Have authd use the AUTH_PG_DB environment variable to detect support for the postgres *Stores, and if postgres is supported, use it. If postgres isn't supported, fall back on the in-memory store. Also create-if-not-exists the test scopes, instead of panicking when the scope already exists.
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 );
62 CREATE TABLE IF NOT EXISTS tokens (
63 access_token VARCHAR(36) PRIMARY KEY,
64 refresh_token VARCHAR(36) UNIQUE NOT NULL,
65 created TIMESTAMPTZ NOT NULL,
66 created_from VARCHAR(128) NOT NULL,
67 expires_in INTEGER NOT NULL,
68 token_type VARCHAR(64) NOT NULL,
69 profile_id VARCHAR(36) NOT NULL,
70 client_id VARCHAR(36) NOT NULL,
71 revoked BOOLEAN NOT NULL,
72 refresh_revoked BOOLEAN NOT NULL
73 );
75 CREATE TABLE IF NOT EXISTS scopes_tokens (
76 token VARCHAR(36) NOT NULL,
77 scope VARCHAR(64) NOT NULL,
78 PRIMARY KEY(token, scope)
79 );
81 CREATE TABLE IF NOT EXISTS authorization_codes (
82 code VARCHAR(36) PRIMARY KEY,
83 created TIMESTAMPTZ NOT NULL,
84 expires_in INTEGER NOT NULL,
85 client_id VARCHAR(36) NOT NULL,
86 redirect_uri TEXT NOT NULL,
87 state TEXT NOT NULL,
88 profile_id VARCHAR(36) NOT NULL,
89 used BOOLEAN NOT NULL
90 );
92 CREATE TABLE IF NOT EXISTS authorization_codes_scopes (
93 code VARCHAR(36) NOT NULL,
94 scope VARCHAR(64) NOT NULL,
95 PRIMARY KEY(code, scope)
96 );