summaryrefslogtreecommitdiff
path: root/migrations/20220626163140_init.up.sql
blob: 916d31cb83180726f85d85c2c3327e22c9283746 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
create domain session_id as bytea check (length(value) = 32);
create domain user_id as bytea check (length(value) = 16);
create domain hawk_key as bytea check (length(value) = 32);
create domain device_id as bytea check (length(value) = 16);
create domain key_fetch_id as bytea check (length(value) = 32);
create domain oauth_token_id as bytea check (length(value) = 32);
create domain oauth_auth_id as bytea check (length(value) = 32);
create domain secret_key as bytea check (length(value) = 32);
create domain verify_hash as bytea check (length(value) = 32);
create domain password_change_id as bytea check (length(value) = 32);
create domain account_reset_id as bytea check (length(value) = 32);
create domain avatar_id as bytea check (length(value) = 16);

create type oauth_token_kind as enum ('access', 'refresh');
create type oauth_access_type as enum ('online', 'offline');
create type device_command as (name text, body text);

create type device_push_info as (
       callback    text,
       public_key  text,
       auth_key    text
);

create table users (
       user_id       user_id not null primary key,
       auth_salt     text not null,
       email         text not null check (length(email) <= 256),
       display_name  text check (length(display_name) <= 256),
       ka            secret_key not null,
       wrapwrap_kb   secret_key not null,
       verify_hash   verify_hash not null,
       verified      bool not null default false,

       created_at    timestamp with time zone not null default now()
);

create unique index user__email__idx on users ((lower(email)));

create table user_avatars (
       user_id       user_id not null primary key references users on delete cascade,
       id            avatar_id not null,
       data          bytea not null check (length(data) <= 128 * 1024),
       content_type  text not null check (length(content_type) < 1024)
);

create table device (
       device_id           device_id not null primary key,
       user_id             user_id not null references users on delete cascade,
       created_at          timestamp with time zone not null default now(),
       name                text not null check (length(name) <= 256),
       type                text not null check (length(type) <= 16),
       push                device_push_info,
       available_commands  device_command[] not null,
       push_expired        boolean not null,
       location            jsonb not null check (jsonb_typeof(location) = 'object')
);

create function insert_or_update_device(
              p_device_id           device_id,
              p_user_id             user_id,
              p_name                text,
              p_type                text,
              p_push                device_push_info,
              p_available_commands  device_command[],
              p_location            jsonb,
              out result device)
       returns device
       language plpgsql
       as $$
       begin
              update device
              set name = coalesce(p_name, name),
                     type = coalesce(p_type, type),
                     push = coalesce(p_push, push),
                     available_commands = coalesce(p_available_commands, available_commands),
                     push_expired = push_expired and p_push is null,
                     location = coalesce(p_location, location)
              where device_id = p_device_id
              returning *
              into result;

              if not found then
                     insert into device (device_id, user_id, name, type, push, push_expired,
                            available_commands, location)
                     values (p_device_id, p_user_id, p_name, p_type, p_push, p_push is null,
                            p_available_commands, coalesce(p_location, '{}'::jsonb))
                     returning *
                     into result;
              end if;
       end
       $$;

create table user_session (
       session_id    session_id not null primary key,
       user_id       user_id not null references users on delete cascade,
       req_hmac_key  hawk_key not null,
       device_id     device_id references device on delete cascade,
       last_active   timestamp with time zone default now(),

       created_at    timestamp with time zone not null default now(),
       verified      bool not null,
       verify_code   text
);

create function cascade_session_delete()
       returns trigger
       language plpgsql
       as $$
       begin
              delete from device where device.device_id = old.device_id;
              return null;
       end
       $$;

create trigger user_session__cascade_device
       after delete on user_session
       for each row
       when (old.device_id is not null)
       execute function cascade_session_delete();

create table verify_codes (
       user_id     user_id not null primary key references users on delete cascade,
       session_id  session_id references user_session on delete set null,
       code        text not null,
       expires_at  timestamp with time zone default (now() + '5 minutes'::interval)
);

create table device_commands (
       index      bigserial not null,
       device_id  device_id not null references device on delete cascade,
       command    text not null check (length(command) <= 2048),
       payload    json not null check (length(command) <= 16 * 1024),
       expires    timestamp with time zone not null,
       sender     varchar(32), -- hex(sender device_id)
       primary key (index)
);

create index device_command__device_id__idx on device_commands (device_id);
create index device_command__expires__idx on device_commands (expires);

create table key_fetch (
       id          key_fetch_id not null primary key,
       hmac_key    hawk_key not null,
       keys        bytea not null,
       expires_at  timestamp with time zone not null default (now() + '5 minutes'::interval)
);

create index key_fetch__expires_at__idx on key_fetch (expires_at);

create table oauth_token (
       id          oauth_token_id not null primary key,
       kind        oauth_token_kind not null,
       user_id     user_id not null references users on delete cascade,
       client_id   text not null,
       scope       text not null check (length(scope) < 1024),
       created_at  timestamp with time zone not null default now(),

       -- refresh tokens can own sessions. refresh tokens are not owned by
       -- sessions, otherwise the login procedure would immediately invalidate
       -- tokens for fenix.
       session_id  session_id
           references user_session on delete cascade
           check (session_id is null or kind = 'refresh'),

       -- access tokens can be owned by refresh tokens or sessions. owned tokens
       -- are invalidated with their parent, revoking eg sync tokens for removed
       -- devices.
       parent_refresh   oauth_token_id
           references oauth_token on delete cascade
           check (parent_refresh is null or kind = 'access'),
       parent_session   session_id
           references user_session on delete cascade
           check (parent_session is null or kind = 'access'),
       expires_at       timestamp with time zone check ((expires_at is not null) = (kind = 'access'))
);

create index oauth_token__expires_at__idx on oauth_token (expires_at);

create function cascade_oauth_delete()
       returns trigger
       language plpgsql
       as $$
       begin
              delete from user_session where user_session.session_id = old.session_id;
              return null;
       end
       $$;

create trigger oauth_token__cascade_session
       after delete on oauth_token
       for each row
       when (old.session_id is not null)
       execute function cascade_oauth_delete();

create function cascade_device_delete()
       returns trigger
       language plpgsql
       as $$
       begin
              with sessions as (
                   delete from user_session where device_id = old.device_id returning session_id
              ) delete from oauth_token where session_id in (select * from sessions);
              return null;
       end
       $$;

create trigger device__cascade_
       after delete on device
       for each row
       execute function cascade_device_delete();

create table oauth_authorization (
       id              oauth_auth_id not null primary key,
       client_id       text not null,
       user_id         user_id not null references users on delete cascade,
       scope           text not null,
       access_type     oauth_access_type not null,
       code_challenge  text not null,
       keys_jwe        text check (length(keys_jwe) < 16 * 1024),
       auth_at         timestamp with time zone not null,
       expires_at      timestamp with time zone default (now() + '5 minutes'::interval)
);

create index oauth_authorization__expires_at__idx on oauth_authorization (expires_at);

create table password_change_tokens (
       id          password_change_id not null primary key,
       user_id     user_id not null references users on delete cascade,
       hmac_key    hawk_key not null,
       expires_at  timestamp with time zone not null default (now() + '5 minutes'::interval),
       forgot_code  text,

       unique (user_id)
);

create table account_reset_tokens (
       id          account_reset_id not null primary key,
       user_id     user_id not null references users on delete cascade,
       hmac_key    hawk_key not null,
       expires_at  timestamp with time zone not null default (now() + '5 minutes'::interval),

       unique (user_id)
);

create procedure reset_user_auth(
       uid user_id,
       salt text,
       wwkb secret_key,
       verify verify_hash)
language sql
begin atomic
      delete from device where user_id = uid;
      delete from user_session where user_id = uid;
      delete from verify_codes where user_id = uid;
      delete from oauth_token where user_id = uid;
      delete from oauth_authorization where user_id = uid;
      update users set auth_salt = salt, wrapwrap_kb = wwkb, verify_hash = verify where user_id = uid;
end;

create table invite_codes (
       code        text not null primary key,
       expires_at  timestamp with time zone not null
);



create procedure prune_expired_tokens()
language sql
begin atomic
       delete from key_fetch where expires_at <= now();
       delete from oauth_token where expires_at <= now();
       delete from oauth_authorization where expires_at <= now();
       delete from device_commands where expires <= now();
       delete from invite_codes where expires_at <= now();
end;

create procedure prune_expired_verify_codes()
language sql
begin atomic
       -- verify codes can be removed in two ways: either using them to verify an account,
       -- in which case the account can stay, or by timeout, in which case the account must
       -- go. triggers can't distinguish those cases.
       with old_verify as (
            delete from verify_codes where expires_at <= now() returning user_id
       )
       delete from users where user_id in (select user_id from old_verify);

       delete from user_session where not verified and created_at <= now() - '5 minutes'::interval;
       delete from password_change_tokens where expires_at <= now();
       delete from account_reset_tokens where expires_at <= now();
end;