💬

SupabaseのRLSを特定のユーザに適用する

2022/07/25に公開

初めに

SupabaseでRLSを使用する際の条件として以下のように書く場合が多いと思います。

-- ユーザはhoge内の自身のデータのみを参照できる
create policy "Users can access their own data" 
    on hoge for select 
    using ( auth.uid() = user_id );

-- 認証済みユーザであればhoge内のデータを参照できる
create policy "Authenticated users can access table data" 
    on hoge for select 
    using ( role() = 'authenticated' );

これだけでもほとんどの機能は実装できますが, もっと具体的な条件でRLSを適用したい場合などあると思います。今回は以下のケースを想定して具体的なRLSの設定について説明します。

  • 勤怠管理アプリのユーザを 正社員, アルバイト, 業務委託, 事務員 に分け, 各社員個人と事務員のみに勤務時間データの取得を, 事務員のみに勤務時間データの更新を許可する

準備

ユーザ情報を格納するusersテーブルと, 勤務時間を格納するworksテーブルを作成するクエリを記述します。各テーブルの情報は以下のようになります。(今回はRLSの説明のみなので簡易的な作りにしています。)

usersテーブル

id user_name employee_position
uuid text integer

worksテーブル

id user_id start_time end_time comment
bigserial uuid time time text
-- ユーザ情報格納テーブル
create table public.users (
  id uuid primary key references auth.users on delete cascade,
  user_name text,
  employee_position integer -- 1 → 正社員, 2 → アルバイト, 3 → 業務委託, 4 → 事務員
);

-- 勤務時間格納テーブル
create table public.works (
  id bigserial primary key not null,
  user_id uuid references auth.users not null,
  start_time time,
  end_time time,
  comment text
);

上記クエリ内にもコメントアウトで記載しましたが, 職位を表す employee_positioninteger型で
それぞれ以下のような対応関係になっています。

  • 1 → 正社員
  • 2 → アルバイト
  • 3 → 業務委託
  • 4 → 事務員

RLSの設定

それでは以下の条件を満たすようなRLSを設定してみましょう。

勤怠管理アプリのユーザを 正社員, アルバイト, 業務委託, 事務員 に分け, 各社員個人と
事務員のみに勤務時間データの取得を, 事務員`のみに勤務時間データの更新を許可する

先ほど記載したクエリに文を追加します。

-- ユーザ情報格納テーブル
create table public.users (
  id uuid primary key references auth.users on delete cascade,
  user_name text,
  employee_position integer -- 1 → 正社員, 2 → アルバイト, 3 → 業務委託, 4 → 事務員
);

-- 勤務時間格納テーブル
create table public.works (
  id bigserial primary key not null,
  user_id uuid references auth.users not null,
  start_time time,
  end_time time,
  comment text
);

-- RLSの適用
alter table public.users enable row level security;
alter table public.works enable row level security;

-- ポリシールールの作成
-- for select
create policy "Clerk or Users can access their own data" on public.works for select using ( uid() in (
    select id from public.users where employee_position = 4
) OR uid() = user_id );

-- for update
create policy "Users can update their own data" on public.works for update using ( uid() in (
    select id from public.users where employee_position = 4
) );

ポリシールールの作成部分について詳しくみてみましょう。

select, updateのポリシールールはそれぞれ以下の様に記述しました。

-- for select
create policy "Clerk or Users can access their own data" on public.works for select using ( uid() in (
    select id from public.users where employee_position = 4
) OR uid() = user_id );

-- for update
create policy "Users can update their own data" on public.works for update using ( uid() in (
    select id from public.users where employee_position = 4
) );

ここから, 条件の部分のみを切り取ってみましょう。

-- for select
uid() in (
    select id from public.users where employee_position = 4
) OR uid() = user_id

--for update
uid() in (
    select id from public.users where employee_position = 4
)

上の式中のinはPostgresの副問い合わせ式です。

A in B とした際にBで得られた結果がAと一致する場合に真(true)を返します。
副問い合わせ式の詳細については以下をご覧ください。

https://www.postgresql.jp/document/7.3/user/functions-subquery.html

つまり, 上の式の uid() in (...) は括弧内の式から得られる結果とuid()が等しいかを検証しているという意味です。

次に, 括弧内の式について見てみましょう。

-- for select and update
select id from public.users where employee_position = 4

これは至ってシンプルですね。何をしているかというとusersテーブルからemployee_positionカラムの値が 4 のユーザのidをselect文で取得しています。

ここで, employee_positionの値はそれぞれの職位を表し, 4の場合は事務員となります。

よって, この式は事務員のユーザIDを取得しているクエリということであり,

uid() in (select id from public.users where employee_position = 4)

という式全体の意味は

データを取得・更新しようとしているユーザのIDが, 事務員として登録されているユーザのIDに一致するかどうか

を表しています。また, selectについては事務員だけでなく個人でもアクセスできるようにする必要があり, それを許可するために OR句を用いて uid() = user_id を繋げています。

これで, 上で記載した条件を満たす様なRLSの設定ができました。

最後に

当記事で記載した方法以外にもやり方があれば教えて頂けると嬉しいです。

参考

https://zenn.dev/hrtk/scraps/a0381fa829dfab

Discussion