警告231件は231種類の問題ではなかった

公開: 2026-09-04

ちょうぼっち(accounting-app)の Supabase プロジェクトで、Security Advisor が Warnings を 231件 出していました。長いあいだ「あとで見る」に積んでいた種類のもので、件数だけ見ると手が止まります。

結論から書くと、231件は231種類の問題ではありませんでした。実質3つです。

画面のエクスポートを使わなかった

最初にやったのは、Advisor の画面から件数を写すのをやめることです。画面は「何件出ているか」は教えてくれますが、出所と危険度で切ってくれません。 231行を目で分類するのは、そのまま数え間違いの入り口になります。

代わりに、本番と同一スキーマのローカルDB(どちらも 0130 まで適用済み)に対して、 Advisor と同じ判定を SQL で直接問い合わせました。

-- search_path が固定されていない public の関数を、
-- 定義者権限の有無(prosecdef)と出所(拡張かどうか)で分類する
select p.prosecdef,
       coalesce(e.extname, '(自前)') as source,
       count(*)
from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
  left join pg_depend d on d.objid = p.oid and d.deptype = 'e'
  left join pg_extension e on e.oid = d.refobjid
where n.nspname = 'public'
  and p.prokind in ('f','p')
  and not exists (
    select 1 from unnest(coalesce(p.proconfig,'{}')) c where c like 'search_path=%'
  )
group by 1, 2;

外部APIを叩かず、件数ではなく内訳が返ってきます。これが分岐点でした。

内訳と、危険度の判定

231件のうち189件は「拡張1個がどのスキーマにあるか」という単一の原因に由来していた。

そして危険度の判定です。search_path の可変性が本当に危ないのは、関数が定義者の権限で動く SECURITY DEFINER の場合に限ります。呼び出し側が search_path を細工して同名の別オブジェクトを先に解決させ、定義者権限で実行させる、という経路があるからです。

関数の種別	search_path 固定あり	固定なし
SECURITY DEFINER	50	0
SECURITY INVOKER	23	207

SECURITY DEFINER の50本はすべて固定済みでした。警告207件はすべて INVOKER で、呼び出し元自身の権限で動くので昇格は起きません。 231件のうち、権限昇格の経路になっているものは0件。

ここが分かった時点で、これは「緊急対応」ではなく「定型警告の掃除」だと確定しました。件数に引きずられて優先度を上げなくてよかった、という判断が最初の成果です。

自前の19本を塞ぐ

INVOKER でも同名オブジェクトの解決順が変わる余地は残るので、自前の19本は塞ぎました。関数の定義には触らず、設定だけを変えます。

alter function public.acc_capability_roles(p_capability text) set search_path = public;

これを19本ぶん並べただけの migration(0131)です。ただ、この手の一括 migration は「実は拡張の関数まで触っていた」「定義を書き換えていた」が後から効いてくるので、SQL の中身そのものをテストで縛りました。

it("拡張が所有する関数へは触らない", () => {
  // btree_gist の関数(gbt_ で始まる)を対象にしていないこと
  expect(sql).not.toMatch(/alter function public\.gbt_/);
});

it("関数の定義を書き換えず設定だけを変える", () => {
  expect(sql).not.toMatch(/create\s+(or\s+replace\s+)?function/i);
  expect(sql).not.toMatch(/drop\s+function/i);
});

migration の SQL をテキストとして assert するのは行儀が悪く見えますが、「やらないこと」を縛るにはこれが一番素直でした。

残り189件 —— 拡張を引っ越す

btree_gist を public から extensions スキーマへ移せば、188件と extension_in_public の1件がまとめて消えます。ためらっていたのは、この拡張に依存している exclude using gist の排他制約が2つあったからです。拡張を動かして制約が黙って効かなくなるのが最悪の結末です。

実際に移してから、重複データを投入して拒否されることを確認しました。制約はすでに作成済みのインデックスで動くので、判定は変わりません。移設後、public の search_path 未固定関数は 207件から0件になりました。

引っ越しの代償はひとつだけあります。今後 gist の排他制約を新しく作る migration では、演算子クラスの解決に extensions が要ります。

set local search_path = public, extensions;

これを宣言するか、スキーマ修飾して書く必要があります。将来の自分は必ず忘れるので、検証スクリプトに「btree_gist が extensions にいること」「排他制約が2つ以上残っていること」を assert として置きました。

ついでに見つかった、もっと悪いもの

この作業中、クリーンな環境での migration 検証スクリプトを読んでいて、2つ気づきました。

apply_range 86 122

if migration_count <> 120 then
  raise exception 'expected 120 unique migration files, got %', migration_count;

上限が数値でベタ書きされていました。 migration は 0134 まで増えているのに、検証は 0122 までしか流していない。件数の期待値も120本で止まっている。つまりmigration を1本足すたびに、検証範囲が静かに置き去りになっていたわけです。テストは通り続けるので、誰も気づきません。

両方ともファイルから都度求めるようにしました。

LATEST_VERSION="$(ls "${MIGRATIONS_DIR}" | grep -E '^[0-9]{4}_.*\.sql$' | sed 's/_.*//' | sort -n | tail -1)"
apply_range 86 "$((10#${LATEST_VERSION}))"

正直、この日いちばん危なかったのは警告231件ではなくこちらです。警告は目に見えていましたが、こちらは通っているテストの内側で範囲が縮んでいました。

次にやること

Advisor の残り約23件はプロジェクト設定側。スキーマからは触れないので設定画面で確認する
「固定値でベタ書きされた検証範囲」が他のスクリプトにも無いか洗う

手順としてまとめた分は Supabase Advisorの読み方に置きました。 DBの可視範囲まわりでは RLSとアプリ側の絞り込みでも似た踏み方をしています。