文章目录
- 一、权限概述
- 1.1 为什么权限管理至关重要？
- 1.2 安全检查清单
- 1.3 权限管理核心原则
- 二、PostgreSQL 权限体系全景图
- 1. 核心概念层级
- 2. 权限类型速查表
- 三、角色（Roles）管理：权限的载体
- 1. 创建角色（用户/组）
- 2. 角色继承与成员关系
- 四、数据库级权限
- 1. 授权连接与创建对象
- 2. 默认权限（关键！）
- 五、Schema 级权限
- 1. 基础权限
- 2. 安全建议
- 六、表级权限（最常用）
- 1. 基础 CRUD 授权
- 2. 列级权限（敏感数据保护）
- 3. 序列权限（自增ID）
- 七、函数与存储过程权限
- 八、权限查询与审计
- 1. 查看对象权限
- 2. 查看角色拥有的权限
- 3. 使用 psql 快捷命令
- 九、企业级权限管理流程
- 场景：为新微服务 `order-service` 配置权限
- 步骤 1：创建专用角色
- 步骤 2：授权数据库连接
- 步骤 3：授权 Schema 权限
- 步骤 4：授权表权限
- 步骤 5：设置默认权限（防遗漏）
- 步骤 6：验证权限
- 十、高级技巧与陷阱规避
- 1. 权限回收（REVOKE）
- 2. PUBLIC 角色陷阱
- 3. 权限与 Ownership
- 4. RLS（行级安全）补充
适用版本：PostgreSQL 12+（语法兼容 10+）
目标读者：DBA、后端开发、运维工程师
核心价值：从零构建企业级权限体系，避免“删库跑路”风险
一、权限概述
1.1 为什么权限管理至关重要？
PostgreSQL 的权限模型是其安全基石。错误的权限配置可能导致：
- 数据泄露（如普通用户读取
users.password） - 服务中断（如应用账号误删表）
- 权限蔓延（如开发人员拥有
SUPERUSER） - 合规失败（违反 GDPR、等保要求）
黄金法则：最小权限原则（Principle of Least Privilege）
1.2 安全检查清单
部署前必查：
- 所有应用账号无
SUPERUSER权限 - 敏感表（如
users）已限制列权限 publicschema 已禁用CREATE- 默认权限已配置
- 无多余
PUBLIC权限 - 密码符合复杂度要求（用
scram-sha-256） - 防火墙限制数据库端口访问 IP
1.3 权限管理核心原则
- 分层授权：Database → Schema → Table → Column
- 角色驱动：用非登录角色作为权限容器
- 默认权限：防止新对象权限遗漏
- 最小特权：只给必要权限，定期审计
- 自动化：用脚本管理权限，避免手工操作
记住：权限体系不是“一次配置，终身无忧”，而是需要持续维护的安全防线。
二、PostgreSQL 权限体系全景图
1. 核心概念层级
Cluster (实例) └── Database (数据库) ├── Schema (模式) │ ├── Table/View/Sequence (对象) │ └── Function (函数) └── Roles (角色) → 权限载体2. 权限类型速查表
| 对象类型 | 可授权权限 | 关键说明 |
|---|---|---|
| DATABASE | CONNECT,CREATE,TEMPORARY | CONNECT是基础 |
| SCHEMA | USAGE,CREATE | USAGE允许访问对象 |
| TABLE | SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER | 列级权限需单独授权 |
| COLUMN | SELECT,INSERT,UPDATE | 高敏感字段保护 |
| SEQUENCE | USAGE,SELECT,UPDATE | 自增ID控制 |
| FUNCTION | EXECUTE | 存储过程调用 |
| ALL PRIVILEGES | 所有权限（慎用！） | 不包含GRANT OPTION |
注意：
GRANT OPTION允许被授权者再授权（默认不包含）
三、角色（Roles）管理：权限的载体
PostgreSQL 中用户 = 角色 + LOGIN 属性
1. 创建角色（用户/组）
-- 创建登录用户（带密码）CREATEROLE app_userWITHLOGIN PASSWORD'StrongPass123!';-- 创建非登录角色（用于权限分组）CREATEROLE read_only;CREATEROLE data_writer;-- 创建超级用户（极度危险！仅限DBA）CREATEROLE dba_adminWITHSUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD'...';2. 角色继承与成员关系
-- 将用户加入权限组（自动继承权限）GRANTread_onlyTOapp_user;GRANTdata_writerTOapp_user;-- 查看角色成员\du app_user最佳实践：
- 用非登录角色作为权限容器（如
read_only）- 用户只加入必要角色，避免直接授权
四、数据库级权限
1. 授权连接与创建对象
-- 允许角色连接数据库GRANTCONNECTONDATABASEmyappTOapp_user;-- 允许在数据库中创建临时表GRANTTEMPORARYONDATABASEmyappTOapp_user;-- 允许创建新 schema（通常只给管理员）GRANTCREATEONDATABASEmyappTOdba_admin;2. 默认权限（关键！）
新创建的对象不会自动继承权限！需设置默认权限：
-- 设置未来在 public schema 创建的表自动授权 SELECT 给 read_onlyALTERDEFAULTPRIVILEGESFORROLE db_owner-- 对象创建者INSCHEMApublicGRANTSELECTONTABLESTOread_only;-- 设置序列 USAGE 权限ALTERDEFAULTPRIVILEGESFORROLE db_ownerINSCHEMApublicGRANTUSAGEONSEQUENCESTOdata_writer;🔥血泪教训：忘记设默认权限 → 新表无法被应用访问！
五、Schema 级权限
1. 基础权限
-- 允许使用 schema（必须先有此权限才能访问内部对象）GRANTUSAGEONSCHEMApublicTOapp_user;-- 允许在 schema 中创建对象（表/函数等）GRANTCREATEONSCHEMAanalyticsTOdata_scientist;2. 安全建议
- 不要滥用
publicschema：生产环境建议创建专用 schema（如app_schema） - 限制
public权限：REVOKECREATEONSCHEMApublicFROMPUBLIC;-- 禁止所有用户在 public 建表
六、表级权限（最常用）
1. 基础 CRUD 授权
-- 授权完整 CRUDGRANTSELECT,INSERT,UPDATE,DELETEONTABLEordersTOdata_writer;-- 只读权限GRANTSELECTONTABLEusersTOread_only;-- 授权所有表（慎用！）GRANTSELECTONALLTABLESINSCHEMApublicTOread_only;2. 列级权限（敏感数据保护）
-- 只允许读取非敏感列GRANTSELECT(id,name,email)ONTABLEusersTOapp_user;-- 禁止更新密码列GRANTUPDATE(name,email)ONTABLEusersTOapp_user;-- 注意：未授权的列将无法 UPDATE3. 序列权限（自增ID）
-- 允许获取下一个 ID（INSERT 必需）GRANTUSAGEONSEQUENCE orders_id_seqTOdata_writer;-- 允许查看当前值（调试用）GRANTSELECTONSEQUENCE orders_id_seqTOread_only;七、函数与存储过程权限
-- 授权执行函数GRANTEXECUTEONFUNCTIONcalculate_discount(numeric)TOapp_user;-- 授权所有函数GRANTEXECUTEONALLFUNCTIONSINSCHEMApublicTOapp_user;💡 函数权限独立于表权限！即使无表 SELECT 权限，仍可执行返回数据的函数。
八、权限查询与审计
1. 查看对象权限
-- 查看表权限SELECTgrantee,privilege_typeFROMinformation_schema.role_table_grantsWHEREtable_name='orders';-- 查看列权限SELECTgrantee,column_name,privilege_typeFROMinformation_schema.column_privilegesWHEREtable_name='users';2. 查看角色拥有的权限
-- 查看角色能访问哪些表SELECTtable_schema,table_name,privilege_typeFROMinformation_schema.table_privilegesWHEREgrantee='app_user';-- 查看默认权限SELECT*FROMpg_default_acl;3. 使用 psql 快捷命令
-- 列出数据库权限\l+ myapp -- 列出 schema 权限\dn+ public -- 列出表权限\dp orders九、企业级权限管理流程
场景：为新微服务order-service配置权限
步骤 1：创建专用角色
-- 权限组角色（非登录）CREATEROLE order_reader;CREATEROLE order_writer;-- 应用用户CREATEROLE order_appWITHLOGIN PASSWORD'...';GRANTorder_reader,order_writerTOorder_app;步骤 2：授权数据库连接
GRANTCONNECTONDATABASEecommerceTOorder_app;步骤 3：授权 Schema 权限
-- 假设使用专用 schemaCREATESCHEMAIFNOTEXISTSorder_schemaAUTHORIZATIONdb_owner;GRANTUSAGEONSCHEMAorder_schemaTOorder_reader;GRANTCREATEONSCHEMAorder_schemaTOdb_owner;-- 仅 DBA步骤 4：授权表权限
-- 只读表GRANTSELECTONTABLEorder_schema.productsTOorder_reader;-- 读写表GRANTSELECT,INSERT,UPDATEONTABLEorder_schema.ordersTOorder_writer;GRANTUSAGEONSEQUENCE order_schema.orders_id_seqTOorder_writer;-- 敏感表（禁止访问）-- 不授权 payment_cards 表步骤 5：设置默认权限（防遗漏）
ALTERDEFAULTPRIVILEGESFORROLE db_ownerINSCHEMAorder_schemaGRANTSELECTONTABLESTOorder_reader;ALTERDEFAULTPRIVILEGESFORROLE db_ownerINSCHEMAorder_schemaGRANTSELECT,INSERT,UPDATEONTABLESTOorder_writer;步骤 6：验证权限
-- 切换到应用用户测试SETROLE order_app;-- 应成功SELECT*FROMorder_schema.productsLIMIT1;INSERTINTOorder_schema.orders(...)VALUES(...);-- 应失败（权限拒绝）SELECT*FROMpayment_cards;-- 表不存在或权限拒绝十、高级技巧与陷阱规避
1. 权限回收（REVOKE）
-- 回收表权限REVOKEDELETEONTABLElogsFROMapp_user;-- 回收角色成员REVOKEdata_writerFROMapp_user;-- 回收默认权限ALTERDEFAULTPRIVILEGESFORROLE db_ownerINSCHEMApublicREVOKESELECTONTABLESFROMread_only;⚠️注意：
REVOKE不会级联影响已存在的对象！需手动处理。
2. PUBLIC 角色陷阱
PUBLIC是隐式包含所有角色的特殊角色- 默认权限可能通过
PUBLIC泄露：-- 检查危险的 PUBLIC 权限SELECT*FROMinformation_schema.role_table_grantsWHEREgrantee='PUBLIC'; - 加固建议：
REVOKEALLONDATABASEmyappFROMPUBLIC;REVOKEALLONSCHEMApublicFROMPUBLIC;
3. 权限与 Ownership
- 对象所有者（Owner）自动拥有所有权限，且不能被 REVOKE
- 转移所有权：
ALTERTABLEorders OWNERTOdb_owner;
4. RLS（行级安全）补充
当需要动态行过滤（如多租户），配合使用 RLS：
-- 启用行级安全ALTERTABLEtenant_dataENABLEROWLEVELSECURITY;-- 创建策略：用户只能看自己的数据CREATEPOLICY tenant_isolationONtenant_dataUSING(tenant_id=current_setting('app.current_tenant')::int);RLS 是权限体系的强力补充，但不替代基础权限。
