GRANT / REVOKE 权限管理
上一页我们创建了角色，但一个刚出生的角色什么也做不了：它连不上数据库、看不见任何模式、读不了任何表。把角色与对象连接起来的，就是本页的主角——GRANT（授予）与 REVOKE（回收）。它们是 PostgreSQL 权限体系中使用频率最高、也是最容易「想当然」的两条命令。
权限管理真正复杂的地方不在语法，而在心智模型：一个会话的权限来自「属主、直接授权、成员继承、PUBLIC」四条路径的并集；GRANT 与 REVOKE 之间隔着 WITH GRANT OPTION 的转授权链；表权限不会自动覆盖序列，模式权限不会自动覆盖表。这些细节决定了「为什么我明明 GRANT 了还是连不上」这类问题的答案。
本页先建立完整的权限矩阵与语法框架，再逐类对象展开，最后给出一套可以直接落地的「模式 → 表 → 序列」授权流程。
前置知识
建议先阅读：CREATE / DROP ROLE
基础概念
权限的四个来源
执行任何一条语句前，PostgreSQL 会检查当前角色（current_user）是否具备所需权限。判断依据是四条路径的并集：
| 来源 | 说明 | 示例 |
|---|---|---|
| 属主（owner） | 对象属主天然拥有该对象的全部可授予权限与 grant option | 建表者对自己的表 |
| 直接授权 | 权限直接授给了当前角色 | GRANT SELECT ON t TO app_ro; |
| 成员继承 | 当前角色是某角色的成员，该角色持有权限 | alice 通过 app_readonly 组获得 SELECT |
| PUBLIC | 授给伪角色 PUBLIC 的权限对所有角色生效（含未来新建的角色） | 函数默认的 EXECUTE |
四个来源里最容易被忽略的是后两个。成员继承让权限审计必须顺着 pg_auth_members 展开才能算清（多级嵌套时更甚）；PUBLIC 则是「新函数人人可执行」「新数据库人人可连」这类现象的根源。第 1 条（属主）也要记住另一半：删除、修改对象的定义不属于可授予权限，它们内生于属主身份，无法 GRANT，只能通过「让某人成为属主角色的成员」来间接获得。
用一小段脚本直观感受「并集」——alice 自己没有任何授权，却依然能读表：
CREATE ROLE app_reader NOLOGIN; -- 组角色
CREATE ROLE alice LOGIN; -- 成员
GRANT app_reader TO alice; -- alice 加入组
GRANT SELECT ON public.orders TO alice; -- 路径 2：直接授权
GRANT SELECT ON public.orders TO app_reader; -- 路径 3：经组继承获得
GRANT SELECT ON public.orders TO PUBLIC; -- 路径 4：所有角色都有
-- 三条路径只要存在任意一条，alice 就能读；
-- 反过来，只 REVOKE 其中一条，另外两条仍然放行——
-- 这就是「明明回收了怎么还能查」的常见原因：没有撤干净全部路径。
REVOKE SELECT ON public.orders FROM alice; -- app_reader 与 PUBLIC 依然放行对象与权限矩阵
不同对象可授予的权限种类不同，这是本页的「总纲」，后文逐类展开：
| 对象类型 | 可授予权限 | 默认授给 PUBLIC |
|---|---|---|
| 数据库 | CONNECT、CREATE、TEMPORARY（别名 TEMP） | CONNECT、TEMPORARY |
| 模式 | USAGE、CREATE | 无 |
| 表（含视图、外表） | SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、MAINTAIN | 无 |
| 列 | SELECT、INSERT、UPDATE、REFERENCES | 无 |
| 序列 | USAGE、SELECT、UPDATE | 无 |
| 函数 / 过程 / 聚合 | EXECUTE | EXECUTE |
| 语言 | USAGE | USAGE |
| 大对象 | SELECT、UPDATE | 无 |
| 类型 / 域 | USAGE | 无 |
| 外部数据封装器（FDW） | USAGE | 无 |
| 外部服务器（foreign server） | USAGE | 无 |
| 表空间 | CREATE | 无 |
| 配置参数 | SET、ALTER SYSTEM | 无 |
记忆口径
「读」类权限基本都是 SELECT 或 USAGE；「写」类是 INSERT/UPDATE/DELETE/TRUNCATE；两类特殊的是 EXECUTE（函数）与 CONNECT（数据库）。CREATE 在数据库、模式、表空间三类对象上都有，但含义分别是「建模式」「建对象」「建表空间内对象」。
GRANTOR 与转授权链
每次授权都会记录授权者（grantor）。普通角色只能授予「自己拥有的对象」或「自己以 WITH GRANT OPTION 持有的权限」；超级用户执行 GRANT 时，记录的授权者是对象属主（成员关系则记为启动超级用户）。这条链决定了 REVOKE ... CASCADE 的波及范围，也让「谁能把权限发出去」变得可审计。
语法与用法
GRANT：对象权限
对象权限的授予语法按对象类型分派（省略号表示可重复）：
-- 表（含视图、外表），也支持 ALL TABLES IN SCHEMA
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ] [ GRANTED BY role_specification ];
-- 列级
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ) [, ...] }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ];
-- 序列，也支持 ALL SEQUENCES IN SCHEMA
GRANT { { USAGE | SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ];
-- 数据库
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ];
-- 模式
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ];
-- 函数 / 过程 / 例程
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ];
-- 类型 / 语言 / FDW / 外部服务器（USAGE）、表空间（CREATE）、大对象（SELECT|UPDATE）、参数（SET|ALTER SYSTEM）
GRANT USAGE ON TYPE type_name TO role_name;
GRANT USAGE ON LANGUAGE lang_name TO role_name;
GRANT USAGE ON FOREIGN DATA WRAPPER fdw_name TO role_name;
GRANT USAGE ON FOREIGN SERVER server_name TO role_name;
GRANT CREATE ON TABLESPACE tablespace_name TO role_name;
GRANT SELECT, UPDATE ON LARGE OBJECT loid TO role_name;
GRANT SET, ALTER SYSTEM ON PARAMETER configuration_parameter TO role_name;其中 role_specification 可以是 [ GROUP ] role_name（GROUP 只是兼容性噪声字）、PUBLIC、CURRENT_ROLE、CURRENT_USER 或 SESSION_USER。
GRANT：角色成员关系
GRANT 的另一种形态授的不是权限，而是成员关系：
GRANT role_name [, ...] TO role_specification [, ...]
[ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ]
[ GRANTED BY role_specification ];ADMIN / INHERIT / SET 三个选项是 PG16 引入的细粒度控制（详见角色继承）；注意成员关系不能授予 PUBLIC。
REVOKE
-- 回收对象权限
REVOKE [ GRANT OPTION FOR ]
{ SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN | ALL [ PRIVILEGES ] }
ON table_name [, ...]
FROM role_specification [, ...] [ CASCADE | RESTRICT ];
-- 回收成员关系（或仅回收 ADMIN 选项）
REVOKE [ ADMIN OPTION FOR ] group_role FROM member_role [, ...] [ CASCADE | RESTRICT ];REASSIGN OWNED / DROP OWNED
-- 把角色的全部对象（及其属主权限）转交给另一个角色
REASSIGN OWNED BY old_role [, ...] TO new_role;
-- 删除角色在被授予方留下的全部对象与权限（含默认权限条目）
DROP OWNED BY old_role [, ...] [ CASCADE | RESTRICT ];最小可用示例
-- 建角色、给库、给模式、给表、给序列
CREATE ROLE app_ro LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE appdb TO app_ro;
GRANT USAGE ON SCHEMA app TO app_ro;
GRANT SELECT ON app.orders TO app_ro;
-- 验证权限（先切身份再看）
SET ROLE app_ro;
SELECT * FROM app.orders LIMIT 1; -- 成功
INSERT INTO app.orders VALUES (1); -- ERROR: permission denied for table orders
RESET ROLE;详细说明
数据库权限：CONNECT / CREATE / TEMPORARY
CONNECT：允许建立到该数据库的连接。注意它必须与 pg_hba.conf 同时放行：hba 拒绝，CONNECT再大也进不来；hba 放行，没有CONNECT同样进不来。默认值方面，CONNECT与TEMPORARY默认已授予PUBLIC——所以「新建数据库人人可连」其实是出厂设置，收紧时第一步就是REVOKE CONNECT ON DATABASE xxx FROM PUBLIC;。CREATE：允许在该数据库中创建模式（schema）。PG15 起它还隐含「创建他人信任的扩展目录对象」的谨慎语义，因此不要再把它随手授给业务角色。TEMPORARY/TEMP：允许创建临时表。默认授予PUBLIC。
模式权限：USAGE / CREATE
模式权限是绝大多数「明明 GRANT 了表还是找不到」的原因：
USAGE：允许访问该模式中包含的对象（前提是对象本身的权限也放行）。没有USAGE，即使对表有SELECT，SELECT * FROM app.orders也会报permission denied for schema app，\dt app.*也看不到表。CREATE：允许在模式中创建新对象。对public模式而言这是历史遗留的大坑——旧版本默认把public的CREATE授给PUBLIC，任何能连库的用户都能在里面建对象，PG15 起新初始化的数据库已默认收回，老实例应当手工回收：
REVOKE CREATE ON SCHEMA public FROM PUBLIC;完整的访问链 = 数据库 CONNECT + 模式 USAGE + 对象权限，三者缺一不可。授权时要按这个顺序补齐，排查时也按这个顺序检查。
表权限详解
| 权限 | 允许的动作 |
|---|---|
SELECT | 读取（也允许在 COPY、EXPLAIN、引用外键等场景读） |
INSERT | 插入行（含 COPY FROM）；与 SELECT 无关 |
UPDATE | 更新行；SELECT ... FOR UPDATE 也需要它 |
DELETE | 删除行 |
TRUNCATE | 清空表（属于高危权限，绕过行级触发器与逐行检查） |
REFERENCES | 在外键中引用本表（或建检查约束时被引用） |
TRIGGER | 在本表上创建触发器 |
MAINTAIN | 对表执行 VACUUM、ANALYZE、CLUSTER、REFRESH MATERIALIZED VIEW、REINDEX、LOCK TABLE（PG17 新增，pg_maintain 预设角色等价于对所有关系持有它） |
两个高频误区：其一，INSERT 返回自增主键需要序列的 USAGE，但表授权不会自动带到序列，GRANT ALL ON orders 也只管 orders 这张表；其二，视图的权限是独立的——用户对视图有 SELECT 不代表对底层表有 SELECT，这正是「用视图收窄访问面」的原理。
列级权限
列级权限支持的类型是 SELECT、INSERT、UPDATE、REFERENCES，典型用途是「允许写、但不许看敏感列」或「只许看某几列」：
-- 只读两列
GRANT SELECT (id, name) ON app.employees TO intern_ro;
-- 允许更新某几列
GRANT UPDATE (status, note) ON app.orders TO ops_rw;
-- 允许插入但不允许读（比如审计流水）
GRANT INSERT (uid, action) ON app.audit TO app_writer;列级与表级不能「相减」
官方文档明确：先授予表级 SELECT，再试图针对某一列 REVOKE，不会实现「除该列外都可读」——表级授权不受列级操作影响。要做「排除列」的效果，必须从一开始就只做列级授予。另外，有列级 SELECT 的用户如果执行整行查询，依然会被拒绝。
序列权限：USAGE / SELECT / UPDATE
| 权限 | 允许的动作 |
|---|---|
USAGE | currval / nextval |
SELECT | currval / lastval |
UPDATE | setval / nextval |
INSERT 到带自增列（serial / identity）的表需要目标序列的 USAGE，这是「授权流程必须包含序列」的原因。批量补齐一个模式的序列权限：
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;函数 EXECUTE 与 PUBLIC 默认
函数的 EXECUTE 默认授予 PUBLIC——新函数一创建就人人可执行。对业务库来说这通常是过宽的，安全基线的做法是：在函数创建后的发布流程中回收 PUBLIC，再按需授予。配合默认权限可以自动化，详见默认权限：
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA app TO app_rw;注意命名细节：FUNCTION 语法涵盖普通函数、聚合与窗口函数但不包含过程（procedure），PROCEDURE 只指过程，ROUTINE 两者皆指。同样地，ALL FUNCTIONS IN SCHEMA 不包含过程，需要用 ALL ROUTINES IN SCHEMA 才能全覆盖。
其他对象：大对象、表空间、类型、FDW、参数
- 大对象：可授予
SELECT（读）与UPDATE（写），按 OID 授予。 - 表空间：只有
CREATE，允许在其中建对象（如表、索引、数据库目录）。 - 类型 / 域：
USAGE，允许在表定义、函数签名中引用该类型。 - FDW / 外部服务器：
USAGE，允许建立到外部服务器的用户映射并访问外部表。 - 配置参数（PG15+）：
SET允许ALTER ROLE ... SET/ 会话内SET该参数；ALTER SYSTEM允许通过ALTER SYSTEM SET修改。这是把「改参数」从超级用户手中分拆出来的最小化授权。
批量授权：ALL ... IN SCHEMA 的边界
GRANT ... ON ALL TABLES IN SCHEMA 是日常使用频率最高的批量形式，但它有几条明确边界，超出预期的事故多半出在这里：
- 支持的对象类型有限：目前只有表、序列、函数/过程三类支持
ALL ... IN SCHEMA。类型、FDW、外部服务器等没有批量形式，只能逐个授予。 ALL TABLES的覆盖面比字面更宽：它同时涵盖普通表、分区表、视图与外表。ALL FUNCTIONS不含过程：它覆盖普通函数、聚合与窗口函数；要连过程一起覆盖需用ALL ROUTINES。- 只覆盖当前已存在的对象：之后新建的表/序列不会被追溯，必须配合
ALTER DEFAULT PRIVILEGES（下一节）才能「存量 + 增量」全覆盖。 - 只作用于列出的模式：不会下钻子模式（PostgreSQL 的模式本就没有层级），多模式要逐个写。
-- 存量：一次性覆盖 schema 里已有的全部对象
GRANT SELECT ON ALL TABLES IN SCHEMA app, archive TO app_readonly;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app, archive TO app_readwrite;
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA app TO app_readwrite;
-- 增量：未来新建的对象自动跟上（由 app_owner 创建时生效）
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT ON TABLES TO app_readonly;PUBLIC 与默认权限
PUBLIC 是一个隐式定义的「伪组」，永远包含所有角色（包括将来创建的）。出厂默认授予 PUBLIC 的只有三类：数据库的 CONNECT 与 TEMPORARY、函数的 EXECUTE、语言的 USAGE。安全加固的常见第一步就是收紧它们：
-- 新库基线（按需取舍）
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;注意 grant option 不能给 PUBLIC
WITH GRANT OPTION 不能授予 PUBLIC，这是 PostgreSQL 与 SQL 标准的差异之一。此外成员关系也永远不能授予 PUBLIC。
WITH GRANT OPTION 与 WITH ADMIN OPTION
两个「转授权」开关作用在不同的东西上：
WITH GRANT OPTION作用于对象权限：被授予者可以把该权限再授予别人。转授出去的权限在链路上记录了 grantor，上层回收（REVOKE ... CASCADE）会级联回收下层。WITH ADMIN OPTION作用于角色成员关系：被授予者可以把该角色的成员关系授予或回收给他人。它不被继承（下一页详述）。
GRANT SELECT ON app.orders TO team_lead WITH GRANT OPTION; -- 对象权限转授
GRANT app_readwrite TO team_lead WITH ADMIN OPTION; -- 成员关系转授一个容易忽略的规则：非属主角色执行 GRANT 时，只有「自己以 grant option 持有的那部分」会真正生效；GRANT ALL PRIVILEGES 若没有任何 grant option，只会发出警告而不会授出任何东西。
GRANT role TO role：成员关系与 PG16 的三选项
成员关系的授予与回收是组角色体系的发动机：
-- 基本形式
GRANT app_readwrite TO alice;
REVOKE app_readwrite FROM alice;
-- PG16 起可按成员关系逐项设置三个开关（默认 ADMIN=false、INHERIT=跟随成员角色属性、SET=true）
GRANT app_admin TO alice WITH ADMIN TRUE, INHERIT TRUE, SET TRUE;
GRANT app_readwrite TO bob WITH INHERIT FALSE, SET TRUE;
-- 修改既有成员关系的选项：重新 GRANT 并写明新值即可，未写的保持原值
GRANT app_readwrite TO bob WITH INHERIT TRUE;
-- 只回收管理权而保留成员身份
REVOKE ADMIN OPTION FOR app_admin FROM alice;ADMIN 允许转授成员关系；INHERIT 决定该成员是否自动可用组权限；SET 决定能否 SET ROLE 切换过去。超级用户可以为任何角色增加/撤销成员；持有 ADMIN OPTION 的普通成员也可以。这些选项与继承链的完整交互，正是下一页的主题。
GRANTED BY：让授权「记在谁头上」可指定
GRANTED BY 子句在两种形态下的语义并不相同，混为一谈会造成审计误判：
- 对象权限：
GRANTED BY目前仅为 SQL 标准兼容而存在，指定的授权者必须就是当前执行语句的用户，实际上改不了任何东西。超级用户给对象授权时，grantor 一律记录为对象属主。 - 角色成员关系：
GRANTED BY有实际作用——可以把这条授权记录为「由指定角色执行」。前提是当前用户具备该角色的权限（能以它的身份行事），且该角色对目标角色持有ADMIN OPTION（引导超级用户除外）。这意味着：如果之后把该角色的ADMIN OPTION收回，以它名义做出的授权也会被级联回收——授权记录与授权者资格是绑定的。
-- 成员关系：把授权记录为 dba_team 所为（当前用户需具备 dba_team 权限，
-- 且 dba_team 需持有 app_admin 的 ADMIN OPTION）
GRANT app_admin TO alice GRANTED BY dba_team;
-- 之后若收回 dba_team 的 ADMIN OPTION，这条授权会一并级联失效
REVOKE ADMIN OPTION FOR app_admin FROM dba_team;对审计的价值在于：授权记录不再是笼统的「超级用户操作的」，而是能追溯到具体的职责角色；同时它也是一种约束——授权者失去资格，其签发的授权随之失效。
REVOKE 详解
REVOKE 有几个决定成败的细节：
RESTRICT/CASCADE：默认RESTRICT——若存在依赖（例如目标角色又把权限转授了别人），回收直接失败；CASCADE则连同依赖一并回收。涉及GRANT OPTION的回收通常需要CASCADE。GRANT OPTION FOR：只收回「转授权的资格」，被转授出去的权限本身是否保留，取决于是否CASCADE。- 属主可以自撤：PostgreSQL 允许属主
REVOKE自己的权限（例如把表的INSERT/UPDATE/DELETE/TRUNCATE撤掉让自己变成只读），这是 SQL 标准不允许的扩展。 - 授权者是超级用户时：记录的 grantor 是对象属主，因此任何属主角色的成员之后都能回收它。
-- 级联回收 team_lead 转授出去的所有 SELECT
REVOKE SELECT ON app.orders FROM team_lead CASCADE;
-- 回收 alice 的全部对象权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA app FROM alice;
REVOKE ALL PRIVILEGES ON DATABASE appdb, SCHEMA app FROM alice;REASSIGN OWNED 与 DROP OWNED
删除角色前的两把扫帚（都必须在该角色拥有对象的每个数据库中执行）：
| 命令 | 作用 | 典型位置 |
|---|---|---|
REASSIGN OWNED BY old TO new | 把 old 拥有的全部对象转给 new（权限记录一并转为 new 持有） | 想保留对象时 |
DROP OWNED BY old | 删除 old 在其他对象上的权限授权、old 拥有的对象（含共享对象），以及 old 建立的默认权限条目 | 想清理干净时 |
标准删除流程：
-- 在每一个涉及的数据库中：
REASSIGN OWNED BY old_app TO app_owner; -- 或 DROP OWNED BY old_app;
-- 全部库处理完后：
DROP ROLE old_app;DROP OWNED 需要谨慎：它不仅撤权限，还会删除该角色拥有的对象（如仅它持有的表），必要时加 CASCADE。组角色的删除则简单得多——DROP ROLE 组角色会自动撤销所有成员关系，成员本身不受影响。
查看权限：\dp、\ddp 与系统目录
# \dp：查看对象的 ACL（权限清单）
\d+ app.orders
# \ddp：查看默认权限（ALTER DEFAULT PRIVILEGES 的产出）
\ddp-- \dp 输出示例（权限缩写含义见下）
Access privileges
Schema | Table | Access privileges | Column privileges | Policies
--------+--------+-------------------------------------------+-------------------+----------
app | orders | app_owner=arwdDxtm/app_owner +| |
| | app_rw=arwd/app_owner +| |
| | app_ro=r/app_owner | |ACL 字符串的缩写与权限的对应关系（详见官方 Access Control Lists 一节）：r=SELECT、a=INSERT、w=UPDATE、d=DELETE、D=TRUNCATE、x=REFERENCES、t=TRIGGER、X=EXECUTE、U=USAGE、C=CREATE、c=CONNECT、T=TEMPORARY、m=MAINTAIN（PG17 起）。格式为 grantee=privs/grantor，空白 grantee 表示 PUBLIC。
SQL 层的查看手段：
-- 信息模式：当前用户经手/持有的表级授权
SELECT grantor, grantee, table_schema, table_name, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE table_schema = 'app'
ORDER BY table_name, grantee;
-- 目录方式：直接解析 ACL（最完整）
SELECT relname, relacl
FROM pg_class
WHERE relnamespace = 'app'::regnamespace AND relkind IN ('r', 'v');
-- 权限检查函数：面向「某角色是否有某权限」的布尔提问
SELECT has_table_privilege('app_ro', 'app.orders', 'SELECT'); -- t
SELECT has_table_privilege('app_ro', 'app.orders', 'INSERT'); -- f
SELECT has_schema_privilege('app_ro', 'app', 'USAGE'); -- t
SELECT has_database_privilege('app_ro', 'appdb', 'CONNECT'); -- t
SELECT has_column_privilege('app_ro', 'app.orders', 'amount', 'SELECT');
SELECT pg_has_role('alice', 'app_readwrite', 'MEMBER'); -- 是否（直接或间接）是成员
SELECT pg_has_role('alice', 'app_readwrite', 'USAGE'); -- 是否无需 SET ROLE 即可用其权限权限生效时机与连接池
GRANT / REVOKE 在提交后立即生效：权限检查发生在语句执行时，读的是当时的授权状态，已存在的连接立刻受影响，不存在「重启才生效」的问题。这一步是可靠的。
真正的坑在连接池。当应用经过 PgBouncer 等池化中间件时：
- 会话级状态（
SET ROLE、SET search_path）可能残留到下一个复用者，务必配置server_reset_query（transaction 模式下 PostgreSQL 14+ 的discarding行为已改善，但仍需按官方建议配置）。 ALTER ROLE ... SET修改的登录默认值只对新连接生效，池内旧连接不受影响，改完应对池执行RELOAD并按需PAUSE; RESUME;让连接重建。- 回收某应用角色的权限后，该角色的存量连接仍然存活，只是后续语句开始报权限错误；若目标是「立即断开」，用
pg_terminate_backend()处理存量会话。
与 RLS 的关系
对象权限决定「能不能碰到这张表」，行级安全决定「表里哪些行可见可改」。两者独立工作：SELECT 权限放行了表访问，但 RLS 策略可能仍然过滤掉大部分行；反之 BYPASSRLS 或表属主可以越过 RLS，却依然要受 SELECT 权限约束。需要在行级做隔离的场景，请阅读行级安全概览。
实战示例
示例一：完整的「模式 → 表 → 序列」授权流程
目标：为业务库建立「只读 / 读写 / 管理」三个组角色，并把成员挂进去。
psql -U postgres -d appdb <<'SQL'
-- 0. 数据库基线：先收紧 PUBLIC
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- 1. 组角色
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
CREATE ROLE app_admin NOLOGIN;
-- 2. 数据库 + 模式
GRANT CONNECT ON DATABASE appdb TO app_readonly, app_readwrite, app_admin;
GRANT USAGE ON SCHEMA app TO app_readonly, app_readwrite;
GRANT USAGE, CREATE ON SCHEMA app TO app_admin;
-- 3. 表
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app TO app_admin;
-- 4. 序列（读写角色要能取自增值）
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA app TO app_admin;
-- 5. 未来对象自动跟上（详见默认权限页）
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;
-- 6. 成员
CREATE ROLE alice LOGIN PASSWORD 'A1ice_P@ss';
CREATE ROLE bob LOGIN PASSWORD 'B0b_P@sswd';
GRANT app_readonly TO alice;
GRANT app_readwrite TO bob;
GRANT app_admin TO alice WITH ADMIN OPTION;
SQL示例二：验证与排错
# 用成员身份登录
psql "dbname=appdb user=bob password=B0b_P@sswd"-- bob 的会话内
SELECT current_user, session_user;
-- current_user | session_user
-- --------------+--------------
-- bob | bob
SELECT * FROM app.orders LIMIT 1; -- 有 SELECT，成功
INSERT INTO app.orders(id) VALUES (9001); -- 有 INSERT + 序列 USAGE，成功
TRUNCATE app.orders; -- 没给 TRUNCATE，失败ERROR: permission denied for table orders
SQLSTATE: 42501排错顺序：数据库 CONNECT → 模式 USAGE → 对象权限 → （列级）→ 报错消息里的 permission denied for <对象类型> 会直接指明卡在哪一层。
示例三：回收与删除角色的完整流程
-- 假设要下线临时角色 temp_etl
-- 1. 断开其存量会话
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'temp_etl';
-- 2. 逐库转交或清理（在 temp_etl 拥有对象的每个库里执行）
REASSIGN OWNED BY temp_etl TO app_admin; -- 保留对象
DROP OWNED BY temp_etl; -- 清掉残留授权与默认权限条目
-- 3. 删除角色
DROP ROLE temp_etl;示例四：列级权限实现「可写、不可读敏感列」
场景：客服工单表 support.tickets 中 customer_phone、id_card 是敏感列。客服角色需要创建并更新工单，但不允许读取敏感列；质检角色只读、且允许补充 review_note 列。
-- 0. 前置：数据库 CONNECT 与模式 USAGE 已按标准流程放行
CREATE ROLE cs_agent NOLOGIN; -- 客服组
CREATE ROLE qc_reader NOLOGIN; -- 质检组
-- 1. 客服：可读非敏感列 + 可写（列级 INSERT / UPDATE，不含敏感列）
GRANT SELECT (id, customer_name, subject, status, created_at)
ON support.tickets TO cs_agent;
GRANT INSERT (id, customer_name, subject, status)
ON support.tickets TO cs_agent;
GRANT UPDATE (subject, status)
ON support.tickets TO cs_agent;
-- 2. 质检：整行只读 + 专属可写列
GRANT SELECT ON support.tickets TO qc_reader;
GRANT UPDATE (review_note) ON support.tickets TO qc_reader;
-- 3. 验证
SET ROLE cs_agent;
SELECT id, subject FROM support.tickets; -- 成功：白名单内的列
SELECT customer_phone FROM support.tickets; -- 失败：敏感列未授权
UPDATE support.tickets SET status = 'closed' WHERE id = 1; -- 成功
RESET ROLE;ERROR: permission denied for table tickets
SQLSTATE: 42501两个落地要点：其一，脚本从第一天起就只用列级授权、从不给表级 SELECT，因为表级授权一旦发出就无法用列级 REVOKE 减掉；其二，新增非敏感列后要记得补一条 GRANT SELECT (新列) ...，列级白名单不会自动扩展——这是列级方案在演进中最大的维护成本，变更清单里必须包含权限条目。
注意事项
授权不等于能连，能连不等于能用。 数据库 CONNECT（且 hba 放行）+ 模式 USAGE + 对象权限三层链条缺一不可；任何一层缺失，报错都会是 permission denied，但指代的对象不同，读报错时要看清楚是 for schema 还是 for table。
表授权不会自动覆盖序列。 serial / identity 列的 INSERT 需要序列 USAGE；GRANT ALL ON ALL TABLES 之后忘掉 ALL SEQUENCES 是最常见的上线事故之一。
列级权限不能从表级权限里「减」出来。 要做列掩码，请只做列级 GRANT；先表级 GRANT 再列级 REVOKE 不会生效。
REVOKE ... FROM PUBLIC 要早于业务上线。 CONNECT、TEMPORARY、EXECUTE、语言 USAGE 的 PUBLIC 默认意味着「先建库后加固」的窗口里权限是开着的；新库初始化脚本应包含基线回收。
WITH GRANT OPTION 会制造授权树。 每一次转授都是一条依赖边，REVOKE 时没有 CASCADE 会被 RESTRICT 拒绝。除非确有分层授权需求，尽量不给业务角色转授权。
权限是立即生效的，连接池是滞后的。 排查「权限改了没生效」时先看池：会话级 SET ROLE 残留、旧连接未应用新的角色级默认参数，都是池化环境的常见原因。
DROP OWNED 是危险命令。 它会删除目标角色拥有的对象，而不只是权限；在共享属主模型下误用可能连带删表。执行前用 \dt / REASSIGN 盘点。
超级用户执行的 GRANT 会「记在别人头上」。 grantor 显示为对象属主（成员关系则显示为启动超级用户），审计日志要留意这一点，否则会误判授权来源。
重复 GRANT 是幂等的，成员关系选项却以最后一次为准。 对对象权限而言，重复授予同一权限不会报错也不会有副作用；但成员关系的三个选项（ADMIN / INHERIT / SET）以最后一次 GRANT 写明的值为准、未写明的保持原值——用脚本重放授权语句时务必意识到这一点，别把「补授权」写成了「改配置」。
information_schema 只显示你有资格看到的授权。 role_table_grants 等视图只会列出当前角色持有的权限或经其转授的记录，用它做全库审计会漏报；全量审计应改用 pg_class.relacl / aclexplode() 或 \dp。
最佳实践
- 权限授给组角色，人通过成员关系获得：禁止把对象权限直接授给
LOGIN角色（临时排障除外），这样权限矩阵永远是「组 × 对象」的小表，而不是「人 × 对象」的大表。 - 按「CONNECT → USAGE → 对象 → 序列」四步走标准授权，并把这套顺序固化成脚手架脚本，杜绝漏层。
- 新库第一件事是收紧 PUBLIC：
REVOKE CONNECT ON DATABASE ... FROM PUBLIC、REVOKE CREATE ON SCHEMA public FROM PUBLIC、按需回收函数EXECUTE。 - 配对使用
GRANT ... ON ALL ... IN SCHEMA与ALTER DEFAULT PRIVILEGES：前者覆盖存量，后者覆盖未来，缺一都会造成「新表忘授权」。 TRUNCATE、MAINTAIN类高危权限单独审批：TRUNCATE绕过逐行检查与删除触发器；维护类权限用pg_maintain精确下发。- 函数发布流程默认回收 PUBLIC 的
EXECUTE，再按需授予调用方；安全敏感函数（处理凭证、跨权限数据）必须如此。 - 需要分层转授时才用
WITH GRANT OPTION，并定期用\dp与information_schema.role_table_grants审计转授链；否则一律不给。 REVOKE带CASCADE前先评估依赖：用\dp确认谁把权限转给了谁，避免生产环境出现「一撤全没了」。- 权限变更纳入版本化迁移（Flyway/Liquibase 等），与 DDL 同一个流水线发布，避免「库里有的、仓库没有」的漂移。
- 删角色走三步脚本：
REASSIGN OWNED/DROP OWNED（逐库）→DROP ROLE；先pg_terminate_backend清会话再动手。 - 在连接池侧配置会话重置：
server_reset_query、变更角色默认参数后RELOAD+ 重建连接，保证权限与会话状态的可预期性。 - 用权限检查函数做自动化断言：在 CI 里用
has_table_privilege()/pg_has_role()对目标环境跑权限断言，防止环境间漂移。
下一节
参考链接
- PostgreSQL 官方文档：GRANT
- PostgreSQL 官方文档：REVOKE
- PostgreSQL 官方文档：Privileges（DDL 章节）
- PostgreSQL 官方文档：REASSIGN OWNED
- PostgreSQL 官方文档：DROP OWNED
- PostgreSQL 官方文档：Role Membership
- PostgreSQL 官方文档：Access Control Lists
- PostgreSQL 官方文档：The Information Schema
- PostgreSQL 官方文档：System Information Functions
- PostgreSQL 官方文档：Predefined Roles