転んで覚える RLS (NEXT + Prisma + Supabase)

こんにちは。
何と Qiita には４年ぶりの投稿になります。
さて、今回は、業務で利用している PostgreSQL で利用できる RLS (Row Level Security) について、ハマった点を書いてみたいと思います。私が知らなかっただけのポイント満載かもしれませんが、Google 検索しても Copilot 君に聞いてもなかなか分からなかった点も多く、RLS を使ってみようかなという皆様のお役に立てれば幸いです。

なお、本稿ではフレームワークとして Next.js、データベースサービスとして Supabase、ORM として prisma を使うことを想定しています。（楽をしようとしたことが問題を引き起こしているとも言えるが...）

RLS とは

砕いて言えば、CRUD 操作時、自動的に追加の制約をかける機能です。
実際には WHERE 節に RLS で設定した内容が追加されます。CRUD 全てに同じ条件を書くこともできますし、別々に設定することもできます。マルチテナント環境では必須の機能です。
ドキュメントは、こちらをご覧ください。

※ RLS アクセスの最適化については色々な記事で言及されているので、本稿では触れません。

今回のプロジェクトの概要

Supabase の RLS 設定はいくつかのテンプレートがあって、そこから選択する分にはサクサク RLS が作れるのですが、今回はそういかない理由がありました。

1. User テーブルでユーザを管理していない
2. 替わりの RealUser テーブルの ID は integer で管理したい
3. ユーザは UserGroup に n:1 で所属する
4. RealUser テーブルには特権ユーザを示す admin フィールド (boolean) がある
5. JWT (Json Web Token) には Payload として、RealUser テーブルの ID と admin を記録する
6. DB アクセスには ORM として prisma を使用する
7. 実行環境は開発、プレビュー、サービスの3つがあり、同じ制約を課したい

参考までに、schema.prisma の内容を示します。

schema.prisma

model UserGroup {
  ID Int @id @default(autoincrement())
  name text
}

model RealUser {
  ID Int @id @default(autoincrement())
  userGroupID Int
  userGroup UserGroup @relation(fields: [userGroupID], references:[ID], onDelete: cascade)
  name text
  admin boolean
}

ハマりポイント１： supabase の auth.uid() が使えない

何も考えずに next-auth を使うと User テーブルの UID フィールドを参照するようです。JWT 作成時に claim の sub 値として自動的にセットされます。
実際、多くの RLS の記事では auth.uid() を使ってUser.id の判定をしていますが、今回はそもそも User テーブルを使用しないので、auth.uid() は使えません。

また、auth.uid() の型は UUID 固定になっています。RealUsesr テーブルの ID は integer なので、その点からもこの方法は使えないです。

ハマりポイント２： Supabase エディタが頭良すぎる

Supabase には背後にある PostgreSQL DB の RLS を編集するエディタがあります。
これがまた高性能...
実際、current_setting('request.jwt.claim.sub') と書くと auth.uid() に変換されてしまいます。（あれ？Sub クレームは uuid 必須でしたっけ？？）

ハマりポイント３： Prisma を使う場合、自分で set_config を書く必要がある

Supabase ベッタリで行くなら supabase の client を使った方が便利です。JWT のクレームは自動で展開してくれますし。
が、DB アクセスに prisma のような ORM をワンクッション置き、アクセスを抽象化したり、トランザクションを組んだりすることは普通にあると思います。

この場合、JWT の claims は set_config を使い、自分で設定する必要があります。

import { NextRequest, NextResponse } from 'next/server'
import { getToken } from 'next-auth'
import { PrismaClient } from '@prisma/client'

type Resp<T> = {
  data?: T
  err?: string
}

type RealUser = {
  ID: int,
  userGroupID: int,
  name: string,
  admin: boolean
}

type JWTRealUser = {
  user: {
    ID: int,
    admin: boolean
  }
}

type UserGroup = {
  ID: int,
  name: string
}

export async function GET(req: NextRequest): Promise<NextResponse<Resp<UserGroup[]>> {
  const token = (await getToken({ req })) as JWTRealUser | null
  if (!token) {
    return NextResponse.json({ err: 'Unauthorized' }, { status: 401 })
  }
  const ctoken= { user: { ID: token.user.ID, admin: token.admin } }
  const claims = JSON.stringify(ctoken)
  const prisma = new PrismaClient()
  try {
    const results: UserGroup[] = await prisma.$transaction(async (tx) => {
      await tx.$executeRawUnsafe(`SELECT set_config('request.jwt.claims', '${claims}', true);`)
      return (await tx.UserGroup.findMany({
        select: {
          ID: true,
          name: true,
        }
      }))
    }).map((e) => { ID: e.ID, name: e.name })
    return NextResponse.json({data: results })
  } catch (error) {
    return NextResponse.json({err: 'rollbacked'}, {status: 500})
  }
}

ハマりポイント４： Next.js から prisma を使う場合、transaction を組むか否かで注意する

前項では set_config の第３パラメータをサクッと書いてしまいましたが、マニュアルによると、transaction 内のみ定義する場合 true, session で保持する場合 false を書くことになっています。

プログラムとして、DB アクセスを抽象化し、例えば repository クラスに押し込めるような記述をすることは少なくないと思います。new PrismaClient() を repository クラス内で行う実装も考えられますが、transaction を考慮すると repository クラスの引数に持ってくる必要があります。

class UserGroupRepository {
  private prisma
  constructor(prisma: PrismaClient) {
    this.prisma = prisma
  }

  getUserGroup() {
    return this.prisma.UserGroup.findMany({
      select: {
        ID: true,
        name: true,
      }
    })
  }
}

transaction を組めない場合は session ワイドに JWT を設定することになりますが、どうやって設定するか悩むことになります。

ハマりポイント５： set_config のタイミングは NextAuth(authOptions) の authOptions 定義内で jwt, session を呼び出された時に設定する

session ワイドに設定する場合、NextJS の作りから言って、next-auth を使う場合は NextAuth(authOptions) で呼び出される authOptions の jwt, session メンバーで設定することになります。

NextAuth のソースコードを確認していませんが、両方で set_config('request.jwt.claims', claims, false) を実行しないといけないようです、

ハマりポイント６： postgres ユーザは bypassrls 属性なので、RLS 無効

これが一番のハマりポイントでした。Supabase の Database ⇨ Rolls をチェックすれば明らかなのですが、よくアクセスに利用する postgres ユーザには bypassrls 属性がついています。
つまり、いくら RLS 設定しても有効化されません。しかも、通常は bypassrls 設定を無効化することはできないようになっています。

• 別の ROLL を設定し、アクセス時にそのユーザでログインする
• 別の ROLL を設定し、SET ROLE で ROLL を変える

のどちらかを実行する必要があるのですが、SET ROLE は怒られました...

ハマりポイント７： アクセス用の新たなユーザを作成/GRANT と ALTER TABLE

仕方がないので、RLS が有効となる新たな DB ユーザを作成します。
ただ作っただけだと 新規ユーザが postgres オーナーの DB インスタンスにアクセスできないため、以下の「おまじない」を唱えます。ここでは acc というユーザを定義するものとします。

CREATE USER acc WITH LOGIN PASSWORD 'xxxxxxxxxxxx';
GRANT CONNECT ON DATABASE postgres TO acc;
GRANT USAGE ON SCHEMA public TO acc;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO acc;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO acc;

ハマりポイント８: ポリシーの書き方にも注意

今までポリシー自体には触れてきませんでしたが、UserGroup 一覧を取得した時、

1. 一般ユーザ (admin = false) は自分の属する UserGroup のみ見ることができる
2. admin = true ユーザは全ての UserGroup を見ることができる

と設定したい場合、JWT に UserGroup を入れてしまう方法と、RealUser.ID から関連する UserGroup を判定する方法があるかと思います。ここでは後者を採用し、RLS を作ってみます。

CREATE POLICY "UserGroup_policy" ON "UserGroup"
USING (
  "ID" IN (SELECT "userGroupID" FROM "RealUser" WHERE current_setting('request.jwt.claims')::JSON->'user'->>'ID' = "ID"::text)
);
CREATE POLICY "UserGroup_admin_policy" ON "UserGroup"
USING (
  current_setting('request.jwt.claims')::JSON->'user'->>'admin' = true::text
)
WITH CHECK (
  current_setting('request.jwt.claims')::JSON->'user'->>'admin' = true::text
);

set_config で設定した変数値は current_setting で取り出します。（第２引数はなくても動く模様）取得後、JSON 型に変換して必要なプロパティを取り出しますが、最後の要素のみ、->>を使って取得します。もし->だとJSON型のままとなってしまうので要注意です。

上記のポリシー定義では ID を text 型に変換していますが、CAST 関数等を使って claims に含まれる値を integer に変換するのもありです。

ハマりポイント９: RLS 有効化・無効化に時間がかかることがある

Supabase の dashboard の Policies に Enable Policy/Disable Policy というボタンがありますが、ボタンの反応がとても遅く、タイムアウトしてしまうことがあります。
日を改めてボタンを押すとうまく行くなどということも...。

原因は全く不明なのですが、prisma の migration ファイルに

ALTER TABLE "UserGroup" ENABLE ROW LEVEL SECURITY;

などと書いてしまうと CI/CD した時にタイムアウトしてしまう可能性があります。（実際、私はハマりました。）ポリシーだけ定義しておいて、有効化・無効化は Supabase dashboard で切り替えた方が安全のようです。

ハマりポイント１０: 残された謎

ハマりポイント５で述べたように、トランザクションを組まない場合はset_configの第３引数を false とする必要があります。また、この時は、セッションをメンテナンスするタイミングで set_config を再度呼び出す必要があります。

具体的には

route.ts

import { NextAuth } from 'next-auth'
const hauth = NextAuth(authOptions)
export { hauth as GET, hauth as POST }

としたときの authOptions の session プロパティでセッション有効性をチェックする際、有効ならば set_config して JWT の一部を保存します。
ただ実装してみると分かりますが、一回の URL 処理に session プロパティは複数呼ばれるため、効率的な実装とは言い難いです。どうして何回も呼び出されるのか確認しようと思いましたが、まだ追いきれていません。

まとめ

本稿では躓きながら RLS を Next.JS + prisma + Supabase 上で動作させるための TIPS を述べてきました。とはいうものの、Next.JS の session 呼び出し間隔など未解明の点もあります。
ここまで解析するのに1.5ヶ月要してしまいました。本格的に利用しましたという記事が少ないのと、prisma を使うケースが極端に少なかったです。
ぜひ、皆様で知見を持ち寄って「あー言うときは、こーするのがいい」と言うTIPSを蓄積していければと思います。