PostgreSQL PRIVILEGES（权限）

作者likuolei

下面是一份 PostgreSQL 权限（PRIVILEGES） 的 完整实战手册，涵盖 角色系统、权限类型、GRANT/REVOKE 语法、默认权限、行级安全（RLS）、权限审计、继承与最佳实践，适合开发、DBA、安全工程师、架构师使用。

一、PostgreSQL 权限核心概念

概念说明
角色（Role）用户或组的统称，USER 是 ROLE 的别名
权限（Privilege）对数据库对象（如表、序列、函数）的操作权限
所有者（Owner）创建对象的角色，默认拥有所有权限
继承（Inheritance）角色可继承其他角色的权限
默认权限（Default Privileges）新建对象自动授予的权限

二、权限类型速查表

对象权限关键字说明
数据库CONNECT, CREATE, TEMPORARY连接、建 schema、建临时表
SchemaCREATE, USAGE建对象、搜索路径
表SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER读写、截断、外键、触发器
列SELECT(col), UPDATE(col)列级权限
序列USAGE, SELECT, UPDATEnextval, currval, setval
函数EXECUTE执行
类型/域USAGE使用
大对象SELECT, UPDATE操作 BLOB

三、角色管理（CREATE / ALTER / DROP ROLE）

-- 创建角色（用户）
CREATE ROLE app_user LOGIN PASSWORD 'secure123';

-- 创建组角色
CREATE ROLE analysts NOLOGIN;

-- 修改角色
ALTER ROLE app_user WITH VALID UNTIL '2026-01-01';
ALTER ROLE app_user SET search_path = public, analytics;

-- 删除角色
DROP ROLE IF EXISTS temp_user;

常用选项

选项说明
LOGIN允许登录（等价于 CREATE USER）
NOLOGIN仅作为组
SUPERUSER超级用户（慎用）
CREATEDB / CREATEROLE建库/建角色
PASSWORD 'xxx'设置密码
VALID UNTIL过期时间

四、GRANT 授予权限

1. 数据库级

GRANT CONNECT, TEMP ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO dba_role;

2. Schema 级

GRANT USAGE ON SCHEMA analytics TO analyst;
GRANT CREATE ON SCHEMA staging TO etl_user;

3. 表级

-- 读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- 截断权限（危险）
GRANT TRUNCATE ON TABLE logs TO cleanup_role;

-- 外键引用
GRANT REFERENCES (user_id) ON TABLE users TO reporting_role;

4. 列级权限

GRANT SELECT (id, email), UPDATE (email) ON users TO support_role;

5. 序列权限（自增列）

GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO app_user;
-- 或者批量
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

6. 函数 / 过程

GRANT EXECUTE ON FUNCTION calculate_tax(NUMERIC) TO accountant;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA utils TO developer;

7. 批量授权（ALL IN SCHEMA）

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readonly_role;

五、REVOKE 回收权限

-- 回收表权限
REVOKE INSERT, UPDATE ON users FROM app_user;

-- 回收所有权限
REVOKE ALL ON DATABASE mydb FROM public;

-- 回收默认权限
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM temp_role;

六、默认权限（Default Privileges）

新建对象自动授予权限，避免逐个 GRANT

-- 未来创建的表，自动给 analyst 读权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO analyst;

-- 未来创建的序列，自动给 app_user 使用权
ALTER DEFAULT PRIVILEGES FOR ROLE dba IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

-- 限制只对当前角色创建的对象生效
ALTER DEFAULT PRIVILEGES FOR ROLE current_user IN SCHEMA analytics
    GRANT EXECUTE ON FUNCTIONS TO api_role;

七、角色继承（INHERIT）

-- 创建组
CREATE ROLE developers NOLOGIN;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO developers;

-- 用户继承组权限
CREATE ROLE alice LOGIN INHERIT;
GRANT developers TO alice;

-- 登录后自动拥有 developers 权限
SET ROLE alice;
SELECT * FROM users;  -- 成功

关闭自动继承（安全）

ALTER ROLE app_user NOINHERIT;
GRANT developers TO app_user;

-- 必须手动切换
SET ROLE developers;
SELECT * FROM users;  -- 才能访问

八、行级安全（RLS）—— 细粒度控制

-- 1. 启用 RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- 2. 创建策略
CREATE POLICY user_policy ON documents
    FOR ALL
    TO app_user
    USING (user_id = current_user_id())
    WITH CHECK (user_id = current_user_id());

-- 3. 授予表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO app_user;

-- 用户只能操作自己的数据

绕过 RLS（管理员）

SET row_security = OFF;  -- 需 SUPERUSER 或 BYPASSRLS

九、PUBLIC 角色（默认权限）

所有角色都继承 PUBLIC，默认有 CONNECT 和 TEMP 权限

-- 查看 PUBLIC 默认权限
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'PUBLIC';

-- 移除 PUBLIC 权限（安全加固）
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

十、权限审计与查询

1. 查看用户权限

-- 角色所属组
SELECT roleid::regrole, member::regrole
FROM pg_auth_members;

-- 表权限
SELECT table_name, privilege_type, grantee
FROM information_schema.table_privileges
WHERE grantee = 'app_user';

-- 列权限
SELECT * FROM information_schema.column_privileges WHERE grantee = 'support_role';

2. 查看有效权限（has_table_privilege）

SELECT has_table_privilege('app_user', 'users', 'SELECT');  -- true/false
SELECT has_sequence_privilege('app_user', 'users_id_seq', 'USAGE');

3. 导出权限脚本

-- 生成 GRANT 语句
SELECT 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO ' || grantee || ';'
FROM information_schema.table_privileges
WHERE grantee NOT IN ('postgres', 'PUBLIC');

十一、生产环境最佳实践

1. 最小权限原则

-- 应用用户：只读写业务表
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders, order_items TO app_user;
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;

-- 报表用户：只读
GRANT CONNECT ON DATABASE app_db TO report_user;
GRANT USAGE ON SCHEMA public TO report_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;

2. 使用组角色

CREATE ROLE web_app NOLOGIN;
CREATE ROLE api_service NOLOGIN;
CREATE ROLE etl_process NOLOGIN;

GRANT web_app TO user_web1, user_web2;

3. 安全加固脚本

-- 1. 移除 PUBLIC 权限
REVOKE ALL ON DATABASE app_db FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- 2. 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM PUBLIC;

-- 3. 启用 RLS（敏感表）
ALTER TABLE salary ENABLE ROW LEVEL SECURITY;

十二、常见陷阱与避坑

问题原因解决
permission denied for sequence没授 USAGEGRANT USAGE ON SEQUENCE ...
新表无权限没设默认权限ALTER DEFAULT PRIVILEGES
视图无法访问底层表无权限视图所有者需有权限
RLS 不生效策略未启用ENABLE ROW LEVEL SECURITY
PUBLIC 泄露默认权限REVOKE FROM PUBLIC

十三、完整示例：生产级权限体系

-- 1. 创建角色
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
CREATE ROLE admin_role NOLOGIN;

-- 2. 授予权限
GRANT CONNECT ON DATABASE ecommerce TO app_readonly, app_readwrite, admin_role;
GRANT USAGE ON SCHEMA public TO app_readonly, app_readwrite, admin_role;

-- 读权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;

-- 读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;

-- 管理员
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO admin_role;

-- 3. 默认权限（未来对象）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

-- 4. 创建用户并分配
CREATE ROLE web_app_user LOGIN PASSWORD 'xxx' INHERIT;
GRANT app_readwrite TO web_app_user;

CREATE ROLE report_user LOGIN PASSWORD 'yyy' INHERIT;
GRANT app_readonly TO report_user;

十四、速查表

命令用途
GRANT SELECT ON table TO role读权限
GRANT USAGE ON SEQUENCE s TO role自增列使用
GRANT ALL ON SCHEMA s TO roleSchema 完全权限
REVOKE ALL FROM PUBLIC移除公共权限
ALTER DEFAULT PRIVILEGES ... GRANT ...默认权限
has_table_privilege(user, table, 'SELECT')检查权限
ALTER TABLE t ENABLE ROW LEVEL SECURITY启用 RLS

十五、权限决策树

graph TD
    A[新用户需要访问?] -->|是| B{读还是写?}
    B -->|读| C[用 readonly 组角色]
    B -->|写| D[用 readwrite 组角色]
    C --> E[GRANT SELECT + USAGE seq]
    D --> F[GRANT CRUD + USAGE/UPDATE seq]
    A -->|否| G[无需操作]
    style E fill:#f9f,stroke:#333
    style F fill:#bbf,stroke:#333

十六、常见面试题

问题答案
PUBLIC 角色默认有什么权限？CONNECT 和 TEMP
如何防止新表被所有人访问？REVOKE ALL FROM PUBLIC + ALTER DEFAULT PRIVILEGES
序列权限叫什么？USAGE 和 SELECT
视图需要什么权限？底层表权限 + 视图 USAGE
如何实现“只能看自己数据”？RLS + USING (user_id = current_user)

需要我提供一个完整的 “多租户 + RLS + 权限模板 + 自动化审计” 的生产级项目吗？

类似文章

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注