From 2f8dce44d3f2be74b5c6ec0a2e7f4ceced715328 Mon Sep 17 00:00:00 2001 From: pennae Date: Wed, 13 Jul 2022 10:33:30 +0200 Subject: initial import --- migrations/20220626163140_init.up.sql | 291 ++++++++++++++++++++++++++++++++++ 1 file changed, 291 insertions(+) create mode 100644 migrations/20220626163140_init.up.sql (limited to 'migrations/20220626163140_init.up.sql') diff --git a/migrations/20220626163140_init.up.sql b/migrations/20220626163140_init.up.sql new file mode 100644 index 0000000..916d31c --- /dev/null +++ b/migrations/20220626163140_init.up.sql @@ -0,0 +1,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; -- cgit v1.2.3