PostgreSQL Group Role Ownership 管理

透過 Group Role Pattern，讓多位 user 共享物件 ownership，消除「必須請管理員介入轉移 owner」的問題。

概述

PostgreSQL 的 ownership 規則是刻意設計的：要轉移物件的 owner，執行者必須同時是「現任 owner」與「目標 owner」兩個 role 的 member。這個限制防止用戶把不要的物件強推給別人、防止繞過權限邊界。

在多人共用同一個 database 的場景，這個限制會造成麻煩：user_a 建立的 table，user_b 即使是「同一個 group 的成員」也無法操作——membership 是單向繼承，兄弟 role 之間沒有繼承關係。常見錯誤訊息：

ERROR: must be owner of table xxx
ERROR: must be member of role "yyy"

解法不是找 superuser 來轉移，而是改用 Group Role Pattern：讓所有共用物件的 owner 統一指向一個 group role，個別 user 作為 member 透過 INHERIT 自動獲得 owner 能力。設定完成後，不再需要在 user 之間互轉 ownership，也不需要 superuser 介入。

核心內容

Ownership 繼承的單向性

PostgreSQL membership 是「向上繼承」：child role 繼承 parent role 的權限，但反方向不成立，兄弟 role 之間也無法互通。

       app_owner          ← 所有共用物件的 owner
       /       \
   user_a    user_b       ← member，繼承 app_owner 的權限

物件 owner	user_a 能做 owner 操作？	user_b 能做 owner 操作？
user_a（個別擁有）	✓	✗ 即使同 group 也不行
app_owner	✓	✓

ALTER ... OWNER TO 的執行條件

執行者必須同時是「現任 owner」與「目標 owner」兩個 role 的 member（superuser 例外）。即使 user_a 想先把物件轉給 app_owner，再讓 app_owner 轉給 user_b，也行不通：parent role 不是 child role 的 member，方向反了。

為什麼 REASSIGN OWNED 比逐一 ALTER 安全

REASSIGN OWNED BY user_a TO app_owner 一次處理 table、view、sequence、function、type、schema 等所有物件，而逐一 ALTER ... OWNER TO 容易漏掉特定物件類型。REASSIGN OWNED BY 是 per-database 操作，多個 DB 需在每個 DB 內各自執行。

Session Preset 的必要性

只做 REASSIGN OWNED 還不夠：user 下次連線建立新物件時，owner 仍然會落回個別 user。設定 ALTER ROLE user_a SET role = 'app_owner' 後，user 每次連線進來都會自動 SET ROLE app_owner，新建物件的 owner 直接就是 group role，問題根絕。

關鍵要點

• Group Role Pattern：讓 group role 持有 ownership，個別 user 作為 member 透過 INHERIT 繼承能力
• REASSIGN OWNED 一次批次轉移所有物件類型，比逐一 ALTER 更安全完整
• ALTER ROLE ... SET role = ... 防止轉移後問題復發
• Group role 命名建議用「用途導向」（app_owner、pnm_team），避免綁人名
• NOINHERIT user 即使是 member 也不繼承權限，建立 user 時應確認 INHERIT 預設值

部署設定參考

完整 Group Role Setup 步驟與驗證 SQL。

環境參數

項目	建議值
Group role 命名	用途導向，例如 app_owner、pnm_app_owner
User INHERIT 設定	預設 INHERIT（除非特別需要隔離）
Role LOGIN 設定	Group role 不需要 LOGIN

完整 Setup 步驟

Step 1：建立 group role

CREATE ROLE app_owner;
-- 不用 LOGIN，這是純粹的 group role

Step 2：賦予 schema CREATE 權限

app_owner 必須對目標 schema 有 CREATE，否則 ownership 轉移會失敗。

GRANT CREATE ON SCHEMA public TO app_owner;  -- 換成實際 schema

Step 3：將個別 user 加入 group

GRANT app_owner TO user_a, user_b;

-- 確認 INHERIT（通常是預設值，不需額外設定）
ALTER ROLE user_a INHERIT;
ALTER ROLE user_b INHERIT;

Step 4：批次轉移既有物件

在目標 DB 的連線下執行（每個 DB 都要各自執行一次）：

REASSIGN OWNED BY user_a TO app_owner;
REASSIGN OWNED BY user_b TO app_owner;

Step 5：防止問題復發（重要）

ALTER ROLE user_a SET role = 'app_owner';
ALTER ROLE user_b SET role = 'app_owner';

之後 user 連線進來會自動 SET ROLE app_owner，建出來的物件 owner 直接就是 app_owner。

Step 6（可選）：轉移 database 本身

ALTER DATABASE mydb OWNER TO app_owner;

成員管理

-- 加入成員
GRANT app_owner TO "user_c@example.com";

-- 一次多人
GRANT app_owner TO "user_c@example.com", "user_d@example.com";

-- 移除成員
REVOKE app_owner FROM "user_c@example.com";

pgAdmin GUI 的「成員」表格右上角有「+」按鈕，但在某些畫面寬度下可能被切掉。建議直接用 SQL。

驗證查詢

-- 查 group 有哪些 member
SELECT r.rolname AS member, g.rolname AS group_role
FROM pg_auth_members m
JOIN pg_roles r ON m.member = r.oid
JOIN pg_roles g ON m.roleid = g.oid
WHERE g.rolname = 'app_owner';

-- 查 table 目前的 owner
SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY tableowner, schemaname, tablename;

-- 查所有物件類型的 owner（更完整）
SELECT n.nspname AS schema,
       c.relname AS object,
       CASE c.relkind
         WHEN 'r' THEN 'table'
         WHEN 'v' THEN 'view'
         WHEN 'm' THEN 'matview'
         WHEN 'S' THEN 'sequence'
         WHEN 'i' THEN 'index'
         WHEN 'f' THEN 'foreign_table'
         WHEN 'p' THEN 'partitioned_table'
         ELSE c.relkind::text
       END AS type,
       pg_get_userbyid(c.relowner) AS owner
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY owner, schema, object;

-- 確認 user 的 INHERIT 設定
SELECT rolname, rolinherit, rolcanlogin
FROM pg_roles
WHERE rolname IN ('user_a', 'user_b', 'app_owner');

常見陷阱

陷阱	說明
只做 REASSIGN 不設 session preset	新物件的 owner 仍會落回個別 user，問題復發
忘了給 schema CREATE 權限	REASSIGN / ALTER OWNER 會失敗
跨 DB 漏執行 REASSIGN	每個 DB 都要在該 DB 連線下獨立執行
Group 命名綁人名	交接離職時尷尬，改用用途導向命名
NOINHERIT user	即使是 member 也不繼承，需手動 SET ROLE 才生效
試圖在 user 之間直接過戶	兄弟 role 之間無法互轉，直接把 owner 掛在 group 上就好

Owner 轉移可行性矩陣

以 user_a 為執行者，table 現任 owner 為 user_a：

目標 owner	可行？	說明
app_owner（user_a 所屬 group）	✓	user_a 是 app_owner 的 member
user_b（同 group 兄弟）	✗	user_a 不是 user_b 的 member
任意獨立 role	✗	通常都不是 member

相關概念

• PgBouncer 連線池（Kubernetes 部署） — PostgreSQL 連線池部署，與 group role 設定配套使用
• MariaDB Helm 部署（Kubernetes） — 同為關聯式資料庫的 Kubernetes 部署方案

來源

• PostgreSQL 官方文件 - ALTER TABLE
• 原始素材：2026-05-13 PostgreSQL Group Role Ownership 管理指南