summaryrefslogtreecommitdiff
path: root/migrations
diff options
context:
space:
mode:
Diffstat (limited to 'migrations')
-rw-r--r--migrations/20220626163140_init.down.sql1
-rw-r--r--migrations/20220626163140_init.up.sql291
2 files changed, 292 insertions, 0 deletions
diff --git a/migrations/20220626163140_init.down.sql b/migrations/20220626163140_init.down.sql
new file mode 100644
index 0000000..8b13789
--- /dev/null
+++ b/migrations/20220626163140_init.down.sql
@@ -0,0 +1 @@
+
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;
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-22 05:32:38 +0000