Debian PostgreSQL权限管理与审计指南

一、权限管理

1. 角色与用户管理

PostgreSQL通过**角色（Roles）**实现权限管理，角色可视为用户的容器，支持权限继承。在Debian系统中，需先切换至postgres用户（PostgreSQL默认超级用户）再操作：

创建角色/用户：使用CREATE ROLE创建角色（可指定登录权限、密码等），CREATE USER是CREATE ROLE WITH LOGIN的别名（专门用于创建可登录用户）。
```
CREATE ROLE readonly_role WITH LOGIN PASSWORD 'SecurePass123' NOSUPERUSER NOCREATEDB;
CREATE USER app_user WITH PASSWORD 'AppPass456' NOSUPERUSER NOCREATEDB;
```
删除角色/用户：使用DROP ROLE删除角色及其所有权限（需确保无对象依赖）。
```
DROP ROLE IF EXISTS readonly_role;
```

2. 权限分配

权限需按数据库对象层级（数据库、模式、表、序列等）分配，遵循最小权限原则：

数据库级别：授予用户对特定数据库的连接或所有权。

GRANT CONNECT ON DATABASE mydb TO app_user;  -- 允许连接数据库
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin_user;  -- 授予数据库所有权限

模式级别：控制用户对模式内对象的访问权限。

GRANT USAGE ON SCHEMA public TO readonly_role;  -- 允许使用public模式

表级别：授予表级别的增删改查权限。

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

序列级别：针对序列（如自增ID）授予权限。

GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO app_user;  -- 允许读取和更新序列

3. 默认权限设置

通过ALTER DEFAULT PRIVILEGES设置未来创建对象的默认权限，避免重复配置：

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly_role;  -- 未来创建的表默认授予readonly_role只读权限

4. 权限维护

修改密码：使用ALTER USER修改用户密码（需定期更换）。
```
ALTER USER app_user WITH PASSWORD 'NewSecurePass789';
```

权限回收：使用REVOKE撤销已授予的权限。

REVOKE INSERT ON ALL TABLES IN SCHEMA public FROM app_user;  -- 撤销app_user的插入权限

角色关联：将用户添加至角色，实现权限复用。

GRANT readonly_role TO app_user;  -- app_user继承readonly_role的权限

5. 权限查看

查看所有角色及权限：使用\du命令（psql终端）。
```
\du
```

查看表级权限明细：查询information_schema.table_privileges视图。

SELECT table_catalog, table_schema, table_name, privilege_type 
FROM information_schema.table_privileges 
WHERE grantee = 'app_user';

二、安全审计

1. 日志配置（基础审计）

通过修改PostgreSQL配置文件postgresql.conf开启日志记录，监控连接、查询等活动：

# 编辑配置文件（版本号需替换为实际安装版本，如14）
sudo nano /etc/postgresql/14/main/postgresql.conf

调整以下参数：

# 记录所有SQL语句（生产环境建议按需设置，如只记录SELECT、UPDATE等）
log_statement = 'all'  

# 记录客户端连接和断开事件
log_connections = true  
log_disconnections = true  

# 记录日志到文件（路径需可写）
logging_collector = on  
log_directory = 'pg_log'  
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

修改后重启PostgreSQL服务使配置生效：

sudo systemctl restart postgresql

2. 使用pgaudit扩展（高级审计）

pgaudit是PostgreSQL的官方审计扩展，支持细粒度审计（如按用户、表、操作类型过滤），满足合规要求（如GDPR、HIPAA）。

安装pgaudit：

sudo apt install postgresql-contrib-14  # 替换为实际版本

启用pgaudit：在postgresql.conf中添加以下配置：

shared_preload_libraries = 'pgaudit'  # 必须设置为shared_preload_libraries
pgaudit.log = 'all'                   # 记录所有审计事件（可细化为read, write, ddl等）
pgaudit.log_relation = on             # 记录表名
pgaudit.log_user = on                 # 记录执行操作的用户

重启服务：
```
sudo systemctl restart postgresql
```
验证pgaudit：登录psql，执行SQL语句后查看日志文件（/var/log/postgresql/pg_log/），日志会包含审计详情（如执行时间、用户、SQL语句、影响的表）。

3. 日志轮转（管理日志文件）

使用logrotate工具自动归档和清理旧日志，避免日志文件占用过多磁盘空间：

创建logrotate配置文件：
```
sudo nano /etc/logrotate.d/postgresql
```

添加以下内容（按需调整保留天数、压缩方式）：

/var/log/postgresql/*.log {
    daily
    missingok
    rotate 7
    compress
    delaycompress
    notifempty
    create 640 postgres adm
    sharedscripts
    postrotate
        systemctl reload postgresql > /dev/null
    endscript
}

测试logrotate：

sudo logrotate -vf /etc/logrotate.d/postgresql

注意事项

最小权限原则：仅授予用户完成工作所需的最低权限，避免过度授权。
定期审计：定期检查日志文件，关注异常操作（如大量DELETE、DROP语句）。
备份配置：修改配置文件前备份原文件（如cp postgresql.conf postgresql.conf.bak）。
SSL加密：建议启用SSL（修改postgresql.conf中的ssl = on，配置证书路径），保护数据传输安全（参考之前的安全设置指南）。

Debian PostgreSQL如何进行权限管理与审计