Open2021/07/22にコメント追加5

PostgraphileのPostgreSQL Schema Designを読む

adwd

フォーラムの実装を例として、Postgraphileを使うのに良いPostgreSQLスキーマ設計を教えてくれる。

adwd

この記事を読むための事前知識

adwd

The Basic

create schema forum_example;
create schema forum_example_private;

スキーマを2つ作る。privateがついてない方はユーザーに公開する。通常はアプリケーションサーバーだけがDBに触るが、ユーザーが直接DBを触るイメージ。普通のやり方ではないが、Postgresはそういう事もできるセキュリティ機能を備えている。

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()
);

first_name text not null check (char_length(first_name) < 80)
- Postgresすごい

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.';

コメントをつけるとPostgraphileからでもそれを見れる
- Markdownを書いてもいいらしい。Graphiqlとがが対応しているらしい

create type forum_example.post_topic as enum (
  'discussion',
  'inspiration',
  'help',
  'showcase'
);

Postgresはカスタムタイプが作れる

enum PostTopic {
  DISCUSSION
  INSPIRATION
  HELP
  SHOWCASE
}

PostgraphileがGraphQL上でこういうEnumにしてくれる。Composite Typeならこうなる

create type my_schema.my_type as (
  foo integer,
  bar integer
);

type MyType {
  foo: Int
  bar: Int
}

postテーブルを作る

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.';

adwd

Database Functions

CREATE FUNCTIONはめちゃすごいらしい。JSやRubyでも書ける。とはいえSQLで関数書くのが辛くてもJS/Rubyで書くのはおすすめじゃないらしい。

create function add(a int, b int) returns int as $$
 select a + b
$$ language sql stable;

$$ 関数の始まりと終わり
language sql stable
- rubyだったら language plruby
- stable はデータベースを変化しないことを示す

3つの関数：ユーザーのフルネーム、投稿の要約、最新の投稿を得る関数を定義する

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 'Get’s the latest post written by the person.';

上記のSQL関数は stable を使うとかの条件を満たしていて、Postgraphileはcomputed fieldとして扱う。そのため Person typeに fullName とかが生えてくる。

type Person {
  id: Int!
  firstName: String!
  lastName: String
  ...
  fullName: String
  latestPost: Post
}

SQL関数は単一の値だけでなくテーブル全体を返すことができて、例えば投稿を検索する機能が実装できる。

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.';

returns setof forum_example.post という書き方がポイント。Postgraphileはこの結果をRelay Connectionとして返す。便利。 returns post[] という書き方だと単なる配列なのでConnectionにはならない。

Triggers

CREATE TRIGGER で作成できるトリガーはINSERT/UPDATE/DELETEとかにフックして処理を走らせることができる。
updateをフックにして更新される updated_at を追加する。

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();

set_updated_at はprivateに作る。ユーザーが直接使うものではないので
CPU負荷の高い処理はLISTEN/NOTIFYで処理を非同期に逃せる

adwd

Authentication and Authorization

ユーザーのログイン情報を扱うテーブルをprivateに定義する。

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.';

privateにEmail、ハッシュ化されたパスワードを置くことでPersonを参照したときにこれらが誤って流出することが防げる。

create extension if not exists "pgcrypto";

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.';

language plpgsql strict security definer
- strict 入力がnullならPostgresはnullを返して関数を実行しない
- security definer この関数の定義者の権限で関数が実行される。ユーザーはprivateに触れないがこの関数の定義者はできる

create role forum_example_postgraphile login password 'xyz';

PostgraphileがPostgresにアクセスするためのロールを作る。このロールを使って以下のようにPostgraphileを起動できる

postgraphile -c postgres://forum_example_postgraphile:xyz@localhost/mydb

create role forum_example_anonymous;
grant forum_example_anonymous to forum_example_postgraphile;

login password がないのでログインはできない。 grant a to b でaのロールが持つ権限をbに持たせることができる。ログインしたユーザー向けのロールを作る。

create role forum_example_person;
grant forum_example_person to forum_example_postgraphile;

anonymousとpersonを合わせた権限をPostgraphileに与えるようにする。なのでPostgraphile越しに非ログイン・ログインユーザーの処理を実行できる。

PostgraphileはJWTを使う。SecretでJWTから読みだしたclaimをローカルトランザクションに設定する。roleは特別扱いされてroleに値が入る。

claim

{
  "role": "forum_example_person",
  "person_id": 1234,
  "exp": ...
}

set local jwt.claims.role to 'forum_example_person'
set local jwt.claims.person_id to 1234
set local jwt.claims.exp to ...

set local role to 'forum_example_person'

このようにJWTにロールを設定することでPostgraphileからのアクセスにロールを設定し、それによって権限を制御できる。
JWTは一度発行すると失効できないのでリフレッシュトークンなどを用いて対策する。

create type forum_example.jwt_token as (
  role text,
  person_id integer,
  exp bigint
);

PostgraphileのCLIで --jwt-token-identifier <identifier> のようにオプションで渡したComposite TypeをJWTとして扱われる。ユーザーのEmailとパスワードが正しければJWTを返す関数を定義する。

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.';

ユーザーの権限を設定する前に、現在のユーザーを取得するUtility関数を定義する。

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), '')::integer
$$ language sql stable;

comment on function forum_example.current_person() is 'Gets the person who was identified by our JWT.';

PostgraphileがJWTの値を設定するので、それを使っている。

set local jwt.claims.person_id to 1234;

where id = nullif(current_setting('jwt.claims.person_id', true), '')::integer

-- after schema creation and before function creation
-- 関数がデフォルトで誰でも実行可能なのを変える
alter default privileges revoke execute on functions from public;

-- anonymous/personにforum_exampleスキーマを公開する（INSERTとかができるわけではない）
grant usage on schema forum_example to forum_example_anonymous, forum_example_person;


-- anonymous/personにpersonテーブルでSELECTする権限を与える
grant select on table forum_example.person to forum_example_anonymous, forum_example_person;

-- ログインユーザーはpersonテーブルでupdate/deleteできる（後の行で修正）。anonymousはできない
grant update, delete on table forum_example.person to forum_example_person;

-- anonymous/personはpostからSELECTできる。personはINSERT/UPDATE/DELETEできる。
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;

-- idカラムがserialなので、INSERTするときにidの次の値が必要
grant usage on sequence forum_example.post_id_seq to forum_example_person;


-- 各関数の実行権限をanonymous/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;

これで基本的な権限は設定できたが、他のユーザーの投稿をUPDATE/DELETEできてほしくはない。そこでRow level Securityを使う。

Row Level Security

RLSを有効にする

alter table forum_example.person enable row level security;
alter table forum_example.post enable row level security;

これで select * from forum_example.person がanonymous/personからリードもライトもできなくなる。

create policy select_person on forum_example.person for select
  using (true);

create policy select_post on forum_example.post for select
  using (true);

これでanonymous/personはperson/postでSELECTできるようになる。

create policy update_person on forum_example.person for update to forum_example_person
  using (id = nullif(current_setting('jwt.claims.person_id', true), '')::integer);

create policy delete_person on forum_example.person for delete to forum_example_person
  using (id = nullif(current_setting('jwt.claims.person_id', true), '')::integer);

これでログインユーザーが自分（person）をUPDATE/SELECTできる。

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), '')::integer);

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), '')::integer);

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), '')::integer);

postをINSERT/UPDATE/DELETEするPolicy
with check using の違い
- 雑に言うと、 using は命令が実行される前にチェックされる。 with check は命令の実行後にチェックされ、失敗の場合は命令はリジェクトされる。INSERTは with check でDELETEは using になる

ここまで設定してCLIで起動するのはこうなる

postgraphile \
  --connection postgres://forum_example_postgraphile:xyz@localhost \
  --schema forum_example \
  --default-role forum_example_anonymous \
  --jwt-secret keyboard_kitten \
  --jwt-token-identifier forum_example.jwt_token