Zenn
younap Tech Blog
📑

SupabaseとRLSで論理削除をサクッと実装する

Kota Iwabuchi
2025/03/06に公開
SQL
RLS
Supabase
PostgreSQL
tech

はじめに

こんにちは、皆さん。データベース運用において、データの削除方法をより安全かつ柔軟にしたいと考えている方も多いのではないでしょうか。
今回は、Supabase（PostgreSQL）のRLS（Row Level Security）とトリガーを組み合わせて、手間をかけずに論理削除を実装する方法を紹介します。

RLSとトリガーを活用すれば、アプリケーション側で特別な対応をせずに論理削除を実装できます！ が、いくつか注意点もあります。この記事では、実装時の課題とその解決方法を解説します。

論理削除とは

データを物理的に削除せず、削除フラグ（is_deletedなど）を設定して「削除済み」として扱う方法です。これにより誤削除のリスクを軽減し、データの復旧も容易になります。

この方法で嬉しいこと

  • アプリケーション側で論理削除を意識しなくていい

    コード中でis_deletedをselect条件に入れ忘れた、などの問題を防げます。コードの可読性も向上します。
    またクライアントに公開されるSupabaseのクライアントは第三者も利用可能なため、論理削除されたデータの保護にはRLSによる制限が必要です。

  • 管理者側からは全レコードが閲覧可能

    RLSはダッシュボードやConnection Stringを使用してDBに接続したときには適用されません。そのため、管理者側からは引き続き全レコードがSELECTされます。

  • RLSで削除を制限したいとき、単純にDELETEにポリシーを設定すればよく分かりやすい

    複数のUPDATEポリシーを作成するよりも明確です。Supabaseやデータベースに詳しくない開発メンバーにも、ポリシーの目的が伝わりやすくなります。

前提

該当テーブルにboolean型のis_deletedカラムが追加されていること。

RLSのポリシーを設定

RLSは、テーブルの行へのアクセスをユーザーやロールに基づいて制御する機能です。これにより、機密データへのアクセス制限や、特定ユーザーへの表示制限などが可能になります。

論理削除を実装する場合、多くは「削除されていないデータ（is_deleted = false）だけをアプリケーションに表示したい」というケースでしょう。これをRLSで実現するには、次のようなポリシーを作成するだけです。シンプルですね。

CREATE POLICY select_not_deleted ON your_table
FOR SELECT
TO public
USING (is_deleted = false);

このポリシーにより、ユーザーがyour_tableに対してSELECTクエリを実行すると、is_deletedがfalseの行だけが返されます。ここまでは簡単です。

UPDATEでハマる

しかし、ここで問題が発生します。論理削除を行うためには、UPDATE文でis_deletedカラムをtrueに更新する必要があります。この時、先程設定したRLSポリシーが邪魔をして、以下のようなエラーが発生します。

ERROR:  new row violates row-level security policy for table "your_table"

これは、PostgreSQLがUPDATE文を処理する際、更新後の行がRLSポリシーに違反していないかをチェックするために起こります。

ポリシー USING (is_deleted = false) は、SELECTだけでなくUPDATE後の行にも適用されるため、「is_deletedをtrueに更新する」という操作がポリシー違反とみなされてしまうのです。

つまり、RLSポリシーでSELECTクエリをis_deleted = falseに限定すると、is_deletedをfalseからtrueにUPDATEできなくなる、という状況に陥ります。

トリガーでDELETE操作をインターセプト

この問題を解決するために、トリガーを使用します。具体的には、DELETE操作をトリガーでインターセプトして、物理削除ではなく論理削除（is_deletedをtrueに更新）に置き換えます。

トリガー関数の作成

まずは、DELETE操作をインターセプトして論理削除を行うトリガー関数を作成します。

create function soft_delete() returns trigger
    security definer
    language plpgsql
as
$$DECLARE
  has_id_column BOOLEAN;
BEGIN
  -- テーブルにidカラムがあるか確認
  SELECT EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = TG_TABLE_SCHEMA
      AND table_name = TG_TABLE_NAME
      AND column_name = 'id'
  ) INTO has_id_column;

  IF has_id_column THEN
    -- idカラムを使用
    EXECUTE format('UPDATE %I SET is_deleted = TRUE WHERE id = $1', TG_TABLE_NAME)
      USING OLD.id;
  ELSE
    -- ctidを使用
    EXECUTE format('UPDATE %I SET is_deleted = TRUE WHERE ctid = $1', TG_TABLE_NAME)
      USING OLD.ctid;
  END IF;

  -- DELETE処理をキャンセル
  RETURN NULL;
END;$$;

このトリガー関数soft_deleteは、idカラムがある場合はそれを使い、ない場合はctid（該当行が格納されているディスクの物理的な位置）を使用します。

ctidの安全性

※同時に別のUPDATEやDELETEトランザクションが実行されている場合は行の物理的な場所が変わる可能性があるため、ctidの使用は完全に安全とは言えませんが、ほとんどの場合は問題ないでしょう。

  1. security definer: RLSのルールを迂回します。 この関数は作成したユーザー（所有者であるpostgresユーザー）の権限で実行されます。

  2. SELECT EXISTS (...) INTO has_id_column;:idカラムがあるかどうかをチェックし、変数に格納します。

  3. EXECUTE format(...): 動的クエリを実行します。TG_TABLE_NAMEは単なる文字列であり、直接そのテーブルを操作できないため、クエリ文字列を生成して実行します。

  4. RETURN NULL;:元のDELETE操作をキャンセルし、物理削除を防ぎます。代わりに、トリガー内で実行したUPDATE処理が適用されます。

トリガーの作成

次に、作成したトリガー関数をテーブルに関連付けます。BEFORE DELETEトリガーとして設定することで、DELETE操作の実行前にトリガーが起動し、物理削除を防ぎます。

CREATE TRIGGER soft_delete_trigger
BEFORE DELETE ON your_table
FOR EACH ROW
EXECUTE FUNCTION soft_delete();

このトリガー soft_delete_trigger は、your_table テーブルに対して DELETE 操作が実行される前に、各行（FOR EACH ROW）に対して soft_delete() 関数を実行するように設定します。

論理削除を実装したいテーブル全てに対してこのトリガーを設定しましょう。
(※is_deletedカラムが必要です)

RLSポリシーとトリガーの連携

これで、RLSポリシーとトリガーを組み合わせ、論理削除が実現できました。

  • SELECT時: RLSポリシー select_not_deleted により、is_deleted = false の行のみが取得できます。

  • DELETE時: soft_delete_triggerがDELETE操作をインターセプトし、物理削除ではなくis_deleted = trueへの更新を行います。このUPDATE操作はRLSポリシーの制限を受けません。なぜなら、UPDATE処理はトリガー関数内でシステム権限で実行されるため、通常ユーザーに適用されるRLSポリシーの影響を受けないからです。

まとめ

この記事では、Supabase（PostgreSQL）のRLSとトリガーを組み合わせた論理削除の実装方法と、その際の課題と解決策について解説しました。

  • RLSポリシーによるSELECT制限と論理削除のためのUPDATE操作の衝突を、トリガーでDELETEをインターセプトすることで解決
  • トリガー関数をsecurity definerで作成することでRLSを回避
  • トリガー内での操作対象テーブル名TG_TABLE_NAMEは単なる文字列なので、動的にクエリを生成する必要がある

この方法を利用すれば、アプリケーション側での変更なしに論理削除を簡潔に実装できます。ぜひ試してみてください。

younap Tech Blog
younap Tech Blog

Discussion