PostgreSQL数据库的用户权限管理与安全审计

一、为什么要管PostgreSQL的用户权限？

不管是自己做小项目还是公司搞大型系统，只要用PostgreSQL存数据，就绕不开“谁能碰什么数据”这个问题。举个最简单的例子：公司财务的工资表，肯定不能让刚入职的实习生随便看；再比如用户的手机号、地址这些隐私信息，要是被无关的人乱改，轻则出bug，重则吃合规罚单。

权限管理说白了就是给不同的人“分钥匙”：有的钥匙能开所有锁（比如管理员），有的只能开特定的几个锁（比如只能查自己负责的表），有的甚至只能看锁里的东西不能改（比如只能读数据不能删）。而安全审计就是“查监控”：谁什么时候用钥匙开了什么锁，干了啥坏事，出了问题能追根溯源。这俩加起来，就是给数据库装了一道安全门。

二、PostgreSQL权限管理的基础操作

要管权限，得先从最基础的“用户”和“角色”说起。很多人会把这俩搞混，其实可以这么理解：用户就是能直接登录数据库的“人”，角色就是一堆权限的“集合”，相当于把权限打包成一个模板，给多个用户用。

2.1 怎么创建用户和角色？

先明确技术栈：所有示例统一使用PostgreSQL 14（兼容12及以上版本），所有命令在psql客户端执行。

创建用户的命令很简单，比如要创建一个叫“test_user”的普通用户，只需要执行：

-- 创建普通用户，设置登录密码为'test123'（实际用的时候要换复杂密码）
CREATE USER test_user WITH LOGIN PASSWORD 'test123';

要是要创建一个打包权限的角色，就把USER换成ROLE：

-- 创建一个叫'read_role'的角色，用于给需要只读权限的人用
CREATE ROLE read_role;

这里要注意，默认创建的用户是没有任何权限的，连最基础的连接数据库都不行，得手动给权限。

2.2 怎么给权限？

权限分很多种，最常用的是对表的增删改查（SELECT、INSERT、UPDATE、DELETE），还有对数据库的连接、创建表等。比如要给刚才创建的read_role角色，赋予对“employee”表的只读权限：

-- 给read_role角色赋予employee表的SELECT权限（只能查不能改）
GRANT SELECT ON employee TO read_role;

要是要给用户直接加权限，就把TO后面的角色换成用户名：

-- 给test_user用户赋予employee表的INSERT权限（能新增数据）
GRANT INSERT ON employee TO test_user;

如果要给一个用户加多个权限，或者给多个表加权限，还可以批量操作，比如给test_user加employee表的增删改查：

-- 批量赋予多个权限，用逗号分隔
GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO test_user;

要是权限给错了怎么办？可以用REVOKE命令收回来，比如收回test_user的DELETE权限：

-- 收回test_user对employee表的DELETE权限
REVOKE DELETE ON employee FROM test_user;

2.3 角色的继承：打包权限的进阶用法

刚才说角色是权限的集合，那如果要给多个用户加一样的权限，不用一个个给，直接把角色授予给用户就行。比如刚才的read_role已经有了employee表的只读权限，现在要给test_user加这个权限，就可以：

-- 把read_role角色授予给test_user，test_user自动获得角色的所有权限
GRANT read_role TO test_user;

反过来，要是要收回角色，就用REVOKE：

-- 收回test_user的read_role角色
REVOKE read_role FROM test_user;

这里有个小细节：默认创建的角色是不能登录数据库的，要是想让某个角色能直接登录，创建的时候要加LOGIN参数，比如：

-- 创建能登录的角色，相当于一个特殊的用户
CREATE ROLE login_role WITH LOGIN PASSWORD 'login123';

三、权限管理的高级玩法：控制粒度和继承

基础操作能应付大部分场景，但要是遇到更复杂的需求，比如只让用户看自己负责的数据，或者不让普通用户碰敏感字段，就得用更细的权限控制。

3.1 行级权限：只让用户看自己的数据

行级权限就是控制用户能看到表中的哪些行，比如用户表“user_info”里有所有用户的信息，我们要让每个用户只能看自己的信息，不能看别人的，就可以这么做：

-- 给test_user赋予user_info表的SELECT权限，加条件：只能看自己id等于test_user的行
GRANT SELECT ON user_info TO test_user WITH CHECK (id = current_user);

这里的current_user是PostgreSQL的内置函数，会返回当前登录的用户名，所以这个命令的意思就是：test_user只能看user_info表中id等于自己用户名的行。

3.2 列级权限：只让用户看指定字段

要是表中有敏感字段，比如“user_info”表中的“phone”和“address”，不想让普通用户看到，就可以给用户只赋予指定字段的权限：

-- 给test_user赋予user_info表的id、name、email字段的SELECT权限，看不到phone和address
GRANT SELECT (id, name, email) ON user_info TO test_user;

要是用户之后需要看phone字段，就单独加这个字段的权限：

-- 给test_user加phone字段的SELECT权限
GRANT SELECT (phone) ON user_info TO test_user;

3.3 继承权限的控制

默认情况下，用户继承角色的所有权限，但有时候我们不想让用户继承所有权限，或者想让某个角色继承另一个角色的权限，就可以用INHERIT参数。比如创建一个不能继承权限的用户：

-- 创建用户no_inherit_user，不能继承角色的权限
CREATE USER no_inherit_user WITH LOGIN PASSWORD 'noinherit123' NOINHERIT;

要是想让这个用户临时继承某个角色的权限，可以用SET ROLE命令：

-- 切换成read_role角色，临时获得该角色的权限
SET ROLE read_role;
-- 用完之后切回自己的身份
RESET ROLE;

四、安全审计：怎么查谁碰了数据库？

权限管得再好，也得有人监督，不然有人偷偷改了数据或者删了表，都不知道是谁干的。安全审计就是通过记录数据库的操作，来追踪问题。

4.1 基础审计：开启日志记录

PostgreSQL默认会记录一些操作，但要做详细的审计，得先改配置文件postgresql.conf，开启日志功能。比如要记录所有用户的登录、登出、DDL操作（比如建表、删表）和DML操作（比如增删改查），可以这么配置：

# 开启日志收集
logging_collector = on
# 日志文件的存储目录，默认是pg_log目录
log_directory = 'pg_log'
# 日志文件的前缀，比如postgresql-2024-05-20.log
log_filename = 'postgresql-%Y-%m-%d.log'
# 日志文件的保留天数，超过这个天数会自动删除
log_rotation_age = 7d
# 日志文件的大小，超过这个大小会自动切割
log_rotation_size = 100MB
# 记录所有的连接请求和断开连接
log_connections = on
log_disconnections = on
# 记录所有的DDL操作（CREATE、DROP、ALTER等）
log_statement = 'ddl'
# 记录所有的DML操作（INSERT、UPDATE、DELETE等），注意生产环境不要随便开，会增加性能开销
# log_statement = 'all'

改完配置之后，要重启PostgreSQL服务才能生效：

# 重启PostgreSQL服务，不同系统命令不一样，这里以CentOS为例
systemctl restart postgresql-14

4.2 进阶审计：用扩展插件pgAudit

要是觉得基础日志不够细，或者不想改配置文件，可以用PostgreSQL的扩展插件pgAudit，它能更灵活地记录审计信息，还能把审计信息存到专门的表中，方便查询。

首先要安装pgAudit扩展，不同系统的安装方式不一样，比如CentOS可以用yum安装：

# 安装pgAudit扩展，版本要和PostgreSQL对应
yum install postgresql14-contrib

然后在数据库中创建扩展：

-- 在当前数据库中创建pgAudit扩展
CREATE EXTENSION pgaudit;

创建完扩展之后，要改postgresql.conf配置文件，开启pgAudit的功能：

# 开启pgAudit的会话日志
shared_preload_libraries = 'pgaudit'
# 记录DDL操作
pgaudit.log = 'ddl'
# 记录DML操作，包括SELECT、INSERT、UPDATE、DELETE
pgaudit.log = 'dml'
# 记录登录登出操作
pgaudit.log = 'session'

改完配置重启服务之后，pgAudit就会把审计信息存到pg_log目录的日志文件中，要是想把审计信息存到表中，可以用pgAudit的自定义函数，比如创建一个表来存审计信息：

-- 创建审计表，用来存储pgAudit的日志
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    username TEXT, -- 操作的用户名
    operation TEXT, -- 操作类型，比如SELECT、INSERT、DROP
    table_name TEXT, -- 操作的表名
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 操作时间
);

然后用pgAudit的函数把日志导入到这个表中，方便查询，比如要查谁在昨天删了employee表：

-- 查询昨天的审计日志，找出DROP操作
SELECT * FROM audit_log WHERE operation = 'DROP' AND table_name = 'employee' AND operation_time >= CURRENT_DATE - INTERVAL '1 day';

4.3 审计的日常使用：怎么查问题？

日常运维中，要是发现数据不对，或者有人非法操作，就可以通过审计日志来查。比如要查test_user昨天的所有操作，可以这么查：

-- 查test_user昨天的所有操作
SELECT * FROM audit_log WHERE username = 'test_user' AND operation_time >= CURRENT_DATE - INTERVAL '1 day';

要是要查所有的删表操作，可以：

-- 查所有的DROP TABLE操作
SELECT * FROM audit_log WHERE operation = 'DROP' AND table_name LIKE '%TABLE%';

五、权限管理和安全审计的应用场景、优缺点及注意事项

5.1 应用场景

权限管理的应用场景很广，比如多用户协作的项目，不同的开发、测试、运维人员需要不同的权限；再比如电商系统，用户只能看自己的订单，客服只能看自己负责的用户信息，财务只能看财务数据；还有合规场景，比如等保2.0要求数据库必须有细粒度的权限控制和审计功能，不然过不了合规检查。

安全审计的应用场景主要是问题排查，比如数据被篡改了，要查是谁改的；再比如有人非法登录数据库，要查登录的时间和IP；还有合规审计，监管部门要查数据库的操作记录，就得有审计日志。

5.2 技术优缺点

权限管理的优点是能有效控制数据的访问范围，减少数据泄露和篡改的风险，还能简化多用户协作的流程；缺点是如果权限配置太细，会增加运维的复杂度，比如一个项目有几十个表，每个表有几个字段，给不同的用户加权限会很麻烦。

安全审计的优点是能追踪问题，方便排查故障和追责，还能满足合规要求；缺点是会增加数据库的性能开销，比如开启所有操作的日志，会占用大量的磁盘空间，还会降低数据库的读写速度。

5.3 注意事项

权限管理的注意事项：第一，最小权限原则，就是给用户的权限只够完成工作，不要给多余的权限，比如开发人员不需要删表的权限，就不要给；第二，定期清理不用的用户和角色，比如项目结束了，要及时删除测试用户，避免被滥用；第三，密码要复杂，不要用简单的密码，比如123456，最好用字母加数字加符号的组合，还要定期更换密码。

安全审计的注意事项：第一，不要随便开启所有操作的日志，生产环境只需要开启必要的日志，比如登录登出、DDL操作、敏感表的DML操作，避免占用太多资源；第二，审计日志要定期备份，不要存在数据库所在的服务器上，要是服务器被入侵，日志也会被删掉；第三，审计日志要定期检查，不要存了日志却不看，不然出了问题也发现不了。

六、文章总结

PostgreSQL的权限管理和安全审计是数据库安全的两道重要防线，权限管理是“事前预防”，通过给不同的用户分配不同的权限，控制数据的访问范围；安全审计是“事后追溯”，通过记录数据库的操作，追踪问题和追责。

在实际使用中，要遵循最小权限原则，合理配置权限，不要给多余的权限；要根据实际需求开启审计功能，不要随便开启所有操作的日志；还要定期清理不用的用户和角色，定期备份审计日志，定期检查审计日志。只有把这两部分结合起来，才能有效保障数据库的安全，避免数据泄露和篡改的风险。