默认权限（ALTER DEFAULT PRIVILEGES）

前面几页的授权都有一个共同的盲区：它们只作用于已经存在的对象。今天给 app_readonly 授了全部表的读权限，明天开发新建一张表，只读组立刻失去对这张表的访问——直到有人记得再 GRANT 一次。靠人记忆补授权，一定会漏。

ALTER DEFAULT PRIVILEGES 就是解决「未来对象」的机制：它预先登记一份 ACL 模板，规定「某个角色今后创建的某类对象，默认授予谁哪些权限」。配上它，「模式 → 表 → 序列」的授权才算真正闭环。

这一页的关键是两个限定词：FOR ROLE（管谁建的对象）与 IN SCHEMA（管哪个模式里的对象）。几乎所有误用都源于对这两个词的误解。

前置知识

建议先阅读：组角色与会话角色

基础概念

问题：新对象只对属主开放

PostgreSQL 的出厂规则是：对象创建时，属主获得全部可授予权限，PUBLIC 按对象类型获得极少量默认权限（数据库的 CONNECT/TEMPORARY、函数的 EXECUTE、语言的 USAGE），除此之外——谁也没有权限。

sql

-- 开发账号 dev 建了一张表
CREATE TABLE app.orders (id int PRIMARY KEY);

-- 只读组成员立即撞墙
SET ROLE app_readonly;
SELECT * FROM app.orders;
-- ERROR:  permission denied for table orders

于是每个团队都会演化出一套「建表后补授权」的土仪式。ALTER DEFAULT PRIVILEGES 把这个仪式交给数据库自己执行。

默认权限是什么

默认权限（default privileges）是一组登记在系统目录 pg_default_acl 里的模板，不是任何现有对象上的 ACL。登记的内容是四元组：创建者角色（可缺省）+ 模式（可缺省）+ 对象类型 + 目标 ACL。当符合前两个条件的对象被创建时，数据库自动把模板中的 ACL 应用到新对象上。

三个由此而来的推论：

只影响未来，不影响过去：登记模板不改变任何已存在对象的权限。
匹配条件是「谁建的」：模板跟着创建者走，不跟着模式走（模式只是可选的附加过滤）。
执行时才检查：对象创建那一刻，只有当前角色（current_user）的默认权限模板会被应用。

还有一个范围推论容易被忽略：默认权限按数据库登记，不是集群级。pg_default_acl 是每个数据库自己的目录，在 appdb 里登记的模板对同集群的 reportdb 毫无作用。多数据库环境里，模板语句要进入每个库的初始化脚本：

sql

-- 在 appdb 里登记的模板……
\c appdb
ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA app
    GRANT SELECT ON TABLES TO app_readonly;

-- ……在 reportdb 里必须重新登记，否则 deploy 在那边建的新表不会自动授权
\c reportdb
ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA app
    GRANT SELECT ON TABLES TO app_readonly;

对比记忆：角色是集群级的，默认权限是库级的。「角色在哪个库都需要」与「模板在哪个库都需要」是两套独立的管理面。

两个维度：FOR ROLE 与 IN SCHEMA

限定词	作用	缺省行为
`FOR ROLE target_role`	模板作用于 target_role 未来创建的对象	作用于执行该语句的角色自己创建的对象
`IN SCHEMA schema_name`	模板只作用于该模式内创建的对象	作用于当前数据库中所有模式（全局模板）

最常见的误用就是把第二个维度当成第一个：以为 ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly; 管住了「app 模式里的所有新表」——实际上它只管「我在 app 模式里建的新表」。如果建表的是别的角色（哪怕是你的组成员），模板根本不会被触发，原因见下一节。

语法与用法

sql

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke

-- abbreviated_grant_or_revoke 的可组合项（与 GRANT/REVOKE 同规则）：
--   TABLES：        SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN | ALL
--   SEQUENCES：     USAGE | SELECT | UPDATE | ALL
--   FUNCTIONS|ROUTINES：EXECUTE | ALL
--   TYPES：         USAGE | ALL
--   SCHEMAS：       USAGE | CREATE | ALL
--   LARGE OBJECTS： SELECT | UPDATE | ALL

基本示例：

sql

-- 我（执行者）未来在 app 模式建的表，自动给只读组 SELECT
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT ON TABLES TO app_readonly;

-- dev 未来建的表，自动给读写组全套 DML 与序列权限
ALTER DEFAULT PRIVILEGES FOR ROLE dev
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE dev
    GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

-- dev 未来建的函数，自动给 app_readwrite 执行权
ALTER DEFAULT PRIVILEGES FOR ROLE dev
    GRANT EXECUTE ON FUNCTIONS TO app_readwrite;

-- 回收：dev 未来建的函数不再默认对 PUBLIC 开放
ALTER DEFAULT PRIVILEGES FOR ROLE dev
    REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

详细说明

出厂默认权限：属主全权 + PUBLIC 三类

任何对象创建时的默认 ACL 由两部分组成：属主的全部可授予权限，加上该对象类型对 PUBLIC 的默认授予。默认对 PUBLIC 开放的只有三处：数据库的 CONNECT 与 TEMPORARY、函数（含聚合、过程）的 EXECUTE、语言的 USAGE。表、序列、模式、类型、大对象、表空间、FDW、参数对 PUBLIC 默认零授权。

ALTER DEFAULT PRIVILEGES 正是在这个出厂状态上做加减法：GRANT 追加、REVOKE 减去。它甚至可以修改「属主权限」——比如把未来新建表的 INSERT 从属主那里撤掉（极少用，但语法允许）。

FOR ROLE：作用于谁创建的新对象

这是整条命令的胜负手，三个规则必须精确：

缺省 = 执行者自己。超级用户执行 ALTER DEFAULT PRIVILEGES GRANT ... ON TABLES TO app_ro; 登记的是「超级用户建的表」的模板——而业务里建表的根本不是超级用户，于是这条模板永远不触发。必须写 FOR ROLE dev（dev 是实际建表的角色）。
只看 current_user。对象创建瞬间，被应用的模板是当前角色（current_user）的默认权限，而不是「会话用户」的。
模板不随成员关系继承。官方文档明确：即便执行者 dev 是某个「管理员组」的成员、而管理员组登记过默认权限，dev 创建对象时也不会应用组的模板——只有 dev 自己名下的模板生效。因此「给组登记一份默认权限，让所有成员都沾光」是行不通的，必须逐个创建者角色登记（这也是下一页速查脚本里要按角色循环的原因）。

sql

-- 典型正确写法：应用发布账号是 deploy，迁移工具账号是 flyway
ALTER DEFAULT PRIVILEGES FOR ROLE deploy  IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE flyway  IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly;

FOR ROLE 可以写多个角色

FOR ROLE a, b, c 允许一次给多个创建者登记同样的模板；FOR ROLE 接受 ROLE 或 USER 关键字，二者等价。

执行权限要求与可管理范围

谁能登记、能登记谁的模板，有一条清晰的边界：

自己的模板：任何角色都可以修改「自己未来创建的对象」的默认权限——这是最常见也最安全的形态，发布账号自己登记自己的模板。
成员的模板：你是某角色（直接或间接）的成员时，可以为它登记模板。配合一条 NOLOGIN 的「发布组」可以集中管理：把 deploy、flyway 挂进组，管理员（组的成员）即可统一维护两者的模板。
无关角色的模板：需要超级用户。替别的团队的角色登记默认权限是超级用户操作，这也解释了为什么「替所有业务角色登记模板」的脚本通常以 postgres 运行。

sql

-- 普通发布账号自己登记（无需任何特权）
SET ROLE deploy;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT ON TABLES TO app_readonly;
RESET ROLE;

顺带说明登记模板需要的对象权限：模板里的 GRANT/REVOKE 遵循与直接 GRANT/REVOKE 相同的规则——授权者（登记者）必须是超级用户、对象（未来的）属主相关方，或以 WITH GRANT OPTION / WITH ADMIN OPTION 持有相应资格。登记时若资格不足，语句会在登记瞬间就报错，而不是等到对象创建。

IN SCHEMA 限定与「只加不减」规则

IN SCHEMA 把模板限定到指定模式，未指定则为数据库级全局模板。关键规则是：模式级模板只能叠加在全局模板之上，不能削减全局模板。

全局给了 EXECUTE ON FUNCTIONS TO PUBLIC 的额外授予，模式级 REVOKE 无法收回。
模式级 REVOKE 的唯一用途是撤销之前某条模式级 GRANT 的效果。

官方文档专门给了这个反例：想在 public 模式收回函数的默认 EXECUTE，ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; 是无效的——因为 PUBLIC 的 EXECUTE 是出厂全局默认，不是全局默认权限机制授予的。正确做法是不带 IN SCHEMA 的全局回收（见下节）。

另外两个语法限制：SCHEMAS 与 LARGE OBJECTS 两类不允许 IN SCHEMA 限定（模式不能嵌套、大对象不属于模式）。

SCHEMAS 类默认权限（PG15+）：管「未来新建的模式」

ON SCHEMAS 是最容易被误解的一类，值得单独说明。它管理的对象是「该数据库中未来被创建的模式」本身的默认 ACL——比如「今后任何人新建模式，自动让 app_admin 拥有 USAGE 和 CREATE」：

sql

-- 今后新建的模式：管理组自动获得 USAGE + CREATE
ALTER DEFAULT PRIVILEGES FOR ROLE deploy
    GRANT USAGE, CREATE ON SCHEMAS TO app_admin;

-- 新模式不再默认对某角色开放（如果有这类全局模板）
ALTER DEFAULT PRIVILEGES FOR ROLE deploy
    REVOKE ALL ON SCHEMAS FROM app_report;

它不是「模式里的未来对象」——那正是 IN SCHEMA 限定下的 ON TABLES / ON SEQUENCES 等模板的职责。两张「时间表」对照记忆：

想管什么	用的语句
`app` 模式里未来的表	`ALTER DEFAULT PRIVILEGES ... IN SCHEMA app GRANT ... ON TABLES ...`
未来新建的模式本身的 ACL	`ALTER DEFAULT PRIVILEGES ... GRANT ... ON SCHEMAS ...`（无 `IN SCHEMA`）

「新建模式后要立刻 GRANT USAGE 给应用组」这个高频手工动作，就是 ON SCHEMAS 模板要消灭的对象。

支持的对象类型

当前支持六类对象的默认权限：

对象类	关键字	可用权限
表（含视图、外表）	`TABLES`	`SELECT`、`INSERT`、`UPDATE`、`DELETE`、`TRUNCATE`、`REFERENCES`、`TRIGGER`、`MAINTAIN`
序列	`SEQUENCES`	`USAGE`、`SELECT`、`UPDATE`
函数（含聚合、过程）	`FUNCTIONS` / `ROUTINES`	`EXECUTE`
类型（含域）	`TYPES`	`USAGE`
模式	`SCHEMAS`	`USAGE`、`CREATE`
大对象	`LARGE OBJECTS`	`SELECT`、`UPDATE`

FUNCTIONS 与 ROUTINES 在此命令中完全等价（无法对函数与过程分别设置），官方推荐新代码使用 ROUTINES。注意 SCHEMAS 类的默认权限管理的是「未来在该数据库中新建模式」时对 PUBLIC/目标角色的授予（PG15 起支持），不是「模式里的未来对象」。

WITH GRANT OPTION 的默认权限

模板的授权子句与普通 GRANT 同规则，因此也能带上 WITH GRANT OPTION：

sql

-- deploy 未来建的表：管理组不仅可读，还能把读权限转授给别人
ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA app
    GRANT SELECT ON TABLES TO app_admin WITH GRANT OPTION;

这条能力是双刃剑：新对象一创建，被授予方就自动持有转授权资格，\dp 里的 ACL 会带上 grant option 标记。除非确有分层授权需求（比如管理组要给报表子组发权限），否则不建议在默认权限里开 WITH GRANT OPTION——它会静默地把「转授权」扩散到每一张未来新建的表上。回收时同样遵循 REVOKE ... CASCADE 的级联规则。

defaclobjtype 编码对照

pg_default_acl.defaclobjtype 用单字符标识对象类型，审计查询里会直接遇到：

编码	对象类型	对应模板关键字
`r`	表 / 视图 / 外表（关系）	`ON TABLES`
`S`	序列	`ON SEQUENCES`
`f`	函数 / 聚合 / 过程	`ON FUNCTIONS`（= `ON ROUTINES`）
`T`	类型 / 域	`ON TYPES`
`n`	模式	`ON SCHEMAS`

sql

-- 把编码翻译成可读类型
SELECT r.rolname AS creating_role,
       CASE d.defaclobjtype
            WHEN 'r' THEN 'tables'
            WHEN 'S' THEN 'sequences'
            WHEN 'f' THEN 'functions'
            WHEN 'T' THEN 'types'
            WHEN 'n' THEN 'schemas'
       END AS object_type,
       n.nspname AS schema,
       d.defaclacl
FROM pg_default_acl d
LEFT JOIN pg_roles r ON r.oid = d.defaclrole
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace;

撤销函数默认 EXECUTE：安全基线动作

函数默认对 PUBLIC 可执行，是最常被收紧的出厂设置。正确姿势：

sql

-- 全局回收（对当前执行者未来创建的函数生效）
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

-- 按创建者登记：发布账号建的函数都不再默认开放
ALTER DEFAULT PRIVILEGES FOR ROLE deploy REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

然后按需显式授予调用方（或用一条全局 GRANT ... ON FUNCTIONS TO app_readwrite 模板）。这条基线配合存储过程的审计，能堵住「内部函数被任意会话调用」这类面。

多创建者场景：登记矩阵

现实项目里「会建对象的角色」通常不止一个：发布账号 deploy、迁移工具 flyway、手工排障的 DBA 个人号、偶尔建表的开发 dev。既然模板不随成员关系继承，就按「创建者 × 对象类型 × 受益组」列一张矩阵，然后逐行落成语句：

创建者	对象类型	受益组	授予内容
`deploy`	`TABLES`	`app_readonly` / `app_readwrite`	`SELECT` / 全套 DML
`deploy`	`SEQUENCES`	`app_readwrite`	`USAGE, SELECT`
`deploy`	`FUNCTIONS`	`app_readwrite`	`EXECUTE`（同时对 `PUBLIC` 收紧）
`flyway`	`TABLES`	同上	同上
`flyway`	`SCHEMAS`	`app_admin`	`USAGE, CREATE`
`dev`	`TABLES`	`app_readonly`	`SELECT`

用 FOR ROLE a, b 可以把相同模板合并登记，矩阵有重复行时优先合并：

sql

-- deploy 与 flyway 的表模板相同，合并为一条
ALTER DEFAULT PRIVILEGES FOR ROLE deploy, flyway IN SCHEMA app
    GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE deploy, flyway IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE deploy, flyway IN SCHEMA app
    GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

-- 函数基线：收紧 PUBLIC + 放开业务组
ALTER DEFAULT PRIVILEGES FOR ROLE deploy, flyway
    REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE deploy, flyway
    GRANT EXECUTE ON FUNCTIONS TO app_readwrite;

-- flyway 还负责建模式（ migrations 常会建新 schema）
ALTER DEFAULT PRIVILEGES FOR ROLE flyway
    GRANT USAGE, CREATE ON SCHEMAS TO app_admin;

维护要点：矩阵要与 CI 的角色清单对齐——新增一个「会建对象」的账号而矩阵没有登记，就是下一场「新表没权限」事故的入口。

查看默认权限：\ddp 与 pg_default_acl

bash

# psql 元命令：列出已登记的默认权限
\ddp

text

            Default access privileges
   Owner   | Schema |   Type   |     Access privileges
-----------+--------+----------+------------------------------------
 deploy    | app    | tables   | app_readonly=r/deploy
           |        |          | app_readwrite=arwd/deploy
 flyway    | app    | sequences| app_readwrite=U/flyway

SQL 层直接查目录 pg_default_acl：

sql

SELECT r.rolname   AS creating_role,
       n.nspname   AS schema,          -- 可能为 NULL（全局模板）
       d.defaclobjtype AS object_type, -- r=表(关系), S=序列, f=函数, T=类型, n=模式
       d.defaclacl AS acl
FROM pg_default_acl d
LEFT JOIN pg_roles r      ON r.oid = d.defaclrole
LEFT JOIN pg_namespace n  ON n.oid = d.defaclnamespace
ORDER BY 1, 2, 3;

defaclobjtype 的单字符编码与 \dp 的 ACL 缩写规则一致（r=SELECT、a=INSERT 等），解码方法见权限管理中的 ACL 一节。

序列与 IDENTITY 列：隐式创建也会命中模板

serial / GENERATED ... AS IDENTITY 列背后的序列是隐式创建的，它同样会触发默认权限模板——创建者是谁？是执行 CREATE TABLE 的角色。因此「deploy 建表 → 表的 identity 序列自动授权给 app_readwrite」只需一条模板即可覆盖，无需对每个序列动手：

sql

ALTER DEFAULT PRIVILEGES FOR ROLE deploy
    GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

两个细节：identity 序列的属主与表一致（且被表「拥有」，随表删除），但权限上仍是独立对象，模板 ACL 会正常应用；如果 INSERT 报 permission denied for sequence orders_id_seq，说明漏了这条模板或建表者不是登记过的角色——报错对象是序列而不是表，是最好的指路牌。

常见误区清单

误区	真相
「登记了模板，所以新表有权限了」——但建表的是别的角色	模板只被模板登记时指定的创建者（或执行者）创建的对象触发；`FOR ROLE` 必须写真实的建表角色
「给组登记默认权限，组员建表自动生效」	创建对象时只应用 current_user 名下的模板，成员关系不继承默认权限；必须逐个创建者登记
「`IN SCHEMA app REVOKE ... FROM PUBLIC` 能收回出厂的 PUBLIC 权限」	模式级模板只能叠加在全局之上、不能削减；收回出厂 `EXECUTE` 必须用全局（无 `IN SCHEMA`）的 REVOKE
「登记完模板，存量表也自动有了」	默认权限只作用于未来对象；存量对象要 `GRANT ... ON ALL TABLES IN SCHEMA` 逐个补
「`IN SCHEMA` 适用于一切类型」	`SCHEMAS` 与 `LARGE OBJECTS` 不允许 `IN SCHEMA` 限定
「`ON SCHEMAS` 管的是模式里的未来对象」	它管的是未来新建的模式本身的默认 ACL（PG15 起支持）

与 pg_dump / 恢复的关系

默认权限条目（pg_default_acl）会随 pg_dump 一起导出，dump 文件中以 ALTER DEFAULT PRIVILEGES 语句的形式出现，恢复时按序重建。由此有三个运维注意点：

恢复后模板仍然有效，不必在新环境重新登记——前提是 dump 覆盖了这些语句（定制 dump 过滤、只恢复部分对象时要留意）。
dump 语句里 FOR ROLE 写的是创建者角色名，跨环境恢复时要求这些角色已存在，否则该语句会失败（通常以超级用户恢复、或先建角色）。
模板与对象恢复顺序：ALTER DEFAULT PRIVILEGES 在对象创建语句之前执行时，新对象才会带上模板 ACL；pg_dump 的输出顺序已处理好这一点，手工拆分恢复时要注意。

多数据库环境的批量登记

模板按库登记的代价在「一实例多库」时显现：每个业务库、每个报表库都要来一遍。工程上两种收敛方式：

方式一：统一迁移脚本。 把模板语句放进与 DDL 同一个迁移工具（Flyway/Liquibase）的公共脚本里，每个库跑同一套迁移——模板与表结构走同一条流水线，天然不会漂移。推荐绝大多数团队用这个。

方式二：psql 循环执行。 一次性批量补登记时用脚本遍历目标库：

bash

#!/usr/bin/env bash
# 为实例中所有业务库登记同一份模板（排除系统库）
DBS=$(psql -U postgres -XAt -c "
  SELECT datname FROM pg_database
  WHERE datallowconn AND NOT datistemplate
    AND datname NOT IN ('postgres')")

for db in $DBS; do
  echo "==> registering default privileges in ${db}"
  psql -U postgres -d "$db" -X -v ON_ERROR_STOP=1 <<'SQL'
  ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA app
      GRANT SELECT ON TABLES TO app_readonly;
  ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA app
      GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;
  ALTER DEFAULT PRIVILEGES FOR ROLE deploy
      REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
SQL
done

批量方式的关键是幂等与断言：模板重复登记无害（同内容覆盖），脚本末尾用 pg_default_acl 查询断言每库的登记数符合预期，把「漏库」变成可发现的问题。

删除角色时的默认权限清理

给「创建者角色」登记过模板后，这个角色就多了一层与世界的关系。删除它之前必须清掉默认权限条目，否则 DROP ROLE 会失败（角色仍被 pg_default_acl 引用）：

sql

-- 方式一：逐条反向删除模板（精确）
ALTER DEFAULT PRIVILEGES FOR ROLE old_deploy IN SCHEMA app
    REVOKE SELECT ON TABLES FROM app_readonly;

-- 方式二：DROP OWNED 一并清理该角色的全部残留（含默认权限条目）
DROP OWNED BY old_deploy;

官方文档的建议即是：要么反向撤销默认权限，要么用 DROP OWNED BY 清除该角色的默认权限条目——这也呼应了删除角色的标准三步流程。

实战示例

示例一：让 app_readonly 自动获得未来表的读权限

完整闭环：存量补授 + 未来自动 + 验证。

bash

psql -U postgres -d appdb <<'SQL'
-- 0. 前提：组与角色就位
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
CREATE ROLE deploy LOGIN PASSWORD 'D3ploy_P@ss';   -- 发布/建表账号

-- 1. 存量表：一次性补授
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;

-- 2. 未来表：按真实创建者登记模板
ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA app
    GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA app
    GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

-- 3. 存量序列同理补授
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;
SQL

验证「未来对象自动获得权限」：

bash

# 用 deploy 建一张新表
psql "dbname=appdb user=deploy password=D3ploy_P@ss" \
  -c "CREATE TABLE app.new_table (id int PRIMARY KEY, note text);"

sql

-- 检查新表 ACL：app_readonly 应已出现
SELECT relname, relacl FROM pg_class
WHERE relnamespace = 'app'::regnamespace AND relname = 'new_table';

-- 也可以直接验证读权限
SELECT has_table_privilege('app_readonly', 'app.new_table', 'SELECT');
--  has_table_privilege
-- ---------------------
--  t

反向验证：把模板登记在错误的角色名下（比如超级用户执行时漏写 FOR ROLE deploy），新表将不会带上 app_readonly——这是本页最重要的自检。

\ddp 会在登记成功后出现这样的条目（注意 Owner 列是创建者而不是受益组）：

text

            Default access privileges
   Owner   | Schema |   Type   |     Access privileges
-----------+--------+----------+------------------------------------
 deploy    | app    | tables   | app_readonly=r/deploy
           |        |          | app_readwrite=arwd/deploy
 deploy    |        | sequences| app_readwrite=U/deploy

函数基线登记后，\ddp 里对应 functions 行的 ACL 显示会因 PUBLIC 被回收而与出厂状态不同；核对回收是否生效更可靠的办法是直接查 pg_default_acl（模板条目是否存在）加新建一个测试函数看它的实际 ACL。

示例二：新函数默认对 PUBLIC 关闭、对业务组开放

bash

psql -U postgres -d appdb <<'SQL'
-- 收紧：deploy 未来建的函数不再默认 PUBLIC 可执行
ALTER DEFAULT PRIVILEGES FOR ROLE deploy
    REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

-- 按需开放：读写组可用
ALTER DEFAULT PRIVILEGES FOR ROLE deploy
    GRANT EXECUTE ON FUNCTIONS TO app_readwrite;

-- 存量函数：一次性收紧 + 授予
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA app FROM PUBLIC;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA app TO app_readwrite;
SQL

验证：用 deploy 建一个测试函数，确认 ACL 不再含 PUBLIC：

bash

psql "dbname=appdb user=deploy password=D3ploy_P@ss" \
  -c "CREATE FUNCTION app.echo_hi() RETURNS text LANGUAGE sql AS 'SELECT ''hi'';'"

sql

SELECT proacl
FROM pg_proc
WHERE oid = 'app.echo_hi()::regprocedure';
-- proacl 里应只见 app_readwrite 的 X（及属主标记），不再有 PUBLIC 的 X

-- 抽查一个「不在业务组」的角色：应已无法执行
SELECT has_function_privilege('postgres', 'app.echo_hi()', 'EXECUTE');
--  has_function_privilege
-- ------------------------
--  f
--（postgres 在这里是普通角色语义的抽查；超级用户身份本身仍可执行一切）

更直接的断言是拿一个无关角色试执行：SET ROLE 成一个未授权角色后调用 app.echo_hi() 应报 permission denied for function echo_hi（SQLSTATE 42501），而 app_readwrite 的成员调用成功——两个结果都符合预期，基线才算闭环。

示例三：查看与清理默认权限

sql

-- 盘点当前登记的全部模板
SELECT r.rolname AS creating_role, n.nspname AS schema, d.defaclobjtype, d.defaclacl
FROM pg_default_acl d
LEFT JOIN pg_roles r ON r.oid = d.defaclrole
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace;

-- 撤销一条具体模板（反向 GRANT）
ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA app
    REVOKE SELECT ON TABLES FROM app_readonly;

-- 下线 deploy 时：DROP OWNED 会连同默认权限条目一起清掉
DROP OWNED BY deploy;

示例四：审计「没被模板覆盖的创建者」

默认权限最大的静默风险是「某个角色在建对象，但没有模板」。下面的审计查询找出当前库里建过对象、却不拥有任何表模板的角色：

sql

-- 有对象在身、但没有登记 TABLES 模板的创建者候选
WITH creators AS (
    -- 拥有表 / 序列 / 函数的角色（排除系统与属主组本身）
    SELECT DISTINCT c.relowner AS role_oid
    FROM pg_class c
    WHERE c.relkind IN ('r', 'v', 'S')
    UNION
    SELECT DISTINCT p.proowner FROM pg_proc p
),
registered AS (
    -- 登记过 TABLES（'r'）或 SEQUENCES（'S'）模板的创建者
    SELECT DISTINCT defaclrole AS role_oid
    FROM pg_default_acl
    WHERE defaclobjtype IN ('r', 'S')
)
SELECT ro.rolname AS creator_without_template,
       count(*)   AS owned_relations
FROM creators cr
JOIN pg_roles ro ON ro.oid = cr.role_oid
LEFT JOIN registered rg ON rg.role_oid = cr.role_oid
WHERE rg.role_oid IS NULL
  AND ro.rolname NOT LIKE 'pg\_%'
GROUP BY 1
ORDER BY 2 DESC;

结果处理口径：属主组（如 app_owner）与超级用户出现在结果里是正常的——前者本来就不需要模板（它拥有对象），后者不该出现在生产建表流程里；真正要处理的是 deploy / flyway / 开发个人号这类「继续在建对象」的角色。这条查询放进巡检脚本，与 pg_default_acl 快照一起作为基线对比。

注意事项

默认权限是模板，不是权限。 它躺在 pg_default_acl 里等待对象被创建；对存量对象毫无作用。「GRANT 过默认权限」与「对象上有 ACL」是两个不同的事实，巡检要分别检查。

FOR ROLE 缺省是新手事故高发区。 超级用户执行而不写 FOR ROLE，登记的是「超级用户建的对象」的模板；生产里没人用超级用户建表，这条模板就成了永远不触发的死配置。写 ALTER DEFAULT PRIVILEGES 必须自问：到底是谁在建表？

默认权限不随成员关系继承。 「给管理员组登记一份，组员都受益」行不通——创建对象时只应用 current_user 自己的模板。多几个发布/迁移账号，就要登记几份。

模式级模板只能叠加，不能削减。 想收回全局或出厂授予（尤其函数 EXECUTE），必须用不带 IN SCHEMA 的全局 REVOKE。

ON SCHEMAS 与 ON TABLES 是两回事。 前者管未来新建的「模式」，后者管模式里的未来「表」；PG15 才开始支持默认权限作用于模式。

登记模板本身需要权限。 你可以修改自己的默认权限，以及「你是其成员」的角色的默认权限；替无关角色登记需要超级用户或相应授权。

跨环境恢复依赖角色存在。 dump 出的 ALTER DEFAULT PRIVILEGES FOR ROLE xxx 在目标环境缺少该角色时会失败，恢复前置检查应包含这些角色。

模板登记 ≠ 一次配置永久有效。 模板本身是库内目录条目，会随库删除而消失，也会被 DROP OWNED 清掉；「换库」「重建库」「下线账号」三个动作都要重新核对模板登记情况，把它纳入 checklist 而不是依赖记忆。

同一（创建者 × 类型 × 模式）重复登记以最后一条为准的感知不可靠。 模板的 GRANT 语义与对象 ACL 一致：再次登记同向 GRANT 是无害叠加，登记反向 REVOKE 则撤销之前的同向效果；复杂组合下不要靠脑内推演，用测试账号建一个对象验证实际 ACL。

最佳实践

「存量 + 未来」成对出现：GRANT ... ON ALL TABLES IN SCHEMA（存量）与 ALTER DEFAULT PRIVILEGES FOR ROLE ...（未来）必须写在一起，作为标准授权脚本的两段式结构。
FOR ROLE 永远显式写出，且指向真实的建表角色（发布账号、迁移工具账号）；把「哪些角色会建对象」写进团队规范。
每个可能建对象的角色都登记一份模板（FOR ROLE a, b, c 可一次写多个），杜绝「某个账号建的新表忘授权」。
新库初始化即收紧函数 EXECUTE：全局 REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC 按创建者登记，业务组按需 GRANT。
默认权限与 REVOKE CREATE ON SCHEMA public FROM PUBLIC 一起构成新库基线，写进初始化清单而不是事后加固。
用 pg_default_acl 查询做配置审计：模板是集群配置的一部分，应与授权矩阵一起进入版本化迁移脚本（Flyway/Liquibase 等）。
模式级模板只用于「额外加权限」，全局收紧类操作一律走无 IN SCHEMA 的全局语句，避免「只加不减」规则造成的假回收。
删除角色走三步：反向撤销模板或 DROP OWNED（逐库）→ REASSIGN OWNED → DROP ROLE，把默认权限条目纳入清理清单。
演练恢复流程：确认 pg_dump 输出包含全部 ALTER DEFAULT PRIVILEGES 语句，并在目标环境先行创建 FOR ROLE 涉及的角色。
验证模板是否真正挂上：用测试账号建一张临时表，has_table_privilege() 断言只读组立即可读；这个 30 秒的检查能拦住「FOR ROLE 写错」的高频事故。

继续学习：权限系统速查

默认权限（ALTER DEFAULT PRIVILEGES） ​

基础概念 ​

问题：新对象只对属主开放 ​

默认权限是什么 ​

两个维度：FOR ROLE 与 IN SCHEMA ​

语法与用法 ​

详细说明 ​

出厂默认权限：属主全权 + PUBLIC 三类 ​

FOR ROLE：作用于谁创建的新对象 ​

执行权限要求与可管理范围 ​

IN SCHEMA 限定与「只加不减」规则 ​

SCHEMAS 类默认权限（PG15+）：管「未来新建的模式」 ​

支持的对象类型 ​

WITH GRANT OPTION 的默认权限 ​

defaclobjtype 编码对照 ​

撤销函数默认 EXECUTE：安全基线动作 ​

多创建者场景：登记矩阵 ​

查看默认权限：\ddp 与 pg_default_acl ​

序列与 IDENTITY 列：隐式创建也会命中模板 ​

常见误区清单 ​

与 pg_dump / 恢复的关系 ​

多数据库环境的批量登记 ​

删除角色时的默认权限清理 ​

默认权限（ALTER DEFAULT PRIVILEGES）

基础概念

问题：新对象只对属主开放

默认权限是什么

两个维度：FOR ROLE 与 IN SCHEMA

语法与用法

详细说明

出厂默认权限：属主全权 + PUBLIC 三类

FOR ROLE：作用于谁创建的新对象

执行权限要求与可管理范围

IN SCHEMA 限定与「只加不减」规则

SCHEMAS 类默认权限（PG15+）：管「未来新建的模式」

支持的对象类型

WITH GRANT OPTION 的默认权限

defaclobjtype 编码对照

撤销函数默认 EXECUTE：安全基线动作

多创建者场景：登记矩阵

查看默认权限：\ddp 与 pg_default_acl

序列与 IDENTITY 列：隐式创建也会命中模板

常见误区清单

与 pg_dump / 恢复的关系

多数据库环境的批量登记

删除角色时的默认权限清理