はじめに

RDB（PostgreSQL）の利用に、Supabaseを採用したので手順を残しておきます。

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

Supabase導入

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

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

RLSについて

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

DB設定

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

Supabaseより

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

個人的メモ

推奨されている通りUTCで扱うのが良さそう
画面出力時のみ時差の9時間を足す

anonユーザーの権限設定

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

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

-- 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;

ポイント

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

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

2-1. テーブル

ALTER DEFAULT privileges IN SCHEMA public REVOKE all ON tables FROM anon;

2-2. 関数

ALTER DEFAULT privileges IN SCHEMA public REVOKE all ON functions FROM anon;

2-3. シーケンス

ALTER DEFAULT privileges IN SCHEMA public REVOKE all ON sequences FROM anon;

ポイント

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

3. 権限付与
anonユーザーに権限付与したいテーブルが出てきたらGRANT文を実行する。

-- testテーブルのSelect権限を付与
GRANT SELECT ON test TO anon;

RLSポリシー設定

前提知識

RLSとはRow Level Securityの略で、Select, Insert, Update, Deleteの操作を行単位で制御できる。
これにより下記のような制御が実現可能になる。

ユーザーによる制御
- Aが作成したデータはAのみ閲覧、編集可能
- BはAのデータを閲覧することすら不可能
その他
- ログイン有無による制御など

注意

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

ポリシー設定

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

testテーブルに制限をかけない場合

-- 例） usingにtrueを設定する
create policy "test_select"
on "public"."test"
as PERMISSIVE
for SELECT
to public
using (
  true
);

testテーブルに制限をかける場合

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

auth.uid()について

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

認証設定

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

updated_atの自動更新

created_at/updated_atはDefault Valueをnow()と設定することで現在日時が登録されるが、updated_atに関しては更新時に最新の現在日時を自動で設定したい。
関数の作成とトリガーを設定することで実現できた。

-- 関数（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();

ポイント

該当テーブルを更新することでupdated_atが最新の日時になっていることを確認できる

関数は使いまわせるが、トリガーに関してはテーブル毎に設定する必要がある。
トリガー名を「update_timestamp_of_テーブル名」とするなど命名規約を作っておくと管理しやすそう。

-- 命名規約に沿ったトリガー一覧の取得
SELECT * FROM pg_trigger WHERE tgname LIKE 'update_timestamp_of_%';

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

個人的メモ

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

最後に

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

参考リンク

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