查询1：登录账号角色属性
需要关闭默认超级管理员
ALTER ROLE postgres NOLOGIN;
展示所有可登录账号自身直接拥有的集群级属性。
SELECT
rolname AS "登录账号",
CASE WHEN rolsuper THEN '是' ELSE '否' END AS "SUPERUSER",
CASE WHEN rolcreaterole THEN '是' ELSE '否' END AS "CREATEROLE",
CASE WHEN rolcreatedb THEN '是' ELSE '否' END AS "CREATEDB",
CASE WHEN rolbypassrls THEN '是' ELSE '否' END AS "BYPASSRLS",
CASE WHEN rolcanlogin THEN '是' ELSE '否' END AS "LOGIN",
rolvaliduntil AS "密码有效期"
FROM pg_roles
WHERE rolcanlogin = true
ORDER BY rolname;
查询2：直接角色继承关系
作用：展示全部直接角色成员关系，并确认被继承角色是否为超级用户。
SELECT
member_role.rolname AS "成员账号或角色",
granted_role.rolname AS "继承的角色",
CASE WHEN member_role.rolcanlogin
THEN '是' ELSE '否' END AS "成员可登录",
CASE WHEN member_role.rolsuper
THEN '是' ELSE '否' END AS "成员自身SUPERUSER",
CASE WHEN granted_role.rolsuper
THEN '是' ELSE '否' END AS "被继承角色SUPERUSER",
CASE WHEN am.admin_option
THEN '是' ELSE '否' END AS "可转授角色"
FROM pg_auth_members am
JOIN pg_roles member_role
ON member_role.oid = am.member
JOIN pg_roles granted_role
ON granted_role.oid = am.roleid
ORDER BY member_role.rolname, granted_role.rolname;
查询3：检查是否间接继承超级用户
检查登录账号是否通过直接或间接角色链获得超级用户角色
WITH RECURSIVE role_chain AS (
SELECT
am.member AS login_oid,
am.roleid AS inherited_oid,
ARRAY[am.member, am.roleid] AS visited
FROM pg_auth_members am
UNION ALL
SELECT
rc.login_oid,
am.roleid,
rc.visited || am.roleid
FROM role_chain rc
JOIN pg_auth_members am
ON am.member = rc.inherited_oid
WHERE NOT am.roleid = ANY(rc.visited)
)
SELECT DISTINCT
login_role.rolname AS "登录账号",
inherited_role.rolname AS "继承的超级用户角色",
'存在超级权限继承风险' AS "检查结论"
FROM role_chain rc
JOIN pg_roles login_role
ON login_role.oid = rc.login_oid
JOIN pg_roles inherited_role
ON inherited_role.oid = rc.inherited_oid
WHERE login_role.rolcanlogin = true
AND inherited_role.rolsuper = true
ORDER BY login_role.rolname;
查询4：三权分立角色矩阵
明确系统管理员、安全管理员、审计管理员的职责、人员和权限。
\c "Dynames_BK_DB"
\pset pager off
\x off
WITH management_matrix (
sort_no,
admin_type,
login_account,
management_role,
responsibility
) AS (
VALUES
(
1,
'系统管理员',
'u_system_admin',
'r_system_admin',
'数据库创建、监控维护、参数配置、逻辑备份恢复'
),
(
2,
'安全管理员',
'u_security_admin',
'r_security_admin',
'账号、角色及权限管理'
),
(
3,
'审计管理员',
'u_audit_admin',
'r_audit_admin',
'安全监控、审计检查及取证'
)
),
business_tables AS (
SELECT c.oid
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
AND n.nspname <> 'information_schema'
AND n.nspname NOT LIKE 'pg_%'
AND n.nspname NOT LIKE 'pg_toast%'
)
SELECT
m.admin_type AS "管理类别",
m.login_account AS "登录账号",
m.management_role AS "授权角色",
m.responsibility AS "主要职责",
CASE WHEN pg_has_role(
m.login_account,
m.management_role,
'MEMBER'
) THEN '是' ELSE '否'
END AS "账号已绑定角色",
CASE WHEN r.rolsuper
THEN '是' ELSE '否'
END AS "SUPERUSER",
CASE WHEN r.rolcreaterole
THEN '是' ELSE '否'
END AS "CREATEROLE",
CASE WHEN r.rolcreatedb
THEN '是' ELSE '否'
END AS "CREATEDB",
CASE WHEN r.rolbypassrls
THEN '是' ELSE '否'
END AS "BYPASSRLS",
CASE WHEN pg_has_role(
m.management_role,
'pg_monitor',
'MEMBER'
) THEN '是' ELSE '否'
END AS "监控权限",
CASE WHEN pg_has_role(
m.management_role,
'pg_maintain',
'MEMBER'
) THEN '是' ELSE '否'
END AS "维护权限",
CASE WHEN pg_has_role(
m.management_role,
'pg_checkpoint',
'MEMBER'
) THEN '是' ELSE '否'
END AS "检查点权限",
CASE WHEN pg_has_role(
m.management_role,
'pg_signal_backend',
'MEMBER'
) THEN '是' ELSE '否'
END AS "会话管理权限",
CASE WHEN pg_has_role(
m.management_role,
'pg_read_all_data',
'MEMBER'
) THEN '是' ELSE '否'
END AS "备份读取权限",
CASE WHEN
has_parameter_privilege(
m.management_role,
'work_mem',
'SET'
)
AND has_parameter_privilege(
m.management_role,
'maintenance_work_mem',
'SET'
)
AND has_parameter_privilege(
m.management_role,
'log_min_duration_statement',
'ALTER SYSTEM'
)
THEN '是' ELSE '否'
END AS "参数配置权限",
CASE WHEN
EXISTS (SELECT 1 FROM business_tables)
AND NOT EXISTS (
SELECT 1
FROM business_tables t
WHERE NOT has_table_privilege(
m.management_role,
t.oid,
'SELECT'
)
)
THEN '是' ELSE '否'
END AS "所有业务表可读",
CASE WHEN EXISTS (
SELECT 1
FROM business_tables t
WHERE has_table_privilege(
m.management_role, t.oid, 'INSERT'
)
OR has_table_privilege(
m.management_role, t.oid, 'UPDATE'
)
OR has_table_privilege(
m.management_role, t.oid, 'DELETE'
)
OR has_table_privilege(
m.management_role, t.oid, 'TRUNCATE'
)
)
THEN '是' ELSE '否'
END AS "可写业务表"
FROM management_matrix m
JOIN pg_roles r
ON r.rolname = m.management_role
ORDER BY m.sort_no;
查询5：三个账号分别登录的越权失败截图
下面这里前四个属性包含账号通过所属角色执行 SET ROLE 后可获得的能力
\c "Dynames_BK_DB"
RESET ROLE;
\pset pager off
\x off
RESET ROLE;
WITH RECURSIVE
lr AS (
SELECT * FROM pg_roles WHERE rolcanlogin
),
rt AS (
SELECT oid AS login_oid, oid AS role_oid FROM lr
UNION
SELECT rt.login_oid, am.roleid
FROM rt
JOIN pg_auth_members am ON am.member = rt.role_oid
WHERE am.set_option
),
ra AS (
SELECT rt.login_oid,
bool_or(p.rolsuper) superuser,
bool_or(p.rolcreaterole) createrole,
bool_or(p.rolcreatedb) createdb,
bool_or(p.rolbypassrls) bypassrls
FROM rt JOIN pg_roles p ON p.oid = rt.role_oid
GROUP BY rt.login_oid
),
rm AS (
SELECT am.member,
string_agg(p.rolname, ', ' ORDER BY p.rolname) member_of
FROM pg_auth_members am
JOIN pg_roles p ON p.oid = am.roleid
GROUP BY am.member
),
bt AS (
SELECT c.oid
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p')
AND n.nspname <> 'information_schema'
AND n.nspname NOT LIKE 'pg_%'
AND n.nspname NOT LIKE 'pg_toast%'
),
dc AS (
SELECT rt.login_oid,
bool_or(has_database_privilege(p.rolname,current_database(),'CONNECT')) conn,
bool_or(has_database_privilege(p.rolname,current_database(),'CREATE')) dbcreate,
bool_or(has_database_privilege(p.rolname,current_database(),'TEMP')) temp,
bool_or(has_schema_privilege(p.rolname,'public','CREATE')) tblcreate
FROM rt JOIN pg_roles p ON p.oid = rt.role_oid
GROUP BY rt.login_oid
),
tc AS (
SELECT rt.login_oid, bt.oid,
bool_or(has_table_privilege(p.rolname,bt.oid,'SELECT')) sel,
bool_or(has_table_privilege(p.rolname,bt.oid,'INSERT')) ins,
bool_or(has_table_privilege(p.rolname,bt.oid,'UPDATE')) upd,
bool_or(has_table_privilege(p.rolname,bt.oid,'DELETE')) del
FROM rt
JOIN pg_roles p ON p.oid = rt.role_oid
CROSS JOIN bt
GROUP BY rt.login_oid, bt.oid
),
ts AS (
SELECT login_oid,
bool_and(sel) sel, bool_and(ins) ins,
bool_and(upd) upd, bool_and(del) del
FROM tc GROUP BY login_oid
)
SELECT
l.rolname AS "登录账号",
COALESCE(rm.member_of,'无') AS "所属角色",
CASE WHEN ra.superuser THEN '是' ELSE '否' END AS "SUPERUSER",
CASE WHEN ra.createrole THEN '是' ELSE '否' END AS "CREATEROLE",
CASE WHEN ra.createdb THEN '是' ELSE '否' END AS "CREATEDB",
CASE WHEN ra.bypassrls THEN '是' ELSE '否' END AS "BYPASSRLS",
CASE WHEN dc.conn THEN '是' ELSE '否' END AS "可连接数据库",
CASE WHEN dc.dbcreate THEN '是' ELSE '否' END AS "可创建Schema",
CASE WHEN dc.temp THEN '是' ELSE '否' END AS "可创建临时表",
CASE WHEN dc.tblcreate THEN '是' ELSE '否' END AS "可创建表",
CASE WHEN COALESCE(ts.sel,false) THEN '是' ELSE '否' END AS "所有表可查询",
CASE WHEN COALESCE(ts.ins,false) THEN '是' ELSE '否' END AS "所有表可新增",
CASE WHEN COALESCE(ts.upd,false) THEN '是' ELSE '否' END AS "所有表可修改",
CASE WHEN COALESCE(ts.del,false) THEN '是' ELSE '否' END AS "所有表可删除"
FROM lr l
JOIN ra ON ra.login_oid = l.oid
JOIN dc ON dc.login_oid = l.oid
LEFT JOIN rm ON rm.member = l.oid
LEFT JOIN ts ON ts.login_oid = l.oid
ORDER BY l.rolname;
查询6：审计配置和审计日志截图
至少体现时间、账号、操作类型、执行结果、客户端地址。
审计配置现状
SELECT
name,
setting,
source,
pending_restart
FROM pg_settings
WHERE name IN (
'logging_collector',
'log_destination',
'log_directory',
'log_filename',
'log_file_mode',
'log_connections',
'log_disconnections',
'log_line_prefix',
'log_min_error_statement',
'log_min_duration_statement',
'log_statement',
'shared_preload_libraries'
)
ORDER BY name;
成功及失败操作结果
临时开启当前会话的成功语句日志
SET application_name = 'EQP_AUDIT_06_TEST';
SET log_min_duration_statement = 0;
生成预期成功的只读操作
SELECT clock_timestamp() AS event_time, session_user AS account, 'SELECT' AS operation_type, 'SUCCESS' AS execution_result, inet_client_addr() AS client_address, inet_client_port() AS client_port, current_database() AS database_name, 'AUDIT_EVIDENCE_06_SUCCESS' AS marker;
生成预期失败的只读操作
SELECT CAST('AUDIT_EVIDENCE_06_EXPECTED_FAILURE' AS integer);
关闭临时成功日志
RESET log_min_duration_statement;
RESET application_name;
审计日志记录
WITH latest_log AS (
SELECT name
FROM pg_ls_logdir()
ORDER BY modification DESC
LIMIT 1
),
log_lines AS (
SELECT
t.ordinality AS line_no,
t.line
FROM latest_log l
CROSS JOIN LATERAL regexp_split_to_table(
pg_read_file(
format(
'%s/%s',
current_setting('log_directory'),
l.name
)
),
E'\n'
) WITH ORDINALITY AS t(line, ordinality)
)
SELECT line
FROM log_lines
WHERE line LIKE '%AUDIT_EVIDENCE_06%'
ORDER BY line_no;
查询7：应急超级管理员管理制度
账号范围 应急账号为 u_pg_breakglass，具有 SUPERUSER 权限，仅用于系统故障、权限恢复、安全事件处置等紧急场景，禁止日常使用。
凭据保管 账号密码存放于受控密码保险柜，由两名授权管理人员分权保管。禁止明文记录、个人保存或通过聊天工具传递。
使用审批 使用前须提交申请，说明事由、时间、操作内容、影响范围和回退方案，经数据库负责人及安全负责人审批。紧急情况下可先处置，事后补办审批。
使用要求 使用时应限定操作时间和范围，优先执行必要的最小操作。禁止处理与应急事项无关的业务数据。操作完成后立即退出账号。
审计留痕 每次使用须记录申请人、审批人、实际操作人、登录时间、客户端地址、执行命令、操作结果及退出时间。相关数据库日志和审批记录应统一归档，禁止操作人员自行删除或修改。
使用后改密 每次使用结束后立即修改账号密码，更新密码保险柜，并确认旧密码失效。异常登录或凭据疑似泄露时应立即停用账号并改密。
定期复核 每季度至少复核一次账号状态、权限、保管人员、密码有效性和使用记录。长期未使用也须验证账号可用性。发现异常授权、无审批使用或日志缺失时，应立即整改并追责。
责任要求 未经审批使用、共享凭据、扩大操作范围或删除审计记录，按安全违规事件处理。
修改用户密码及有效期
正常登录
psql -h 127.0.0.1 -p 5432 -U postgres -d Dynames_BK_DB -W修改单个用户
\password u_system_admin
ALTER ROLE u_system_admin VALID UNTIL 'infinity';第一条修改密码，第二条设置密码永不过期。
批量设置所有用户永不过期
DO $$
DECLARE u record;
BEGIN
FOR u IN SELECT rolname FROM pg_roles WHERE rolcanlogin LOOP
EXECUTE format(
'ALTER ROLE %I VALID UNTIL %L',
u.rolname,
'infinity'
);
END LOOP;
END $$;批量修改密码需要逐个输入：
\password dynames
\password u_admin_01
\password u_audit_admin
\password u_dev_01
\password u_pg_breakglass
\password u_ro_01
\password u_security_admin
\password u_system_adminpostgres 已过期或忘记密码
直接进入容器：
podman exec -it postgres psql -U postgres -d Dynames_BK_DB然后执行：
\password postgres
ALTER ROLE postgres LOGIN VALID UNTIL 'infinity';检查结果
SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolcanlogin
ORDER BY rolname;显示 infinity 就是永不过期。