Supabase導入手順＆セキュリティー面含む各種設定

# はじめに
RDB（PostgreSQL）の利用に、Supabaseを採用したので手順を残しておきます。
- 現在動作検証を行いながら進めているので記事は充実させていきます（2024年8月くらいまでは頻繁に更新します）
- 運用が始まってからも気になる箇所があれば確実に更新します（特にセキュリティー面）
- 気をつけてはいますが万が一記事の内容に間違いがありましたらコメントをお願いします

# Supabase導入
DBからデータを取得できるところまでの確認を行う。

1. Supabase側（管理画面）の作業
    1. 会員登録
    1. プロジェクト作成
    1. テーブル作成
    1. 仮データのInsert
    1. RLSオフ
        1. Authentication > PoliciesからRLSを一時的にオフにする
1. クライアント側の作業
    1. （Next.jsなどで）DBからのデータ取得処理実装
    1. 疎通確認
1. Supabase側の作業
    1. RLSオン
        1. Authentication > PoliciesからRLSをオンに戻す
1. 導入完了

:::note info
RLSについて
- 導入時点ではRLSをオフにしないとデータの中身が空になるので一時的に変更している
- セキュリティ面で重要な設定になるので疎通確認後はオンに戻す（RLSについては後述します）
:::

# DB設定
任意でタイムアウト、タイムゾーンなどの変更を行う。
公式ドキュメントを参照してください。

https://supabase.com/docs/guides/database/postgres/configuration

:::note alert
Supabaseより
- タイムゾーンはデフォルトのUTCを使うことを強く推奨
- DB内がUTCで統一されているとタイムゾーン間の計算がかなり簡単になるため
:::

:::note info
個人的メモ
- 推奨されている通りUTCで扱うのが良さそう
- 画面出力時のみ時差の9時間を足す
:::

# anonユーザーの権限設定
API Key（anon key）はクライアント側に渡すため、デフォルトの設定ではAPI Keyを使用し自由にDBを操作される。
API Key経由のDBアクセスは、anonユーザーとして扱われるため、anonユーザーへの権限付与はセキュリティ面で気をつけなければいけない。
API Keyは未ログインユーザーがDBにアクセスするために使用する。

1\. anonユーザーの権限を全て削除

``` sql
-- grantee="anon"が存在することを確認
SELECT grantee, table_name, privilege_type FROM information_schema.role_table_grants WHERE table_schema = 'public' ORDER BY grantee, table_name, privilege_type;

-- 権限削除
REVOKE all privileges ON all tables IN SCHEMA public FROM anon;
```

:::note info
ポイント
- SQLでgrantee="anon"が削除されていることを確認できる
- RLSをオフにしてもクライアント側でAPI Keyを使ったデータ取得ができなくなったことを確認できる
:::

2\. デフォルト権限の変更
今後テーブルなどを作成した際、anonユーザーに権限が付与されないようにする。

2-1\. テーブル
``` sql
ALTER DEFAULT privileges IN SCHEMA public REVOKE all ON tables FROM anon;
```

2-2\. 関数
``` sql
ALTER DEFAULT privileges IN SCHEMA public REVOKE all ON functions FROM anon;
```

2-3\. シーケンス
``` sql
ALTER DEFAULT privileges IN SCHEMA public REVOKE all ON sequences FROM anon;
```

:::note info
ポイント
- 新規でテーブルを作成してもanonユーザーに権限が付与されないことを確認できる
:::


3\. 権限付与
anonユーザーに権限付与したいテーブルが出てきたらGRANT文を実行する。
``` sql
-- testテーブルのSelect権限を付与
GRANT SELECT ON test TO anon;
```


# RLSポリシー設定
### 前提知識
RLSとはRow Level Securityの略で、Select, Insert, Update, Deleteの操作を行単位で制御できる。
これにより下記のような制御が実現可能になる。
- ユーザーによる制御
    - Aが作成したデータはAのみ閲覧、編集可能
    - BはAのデータを閲覧することすら不可能
- その他
    - ログイン有無による制御など

:::note alert
注意
- RLSはデフォルトでオンになっているが、疎通確認などで変更した場合は、Authentication > Policiesからオンに戻す
:::

### ポリシー設定

ポリシーを追加することで細かい制御が可能になるので、各テーブル4つ（Select, Insert, Update, Delete）の設定を行う。
Authentication > Policies > Create a new policy

- testテーブルに制限をかけない場合
``` sql
-- 例） usingにtrueを設定する
create policy "test_select"
on "public"."test"
as PERMISSIVE
for SELECT
to public
using (
  true
);
```

- testテーブルに制限をかける場合
``` sql
-- 例） auth.uid()を利用しデータを所有するユーザーにのみSelect権限を与える
create policy "test_select"
on "public"."test"
as PERMISSIVE
for SELECT
to public
using (
  (auth.uid() = user_id)
);
```

:::note info
auth.uid()について
- auth.uid()はログイン中のユーザーIDを返す
- Supabaseが提供している関数
- テーブル定義でも利用可能でuser_idのDefault Valueに設定すると便利
:::

# 認証設定
- Auth Providers、Rate Limits、Email Templates、URL Configuration
    - メール認証に関する詳細設定や、他サービスとの連携が行える
- ログイン失敗回数
    - 連続でパスワードを間違えた場合、第三者がログインしようとしている可能性もあるため失敗回数を決めて止めたい
    - <font color="Red">後日記載します</font>
- ログイン有効時間
    - <font color="Red">後日記載します</font>
- 2段階認証の設定
    - <font color="Red">後日記載します</font>
- 新規会員登録を不特定多数のユーザーにされたくない場合は注意が必要
    - API Keyを悪用され新規会員登録画面の実装が可能なため、不特定多数のユーザーに新規会員登録を行われる可能性がある
    - Authentication > Email Templates > Confirm signupからConfirmationURLを削除することで回避が可能そう
    - その場合、ユーザーの作成は運営者がSupabaseの管理画面から行い、仮パスワードでログインしてもらうという手間は発生する
- ユーザーがログインできなくなった時の対応は想定しておきたい
    - メールアドレスを忘れた
        - auth.usersテーブルからユーザー情報の確認ができる
        - SQL Editorから下記SQLを実行しemailの確認が可能
        - SELECT * FROM auth.users;
    - 登録しているメールアドレスが使えなくなった
        - SQL Editorから下記SQLを実行しemailの変更が可能 <font color="Red">※ユーザー情報を破壊してしまう可能性があるのでWhere句の指定忘れ、誤りには気をつけてください</font>
        - UPDATE auth.users SET email = 'new_email@example.com' WHERE email = 'old_email@example.com';
    - パスワードを忘れた
        - <font color="Red">後日記載します</font>
    - 2段階認証ができない状況になった
        - <font color="Red">後日記載します</font>

# updated_atの自動更新
created_at/updated_atはDefault Valueをnow()と設定することで現在日時が登録されるが、updated_atに関しては更新時に最新の現在日時を自動で設定したい。
関数の作成とトリガーを設定することで実現できた。
``` sql
-- 関数（update_timestamp）の定義
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- トリガー（update_timestamp_of_test）の定義
CREATE TRIGGER update_timestamp_of_test
BEFORE UPDATE ON test
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
```

:::note info
ポイント
- 該当テーブルを更新することでupdated_atが最新の日時になっていることを確認できる
:::

関数は使いまわせるが、トリガーに関してはテーブル毎に設定する必要がある。
トリガー名を「update_timestamp_of_テーブル名」とするなど命名規約を作っておくと管理しやすそう。
``` sql
-- 命名規約に沿ったトリガー一覧の取得
SELECT * FROM pg_trigger WHERE tgname LIKE 'update_timestamp_of_%';

-- トリガー（testテーブルのupdate_timestamp_of_test）の削除
DROP TRIGGER update_timestamp_of_test ON test;
```

:::note warn
個人的メモ
- テーブル毎にトリガーを設定するのは面倒＆漏れる可能性も出てくるので良い方法を見つけたい（テーブル作成時に自動でトリガーを設定するなど）
- updated_atのもっと良い自動更新方法はあるかも（軽くしか調べられていない）
:::

# 最後に
最後に宣伝失礼します。
お酒が好きな方はスマホアプリ「ビールねこ」をダウンロードしてみてください。
ビール好きはもちろん、ビールが嫌いな人でもこれを見れば好きになれるはずです笑

https://beerncat.com/app?id=qiita

# 参考リンク
下記記事を参考にさせていただきました。ありがとうございます。

https://zenn.dev/k_log24/articles/ff1581de72b0aa

https://qiita.com/masakinihirota/items/7f65f732ecbafbd5cb00

https://supabase.com/docs