🍆

【Supabase】idで使いがちな auto_increment へのイタズラの対策をする

2023/11/15に公開

PostgreSQL

Supabaseではフロントエンド側でINSERTやUPDATEなどのクエリを投げられて便利ですが、その代わりに考慮しなければならないことが多くあります。

その一つに表題の問題があります。

どういう問題なのか？

Supabaseでは ChromeのNetworkタブのリクエストから header情報で Apikey を抽出して Postmanなどから任意のリクエストを投げられてしまう恐れがあります。（これ自体はRLSやCHECK制約をしっかりしていれば怖いものではありません。）

▼その雰囲気がわかる記事

これを悪用する感じで、 INSERTリクエストで idカラムに任意の数字を入れた状態で投げられるとどうなるでしょうか。

例として300件しかないテーブルに 400 というidの値をイタズラで投げられたとします。idカラムの400という値は先行して確保されてしまいます。投稿は300から1ずつ増えていき、ちょうどidの400を踏んだクエリはエラー扱いになってしまいます。再試行すれば正常に処理されますが、ユーザーとしては意味不明な理由でエラーが出て嫌ですよね。ユーザー体験を守るためにも対策しておきたいです。

SQL関数を作ってトリガーで検証して解決！

データ挿入前にトリガーで id の auto_increment() の値が適切かどうかをチェックすれば解決できます。

解決の手順です。

1. 対象のauto_incrementのシーケンスを調べる

SQL

SELECT * FROM pg_sequences WHERE schemaname = 'public';

これをSQLエディタで実行してみましょう。
対象テーブルのidに関するauto_incrementを見つけて sequencename の値（シーケンス名）をメモしておきましょう。

2. SQL関数を作る

SQL

CREATE OR REPLACE FUNCTION public.check_auto_increment()
  RETURNS TRIGGER
  LANGUAGE plpgsql
  SECURITY DEFINER
  SET search_path TO 'public'
AS $function$
BEGIN
  IF NEW.id != (select currval('【ここにシーケンス名】')) THEN
    RAISE EXCEPTION 'idカラムの値は指定できません。';
  END IF;
  RETURN NEW;
END;
$function$;

こんな感じでデータ挿入前の id と今回生成されたauto_incrementの値を比較します。
一致しなければエラー扱いにしてしまう関数です。（ currval で今回のセッションで生成されたauto_incrementの値を参照してくれます。）

※フロント側からidの値がPOSTされなければauto_increment()で生成された値がnew.idに入ってくれています。何か値がPOSTされていれば、new.idにPOSTされた値が入っているので、それをifで判定できるというわけです。

3. トリガーを作る

トリガーはこのようにします。
ポイントは Trigger type を Before にすること。（データ挿入前に検証したいので）

実行して確認してみよう。

id カラムに適当に値を入力してデータ挿入を試してみましょう。
今回生成されたauto_increment() の値と一致しなければエラーになり、クエリが破棄されるかと思います。

これでUXを阻害するようなauto_incrementへのイタズラ対策ができました！

懸念点

トリガーで select currval('【ここにシーケンス名】') という余計なサブクエリが動くのが気になりますね。このクエリは軽いのかどうかが気になります。何か別の良い方法があれば知りたいです！

そもそも...。

idカラムを用意しないというのも手段の一つです。
もしくは auto_increment() をやめて uuid にしてしまうのも良いでしょう。