實測環境與硬體規格
| 項目 | 實際測試配置 |
| 測試日期 | 2026-10-02 |
| 作業系統 | Ubuntu 24.04.5 LTS |
| 主機數量 | 1 台新建隔離 VM；下列資源為每台配置 |
| CPU | 2 vCPU |
| 記憶體 | 4 GiB |
| 系統磁碟 | 32 GiB；HDD 儲存、VirtIO、ext4 |
| Linux 核心 | 6.8.0-139-generic |
| PostgreSQL | psql (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1) |
將資料庫擁有者、應用讀寫帳號與報表唯讀帳號分開，實測允許和拒絕的操作，並處理新增資料表之後權限沒有自動跟上的問題。
文章目錄
本文以全新 Ubuntu 24.04 隔離主機和合成資料實測。套件來自經過簽章驗證、符合 Noble 的套件來源；沒有使用正式資料庫或正式網站的帳號。讀者請依自己的環境確認來源與版本。
圖中是測試主機實際 bash PTY 的執行紀錄，由瀏覽器呈現；提示符統一為 lab$，執行結果未改寫。文字命令另行保留，方便複製。
開始前需完成 PostgreSQL 16 的原生安裝與叢集啟動，可先閱讀 Ubuntu 24.04 安裝篇。本系列使用 demo 範例資料庫；需要既有 sample 表的篇章，依 搬機篇的資料建立步驟 準備 100 筆合成資料。請在獨立教學環境操作。
一、帳號能登入，不代表能讀寫資料
這次使用 demo_owner 擁有資料庫與表，app_writer 負責應用讀寫，report_reader 只讀報表。應用帳號不需要 superuser，也不應為了排錯而變成資料表擁有者。
| 角色 | 用途 | 本篇允許的範圍 |
| demo_owner | 管理物件 | 建立表、設定權限 |
| app_writer | 應用執行 | 既有 sample 表的 SELECT/INSERT/UPDATE/DELETE |
| report_reader | 報表查詢 | 目前及 demo_owner 未來新增表的 SELECT |
本文先用本機 postgres 執行 SET ROLE，驗證物件權限；這與真正以不同密碼建立 TCP 連線是兩個測試。登入、TLS 與 HBA 的實測請另見 安全遠端連線篇。
二、建立空白教學資料庫與三個角色
sudo -u postgres psql -X -v ON_ERROR_STOP=1 <<'SQL'
CREATE ROLE demo_owner LOGIN;
CREATE ROLE app_writer LOGIN;
CREATE ROLE report_reader LOGIN;
CREATE DATABASE demo OWNER demo_owner;
GRANT CONNECT ON DATABASE demo TO app_writer, report_reader;
SQL
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d demo <<'SQL'
SET ROLE demo_owner;
CREATE TABLE sample(id integer PRIMARY KEY, note text NOT NULL);
INSERT INTO sample SELECT n, 'sample-' || n FROM generate_series(1,100) n;
SQL範例的角色在建立時沒有設定密碼，僅進行本機管理者的角色切換測試。若要正式登入，請使用 \password app_writer 和 \password report_reader 互動設定，再逐一測試；不要把管理者權限授予這兩個角色。若擁有者只用於 migration，可另外採用 NOLOGIN 的擁有者角色設計。
三、同時處理 schema 與資料表權限
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d demo <<'SQL'
SET ROLE demo_owner;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_writer, report_reader;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_reader;
SQLUSAGE ON SCHEMA 讓角色存取 schema 中的物件名稱；實際讀寫還需要表權限。GRANT ... ON ALL TABLES 只處理當下已存在的表。這些權限不是互相替代的。PostgreSQL 權限說明
本篇主鍵是自行填入的整數，沒有 sequence。若應用使用 serial、nextval 或其他序列，還需按實際使用方式配置 sequence 權限；此篇沒有宣稱驗證該情境。
四、設定未來資料表的預設權限
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d demo <<'SQL'
SET ROLE demo_owner;
ALTER DEFAULT PRIVILEGES FOR ROLE demo_owner IN SCHEMA public
GRANT SELECT ON TABLES TO report_reader;
CREATE TABLE future_table(id integer);
INSERT INTO future_table VALUES(1);
SQL預設權限與「未來誰建立物件」有關。這裡只涵蓋 demo_owner 在 public 建立的表；若 migration 改由另一個角色建立，應替那個角色設定。它不會回頭補上舊資料表，也不會自動套到另一個 schema。
五、驗證唯讀成功與寫入拒絕
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d demo <<'SQL'
SET ROLE report_reader;
SELECT current_user, count(*) FROM sample GROUP BY current_user;
SELECT * FROM future_table;
SQL
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d demo <<'SQL'
SET ROLE report_reader;
UPDATE sample SET note='forbidden' WHERE id=1;
SQL
第一段應能讀到 100 筆與新資料表；第二段應顯示 permission denied for table sample。使用 ON_ERROR_STOP=1 時，SQL 錯誤結束碼是 3，這裡是預期的拒絕結果。
六、驗證應用帳號可以工作，但不能改結構
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d demo <<'SQL'
SET ROLE app_writer;
BEGIN;
INSERT INTO sample VALUES(101,'allowed');
UPDATE sample SET note='updated' WHERE id=101;
DELETE FROM sample WHERE id=101;
COMMIT;
SQL
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d demo <<'SQL'
SET ROLE app_writer;
CREATE TABLE forbidden(id integer);
SQL讀寫應成功，建立新表應被 schema 權限拒絕。這樣應用能操作資料，DDL 則由管理流程處理。實測後主機重新開機，資料筆數仍是 100，角色與權限保留。
這次驗證了什麼
| 驗證項目 | 結果 |
| 唯讀 SELECT 成功、UPDATE 被拒絕 | 通過 |
| 應用 INSERT/UPDATE/DELETE 成功 | 通過 |
| 唯讀與應用角色的 DDL 被拒絕 | 通過 |
| 新資料表的唯讀預設權限生效 | 通過 |
| 重新開機後資料與權限保留 | 通過 |
實測範圍：本篇驗證 SET ROLE 下的物件權限與預設權限；未把它當作不同使用者的密碼登入測試。sequence、跨 schema、RLS 和正式 migration 角色需依應用另測。