Supabase（PostgreSQL）のRLSで、予約と顧客のデータを「自分の店の分だけ」見せる実装。店舗ごとの分離、auth.uid()とロール、ポリシーの書き方、service_roleキーの扱い、抜けを見つけるテスト

create function auth.uid() returns uuid language sql stable as $$
  select coalesce(
    nullif(current_setting('request.jwt.claim.sub', true), ''),
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
  )::uuid
$$;

async function as(role, sub, fn) {
  await db.exec('begin');
  try {
    await db.query(`set local role ${role}`);
    const claims = JSON.stringify(sub ? { sub, role } : { role });
    await db.query(`select set_config('request.jwt.claims', $1, true)`, [claims]);
    return await fn();
  } finally {
    await db.exec('rollback');
  }
}

alter table public.reservations enable row level security;

create policy "所属している店の予約を見る" on public.reservations
  for select to authenticated
  using (tenant_id in (select private.my_tenant_ids()));
create policy "所属している店に予約を入れる" on public.reservations
  for insert to authenticated
  with check (tenant_id in (select private.my_tenant_ids()));
create policy "所属している店の予約を直す" on public.reservations
  for update to authenticated
  using (tenant_id in (select private.my_tenant_ids()))
  with check (tenant_id in (select private.my_tenant_ids()));
create policy "予約の削除は店のオーナーだけ" on public.reservations
  for delete to authenticated
  using ((select private.is_owner(tenant_id)));

create function private.my_tenant_ids() returns setof uuid
language sql stable security definer set search_path = '' as $$
  select m.tenant_id from public.memberships m where m.user_id = (select auth.uid())
$$;

ok 1 - 店舗Aのスタッフには、店舗Aの予約2件だけが見える
ok 2 - 店舗Bのオーナーには、店舗Bの予約1件だけが見える
ok 3 - ログインしていない（anon）と、予約も顧客も0件
ok 4 - IDを直接指定しても、他店の顧客は0件（エラーではなく見えないだけ）
   → new row violates row-level security policy for table "reservations"
ok 5 - 他店に予約を入れようとすると、WITH CHECK で止まる
   → new row violates row-level security policy for table "reservations"
ok 6 - 自店の予約を他店へ付け替える更新も止まる
ok 7 - 他店の予約を消そうとしても、0件の削除で終わる（エラーにならない）
ok 8 - スタッフは自店の予約も消せない。オーナーは消せる
   → insert or update on table "reservations" violates foreign key constraint "reservations_tenant_id_customer_id_fkey"
ok 9 - 自店の予約に他店の顧客をつなごうとすると、複合の外部キーで止まる
ok 10 - service_role はポリシーを素通りして3件すべて見える
┌─────────┬────────────────┬──────┬──────────┐
│ (index) │ table          │ rls  │ policies │
├─────────┼────────────────┼──────┼──────────┤
│ 0       │ 'customers'    │ true │ 3        │
│ 1       │ 'memberships'  │ true │ 1        │
│ 2       │ 'reservations' │ true │ 4        │
│ 3       │ 'tenants'      │ true │ 1        │
└─────────┴────────────────┴──────┴──────────┘
ok 11 - public の全表で RLS が有効
   RLS が無効の表 → [ 'reservation_notes' ]
   ログインなしで読めたメモ → [ 'Aのメモ', 'Bのメモ' ]
ok 12 - わざと RLS を付け忘れた表を、検査が見つける

12 件すべて通りました

店舗Aのスタッフが、店舗Bの顧客IDで予約を登録 → 1 件登録できた
そのスタッフから店舗Bの顧客は見えるか → 0 件
current_user = postgres
表の持ち主で読んだ予約 → 3 件

// ビルドの出力（ブラウザに配るファイル）に、Supabase の秘密の鍵が混ざっていないか調べる
// 使い方: node scan-secrets.mjs <ディレクトリ>   見つかったら終了コード1（CI を止める）
import { readdirSync, readFileSync, statSync } from 'node:fs';
import { join } from 'node:path';

const dir = process.argv[2] ?? 'dist';
const TEXT = /\.(js|mjs|cjs|html|css|json|map|txt)$/;
const findings = [];

function* walk(d) {
  for (const name of readdirSync(d)) {
    const p = join(d, name);
    if (statSync(p).isDirectory()) yield* walk(p);
    else if (TEXT.test(name)) yield p;
  }
}

for (const file of walk(dir)) {
  const text = readFileSync(file, 'utf8');
  // 新しい形式の秘密の鍵
  for (const m of text.matchAll(/sb_secret_[A-Za-z0-9_-]+/g)) {
    findings.push({ file, kind: 'sb_secret_ の鍵', head: m[0].slice(0, 14) + '…' });
  }
  // 旧形式（JWT）の鍵：中身の role が service_role なら秘密の鍵
  for (const m of text.matchAll(/eyJ[A-Za-z0-9_-]+\.(eyJ[A-Za-z0-9_-]+)\.[A-Za-z0-9_-]+/g)) {
    try {
      const payload = JSON.parse(Buffer.from(m[1], 'base64url').toString('utf8'));
      if (payload.role === 'service_role') {
        findings.push({ file, kind: 'role が service_role の JWT', head: m[0].slice(0, 14) + '…' });
      }
    } catch { /* JWT の形をした別の文字列は無視 */ }
  }
}

if (findings.length) {
  console.table(findings);
  console.error(`秘密の鍵らしき文字列が ${findings.length} 件あります。公開しないでください。`);
  process.exit(1);
}
console.log(`${dir}: 秘密の鍵は見つかりませんでした`);

$ node scan-secrets.mjs fake-ok
fake-ok: 秘密の鍵は見つかりませんでした
exit=0
$ node scan-secrets.mjs fake-ng
┌─────────┬─────────────────────────┬───────────────────────────────┬───────────────────┐
│ (index) │ file                    │ kind                          │ head              │
├─────────┼─────────────────────────┼───────────────────────────────┼───────────────────┤
│ 0       │ 'fake-ng/assets/app.js' │ 'sb_secret_ の鍵'             │ 'sb_secret_DUMM…' │
│ 1       │ 'fake-ng/assets/app.js' │ 'role が service_role の JWT' │ 'eyJhbGciOiJIUz…' │
└─────────┴─────────────────────────┴───────────────────────────────┴───────────────────┘
秘密の鍵らしき文字列が 2 件あります。公開しないでください。
exit=1

select c.relname as table,
       c.relrowsecurity as rls,
       count(p.polname)::int as policies
from pg_class c
join pg_namespace ns on ns.oid = c.relnamespace
left join pg_policy p on p.polrelid = c.oid
where ns.nspname = 'public' and c.relkind = 'r'
group by c.relname, c.relrowsecurity
order by c.relname;

-- ===== 1. Supabase の土台をまねる部分（本物の Supabase では最初から用意されている） =====
create role anon nologin;
create role authenticated nologin;
create role service_role nologin bypassrls;

create schema auth;
create table auth.users (id uuid primary key, email text);

-- Supabase Auth のマイグレーションと同じ定義：PostgREST が入れた JWT の sub を読む
create function auth.uid() returns uuid language sql stable as $$
  select coalesce(
    nullif(current_setting('request.jwt.claim.sub', true), ''),
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
  )::uuid
$$;
grant usage on schema auth to anon, authenticated, service_role;

-- ===== 2. アプリの表 =====
create table public.tenants (
  id uuid primary key,
  name text not null
);

create table public.memberships (
  tenant_id uuid not null references public.tenants(id),
  user_id uuid not null references auth.users(id),
  role text not null check (role in ('owner', 'staff')),
  primary key (tenant_id, user_id)
);
create index on public.memberships (user_id);

create table public.customers (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id),
  name text not null,
  phone text,
  unique (tenant_id, id)
);
create index on public.customers (tenant_id);

create table public.reservations (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id),
  customer_id uuid not null,
  starts_at timestamptz not null,
  status text not null default 'booked',
  -- 顧客は「同じ店の顧客」しか指せないようにする（RLS は外部キーの確認を止めないため）
  foreign key (tenant_id, customer_id) references public.customers(tenant_id, id)
);
create index on public.reservations (tenant_id);

-- 既存の Supabase のプロジェクトでは、public の新しい表に anon・authenticated・service_role の権限が自動で付く。それをまねる
grant usage on schema public to anon, authenticated, service_role;
grant select, insert, update, delete on all tables in schema public to anon, authenticated, service_role;

-- ===== 3. 所属を調べる関数（API に出さないスキーマに置く） =====
create schema private;
create function private.my_tenant_ids() returns setof uuid
language sql stable security definer set search_path = '' as $$
  select m.tenant_id from public.memberships m where m.user_id = (select auth.uid())
$$;
create function private.is_owner(t uuid) returns boolean
language sql stable security definer set search_path = '' as $$
  select exists (
    select 1 from public.memberships m
    where m.tenant_id = t and m.user_id = (select auth.uid()) and m.role = 'owner'
  )
$$;
grant usage on schema private to authenticated;
revoke execute on all functions in schema private from public;
grant execute on all functions in schema private to authenticated;

-- ===== 4. RLS とポリシー =====
alter table public.tenants      enable row level security;
alter table public.memberships  enable row level security;
alter table public.customers    enable row level security;
alter table public.reservations enable row level security;

create policy "所属している店だけ見える" on public.tenants
  for select to authenticated
  using (id in (select private.my_tenant_ids()));

create policy "自分の所属だけ見える" on public.memberships
  for select to authenticated
  using (user_id = (select auth.uid()));

create policy "所属している店の顧客を見る" on public.customers
  for select to authenticated
  using (tenant_id in (select private.my_tenant_ids()));
create policy "所属している店に顧客を登録する" on public.customers
  for insert to authenticated
  with check (tenant_id in (select private.my_tenant_ids()));
create policy "所属している店の顧客を直す" on public.customers
  for update to authenticated
  using (tenant_id in (select private.my_tenant_ids()))
  with check (tenant_id in (select private.my_tenant_ids()));

create policy "所属している店の予約を見る" on public.reservations
  for select to authenticated
  using (tenant_id in (select private.my_tenant_ids()));
create policy "所属している店に予約を入れる" on public.reservations
  for insert to authenticated
  with check (tenant_id in (select private.my_tenant_ids()));
create policy "所属している店の予約を直す" on public.reservations
  for update to authenticated
  using (tenant_id in (select private.my_tenant_ids()))
  with check (tenant_id in (select private.my_tenant_ids()));
create policy "予約の削除は店のオーナーだけ" on public.reservations
  for delete to authenticated
  using ((select private.is_owner(tenant_id)));

insert into auth.users values
  ('aaaaaaaa-0000-0000-0000-000000000001', '[email protected]'),
  ('bbbbbbbb-0000-0000-0000-000000000002', '[email protected]');
insert into public.tenants values
  ('11111111-0000-0000-0000-000000000001', '店舗A'),
  ('22222222-0000-0000-0000-000000000002', '店舗B');
insert into public.memberships values
  ('11111111-0000-0000-0000-000000000001', 'aaaaaaaa-0000-0000-0000-000000000001', 'staff'),
  ('22222222-0000-0000-0000-000000000002', 'bbbbbbbb-0000-0000-0000-000000000002', 'owner');
insert into public.customers (id, tenant_id, name, phone) values
  ('c0000000-0000-0000-0000-00000000000a', '11111111-0000-0000-0000-000000000001', '顧客A1', '090-0000-0001'),
  ('c0000000-0000-0000-0000-00000000000b', '22222222-0000-0000-0000-000000000002', '顧客B1', '090-0000-0002');
insert into public.reservations (tenant_id, customer_id, starts_at) values
  ('11111111-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-00000000000a', '2026-10-10 10:00+09'),
  ('11111111-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-00000000000a', '2026-10-11 10:00+09'),
  ('22222222-0000-0000-0000-000000000002', 'c0000000-0000-0000-0000-00000000000b', '2026-10-10 13:00+09');

// RLS のテスト。PGlite（WebAssembly の PostgreSQL）で、Supabase の API が1リクエストごとに行うことをまねる：
//   トランザクションを始める → ロールを切り替える → JWT の中身を request.jwt.claims に入れる → SQL を流す
import { PGlite } from '@electric-sql/pglite';
import { readFileSync } from 'node:fs';
import assert from 'node:assert/strict';

const db = new PGlite();
await db.exec(readFileSync('schema.sql', 'utf8'));
await db.exec(readFileSync('seed.sql', 'utf8'));

const STAFF_A = 'aaaaaaaa-0000-0000-0000-000000000001';
const OWNER_B = 'bbbbbbbb-0000-0000-0000-000000000002';
const TENANT_A = '11111111-0000-0000-0000-000000000001';
const TENANT_B = '22222222-0000-0000-0000-000000000002';
const CUSTOMER_A = 'c0000000-0000-0000-0000-00000000000a';
const CUSTOMER_B = 'c0000000-0000-0000-0000-00000000000b';

// role と sub を決めて、1つのトランザクションの中で fn を動かし、最後に必ず取り消す
async function as(role, sub, fn) {
  await db.exec('begin');
  try {
    await db.query(`set local role ${role}`);
    const claims = JSON.stringify(sub ? { sub, role } : { role });
    await db.query(`select set_config('request.jwt.claims', $1, true)`, [claims]);
    return await fn();
  } finally {
    await db.exec('rollback');
  }
}
const rows = async (sql, params) => (await db.query(sql, params)).rows;
async function errorOf(fn) {
  try { await fn(); return null; } catch (e) { return e.message; }
}

let n = 0;
async function check(label, fn) {
  await fn();
  console.log(`ok ${++n} - ${label}`);
}

await check('店舗Aのスタッフには、店舗Aの予約2件だけが見える', () =>
  as('authenticated', STAFF_A, async () => {
    const r = await rows('select tenant_id from reservations');
    assert.equal(r.length, 2);
    assert.ok(r.every((x) => x.tenant_id === TENANT_A));
  }));

await check('店舗Bのオーナーには、店舗Bの予約1件だけが見える', () =>
  as('authenticated', OWNER_B, async () => {
    const r = await rows('select tenant_id from reservations');
    assert.deepEqual(r.map((x) => x.tenant_id), [TENANT_B]);
  }));

await check('ログインしていない（anon）と、予約も顧客も0件', () =>
  as('anon', null, async () => {
    assert.equal((await rows('select * from reservations')).length, 0);
    assert.equal((await rows('select * from customers')).length, 0);
  }));

await check('IDを直接指定しても、他店の顧客は0件（エラーではなく見えないだけ）', () =>
  as('authenticated', STAFF_A, async () => {
    assert.equal((await rows('select * from customers where id = $1', [CUSTOMER_B])).length, 0);
  }));

await check('他店に予約を入れようとすると、WITH CHECK で止まる', () =>
  as('authenticated', STAFF_A, async () => {
    const msg = await errorOf(() => db.query(
      `insert into reservations (tenant_id, customer_id, starts_at) values ($1, $2, now())`,
      [TENANT_B, CUSTOMER_B]));
    console.log('   →', msg);
    assert.match(msg, /row-level security/);
  }));

await check('自店の予約を他店へ付け替える更新も止まる', () =>
  as('authenticated', STAFF_A, async () => {
    const msg = await errorOf(() => db.query(`update reservations set tenant_id = $1`, [TENANT_B]));
    console.log('   →', msg);
    assert.match(msg, /row-level security/);
  }));

await check('他店の予約を消そうとしても、0件の削除で終わる（エラーにならない）', () =>
  as('authenticated', STAFF_A, async () => {
    const r = await db.query(`delete from reservations where tenant_id = $1`, [TENANT_B]);
    assert.equal(r.affectedRows, 0);
  }).then(() =>
  // 削除できる立場（店舗Bのオーナー）でも、他店（店舗A）の予約は0件
  as('authenticated', OWNER_B, async () => {
    const r = await db.query(`delete from reservations where tenant_id = $1`, [TENANT_A]);
    assert.equal(r.affectedRows, 0);
  })));

await check('スタッフは自店の予約も消せない。オーナーは消せる', async () => {
  await as('authenticated', STAFF_A, async () => {
    const r = await db.query(`delete from reservations where tenant_id = $1`, [TENANT_A]);
    assert.equal(r.affectedRows, 0);
  });
  await as('authenticated', OWNER_B, async () => {
    const r = await db.query(`delete from reservations where tenant_id = $1`, [TENANT_B]);
    assert.equal(r.affectedRows, 1);
  });
});

await check('自店の予約に他店の顧客をつなごうとすると、複合の外部キーで止まる', () =>
  as('authenticated', STAFF_A, async () => {
    const msg = await errorOf(() => db.query(
      `insert into reservations (tenant_id, customer_id, starts_at) values ($1, $2, now())`,
      [TENANT_A, CUSTOMER_B]));
    console.log('   →', msg);
    assert.match(msg, /foreign key/);
  }));

await check('service_role はポリシーを素通りして3件すべて見える', () =>
  as('service_role', null, async () => {
    assert.equal((await rows('select * from reservations')).length, 3);
  }));

// ---- ポリシーの抜けを見つける検査（CI で毎回流す想定） ----
async function auditPublicSchema() {
  return rows(`
    select c.relname as table,
           c.relrowsecurity as rls,
           count(p.polname)::int as policies
    from pg_class c
    join pg_namespace ns on ns.oid = c.relnamespace
    left join pg_policy p on p.polrelid = c.oid
    where ns.nspname = 'public' and c.relkind = 'r'
    group by c.relname, c.relrowsecurity
    order by c.relname`);
}

await check('public の全表で RLS が有効', async () => {
  const r = await auditPublicSchema();
  console.table(r);
  assert.deepEqual(r.filter((x) => !x.rls).map((x) => x.table), []);
});

// ---- わざと抜けを作って、検査が見つけるか確かめる ----
await db.exec(`
  create table public.reservation_notes (
    id serial primary key, tenant_id uuid not null, body text not null);
  grant select on public.reservation_notes to anon, authenticated;
  insert into public.reservation_notes (tenant_id, body) values
    ('${TENANT_A}', 'Aのメモ'), ('${TENANT_B}', 'Bのメモ');
`);
const missing = (await auditPublicSchema()).filter((x) => !x.rls).map((x) => x.table);
console.log('   RLS が無効の表 →', missing);
assert.deepEqual(missing, ['reservation_notes']);
const leaked = await as('anon', null, () => rows('select body from reservation_notes'));
console.log('   ログインなしで読めたメモ →', leaked.map((x) => x.body));
console.log(`ok ${++n} - わざと RLS を付け忘れた表を、検査が見つける`);

console.log(`\n${n} 件すべて通りました`);

// 比較用：顧客への外部キーを customer_id だけにした場合（複合にしない場合）
import { PGlite } from '@electric-sql/pglite';
import { readFileSync } from 'node:fs';

const db = new PGlite();
const schema = readFileSync('schema.sql', 'utf8').replace(
  'foreign key (tenant_id, customer_id) references public.customers(tenant_id, id)',
  'foreign key (customer_id) references public.customers(id)');
await db.exec(schema);
await db.exec(readFileSync('seed.sql', 'utf8'));

await db.exec('begin');
await db.query('set local role authenticated');
await db.query(`select set_config('request.jwt.claims', $1, true)`,
  [JSON.stringify({ sub: 'aaaaaaaa-0000-0000-0000-000000000001', role: 'authenticated' })]);
const r = await db.query(
  `insert into reservations (tenant_id, customer_id, starts_at)
   values ('11111111-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-00000000000b', now())`);
console.log('店舗Aのスタッフが、店舗Bの顧客IDで予約を登録 →', r.affectedRows, '件登録できた');
const seen = await db.query(`select count(*)::int as n from customers where id = 'c0000000-0000-0000-0000-00000000000b'`);
console.log('そのスタッフから店舗Bの顧客は見えるか →', seen.rows[0].n, '件');
await db.exec('rollback');

// 表の持ち主（ここでは PGlite の既定ユーザー postgres）で読むと、ポリシーは効かない
console.log('current_user =', (await db.query('select current_user')).rows[0].current_user);
console.log('表の持ち主で読んだ予約 →', (await db.query('select count(*)::int as n from reservations')).rows[0].n, '件');