PostgreSQL 入门学习教程，从入门到精通，PostgreSQL 16 用户与权限管理详解 —— 语法、案例与实战(15）

PostgreSQL 16 用户与权限管理详解 —— 语法、案例与实战

✅ 一、组角色管理（Group Role Management）

PostgreSQL 中 “用户” 和 “组” 统一为“角色（Role）”，通过 LOGIN 属性区分是否可登录。

1.1 创建组角色（无登录权限）

— 语法：
CREATE ROLE role_name [WITH] option [...];

— 常用选项：
— NOLOGIN（默认）→ 不能登录，仅用于权限分组
— NOSUPERUSER, NOCREATEDB, NOCREATEROLE → 默认安全设置

— ✅ 案例：创建只读组、数据分析师组、管理员组
CREATE ROLE readonly_group NOLOGIN;
CREATE ROLE analyst_group NOLOGIN;
CREATE ROLE admin_group NOLOGIN;

— ✅ 验证角色创建
\\du readonly_group
— 输出：Role name: readonly_group | Attributes: Cannot login

⚠️ 注意：

• NOLOGIN 是默认行为，可省略
• 组角色本身不能登录，只能被其他角色继承

1.2 查看和修改组角色

— ✅ 查看所有角色
\\du

— 或使用 SQL 查询
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
WHERE rolname LIKE '%group%';

— ✅ 修改组角色属性（如允许登录 —— 通常不推荐）
ALTER ROLE readonly_group WITH LOGIN; — ❌ 不推荐！组角色应保持 NOLOGIN

— ✅ 更安全的做法：修改描述或重命名
ALTER ROLE readonly_group RENAME TO read_only_analyst;
ALTER ROLE read_only_analyst SET client_encoding TO 'UTF8';

— ✅ 重置为组角色（禁止登录）
ALTER ROLE read_only_analyst WITH NOLOGIN;

1.3 删除组角色

— ✅ 删除空组角色（无成员、无权限）
DROP ROLE IF EXISTS read_only_analyst;

— ❌ 如果组角色仍有成员或权限，需先处理
— ERROR: role "admin_group" cannot be dropped because some objects depend on it

— ✅ 安全删除步骤：
— 1. 移除成员
REASSIGN OWNED BY admin_group TO postgres; — 转移对象所有权
— 2. 撤销所有权限（见后文）
— 3. 删除角色
DROP ROLE admin_group;

✅ 最佳实践：

• 删除前用 \\du+ 查看角色详情
• 使用 REASSIGN OWNED 或 DROP OWNED 清理依赖

✅ 二、账户管理（可登录角色）

2.1 创建用户（登录角色）

— 语法：
CREATE ROLE user_name [WITH] LOGIN [PASSWORD 'password'] [option ...];

— 或使用 CREATE USER（等价于 CREATE ROLE … LOGIN）
CREATE USER user_name [WITH] [PASSWORD 'password'] [option ...];

— ✅ 案例：创建不同权限用户
CREATE USER alice WITH PASSWORD 'SecurePass123!'
VALID UNTIL '2026-01-01'; — 设置密码过期

CREATE USER bob WITH PASSWORD 'BobPass456!'
CONNECTION LIMIT 5; — 限制最大连接数

CREATE USER charlie WITH PASSWORD 'Charlie789!'
INHERIT; — 默认继承组权限（推荐）

— ✅ 验证用户创建
\\du alice
— 输出：Role name: alice | Attributes: Password valid until 2026-01-01 00:00:00+08, Can login

⚠️ 安全建议：

• 密码应复杂且定期更换
• 设置 VALID UNTIL 避免永久有效密码
• 限制 CONNECTION LIMIT 防止资源滥用

2.2 删除用户

— ✅ 删除用户（无对象依赖）
DROP USER IF EXISTS charlie;

— ❌ 如果用户拥有数据库对象，需先处理
— ERROR: role "alice" cannot be dropped because some objects depend on it

— ✅ 安全删除步骤：
— 方法1：转移对象所有权
REASSIGN OWNED BY alice TO postgres;

— 方法2：删除用户拥有的所有对象
DROP OWNED BY alice;

— 然后删除用户
DROP USER alice;

✅ REASSIGN OWNED vs DROP OWNED：

• REASSIGN OWNED：将对象转移给其他角色（保留数据）
• DROP OWNED：删除该角色拥有的所有对象（慎用！）

2.3 修改用户密码

— ✅ 方法1：ALTER ROLE
ALTER ROLE bob WITH PASSWORD 'NewStrongPassword!';

— ✅ 方法2：\\password 命令（交互式，更安全）
\\password bob
— 输入新密码（不显示明文）

— ✅ 强制下次登录修改密码
ALTER ROLE bob VALID UNTIL 'tomorrow'; — 明天过期

— ✅ 取消密码有效期
ALTER ROLE bob VALID UNTIL 'infinity';

✅ 密码策略建议：

• 使用 pgcrypto 扩展强制复杂度
• 定期审计密码有效期
• 生产环境避免明文密码出现在SQL文件中

✅ 三、角色权限管理（授权与回收）

3.1 对组角色授权（GRANT）

— ✅ 语法：
GRANT { privilege [, ...] | ALL [PRIVILEGES] }
ON { TABLE table_name | DATABASE db_name | SCHEMA schema_name | ... }
TO role_name [, ...] [WITH GRANT OPTION];

— ✅ 案例1：授予组角色表权限
GRANT SELECT ON TABLE employees TO readonly_group; — 只读
GRANT SELECT, INSERT, UPDATE ON TABLE sales TO analyst_group; — 读写
GRANT ALL PRIVILEGES ON TABLE config TO admin_group; — 全权限

— ✅ 案例2：授予数据库连接权限
GRANT CONNECT ON DATABASE company_db TO readonly_group;

— ✅ 案例3：授予模式（Schema）使用权限
GRANT USAGE ON SCHEMA public TO readonly_group;
GRANT CREATE ON SCHEMA public TO admin_group; — 允许创建对象

— ✅ 验证权限
\\dp employees
— 输出：Access privileges: readonly_group=r/postgres
— r = SELECT, w = UPDATE, a = INSERT, d = DELETE, D = TRUNCATE, x = REFERENCES, t = TRIGGER

📌 权限缩写含义：

• r — SELECT
• w — UPDATE
• a — INSERT
• d — DELETE
• D — TRUNCATE
• x — REFERENCES
• t — TRIGGER
• X — EXECUTE（函数）
• U — USAGE（模式/序列）
• C — CREATE（模式/数据库）

3.2 对用户授权（通过继承组角色）

— ✅ 将用户加入组角色（继承权限）
GRANT readonly_group TO alice; — Alice 继承只读权限
GRANT analyst_group TO bob; — Bob 继承分析师权限

— ✅ 验证成员关系
\\du alice
— 输出：Member of: readonly_group

— ✅ 直接授予用户特定权限（不推荐，应通过组管理）
GRANT DELETE ON TABLE logs TO bob; — 临时授权

✅ 最佳实践：

• 优先使用 组角色授权，用户通过继承获得权限
• 避免直接对用户授权，便于集中管理

3.3 收回组角色权限（REVOKE）

— ✅ 语法：
REVOKE [GRANT OPTION FOR] { privilege [, ...] | ALL [PRIVILEGES] }
ON { TABLE table_name | ... }
FROM role_name [, ...] [CASCADE | RESTRICT];

— ✅ 案例1：收回表权限
REVOKE INSERT, UPDATE ON TABLE sales FROM analyst_group;

— ✅ 案例2：收回所有权限
REVOKE ALL PRIVILEGES ON TABLE config FROM admin_group;

— ✅ 案例3：收回数据库连接权限
REVOKE CONNECT ON DATABASE company_db FROM readonly_group;

— ✅ 验证权限是否收回
\\dp sales
— analyst_group 权限应减少

3.4 收回用户权限

— ✅ 方法1：从组角色收回（影响所有成员）
REVOKE SELECT ON TABLE employees FROM readonly_group; — Alice 也失去权限

— ✅ 方法2：直接收回用户权限（如果曾直接授权）
REVOKE DELETE ON TABLE logs FROM bob;

— ✅ 方法3：将用户移出组角色
REVOKE readonly_group FROM alice; — Alice 不再继承权限

— ✅ 验证
\\du alice
— Member of: (空)

⚠️ 注意：

• REVOKE 不会级联删除用户，除非使用 CASCADE
• 权限收回后，用户需重新连接才能生效（缓存机制）

✅ 四、数据库权限管理

4.1 修改数据库的拥有者

— ✅ 语法：
ALTER DATABASE database_name OWNER TO new_owner;

— ✅ 案例：将数据库转移给管理员角色
ALTER DATABASE company_db OWNER TO admin_group;

— ❌ 错误：组角色不能拥有数据库（除非有 CREATEDB 权限）
— ERROR: must be member of role "admin_group"

— ✅ 正确做法：转移给可登录角色
CREATE USER db_owner WITH PASSWORD 'OwnerPass!';
ALTER DATABASE company_db OWNER TO db_owner;

— ✅ 验证
SELECT datname, pg_get_userbyid(datdba) as owner
FROM pg_database
WHERE datname = 'company_db';

✅ 建议：

• 数据库拥有者应为专用管理账户，非个人用户
• 拥有者自动拥有该数据库所有对象的全部权限

4.2 增加用户的数据表权限（精细化授权）

— ✅ 场景：为特定用户授予特定列的权限
GRANT SELECT (name, email) ON TABLE employees TO alice; — 只能查姓名和邮箱
GRANT UPDATE (salary) ON TABLE employees TO bob; — 只能更新薪资

— ✅ 验证列级权限
SELECT grantee, privilege_type, column_name
FROM information_schema.column_privileges
WHERE table_name = 'employees' AND grantee IN ('alice', 'bob');

— ✅ 授予序列权限（用于自增ID）
GRANT USAGE, SELECT ON SEQUENCE employees_id_seq TO analyst_group;

— ✅ 授予函数执行权限
GRANT EXECUTE ON FUNCTION calculate_bonus(INT) TO analyst_group;

✅ 列级权限适用场景：

• 敏感数据（薪资、身份证）限制访问
• 不同部门访问不同字段

✅ 五、PostgreSQL 16 新特性

5.1 新增3个默认角色

PostgreSQL 16 新增以下预定义角色，简化权限管理：

— ✅ 案例：创建监控用户
CREATE USER monitor_user WITH PASSWORD 'MonitorPass!';
GRANT pg_monitor TO monitor_user; — 授予监控权限

— ✅ 创建只读全库用户
CREATE USER auditor WITH PASSWORD 'AuditPass!';
GRANT pg_read_all_data TO auditor;

— ✅ 验证
\\du auditor
— Member of: pg_read_all_data

✅ 优势：

• 无需逐表授权，一键赋予全库权限
• 角色权限由系统维护，安全可靠

5.2 下放4个系统函数权限

PostgreSQL 16 将以下函数权限从超级用户下放给 pg_monitor 角色：

— 1. pg_ls_waldir() —— 查看WAL文件
— 2. pg_ls_archive_statusdir() —— 查看归档状态
— 3. pg_ls_logdir() —— 查看日志目录
— 4. pg_read_file() —— 读取服务器文件（受限路径）

— ✅ 案例：监控用户查看WAL文件
GRANT pg_monitor TO monitor_user;

— 以 monitor_user 身份执行
SELECT * FROM pg_ls_waldir() LIMIT 5;
— 成功！无需超级用户权限

✅ 安全改进：

• 减少超级用户使用频率
• 精细化权限控制，符合最小权限原则

✅ 六、综合实战案例

🎯 案例1：构建企业级权限体系

— 步骤1：创建组角色
CREATE ROLE hr_dept NOLOGIN; — 人力资源部
CREATE ROLE finance_dept NOLOGIN; — 财务部
CREATE ROLE it_admin NOLOGIN; — IT管理员

— 步骤2：创建用户
CREATE USER hr_manager WITH PASSWORD 'HRPass123!' INHERIT;
CREATE USER finance_analyst WITH PASSWORD 'Finance456!' INHERIT;
CREATE USER db_admin WITH PASSWORD 'Admin789!' SUPERUSER; — 仅DBA用

— 步骤3：用户加入组
GRANT hr_dept TO hr_manager;
GRANT finance_dept TO finance_analyst;
GRANT it_admin TO db_admin;

— 步骤4：授予权限
— HR部：可读写员工表，只读薪资表
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employees TO hr_dept;
GRANT SELECT ON TABLE salaries TO hr_dept;

— 财务部：可读写薪资表，只读员工表
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE salaries TO finance_dept;
GRANT SELECT ON TABLE employees TO finance_dept;

— IT管理员：全库权限
GRANT pg_read_all_data, pg_write_all_data TO it_admin;

— 步骤5：验证权限
— 以 hr_manager 身份连接，测试：
SELECT * FROM employees; — ✅ 成功
SELECT * FROM salaries; — ✅ 成功（只读）
UPDATE salaries SET amount = 5000 WHERE id = 1; — ❌ 失败！无UPDATE权限

— 以 finance_analyst 身份：
UPDATE salaries SET amount = 5000 WHERE id = 1; — ✅ 成功
DELETE FROM employees WHERE id = 1; — ❌ 失败！无DELETE权限

🎯 案例2：安全审计用户（只读 + 不能删数据）

— 创建审计组
CREATE ROLE auditor_group NOLOGIN;

— 授予只读权限（所有表）
GRANT pg_read_all_data TO auditor_group;

— 禁止TRUNCATE和DELETE（即使有权限也不行）
— 方法：创建行级安全策略（RLS）
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

— 创建策略：禁止删除和清空
CREATE POLICY no_delete_policy ON employees
FOR DELETE USING (false); — 永远不满足条件

CREATE POLICY no_truncate_policy ON employees
FOR TRUNCATE USING (false);

— 创建审计用户
CREATE USER auditor WITH PASSWORD 'AuditSecure!';
GRANT auditor_group TO auditor;

— ✅ 测试：
— auditor 用户执行：
SELECT * FROM employees; — ✅ 成功
DELETE FROM employees; — ❌ ERROR: permission denied for relation employees
TRUNCATE employees; — ❌ ERROR: permission denied for relation employees

✅ RLS 优势：

• 在权限基础上增加行级控制
• 即使用户有 DELETE 权限，策略也可阻止

🎯 案例3：自动化权限脚本（每月重置实习生权限）

— 创建存储过程：重置实习生权限
CREATE OR REPLACE PROCEDURE reset_intern_permissions()
LANGUAGE plpgsql
AS $$
DECLARE
intern_user TEXT;
BEGIN
RAISE NOTICE '开始重置实习生权限…';

— 遍历所有实习生用户（命名约定 intern_ 开头）
FOR intern_user IN
SELECT rolname FROM pg_roles
WHERE rolname LIKE 'intern_%' AND rolcanlogin
LOOP
BEGIN
— 1. 撤销所有权限
EXECUTE 'REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM ' || quote_ident(intern_user);
EXECUTE 'REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM ' || quote_ident(intern_user);
EXECUTE 'REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM ' || quote_ident(intern_user);

— 2. 重新授予只读权限
EXECUTE 'GRANT pg_read_all_data TO ' || quote_ident(intern_user);

— 3. 重置密码（强制修改）
EXECUTE 'ALTER USER ' || quote_ident(intern_user) || ' VALID UNTIL ''tomorrow''';

RAISE NOTICE '已重置用户: %', intern_user;

EXCEPTION WHEN OTHERS THEN
RAISE WARNING '重置用户 % 失败: %', intern_user, SQLERRM;
END;
END LOOP;

RAISE NOTICE '权限重置完成！';
END;
$$;

— ✅ 创建实习生用户
CREATE USER intern_john WITH PASSWORD 'JohnPass!';
CREATE USER intern_mary WITH PASSWORD 'MaryPass!';

— ✅ 执行重置
CALL reset_intern_permissions();

— ✅ 验证
\\du intern_john
— Member of: pg_read_all_data, Password valid until tomorrow

✅ 七、常见问题及解答（FAQ）

❓ 疑问1：如何撤销用户对数据表的操作权限？

分两种情况：

情况1：用户通过组角色继承权限

— 撤销组角色的权限（影响所有成员）
REVOKE UPDATE, DELETE ON TABLE target_table FROM group_role;

情况2：用户被直接授权

— 直接撤销用户权限
REVOKE ALL PRIVILEGES ON TABLE target_table FROM user_name;

情况3：想保留组权限，但移除特定用户

— 将用户移出组角色
REVOKE group_role FROM user_name;

✅ 推荐做法：
优先使用 组角色授权，撤销时修改组权限，便于统一管理！

❓ 疑问2：组角色和登录角色之间的区别是什么？

✅ 核心区别：

• 组角色 = 权限容器（不能登录）
• 登录角色 = 实际用户（可登录）
• 用户通过 GRANT group_role TO user 继承组权限

📌 PostgreSQL 无“组”概念，组角色本质是 NOLOGIN 的角色！

❓ 疑问3：为什么要谨慎使用超级用户权限？

超级用户（SUPERUSER）拥有：

• 访问所有数据库、所有表
• 绕过所有权限检查
• 执行任何操作（包括 DROP DATABASE、修改系统表）
• 读取服务器文件（pg_read_file）
• 终止其他用户会话

⚠️ 风险：

✅ 最佳实践：

• 禁止应用使用超级用户连接
• DBA日常操作使用普通角色，仅必要时切换
• 使用 pg_monitor、pg_read_all_data 等预定义角色替代
• 启用日志审计：log_statement = 'all', log_connections = on

— ✅ 创建受限管理员（非超级用户）
CREATE USER limited_dba WITH PASSWORD 'DBAPass!';
GRANT pg_read_all_data, pg_write_all_data, pg_monitor TO limited_dba;

— ❌ 避免：
CREATE USER app_user SUPERUSER; — 绝对禁止！

✅ 八、权限管理最佳实践总结

🚀 牢记：
权限管理 = 安全基石！
合理的角色设计 + 精细化的权限控制 + 定期审计 = 健壮的数据库安全体系！

📚 建议结合 pg_hba.conf（主机认证）和 ALTER ROLE … SET（会话参数）实现全方位访问控制！