PostgreSQL 最小权限安全原则

最小权限原则核心概念

最小权限原则是数据库安全的核心原则之一，指用户或程序只应被授予完成任务所需的最小权限集合，不授予任何额外权限，可有效降低安全风险和攻击面。

核心原则

1. 按需授权：只授予用户完成工作所需的权限
2. 权限分离：不同角色拥有不同权限，避免权限集中
3. 定期审计：定期检查和调整权限设置
4. 最小作用域：权限范围应尽可能小（数据库、表、列级别）

角色设计与权限管理

1. 角色分类设计

-- 创建管理员角色（最高权限）
CREATE ROLE db_admin WITH SUPERUSER LOGIN PASSWORD 'admin123';

-- 创建应用程序角色（业务访问）
CREATE ROLE app_user WITH LOGIN PASSWORD 'app123';

-- 创建只读角色（查询访问）
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'read123';

-- 创建审计角色（监控访问）
CREATE ROLE audit_user WITH LOGIN PASSWORD 'audit123';

-- 创建维护角色（备份恢复）
CREATE ROLE maintenance_user WITH LOGIN PASSWORD 'maintain123';

2. 数据库级权限控制

-- 授予app_user对特定数据库的连接权限
GRANT CONNECT ON DATABASE mydb TO app_user;

-- 授予app_user对特定数据库的临时表创建权限
GRANT TEMPORARY ON DATABASE mydb TO app_user;

-- 拒绝其他角色的连接权限
REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;

3. schema级权限控制

-- 授予app_user对public schema的使用权限
GRANT USAGE ON SCHEMA public TO app_user;

-- 授予app_user对特定schema的所有权限
GRANT ALL PRIVILEGES ON SCHEMA myschema TO app_user;

4. 表级权限控制

-- 授予app_user对特定表的SELECT权限
GRANT SELECT ON TABLE mytable TO app_user;

-- 授予app_user对特定表的INSERT、UPDATE、DELETE权限
GRANT INSERT, UPDATE, DELETE ON TABLE mytable TO app_user;

-- 授予app_user对特定表的所有权限
GRANT ALL PRIVILEGES ON TABLE mytable TO app_user;

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

-- 设置默认权限，新创建的表自动继承权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

5. 列级权限控制

-- 授予app_user对特定表特定列的SELECT权限
GRANT SELECT (id, name, email) ON TABLE users TO app_user;

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

-- 拒绝app_user对敏感列的访问
REVOKE SELECT (password, credit_card) ON TABLE users FROM app_user;

6. 函数和序列权限控制

-- 授予app_user对特定函数的执行权限
GRANT EXECUTE ON FUNCTION my_function() TO app_user;

-- 授予app_user对特定序列的使用权限
GRANT USAGE, SELECT ON SEQUENCE my_sequence TO app_user;

-- 授予app_user对所有函数的执行权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

应用程序权限最佳实践

1. 应用程序角色设计

-- 创建应用程序专用角色
CREATE ROLE myapp WITH LOGIN PASSWORD 'myapp123';

-- 授予最小必要权限
GRANT CONNECT ON DATABASE mydb TO myapp;
GRANT USAGE ON SCHEMA public TO myapp;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE users TO myapp;
GRANT SELECT, INSERT ON TABLE orders TO myapp;
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO myapp;

2. 存储过程权限封装

-- 创建存储过程，封装复杂操作
CREATE OR REPLACE FUNCTION update_user_email(user_id INT, new_email VARCHAR)
RETURNS BOOLEAN AS $$
BEGIN
  UPDATE users SET email = new_email WHERE id = user_id;
  RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 授予应用程序执行存储过程的权限，而不是直接修改表
GRANT EXECUTE ON FUNCTION update_user_email(INT, VARCHAR) TO app_user;

3. 行级安全策略（RLS）

-- 启用行级安全策略
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- 创建行级安全策略，用户只能访问自己的数据
CREATE POLICY user_access_policy ON users
  USING (user_id = current_user_id());

-- 授予app_user使用行级安全策略的权限
GRANT SELECT, UPDATE ON users TO app_user;

权限审计与监控

1. 权限审计查询

-- 查看所有角色
\du

-- 查看特定角色的权限
\du app_user

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

-- 查询用户对列的权限
SELECT grantee, table_name, column_name, privilege_type 
FROM information_schema.role_column_grants 
WHERE grantee = 'app_user';

-- 查询默认权限设置
SELECT grantee, table_schema, privilege_type 
FROM information_schema.default_privileges 
WHERE grantee = 'app_user';

2. 权限变更监控

-- 启用审计日志（在postgresql.conf中设置）
-- log_statement = 'ddl'  -- 记录所有DDL语句
-- log_connections = on   -- 记录连接信息
-- log_disconnections = on -- 记录断开连接信息

-- 使用pg_stat_statements扩展监控权限相关操作
SELECT query, calls, total_time 
FROM pg_stat_statements 
WHERE query LIKE '%GRANT%' OR query LIKE '%REVOKE%';

3. 定期权限审查

# 创建权限审计脚本
#!/bin/bash

echo "=== PostgreSQL 权限审计报告 ==="
echo "生成时间: $(date)"
echo ""

# 连接到数据库并执行审计查询
psql -U postgres -d mydb << EOF
\o /tmp/privilege_audit_report.txt

\echo "1. 所有数据库角色:"
\du

\echo "\n2. 角色权限详情:"
SELECT grantee, table_name, privilege_type 
FROM information_schema.role_table_grants 
ORDER BY grantee, table_name;

\echo "\n3. 默认权限设置:"
SELECT grantee, table_schema, privilege_type 
FROM information_schema.default_privileges;

\echo "\n4. 超级用户列表:"
SELECT usename FROM pg_user WHERE usesuper = true;
EOF

cat /tmp/privilege_audit_report.txt

最小权限原则实施步骤

1. 需求分析

• 识别系统中的所有角色和用户
• 确定每个角色的职责和权限需求
• 划分权限级别和范围

2. 角色创建

• 创建不同级别的角色
• 设置适当的认证方式
• 配置角色继承关系

3. 权限授予

• 从最小权限开始授予
• 逐步增加必要的权限
• 避免使用GRANT ALL

4. 测试验证

• 测试每个角色的权限是否符合预期
• 验证权限限制是否有效
• 测试边界情况

5. 监控与调整

• 定期审计权限设置
• 监控权限相关操作
• 根据业务变化调整权限

常见问题（FAQ）

Q1：如何实现权限的批量管理？

A1：可以使用角色继承和默认权限实现批量管理：

-- 创建权限组角色
CREATE ROLE read_role;
CREATE ROLE write_role;

-- 授予权限组权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_role;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO write_role;

-- 将用户添加到权限组
GRANT read_role TO user1, user2;
GRANT write_role TO user3;

Q2：如何撤销用户的所有权限？

A2：可以使用REVOKE语句撤销权限：

-- 撤销用户对特定表的所有权限
REVOKE ALL PRIVILEGES ON TABLE mytable FROM app_user;

-- 撤销用户对所有表的所有权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM app_user;

-- 撤销用户的角色成员资格
REVOKE read_role FROM app_user;

Q3：如何处理应用程序需要的临时权限？

A3：可以使用临时权限或存储过程：

-- 方法1：临时授予权限，用完即收回
GRANT TEMPORARY ON DATABASE mydb TO app_user;

-- 方法2：使用存储过程封装临时操作
CREATE OR REPLACE FUNCTION perform_maintenance()
RETURNS VOID AS $$
BEGIN
  -- 执行维护操作
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION perform_maintenance() TO app_user;

Q4：如何确保新创建的对象自动应用最小权限？

A4：使用默认权限设置：

-- 设置默认权限，新表自动授予SELECT权限给readonly_role
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;

-- 设置默认权限，新函数自动授予EXECUTE权限给app_role
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO app_role;

Q5：如何审计权限变更历史？

A5：启用PostgreSQL的审计日志功能：

-- 在postgresql.conf中设置
log_statement = 'ddl'  -- 记录所有DDL语句，包括GRANT/REVOKE
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '  -- 记录详细信息

Q6：如何处理最小权限与易用性的平衡？

A6：

• 使用角色继承简化权限管理
• 合理设计权限组，减少管理复杂度
• 使用存储过程封装复杂操作
• 定期审查权限，移除不再需要的权限
• 结合自动化工具进行权限管理