PostgreSQL 定期权限检查

权限检查范围

定期权限检查应覆盖以下范围：

1. 超级用户检查

sql

-- 列出所有超级用户
SELECT usename, usesuper, usecreatedb, useinherit 
FROM pg_user 
WHERE usesuper = true;

-- 检查是否有异常超级用户
SELECT usename, usesuper, usecreatedb, useinherit 
FROM pg_user 
WHERE usesuper = true 
AND usename NOT IN ('postgres', 'admin'); -- 列出预期的超级用户

2. 角色与权限检查

sql

-- 列出所有角色及其权限
\du

-- 查询角色对表的权限
SELECT grantee, table_name, privilege_type 
FROM information_schema.role_table_grants 
ORDER BY grantee, table_name;

-- 查询角色对列的权限
SELECT grantee, table_name, column_name, privilege_type 
FROM information_schema.role_column_grants 
ORDER BY grantee, table_name, column_name;

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

3. 数据库级权限检查

sql

-- 查询数据库级权限
SELECT grantee, datname, privilege_type 
FROM pg_database 
JOIN pg_default_acl ON pg_database.datdba = pg_default_acl.defaclrole 
JOIN pg_roles ON pg_default_acl.defaclrole = pg_roles.oid;

-- 检查PUBLIC角色的数据库权限
SELECT datname, datacl 
FROM pg_database 
WHERE datacl IS NOT NULL;

4. Schema级权限检查

sql

-- 查询schema级权限
SELECT grantee, schema_name, privilege_type 
FROM information_schema.schema_privileges 
ORDER BY grantee, schema_name;

-- 检查PUBLIC角色的schema权限
SELECT schema_name, privilege_type 
FROM information_schema.schema_privileges 
WHERE grantee = 'PUBLIC';

自动化权限检查脚本

1. 基本权限检查脚本

bash

#!/bin/bash

# PostgreSQL 定期权限检查脚本
# 执行权限检查并生成报告

DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
DB_NAME="postgres"
REPORT_FILE="/tmp/privilege_check_$(date +%Y%m%d_%H%M%S).txt"

# 连接到数据库并执行检查
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "
-- 超级用户检查
\echo '=== 超级用户检查 ==='
SELECT usename, usesuper, usecreatedb, useinherit 
FROM pg_user 
WHERE usesuper = true;

-- 角色权限检查
\echo '\n=== 角色表权限检查 ==='
SELECT grantee, table_name, privilege_type 
FROM information_schema.role_table_grants 
ORDER BY grantee, table_name;

-- PUBLIC角色权限检查
\echo '\n=== PUBLIC角色权限检查 ==='
SELECT schema_name, privilege_type 
FROM information_schema.schema_privileges 
WHERE grantee = 'PUBLIC';

-- 默认权限检查
\echo '\n=== 默认权限检查 ==='
SELECT grantee, table_schema, privilege_type 
FROM information_schema.default_privileges;
" > $REPORT_FILE

echo "权限检查报告已生成：$REPORT_FILE"

2. 高级权限审计脚本

bash

#!/bin/bash

# 高级权限审计脚本，包含异常检测

DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
DB_NAME="postgres"
REPORT_FILE="/tmp/privilege_audit_$(date +%Y%m%d_%H%M%S).html"

# 预期的超级用户列表
EXPECTED_SUPERUSERS=("postgres" "admin")

# 生成HTML报告头
cat > $REPORT_FILE << EOF
<!DOCTYPE html>
<html>
<head>
    <title>PostgreSQL 权限审计报告</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        h1 { color: #333; }
        h2 { color: #555; border-bottom: 1px solid #ccc; padding-bottom: 5px; }
        table { border-collapse: collapse; width: 100%; margin: 10px 0; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        .warning { background-color: #fff3cd; }
        .danger { background-color: #f8d7da; }
        .success { background-color: #d4edda; }
    </style>
</head>
<body>
    <h1>PostgreSQL 权限审计报告</h1>
    <p>生成时间：$(date)</p>
    <p>数据库：$DB_NAME@$DB_HOST:$DB_PORT</p>
EOF

# 检查超级用户
SUPERUSERS=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "
SELECT usename FROM pg_user WHERE usesuper = true;")

cat >> $REPORT_FILE << EOF
    <h2>1. 超级用户检查</h2>
    <table>
        <tr><th>用户名</th><th>状态</th></tr>
EOF

for user in $SUPERUSERS; do
    user=$(echo $user | xargs)  # 去除空格
    if [[ " ${EXPECTED_SUPERUSERS[@]} " =~ " $user " ]]; then
        STATUS="<span class='success'>正常</span>"
    else
        STATUS="<span class='danger'>异常 - 未知超级用户</span>"
    fi
    cat >> $REPORT_FILE << EOF
        <tr><td>$user</td><td>$STATUS</td></tr>
EOF
done

cat >> $REPORT_FILE << EOF
    </table>
EOF

# 检查PUBLIC角色权限
PUBLIC_PRIVILEGES=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "
SELECT schema_name, privilege_type 
FROM information_schema.schema_privileges 
WHERE grantee = 'PUBLIC';")

cat >> $REPORT_FILE << EOF
    <h2>2. PUBLIC角色权限检查</h2>
    <table>
        <tr><th>Schema</th><th>权限</th><th>状态</th></tr>
EOF

while read -r line; do
    SCHEMA=$(echo $line | awk '{print $1}')
    PRIVILEGE=$(echo $line | awk '{print $2}')
    # 检查危险权限
    if [[ "$PRIVILEGE" == "ALL" || "$PRIVILEGE" == "CREATE" || "$PRIVILEGE" == "USAGE" ]]; then
        STATUS="<span class='warning'>警告 - 需确认是否必要</span>"
    else
        STATUS="<span class='success'>正常</span>"
    fi
    cat >> $REPORT_FILE << EOF
        <tr><td>$SCHEMA</td><td>$PRIVILEGE</td><td>$STATUS</td></tr>
EOF
done <<< "$PUBLIC_PRIVILEGES"

cat >> $REPORT_FILE << EOF
    </table>
    </body>
    </html>
EOF

echo "高级权限审计报告已生成：$REPORT_FILE"

定期检查流程

1. 检查频率

超级用户检查：每周一次
角色权限检查：每两周一次
完整权限审计：每月一次
特殊权限变更后：立即检查

2. 检查步骤

执行权限检查脚本：运行自动化脚本生成初步报告
分析报告：检查是否有异常权限或未授权的角色
验证权限必要性：确认每个角色的权限是否与其职责匹配
记录检查结果：将检查结果和发现的问题记录到审计日志
处理异常权限：对发现的异常权限进行调整或撤销
更新权限基线：定期更新权限基线，用于后续对比

3. 权限基线管理

bash

#!/bin/bash

# 权限基线生成脚本

DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
DB_NAME="postgres"
BASELINE_FILE="/etc/postgresql/privilege_baseline_$(date +%Y%m%d).sql"

# 生成权限基线
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "
-- 生成超级用户基线
\o $BASELINE_FILE
\echo '-- PostgreSQL 权限基线 - 生成时间：' $(date)
\echo '-- 超级用户基线'
SELECT usename FROM pg_user WHERE usesuper = true;

\echo '-- 角色权限基线'
SELECT grantee, table_name, privilege_type FROM information_schema.role_table_grants;

\echo '-- PUBLIC角色权限基线'
SELECT schema_name, privilege_type FROM information_schema.schema_privileges WHERE grantee = 'PUBLIC';
\o
" > /dev/null

echo "权限基线已生成：$BASELINE_FILE"

权限检查最佳实践

1. 建立权限基线

初始部署时生成权限基线
每次权限变更后更新基线
定期将当前权限与基线对比

2. 自动化检查

使用cron或其他调度工具定期执行检查脚本
将检查结果发送到安全团队邮箱
对异常权限设置告警

3. 文档化权限变更

所有权限变更必须有工单记录
权限变更需经过审批
记录权限变更的原因和审批人

4. 最小权限原则

定期审查权限，移除不再需要的权限
避免使用GRANT ALL PRIVILEGES
按角色分配最小必要权限

5. 分离职责

数据库管理员与应用程序管理员分离
审计人员与操作人员分离
不同业务系统使用不同角色

常见问题（FAQ）

Q1：如何确定权限是否必要？

A1：

查看角色的职责和工作内容
检查角色的实际使用情况（通过pg_stat_statements）
与业务部门确认权限需求
参考最小权限原则

Q2：如何处理发现的异常权限？

A2：

记录发现的异常权限
与权限所有者确认权限需求
如果权限不必要，生成权限撤销工单
经过审批后撤销权限
更新权限基线

Q3：如何自动化权限检查？

A3：

使用cron定期执行检查脚本
将检查结果发送到指定邮箱
对异常权限设置告警
集成到现有安全审计系统

Q4：如何生成权限检查报告？

A4：

使用本文提供的自动化脚本
可以生成文本报告或HTML报告
报告应包含检查时间、数据库信息、检查内容和异常发现
报告应定期归档，便于审计

Q5：权限检查会影响数据库性能吗？

A5：

权限检查主要查询系统视图，对数据库性能影响很小
建议在低峰时段执行完整权限审计
可以限制查询的返回结果数量，减少资源消耗

Q6：如何处理复杂的权限继承关系？

A6：

使用\du+命令查看角色继承关系
查询pg_roles视图了解角色属性
检查role_table_grants和role_column_grants视图
从上层角色开始，逐层检查权限

PostgreSQL 定期权限检查 ​

权限检查范围 ​

1. 超级用户检查 ​

2. 角色与权限检查 ​

3. 数据库级权限检查 ​

4. Schema级权限检查 ​

自动化权限检查脚本 ​

1. 基本权限检查脚本 ​

2. 高级权限审计脚本 ​

定期检查流程 ​

1. 检查频率 ​

2. 检查步骤 ​

3. 权限基线管理 ​

权限检查最佳实践 ​

1. 建立权限基线 ​

2. 自动化检查 ​

3. 文档化权限变更 ​

4. 最小权限原则 ​

5. 分离职责 ​

常见问题（FAQ） ​

Q1：如何确定权限是否必要？ ​

Q2：如何处理发现的异常权限？ ​

Q3：如何自动化权限检查？ ​

Q4：如何生成权限检查报告？ ​

Q5：权限检查会影响数据库性能吗？ ​

Q6：如何处理复杂的权限继承关系？ ​

PostgreSQL 定期权限检查

权限检查范围

1. 超级用户检查

2. 角色与权限检查

3. 数据库级权限检查

4. Schema级权限检查

自动化权限检查脚本

1. 基本权限检查脚本

2. 高级权限审计脚本

定期检查流程

1. 检查频率

2. 检查步骤

3. 权限基线管理

权限检查最佳实践

1. 建立权限基线

2. 自动化检查

3. 文档化权限变更

4. 最小权限原则

5. 分离职责

常见问题（FAQ）

Q1：如何确定权限是否必要？

Q2：如何处理发现的异常权限？

Q3：如何自动化权限检查？

Q4：如何生成权限检查报告？

Q5：权限检查会影响数据库性能吗？

Q6：如何处理复杂的权限继承关系？