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;
|