解決策から見たい方はこちらを参照。

背景

Supabaseを利用中、publicスキーマ内に作成したテーブルに、Supbase Authで認証したユーザの情報を保持したいと考えていた。

Supabaseのダッシュボードを見ていたら、Supbaseの認証で利用しているテーブルauth.usersのidを紐づけられそうだった。

そこで自身が作成したuser_idとauth.usersのidを紐づけてリレーションを作成した。

上記の設定をER図にすると、以下のイメージ。

リレーションが作成でき、アプリケーションからデータのinsertができた。

しかし登録したデータをユーザ情報と共にselect文で取得をしようとしたところ、以下のエラーが表示され取得に失敗した。

{
    "code": "PGRST100",
    "details": "unexpected "u" expecting "sum", "avg", "count", "max" or "min"",
    "hint": null,
    "message": ""failed to parse select parameter (auth.users(*))" (line 1, column 33)"
}

原因

Supabaseの仕様として、Supabaseで自動生成されるauth.users等のテーブルに対し、クライアントからアクセス可能な権限を提供していない。
それにより、selectなどでauth.usersテーブルのデータを取得しようとするとクエリが失敗してしまう。

解決策を探すと、権限を付与する方法と、public内に同期するテーブルを作りtriggerで同期する手順の2つがあった。

同期テーブルを作る方法

権限を付与する方法

今回は以下の理由より、同期テーブルを作る方法で対応した。

同期テーブルを作る方法は公式ドキュメントで公開されている方法
auth.usersの権限を触るのはSupbaseの推奨設定を変更する為、あまりよくなさそう

解決策

Supabaseの公式ドキュメントに手順を参考に進める。

具体的な手順は以下の通り。

auth.usersテーブルの情報と同期するprofilesテーブルを新規作成
atuh.usersにデータが作成されたら、そのデータをprofilesに同期するトリガー作成

1. `auth.users`テーブルの情報と同期する`profiles`テーブルを新規作成

はじめにauth.usersテーブルとユーザ情報を同期する、profilesテーブルを新規作成する。

create table public.profiles (
  id uuid not null references auth.users on delete cascade, # auth.usersのユーザが削除されたら、このテーブルのデータも削除する
  first_name text,
  last_name text,

  primary key (id)
);

alter table public.profiles enable row level security;

idカラムは、auth.usersテーブルのユーザ情報と同期する必要があるため、auth.usersのデータが削除された時にprfilesのデータも削除する参照制約を付与している。

2. `atuh.users`にデータが作成されたら、そのデータを`profiles`に同期するトリガー作成

次にauth.usersテーブルにデータが作成されたら、profilesテーブルにユーザ情報を同期するfunctionを作成する。

create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  insert into public.profiles (id, first_name, last_name)
  values (new.id, new.raw_user_meta_data ->> 'first_name', new.raw_user_meta_data ->> 'last_name');
  return new;
end;
$$;

具体的な処理は、ユーザが追加されたらauth.usersテーブルのidと、raw_user_meta_dataテーブルのfirst_nameとlast_nameを、profilesテーブルにインサートするというものである。

最後に上記のfunctionを呼び出すtriggerを作成する。

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

こちらで設定は完了。

Supbase認証で新しくユーザが追加されるたびに、profilesにそのユーザが追加されるようになる。