evqjaUmQ

1begin;
2
3drop schema friendworld cascade;
4drop schema friendworld_private cascade;
5drop domain username_domain;
6drop role friendworld_root;
7drop role friendworld_anonymous;
8drop role friendworld_user;
9
10create extension if not exists "uuid-ossp";
11create extension if not exists citext;
12create extension if not exists "pgcrypto";
13
14create schema friendworld;
15create schema friendworld_private;
16
17
18create domain username_domain as citext check (value ~* '^[A-Za-z0-9._%-]+$');
19create table friendworld.users (
20  id            uuid primary key unique default uuid_generate_v4()
21, created_at    timestamp default now()
22, updated_at    timestamp default now()
23, username      username_domain not null unique
24, email         citext check (email ~* '^.+@.+\..+$')
25, tracking_info json
26);
27
28comment on table friendworld.users is 'A friendworld user';
29comment on column friendworld.users.updated_at is E'@omit';
30
31
32create table friendworld_private.accounts (
33  user_id        uuid primary key references friendworld.users(id)
34, password_hash  text not null
35);
36
37create table friendworld.threads (
38  id            uuid primary key unique default uuid_generate_v4()
39, created_at    timestamp default now()
40, updated_at    timestamp default now()
41, title         text not null
42);
43
44
45create table friendworld.posts (
46  id            uuid primary key unique default uuid_generate_v4()
47, created_at    timestamp default now()
48, updated_at    timestamp default now()
49, author_id     uuid references friendworld.users(id)
50, thread_id     uuid references friendworld.threads(id)
51, content       text not null
52);
53
54
55/*
56create view friendworld.view_test as
57  select tracking_info
58  from friendworld.users;
59*/
60
61create type friendworld_private.jwt_token as (
62  role     text
63, aud      text
64, exp      integer
65, user_id  uuid
66);
67
68create function friendworld.signup(
69  username username_domain
70, password text
71, email text default null
72) returns friendworld.users as $$
73declare
74  u friendworld.users;
75
76begin
77  insert into friendworld.users (username, email) 
78    values (username, email)
79    returning * into u;
80
81  insert into friendworld_private.accounts (user_id, password_hash)
82    values (u.id, crypt(password, gen_salt('bf')));
83
84  return u;
85end;
86$$ language plpgsql;
87
88
89create function friendworld.login(
90  username      username_domain
91, password      text
92) returns friendworld_private.jwt_token as $$
93#variable_conflict use_variable
94
95declare
96  account friendworld_private.accounts;
97
98begin
99  select friendworld_private.accounts.* into account
100  from friendworld.users
101  inner join friendworld_private.accounts 
102    on friendworld.users.id = friendworld_private.accounts.user_id
103  where friendworld.users.username = username;
104
105  if account.password_hash = crypt(password, account.password_hash) then
106    return ('friendworld_user', 'postgraphile', extract(epoch from now())::int + 7776000, account.user_id)::friendworld_private.jwt_token;
107  else
108    return null;
109  end if;
110end;
111$$ language plpgsql;
112
113
114create function friendworld.current_user() returns friendworld.users as $$
115  select * 
116  from friendworld.users
117  where id = nullif(current_setting('jwt.claims.user_id', true), '')::uuid;
118$$ language sql stable;
119
120create role friendworld_root;
121
122create role friendworld_anonymous;
123grant friendworld_anonymous to friendworld_root;
124
125create role friendworld_user;
126grant friendworld_user to friendworld_root;
127
128commit;