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

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

また、タイトルでは「Supabaseのデータベース関数」（英語でSupabase Database Functions）と書きましたが、実際にはただのPostgres Functionなので、Postgres Functionsについてすでにご存知の方はそんなに新しい情報はないかもしれません。

基本的な構造

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

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

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

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

select hello_world();

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

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

トランザクション実装例

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

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を使って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が入る形になっていました。

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に慣れていない人は最初データベース関数に触れるのに少し抵抗があるかなと思いますので、少しでもお役に立てたならば光栄です。