Skip to content

Instantly share code, notes, and snippets.

@Param-Harrison

Param-Harrison/postgres-schema-design.sql

Created July 8, 2020 20:08
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Param-Harrison/0f42e9f142f931a1b69c9830c6e27c70 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Param-Harrison/0f42e9f142f931a1b69c9830c6e27c70 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
postgres-schema-design.sql
---------------------------------------------------------------------------------------------------------------------
/* Schemas */
---------------------------------------------------------------------------------------------------------------------
create schema forum_example;
create schema forum_example_private;
---------------------------------------------------------------------------------------------------------------------
/* Extenstions */
---------------------------------------------------------------------------------------------------------------------
create extension if not exists "uuid-ossp";
create extension if not exists "pgcrypto";
---------------------------------------------------------------------------------------------------------------------
/* People */
---------------------------------------------------------------------------------------------------------------------
create table forum_example.person (
id uuid primary key default uuid_generate_v1mc(),
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 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.';
alter table forum_example.person add column updated_at timestamp default now();
---------------------------------------------------------------------------------------------------------------------
/* Posts */
---------------------------------------------------------------------------------------------------------------------
create type forum_example.post_topic as enum (
'discussion',
'inspiration',
'help',
'showcase'
);
create table forum_example.post (
id uuid primary key default uuid_generate_v1mc(),
author_id uuid 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.';
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 'Get’s 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 position(search in post.headline) > 0 or position(search in post.body) > 0
$$ language sql stable;
comment on function forum_example.search_posts(text) is 'Returns posts containing a given search term.';
alter table forum_example.post add column updated_at timestamp default now();
---------------------------------------------------------------------------------------------------------------------
/* Trigger - updatedAt */
---------------------------------------------------------------------------------------------------------------------
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();
---------------------------------------------------------------------------------------------------------------------
/* Accounts */
---------------------------------------------------------------------------------------------------------------------
create table forum_example_private.person_account (
person_id uuid 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 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.';
---------------------------------------------------------------------------------------------------------------------
/* Roles */
---------------------------------------------------------------------------------------------------------------------
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;
---------------------------------------------------------------------------------------------------------------------
/* Auth */
---------------------------------------------------------------------------------------------------------------------
create type forum_example.jwt_token as (
role text,
person_id uuid,
exp bigint
);
create function forum_example.authenticate(
email text,
password text
) returns forum_example.jwt_token as $$
declare
account forum_example_private.person_account;
begin
select a.* into account
from forum_example_private.person_account as a
where a.email = $1;
if account.password_hash = crypt(password, account.password_hash) then
return ('forum_example_person', account.person_id, extract(epoch from (now() + interval '2 days')))::forum_example.jwt_token;
else
return null;
end if;
end;
$$ language plpgsql 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. This token expires in 2 days.';
create function forum_example.current_person() returns forum_example.person as $$
select *
from forum_example.person
where id = nullif(current_setting('jwt.claims.person_id', true), '')::uuid
$$ language sql stable;
comment on function forum_example.current_person() is 'Gets the person who was identified by our JWT.';
---------------------------------------------------------------------------------------------------------------------
/* Permissions */
---------------------------------------------------------------------------------------------------------------------
-- after schema creation and before function creation
alter default privileges revoke execute on functions from public;
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 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.register_person(text, text, text, text) to forum_example_anonymous;
---------------------------------------------------------------------------------------------------------------------
/* ROW Level Security */
---------------------------------------------------------------------------------------------------------------------
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 = nullif(current_setting('jwt.claims.person_id', true), '')::uuid);
create policy delete_person on forum_example.person for delete to forum_example_person
using (id = nullif(current_setting('jwt.claims.person_id', true), '')::uuid);
create policy insert_post on forum_example.post for insert to forum_example_person
with check (author_id = nullif(current_setting('jwt.claims.person_id', true), '')::uuid);
create policy update_post on forum_example.post for update to forum_example_person
using (author_id = nullif(current_setting('jwt.claims.person_id', true), '')::uuid);
create policy delete_post on forum_example.post for delete to forum_example_person
using (author_id = nullif(current_setting('jwt.claims.person_id', true), '')::uuid);
---------------------------------------------------------------------------------------------------------------------
/* End */
---------------------------------------------------------------------------------------------------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment