Supabaseのデータベース関数の構造やパーミッション周りについて

Supabaseではより高度なクエリーや[地形データ](https://supabase.com/docs/guides/database/extensions/postgis)などのデータ型を使いたい際、もしくはトランザクションを行いたい際などにデータベース関数を使うことで実装できるような形になっています。今回はデータベース関数の基礎的な部分をお伝えいたします。内容は[こちら](https://supabase.com/docs/guides/database/functions)の公式ガイドとほぼ同じです。

また、タイトルでは「Supabaseのデータベース関数」（英語でSupabase Database Functions）と書きましたが、実際にはただの[Postgres Function](https://www.postgresql.org/docs/current/sql-createfunction.html)なので、Postgres Functionsについてすでにご存知の方はそんなに新しい情報はないかもしれません。

## 基本的な構造
まずはデータベース関数の基本的な書き方を見ていきましょう。

```sql
create or replace function hello_world() -- 1
returns text -- 2
language sql -- 3
as $$  -- 4
  select 'hello world';  -- 5
$$; --6
```

1. `create or replace function hello_world()`: データベース関数を宣言しています。`hello_world`が関数の名前で、`create or replace`と書くことで`hello_world`という関数がすでにある場合は上書き、ない場合は新規作成することができます。
2. `returns text`: 関数の戻り値の型です。特に戻り値がない場合は`void`に設定します。
3. `language sql`: 関数定義の言語です。普通のSQLで書く他に`plpgsql`, `plv8`, `plpython`などを使うことができます。
4. `as $$`: 関数の定義が始まることを知らせるシンボルです。
5. `select 'hello world';`: 関数のメインの定義部分で、ここに記載されたものを実行する形になります。
6. `$$;`: 関数の終わりにつけるシンボルです。

関数が定義できたらSQLで呼ぶことができます。

```sql
select hello_world();
```

また、Supabaseのクライアントライブラリーを使っても呼び出すことができます。

```typescript
const { data, error } = await supabase.rpc('hello_world')
```

## トランザクション実装例

次はもう少し複雑な例を見ていきましょう。例えば、チャットアプリで「チャットの部屋を新規作成し、その部屋に作成者のユーザーを追加する」を行ってくれる関数はこのような形で実装できます。

```sql
create or replace function create_room(name text default null)
returns rooms
language plpgsql
as $$
    declare
        new_room rooms;
    begin
        insert into public.rooms (name)
        values(create_room.name)

        returning * into new_room;

        insert into room_participants(room_id, profile_id)
        values(new_room.id, auth.uid());

        return new_room;
    end
$$ security definer;
```

こちらの関数はチャットアプリ内で新しいチャット部屋を作ることを想定とした関数です。まず、`(name text default null)`の箇所で`name`という`text`型でデフォルト値が`null`の引数を取っていることがわかります。

戻り値の`returns rooms`の部分ですが、これは`rooms`というテーブルがあり、そのテーブルの一行と同じ型を返すことを示しています。

さらに、今度は`sql`ではなく`plpgsql`を使って関数が書かれています。`plpgsql`はぱっと見SQLっぽいのですが、`declare`で変数を用意できたりもう少しできることの幅がある言語です。

メインの定義では、まず`new_room`という`rooms`型の変数を作っておき、`rooms`テーブルに新しく行を挿入、そしてその挿入でできた新しい行を`new_room`に代入します。あとは`room_participants`テーブルというどのユーザーがどのチャット部屋に入っているかを示すテーブルにこの関数を呼んだ人を挿入することで新規作成したチャット部屋に自動的に作成者を追加し、作成した部屋情報を返して終わりです。

このように簡単なトランザクションが実装できていることがわかります。

一番最後に`security definer`と書いてありますね。こちらも少し説明できたらなと思います。

## データベース関数のパーミッションについて

データベース関数を定義するときに、その関数を呼んだ際に実行されるパーミッションを以下の２種類で定義することができます。

- `security definer`: データベース関数を呼んだ際には常に関数を設定した人のパーミッションを使って実行する
- `security invoker`: データベース関数を呼んだ際に関数を呼んだ人のパーミッションを使って実行する

例えば、上記の`create_room`の例で[Row Level Security](https://supabase.com/docs/guides/auth/row-level-security)を使って`rooms`テーブルに新しい行を挿入することは`authenticated`ユーザー（Supabase authでログインしたユーザー）はできないように設定されていたとしましょう。

このような場合、例えば`create_room`が`security invoker`になっていた場合、クライアントから`.rpc()`を使って`create_room`関数を呼ぶと、呼んだユーザーの権限で関数を実行することになります。この場合、RLSで`rooms`テーブルに新しい行を挿入することはできないようになっているので、シンプルにこの関数はエラーを吐いて終わりです。

それに対して、`security definer`と設定されていると、この関数を定義したユーザーの権限で関数を呼ぶことになります。Supabaseで関数を定義するときはダッシュボードのSQLエディターなどからSQLを実行して関数を作ることになりますので、その場合は`postgres`ユーザー権限で関数を作ることになります。このユーザーはデフォルトでRLSがかかっていてもテーブルデータを全て読み書きすることができるので、`security definer`に設定してあげれば問題なくどのユーザーがその関数をコールしても`rooms`テーブルへのデータの挿入などが実行できる形になります。

しかし、`security_definer`の関数は使い方を間違えるとセキュリティホールになりとても危険です。上記のチャット部屋を作る関数の例がこのような定義だった場合を考えてみましょう。ここでは`user_id`という引数が追加され、作成された部屋にそのユーザーを追加するような形になっています。上の例では`auth.uid()`という関数を呼び自動的に関数を呼んでいるユーザーのユーザーIDが入る形になっていました。

```sql
create or replace function create_room(name text default null, user_id uuid)
returns rooms
language plpgsql
as $$
    declare
        new_room rooms;
    begin
        insert into public.rooms (name)
        values(create_room.name)

        returning * into new_room;

        insert into room_participants(room_id, profile_id)
        values(new_room.id, user_id);

        return new_room;
    end
$$ security definer;
```

このように書いてしまうと、どのユーザーでもどの他のユーザーのために好き勝手チャット部屋を作ることができてしまいます。この例の場合だと、空のチャット部屋を作るだけなので、特に大きなセキュリティリスクではないかもしれませんが、これが特定のユーザーデータにアクセスするような関数だった場合は話が違います。

このように大いなる力には大きなる責任がともない、`security_definer`関数を作るときは気をつけなければいけません。

関数は特に指定しなかった場合は`security_invoker`で作成される形になっているので、そこは安心です。

## 最後に

今回はSupabaseのデータベースに関数に関して少しだけ深堀してみました。僕もそうだったのですが、SQLに慣れていない人は最初データベース関数を触る事に少し抵抗があるかなと思いますので、少しでもお役に立てたならば光栄です。