Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
243 lines (190 sloc) 10.1 KB
-- This file was automatically generated from the `TUTORIAL.md` which
-- contains a complete explanation of how this schema works and why certain
-- decisions were made. If you are looking for a comprehensive tutorial,
-- definitely check it out as this file is a little tough to read.
--
-- If you want to contribute to this file, please change the
-- `TUTORIAL.md` file and then rebuild this file :)
begin;
create schema forum_example;
create schema forum_example_private;
create table forum_example.person (
id serial primary key,
first_name text not null check (char_length(first_name) < 80),
last_name text check (char_length(last_name) < 80),
about text,
created_at timestamp default now()
);
comment on table forum_example.person is 'A user of the forum.';
comment on column forum_example.person.id is 'The primary unique identifier for the person.';
comment on column forum_example.person.first_name is 'The person’s first name.';
comment on column forum_example.person.last_name is 'The person’s last name.';
comment on column forum_example.person.about is 'A short description about the user, written by the user.';
comment on column forum_example.person.created_at is 'The time this person was created.';
create type forum_example.post_topic as enum (
'discussion',
'inspiration',
'help',
'showcase'
);
create table forum_example.post (
id serial primary key,
author_id integer not null references forum_example.person(id),
headline text not null check (char_length(headline) < 280),
body text,
topic forum_example.post_topic,
created_at timestamp default now()
);
comment on table forum_example.post is 'A forum post written by a user.';
comment on column forum_example.post.id is 'The primary key for the post.';
comment on column forum_example.post.headline is 'The title written by the user.';
comment on column forum_example.post.author_id is 'The id of the author user.';
comment on column forum_example.post.topic is 'The topic this has been posted in.';
comment on column forum_example.post.body is 'The main body text of our post.';
comment on column forum_example.post.created_at is 'The time this post was created.';
alter default privileges revoke execute on functions from public;
create function forum_example.person_full_name(person forum_example.person) returns text as $$
select person.first_name || ' ' || person.last_name
$$ language sql stable;
comment on function forum_example.person_full_name(forum_example.person) is 'A person’s full name which is a concatenation of their first and last name.';
create function forum_example.post_summary(
post forum_example.post,
length int default 50,
omission text default ''
) returns text as $$
select case
when post.body is null then null
else substr(post.body, 0, length) || omission
end
$$ language sql stable;
comment on function forum_example.post_summary(forum_example.post, int, text) is 'A truncated version of the body for summaries.';
create function forum_example.person_latest_post(person forum_example.person) returns forum_example.post as $$
select post.*
from forum_example.post as post
where post.author_id = person.id
order by created_at desc
limit 1
$$ language sql stable;
comment on function forum_example.person_latest_post(forum_example.person) is 'Gets the latest post written by the person.';
create function forum_example.search_posts(search text) returns setof forum_example.post as $$
select post.*
from forum_example.post as post
where post.headline ilike ('%' || search || '%') or post.body ilike ('%' || search || '%')
$$ language sql stable;
comment on function forum_example.search_posts(text) is 'Returns posts containing a given search term.';
alter table forum_example.person add column updated_at timestamp default now();
alter table forum_example.post add column updated_at timestamp default now();
create function forum_example_private.set_updated_at() returns trigger as $$
begin
new.updated_at := current_timestamp;
return new;
end;
$$ language plpgsql;
create trigger person_updated_at before update
on forum_example.person
for each row
execute procedure forum_example_private.set_updated_at();
create trigger post_updated_at before update
on forum_example.post
for each row
execute procedure forum_example_private.set_updated_at();
create table forum_example_private.person_account (
person_id integer primary key references forum_example.person(id) on delete cascade,
email text not null unique check (email ~* '^.+@.+\..+$'),
password_hash text not null
);
comment on table forum_example_private.person_account is 'Private information about a person’s account.';
comment on column forum_example_private.person_account.person_id is 'The id of the person associated with this account.';
comment on column forum_example_private.person_account.email is 'The email address of the person.';
comment on column forum_example_private.person_account.password_hash is 'An opaque hash of the person’s password.';
create extension if not exists "pgcrypto";
create function forum_example.register_person(
first_name text,
last_name text,
email text,
password text
) returns forum_example.person as $$
declare
person forum_example.person;
begin
insert into forum_example.person (first_name, last_name) values
(first_name, last_name)
returning * into person;
insert into forum_example_private.person_account (person_id, email, password_hash) values
(person.id, email, crypt(password, gen_salt('bf')));
return person;
end;
$$ language plpgsql strict security definer;
comment on function forum_example.register_person(text, text, text, text) is 'Registers a single user and creates an account in our forum.';
create role forum_example_postgraphile login password 'xyz';
create role forum_example_anonymous;
grant forum_example_anonymous to forum_example_postgraphile;
create role forum_example_person;
grant forum_example_person to forum_example_postgraphile;
create type forum_example.jwt_token as (
role text,
person_id integer
);
create function forum_example.authenticate(
email text,
password text
) returns forum_example.jwt_token as $$
select ('forum_example_person', person_id)::forum_example.jwt_token
from forum_example_private.person_account
where
person_account.email = $1
and person_account.password_hash = crypt($2, person_account.password_hash);
$$ language sql strict security definer;
comment on function forum_example.authenticate(text, text) is 'Creates a JWT token that will securely identify a person and give them certain permissions.';
create function forum_example.current_person() returns forum_example.person as $$
select *
from forum_example.person
where id = current_setting('jwt.claims.person_id', true)::integer
$$ language sql stable;
comment on function forum_example.current_person() is 'Gets the person who was identified by our JWT.';
create function forum_example.change_password(current_password text, new_password text)
returns boolean as $$
declare
current_person forum_example.person;
begin
current_person := forum_example.current_person();
if exists (select 1 from forum_example_private.person_account where person_account.person_id = current_person.id and person_account.password_hash = crypt($1, person_account.password_hash))
then
update forum_example_private.person_account set password_hash = crypt($2, gen_salt('bf')) where person_account.person_id = current_person.id;
return true;
else
return false;
end if;
end;
$$ language plpgsql strict security definer;
grant usage on schema forum_example to forum_example_anonymous, forum_example_person;
grant select on table forum_example.person to forum_example_anonymous, forum_example_person;
grant update, delete on table forum_example.person to forum_example_person;
grant select on table forum_example.post to forum_example_anonymous, forum_example_person;
grant insert, update, delete on table forum_example.post to forum_example_person;
grant usage on sequence forum_example.post_id_seq to forum_example_person;
grant execute on function forum_example.person_full_name(forum_example.person) to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.post_summary(forum_example.post, integer, text) to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.person_latest_post(forum_example.person) to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.search_posts(text) to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.authenticate(text, text) to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.current_person() to forum_example_anonymous, forum_example_person;
grant execute on function forum_example.change_password(text, text) to forum_example_person;
grant execute on function forum_example.register_person(text, text, text, text) to forum_example_anonymous;
alter table forum_example.person enable row level security;
alter table forum_example.post enable row level security;
create policy select_person on forum_example.person for select
using (true);
create policy select_post on forum_example.post for select
using (true);
create policy update_person on forum_example.person for update to forum_example_person
using (id = current_setting('jwt.claims.person_id', true)::integer);
create policy delete_person on forum_example.person for delete to forum_example_person
using (id = current_setting('jwt.claims.person_id', true)::integer);
create policy insert_post on forum_example.post for insert to forum_example_person
with check (author_id = current_setting('jwt.claims.person_id', true)::integer);
create policy update_post on forum_example.post for update to forum_example_person
using (author_id = current_setting('jwt.claims.person_id', true)::integer);
create policy delete_post on forum_example.post for delete to forum_example_person
using (author_id = current_setting('jwt.claims.person_id', true)::integer);
commit;
You can’t perform that action at this time.