PostgreSQL 访问控制规范

角色与权限管理

角色创建与管理

创建角色

sql

-- 创建管理员角色
CREATE ROLE db_admin WITH LOGIN PASSWORD 'secure_password' CREATEDB CREATEROLE;

-- 创建普通用户角色
CREATE ROLE app_user WITH LOGIN PASSWORD 'user_password';

-- 创建组角色
CREATE ROLE reporting_group;

角色权限分配

sql

-- 将用户添加到组
GRANT reporting_group TO app_user;

-- 授予数据库权限
GRANT CONNECT ON DATABASE dbname TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- 授予表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE table_name TO app_user;

-- 授予序列权限
GRANT USAGE, SELECT ON SEQUENCE sequence_name TO app_user;

角色属性管理

sql

-- 允许角色创建数据库
ALTER ROLE app_user CREATEDB;

-- 允许角色创建角色
ALTER ROLE db_admin CREATEROLE;

-- 设置角色有效期
ALTER ROLE app_user VALID UNTIL '2025-12-31';

权限回收

回收表权限

sql

REVOKE DELETE ON TABLE table_name FROM app_user;

回收角色成员资格
sql
```
REVOKE reporting_group FROM app_user;
```
删除角色
sql
```
DROP ROLE IF EXISTS temp_role;
```

认证方式配置

pg_hba.conf 配置

基本配置格式

# 类型  数据库        用户            地址                 认证方法
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256

认证方法选择
- scram-sha-256：推荐的密码认证方式，安全性高
- md5：兼容旧版本的密码认证方式
- peer：本地认证，使用操作系统用户名
- ident：基于客户端操作系统用户名的认证
- trust：无条件信任，仅用于测试环境

生产环境示例配置

# 本地认证
local   all             postgres                                peer

# 应用服务器访问
host    dbname          app_user        192.168.1.100/32        scram-sha-256

# 管理员访问
host    all             db_admin        10.0.0.0/8              scram-sha-256

# 复制用户访问
host    replication     replication     192.168.1.0/24          scram-sha-256

密码策略配置

密码复杂度要求

sql

-- 安装密码验证扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS passwordcheck;

-- 配置密码策略
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
ALTER SYSTEM SET password_min_length = '12';
ALTER SYSTEM SET password_min_digital = '2';
ALTER SYSTEM SET password_min_uppercase = '2';
ALTER SYSTEM SET password_min_lowercase = '2';
ALTER SYSTEM SET password_min_special = '1';

密码有效期

sql

-- 设置默认密码有效期为90天
ALTER ROLE app_user VALID UNTIL (current_date + interval '90 days');

-- 检查密码有效期
SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolvaliduntil IS NOT NULL;

网络访问控制

listen_addresses 配置

配置监听地址

sql

-- 监听所有地址
ALTER SYSTEM SET listen_addresses = '*';

-- 监听特定地址
ALTER SYSTEM SET listen_addresses = '127.0.0.1,192.168.1.50';

端口配置

sql

-- 使用默认端口
ALTER SYSTEM SET port = '5432';

-- 使用非默认端口
ALTER SYSTEM SET port = '5433';

防火墙配置

iptables 配置

bash

# 允许本地访问
iptables -A INPUT -p tcp -s 127.0.0.1 --dport 5432 -j ACCEPT

# 允许应用服务器访问
iptables -A INPUT -p tcp -s 192.168.1.100 --dport 5432 -j ACCEPT

# 拒绝其他所有访问
iptables -A INPUT -p tcp --dport 5432 -j DROP

firewalld 配置

bash

# 添加PostgreSQL服务
firewall-cmd --add-service=postgresql --permanent

# 允许特定IP访问
firewall-cmd --add-rich-rule='rule family="ipv4" source address="192.168.1.100/32" port protocol="tcp" port="5432" accept' --permanent

# 重新加载配置
firewall-cmd --reload

列级和行级权限

列级权限

授予列级权限

sql

-- 仅授予部分列的SELECT权限
GRANT SELECT (id, name, email) ON TABLE users TO app_user;

-- 仅授予UPDATE特定列的权限
GRANT UPDATE (email, last_login) ON TABLE users TO app_user;

使用视图实现列级权限

sql

-- 创建视图，隐藏敏感列
CREATE VIEW public.users_view AS
SELECT id, name, email FROM users;

-- 授予视图权限
GRANT SELECT ON public.users_view TO app_user;

行级权限

行级安全性策略

sql

-- 启用表的行级安全性
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 创建行级安全性策略
CREATE POLICY orders_by_user ON orders
FOR SELECT TO app_user
USING (user_id = current_user_id());

-- 授予表权限
GRANT SELECT ON orders TO app_user;

多策略管理

sql

-- 为不同角色创建不同策略
CREATE POLICY orders_admin ON orders
FOR ALL TO db_admin
USING (true);

CREATE POLICY orders_customer ON orders
FOR SELECT TO customer_role
USING (customer_id = current_customer_id());

审计和监控

审计日志配置

启用审计日志

sql

-- 配置日志记录所有语句
ALTER SYSTEM SET log_statement = 'all';

-- 配置日志记录DDL语句
ALTER SYSTEM SET log_statement = 'ddl';

-- 配置日志记录修改语句
ALTER SYSTEM SET log_statement = 'mod';

使用 pgAudit 扩展

sql

-- 安装pgAudit扩展
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- 配置审计策略
ALTER SYSTEM SET pgaudit.log = 'READ,WRITE';
ALTER SYSTEM SET pgaudit.log_catalog = 'on';
ALTER SYSTEM SET pgaudit.log_relation = 'on';

访问监控

监控当前连接

sql

-- 查看当前连接
SELECT usename, application_name, client_addr, backend_start, state FROM pg_stat_activity;

-- 查看活跃连接
SELECT usename, application_name, client_addr, query FROM pg_stat_activity WHERE state <> 'idle';

监控权限变更

sql

-- 查看权限变更日志
SELECT * FROM pg_log WHERE message LIKE '%GRANT%' OR message LIKE '%REVOKE%';

最佳实践

生产环境配置建议

最小权限原则
- 只为用户授予完成任务所需的最小权限
- 定期审查和回收不必要的权限
- 使用角色组管理权限，避免直接向用户授予权限
强密码策略
- 使用scram-sha-256加密密码
- 设置密码复杂度要求
- 定期更换密码
- 禁止使用默认密码
网络安全
- 仅监听必要的网络接口
- 使用防火墙限制访问
- 为不同类型的访问配置不同的认证方式
- 避免使用trust认证方式
审计与监控
- 启用适当级别的审计日志
- 定期审查审计日志
- 监控异常访问模式
- 使用pgAudit扩展进行详细审计

权限管理最佳实践

角色命名规范
- 使用清晰的命名约定，如：
  - db_admin：数据库管理员角色
  - app_前缀：应用用户角色
  - _group后缀：角色组
  - readonly_前缀：只读角色

定期权限审查

sql

-- 检查用户权限
SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants WHERE table_schema = 'public';

-- 检查角色成员关系
SELECT roleid::regrole, member::regrole FROM pg_auth_members;

权限变更流程
- 建立权限变更的审批流程
- 记录所有权限变更
- 在非生产环境测试权限变更
- 定期备份权限配置

常见问题（FAQ）

Q1：如何查看用户的权限？

A1：

sql

-- 查看用户的表权限
SELECT grantee, privilege_type, table_name 
FROM information_schema.role_table_grants 
WHERE grantee = 'app_user';

-- 查看用户的数据库权限
SELECT grantee, privilege_type, database_name 
FROM information_schema.role_database_grants 
WHERE grantee = 'app_user';

-- 查看用户的角色成员资格
SELECT member::regrole, roleid::regrole 
FROM pg_auth_members 
WHERE member::regrole = 'app_user';

Q2：如何实现只读用户？

A2：

sql

-- 创建只读角色
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'readonly_password';

-- 授予数据库访问权限
GRANT CONNECT ON DATABASE dbname TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;

-- 授予所有表的只读权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

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

Q3：如何限制用户只能访问特定IP？

A3：编辑pg_hba.conf文件，添加IP限制规则：

# 允许特定IP访问
host    dbname    username    192.168.1.100/32    scram-sha-256

然后重新加载配置：

bash

pg_ctl reload -D /path/to/data

Q4：如何审计用户的访问行为？

A4：

使用pgAudit扩展：

sql

CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'READ,WRITE';
SELECT pg_reload_conf();

配置日志记录：

sql

ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
SELECT pg_reload_conf();

Q5：如何回收用户的所有权限？

A5：

sql

-- 回收数据库权限
REVOKE ALL PRIVILEGES ON DATABASE dbname FROM app_user;

-- 回收 schema 权限
REVOKE ALL PRIVILEGES ON SCHEMA public FROM app_user;

-- 回收所有表权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM app_user;

-- 回收所有序列权限
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM app_user;

-- 回收角色成员资格
REVOKE ALL PRIVILEGES FROM app_user;

PostgreSQL 访问控制规范 ​

角色与权限管理 ​

角色创建与管理 ​

权限回收 ​

认证方式配置 ​

pg_hba.conf 配置 ​

密码策略配置 ​

网络访问控制 ​

listen_addresses 配置 ​

防火墙配置 ​

列级和行级权限 ​

列级权限 ​

行级权限 ​

审计和监控 ​

审计日志配置 ​

访问监控 ​

最佳实践 ​

生产环境配置建议 ​

权限管理最佳实践 ​

常见问题（FAQ） ​

Q1：如何查看用户的权限？ ​

Q2：如何实现只读用户？ ​

Q3：如何限制用户只能访问特定IP？ ​

Q4：如何审计用户的访问行为？ ​

Q5：如何回收用户的所有权限？ ​

PostgreSQL 访问控制规范

角色与权限管理

角色创建与管理

权限回收

认证方式配置

pg_hba.conf 配置

密码策略配置

网络访问控制

listen_addresses 配置

防火墙配置

列级和行级权限

列级权限

行级权限

审计和监控

审计日志配置

访问监控

最佳实践

生产环境配置建议

权限管理最佳实践

常见问题（FAQ）

Q1：如何查看用户的权限？

Q2：如何实现只读用户？

Q3：如何限制用户只能访问特定IP？

Q4：如何审计用户的访问行为？

Q5：如何回收用户的所有权限？