為什麼我選擇 PostgreSQL Row Level Security 而不是應用層權限檢查

TL;DR

一句話結論：在多角色醫療系統中，把權限規則寫在資料庫層，比寫在應用層更安全、更可維護，但學習曲線陡。

我在 ExClinCalc 用 PostgreSQL Row Level Security 實作了 6 個角色（醫師、護理師、藥師、行政、管理員、超級管理員）對 14 張表的細粒度權限控制，總共 29 條 policy。應用層幾乎不需要寫 if (user.role === 'doctor') 這種程式碼。

背景：為什麼權限是醫療系統的核心問題

ExClinCalc 是我獨力開發的診所臨床決策支援系統，實作了完整工作流程閉環：掛號 → 護理分診 → 醫師 SOAP 七步驟病歷 → 處方藥物交互檢查 → 藥師調配。

醫療系統有一個業界共識：權限做錯一次，就是隱私洩漏事件。

我在設計初期就有兩個選擇：

應用層權限：每個 API endpoint 都檢查當前使用者的角色，再決定能不能操作。
資料庫層權限：在 PostgreSQL 內定義規則，所有查詢自動套用。

我選擇 2。本文說明為什麼。

應用層權限的「五個失敗模式」

失敗模式 1：忘記寫權限檢查

// ❌ 失敗的程式
app.get('/api/patients', async (req, res) => {
  const patients = await db.query('SELECT * FROM patients')
  res.json(patients)
})

工程師寫得急、沒加 WHERE doctor_id = ? → 所有醫師都能看所有病人。這不是假設，是真實常見 bug。

失敗模式 2：權限檢查條件不一致

當你有 50 個 endpoint，user.role === 'doctor' vs user.role.includes('doctor') vs ['doctor', 'attending'].includes(user.role) 這種不一致會慢慢累積成大問題。

失敗模式 3：JOIN 時權限失效

第一段有 WHERE doctor_id = ?，第二段 JOIN 時忘了加。攻擊者改 patient_id 就能讀別人的處方。

失敗模式 4：SQL injection 繞過

如果應用層權限是用 WHERE doctor_id = ? 加在最後一段，SQL injection 就能繞過整個權限系統。

失敗模式 5：跨應用無法共享

未來想加 mobile app、第三方整合、CLI 管理工具，每一個應用都要重新實作一次權限邏輯。

PostgreSQL RLS 解決了什麼

PostgreSQL 的 Row Level Security 讓你定義「哪一筆資料對哪個使用者可見」的規則，寫在資料庫，不寫在應用層。

-- 啟用 patients 表的 RLS
ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

-- 規則：醫師只能看到自己診間的病人
CREATE POLICY "doctors see own clinic patients" ON patients
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM users
      WHERE users.id = auth.uid()
        AND users.role = 'doctor'
        AND users.clinic_id = patients.clinic_id
    )
  );

應用層程式碼變得很簡單 ── PostgreSQL 自動套用 policy，不需要應用層 WHERE。

核心優勢：

單一事實來源：規則在 PG 內，所有應用共享。
SQL injection 也擋得住：即使攻擊者繞過應用層直接打 PG，policy 仍然套用。
JOIN 自動處理：你 SELECT prescriptions JOIN patients，PG 對兩張表都套 policy。
新應用零成本：下一個應用拿到的權限自動正確。

ExClinCalc 的 29 條 policy 設計原則

原則 1：所有 policy 從 `auth.uid()` 開始

每條 policy 第一行一定是 EXISTS (SELECT 1 FROM users WHERE users.id = auth.uid() ...)。anchor 在登入者身分上，往下推導其他條件。

原則 2：4 種操作分開寫（SELECT / INSERT / UPDATE / DELETE）

PG RLS 允許一條 policy 涵蓋多個操作，但我為每個操作寫獨立 policy。原因：可讀性。

原則 3：Policy 命名約定

{role}_{action}_{table} — 例：doctor_select_patients。29 條 policy 一致命名。

原則 4：用 helper function 簡化重複邏輯

CREATE FUNCTION current_user_role() RETURNS text AS $$
  SELECT role FROM users WHERE id = auth.uid();
$$ LANGUAGE SQL SECURITY DEFINER;

原則 5：超級管理員例外

每張表都有一條 superadmin 的 ALL policy，避免 debug 時還要切帳號。

真實代價：RLS 不是銀彈

代價 1：debug 困難

PG 對 RLS 拒絕的查詢，錯誤訊息只是「沒有 row」。是「真的沒這筆」？還是「RLS 擋住了」？無法區分。我的對策：在 dev 環境用 RESET ROLE 切到 superuser 重跑同一查詢。

代價 2：複雜查詢效能下降

EXISTS 子查詢在每筆資料 row 都跑一次。對策：在 users.id 加 index、用 STABLE 函式快取結果、對熱資料用 materialized view。

代價 3：Schema migration 必須同步

加新欄位時要記得同步改 RLS policy。對策：每次 migration 加一個 RLS test 跑過 6 個帳號。

代價 4：學習曲線陡

我自己花了大概 2 週才把 RLS 寫得順。新進工程師理解需要時間。

代價 5：Vendor 風險

RLS 是 PG 原生功能，但我有用 Supabase 提供的 auth.uid()。如果未來搬離 Supabase，要改寫 policy。

什麼時候該用 RLS？

✅ 該用

多角色系統（醫療、教育、SaaS 多租戶）
資料敏感度高（個資、財務、醫療）
不只一個應用會讀資料（web + mobile + admin）
團隊願意承擔學習成本

❌ 不該用

單一應用、單一角色（個人 to-do app）
需要極致效能（高頻交易系統）
權限規則經常劇烈變動（每週改，policy 跟不上）
團隊不熟 PG

結論：選擇背後的思考

我不認為 RLS 是「應用層權限」的替代品。它們解決的不是同一個問題：

應用層權限：「這個 API 該不該被呼叫」
RLS：「這筆資料該不該被讀到」

ExClinCalc 同時用兩層 ── 應用層 RBAC 控制「醫師能不能進入處方頁面」，RLS 控制「即使進入了能看到哪些資料」。但核心安全保障在 RLS。應用層被攻破，RLS 還在保護。

想看實際 code？

兩個專案都已開源：

ExClinCalc on GitHub — 完整 14 張表 29 條 RLS policy
Live Demo — 4 個角色 demo 帳號可試

歡迎看 code、提 issue、或寄信給我討論。