SolidJS で Supabase の Row Level Security を試してみた…の続き（補足）

:::note warn
**2022/12/6 追記：**
GitHub リポジトリ側を supabase-js v2 にバージョンアップした関係で、リンク先のソースコードとは行番号や内容が噛み合わなくなっています。
（後日、この記事を supabase-js v2 に合わせて修正する予定）
:::

先日、

- **[SolidJS で Supabase の Row Level Security を試してみた](https://qiita.com/hmatsu47/items/b6ba2d2994e1632c13ea)**

https://qiita.com/hmatsu47/items/b6ba2d2994e1632c13ea

の、

- **[Row Level Security による認可](https://qiita.com/hmatsu47/items/b6ba2d2994e1632c13ea#row-level-security-%E3%81%AB%E3%82%88%E3%82%8B%E8%AA%8D%E5%8F%AF)**

で、

> - 更新（`UPDATE`）時に以下を`OR`条件でチェック
>   - 実行ユーザと投稿者（`userid`）の一致
>   - 投稿の「他のユーザに許可する操作」が「`3`:読み取りと編集」
>   - 実はこれらだけでは不十分ですが（`userid`列値を投稿者以外に上書き更新できるため）、今回は内容を簡単にするためにこのラインにとどめています
>     - **2022/5/29 追記：**[リポジトリ](https://github.com/hmatsu47/pgunconf-sample-app)は修正版になっています
>       - [db-create.sql](https://github.com/hmatsu47/pgunconf-sample-app/blob/main/db-create.sql)
>       - [EditItem.tsx](https://github.com/hmatsu47/pgunconf-sample-app/blob/main/src/EditItem.tsx)
>       - [List.tsx](https://github.com/hmatsu47/pgunconf-sample-app/blob/main/src/List.tsx)

と記した点の補足です。

## 何が問題か？

先の箇条書きにもありますが、

- 「他のユーザに許可する操作」（`note_type`）が「`3`:読み取りと編集」の投稿で、
- 投稿者とは別のユーザが
- `userid`列値や`note_type`列値を上書きできる

ので、

- 本来の投稿者ではなく他のユーザに投稿者が入れ替わってしまう
- 本来の投稿者が上書き編集できなくなってしまう

事態が発生します。

そういう仕様のアプリケーションであれば問題はないのですが、先の記事のサンプルの仕様は、

- 投稿者は（当初）投稿した本人の名前
- 他ユーザに許可する権限の変更を他ユーザに認めていない

でしたので、

- ブラウザの開発者ツールでコードを書き換えて実行される等

で、データを不正な状態に書き換えることができてしまう、といえます。

## 回避策

投稿者（`userid`）や「他のユーザに許可する操作」（`note_type`）が、投稿者以外に書き換えできないように何らかの形で制約を追加します。

一例として、

- 投稿者（`authors`）テーブルを追加し、ここに`articles`テーブルと同じ`id`および`userid`の値を投稿時に`INSERT`する
- `id`列で`articles`テーブルへの外部キー制約を設定する
- `authors`テーブルに追加の Row Level Security を設定する
- `articles`からの行`DELETE`直前に、対応する`authors`テーブルの行を`DELETE`する

方法を示します。

### authors テーブル（投稿者を保管）

**[db-create.sql](https://github.com/hmatsu47/pgunconf-sample-app/blob/main/db-create.sql#L80)**（80 行目〜）

```sql:authorsテーブルとRLS
create table authors (
  id bigint not null,
  updated_at timestamp with time zone,
  userid uuid not null,

  primary key (id)
);

alter table authors enable row level security;

create policy "Authenticated Users can view all article-authors."
  on authors for select
  using ( auth.role() = 'authenticated' );

create policy "Users can insert their own article-authors."
  on authors for insert
  with check ( auth.uid() = authors.userid );

create policy "Users can delete their own article-authors."
  on authors for delete
  using ( ( auth.uid() = authors.userid ) );
```

- 参照（`SELECT`）は認証ユーザなら誰でも読める
- 挿入（`INSERT`）時に実行ユーザと挿入する投稿者列（`userid`）の一致をチェック
- 更新（`UPDATE`）は許可しない（記述なし）
- 削除（`DELETE`）時に実行ユーザと対象行の投稿者列（`userid`）の一致をチェック

#### articles テーブル（投稿情報を保管）の Row Level Security 設定変更

**[db-create.sql](https://github.com/hmatsu47/pgunconf-sample-app/blob/main/db-create.sql#L102)**（102 行目〜）

```sql:articlesテーブルのRLS変更
alter table authors add foreign key (id) references articles;

alter policy "Users can update their own articles or free-updatable articles."
  on articles
  using ( (
            ( auth.uid() = articles.userid ) or ( articles.note_type = 3 )
          ) and
          ( articles.userid = (
            select userid from authors where articles.id = authors.id)
          )
        );
```

- 更新（`UPDATE`）時に以前の設定（『実行ユーザと投稿者（`userid`）の一致』および『投稿の「他のユーザに許可する操作」が「`3`:読み取りと編集」』の`OR`条件でチェック）と`AND`条件で以下をチェック
  - `id`列および`userid`列の値が一致する`authors`テーブル行の存在

これらを設定することで、

- `UPDATE`時に`userid`を変更する
- `UPDATE`時に`userid`のユーザ **以外** が`note_type`を変更する

を防ぐことができます。

※アプリケーション側のコードについての説明は省略します。[GitHub リポジトリ](https://github.com/hmatsu47/pgunconf-sample-app)で確認してください。

- **[pgunconf-sample-app](https://github.com/hmatsu47/pgunconf-sample-app)**

https://github.com/hmatsu47/pgunconf-sample-app

#### 追加：`note_type`の値の範囲を`CHECK`制約で制限する

Row Level Security とは別の話になりますが、アプリケーションの改ざんにより不正なデータが保存されるのを防ぐために、`articles`テーブルに`CHECK`制約を追加します。

**[db-create.sql](https://github.com/hmatsu47/pgunconf-sample-app/blob/main/db-create.sql#L114)**（114 行目〜）

```sql:articlesテーブルにCHECK制約追加
alter table articles
  add constraint note_type_range check (note_type between 1 and 3);
```

## 残った課題

これで一応対処はできるのですが、本来`articles`テーブルへのデータ行挿入と、対応する`authors`テーブルへの行挿入、およびそれらの行削除は **トランザクションで処理すべき** です。

ところが、Supabase のクエリビルダには今のところトランザクションの機能がないので（何度か GitHub の Issue に上がって対処されずに Close されている模様？）、クエリビルダではなくストアドファンクションを使って RPC で実行する必要があります。

今回はここまでの対応は行いませんでした。

**参考：**

- **[Supabaseのデータベースを使うときに役立つ情報・トランザクション](https://qiita.com/kabochapo/items/26b1bb753116a6904664#%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3)（kabochapo さん）**

https://qiita.com/kabochapo/items/26b1bb753116a6904664#%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3

## というわけで

フロントエンドのアプリケーションコードは悪意のあるユーザが書き換えて実行することができます。

Supabase で Database の自動生成 API を使って（ユーザを識別してデータアクセスする）ブラウザ向けアプリケーションを作るときは、Row Level Security でしっかりアクセス制限を掛けましょう。

### おまけ：第 33 回 PostgreSQL アンカンファレンス＠オンラインの発表資料

https://speakerdeck.com/hmatsu47/solidjs-de-supabase-false-row-level-security-woshi-sitemita