PostgreSQL教程FG137-PG权限元数据：查询角色与对象权限关系

本文档风哥主要介绍PostgreSQL数据库权限元数据的查询方法，包括角色权限查询、对象权限查询、权限继承关系查询等内容，风哥教程参考PostgreSQL官方文档Database Roles、Client
Authentication等内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PostgreSQL数据库权限元数据概念

PostgreSQL数据库权限元数据是指存储在系统目录中的权限相关信息，包括角色信息、权限分配、权限继承关系等。这些元数据对于数据库安全管理和权限审计非常重要。更多视频教程www.fgedu.net.cn

PostgreSQL数据库权限元数据的特点：

存储在系统目录中
包含角色定义和权限分配信息
支持权限继承机制
可通过SQL查询获取
是权限审计的基础

1.2 PostgreSQL数据库系统目录

PostgreSQL数据库的权限元数据主要存储在以下系统目录中：

pg_roles：存储角色信息
pg_authid：存储角色认证信息（包含密码）
pg_namespace：存储模式信息
pg_class：存储表、视图等对象信息
pg_attribute：存储列信息
pg_default_acl：存储默认权限
pg_shdepend：存储对象依赖关系

1.3 PostgreSQL数据库权限类型

PostgreSQL数据库支持多种权限类型：

# 对象权限
– SELECT：查询权限
– INSERT：插入权限
– UPDATE：更新权限
– DELETE：删除权限
– TRUNCATE：截断权限
– REFERENCES：引用权限
– TRIGGER：触发器权限
– CREATE：创建权限
– CONNECT：连接权限
– TEMPORARY：临时表权限
– EXECUTE：执行权限
– USAGE：使用权限

# 角色属性权限
– SUPERUSER：超级用户权限
– CREATEDB：创建数据库权限
– CREATEROLE：创建角色权限
– REPLICATION：复制权限
– BYPASSRLS：绕过行级安全策略权限

Part02-生产环境规划与建议

2.1 PostgreSQL数据库权限审计规划

生产环境中，权限审计是确保数据库安全的重要环节。建议：

# 权限审计规划要点
– 定期执行权限审计（如每月）
– 建立权限基线
– 监控权限变更
– 审计高权限角色
– 定期清理不必要的权限
– 文档化权限分配

# 审计频率
– 常规审计：每月一次
– 重大变更后：立即审计
– 安全事件后：全面审计

2.2 PostgreSQL数据库元数据查询策略

元数据查询策略建议：

定期查询：建立定期查询机制
自动化：编写脚本自动生成审计报告
重点关注：高权限角色和敏感对象
历史对比：保存历史数据进行对比

2.3 PostgreSQL数据库安全合规要求

安全合规要求：

风哥教程针对风哥教程针对风哥教程针对生产环境建议：建立权限审计机制，定期检查权限分配情况，确保权限最小化原则，及时清理不必要的权限。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 PostgreSQL数据库角色权限查询

3.1.1 PostgreSQL数据库查询所有角色

$ psql -U pgsql -d fgedudb

# 查询所有角色
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication
FROM pg_roles
ORDER BY rolname;

— 执行结果
rolname | rolsuper | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication
——————+———-+—————+————-+————-+—————-
fgedu | f | f | f | t | f
pgsql | t | t | t | t | t

3.1.2 PostgreSQL数据库查询角色权限详情

# 查询角色权限详情
SELECT
r.rolname,
CASE
WHEN r.rolsuper THEN ‘超级用户’
ELSE ‘普通用户’
END as 角色类型,
CASE
WHEN r.rolcreaterole THEN ‘是’
ELSE ‘否’
END as 可创建角色,
CASE
WHEN r.rolcreatedb THEN ‘是’
ELSE ‘否’
END as 可创建数据库,
CASE
WHEN r.rolcanlogin THEN ‘是’
ELSE ‘否’
END as 可登录,
CASE
WHEN r.rolreplication THEN ‘是’
ELSE ‘否’
END as 复制权限
FROM pg_roles r
ORDER BY r.rolname;

— 执行结果
rolname | 角色类型 | 可创建角色 | 可创建数据库 | 可登录 | 复制权限
——————+———-+————+————–+——–+———-
fgedu | 普通用户 | 否 | 否 | 是 | 否
pgsql | 超级用户 | 是 | 是 | 是 | 是

3.2 PostgreSQL数据库对象权限查询

3.2.1 PostgreSQL数据库查询表权限

# 查询表权限
SELECT
nspname as 模式名,
relname as 表名,
grantee as 被授权者,
privilege_type as 权限类型,
grantor as 授权者,
is_grantable as 是否可授权
FROM information_schema.role_table_grants
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY nspname, relname, grantee;

3.2.2 PostgreSQL数据库查询模式权限

# 查询模式权限
SELECT
nspname as 模式名,
grantee as 被授权者,
privilege_type as 权限类型,
grantor as 授权者,
is_grantable as 是否可授权
FROM information_schema.role_schema_grants
WHERE schema_name NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY schema_name, grantee;

3.3 PostgreSQL数据库权限继承关系查询

3.3.1 PostgreSQL数据库查询角色继承关系

# 创建角色继承关系示例
CREATE ROLE fgedu_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fgedu_read;

CREATE ROLE fgedu_write;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO fgedu_write;

CREATE ROLE fgedu_admin;
GRANT fgedu_read, fgedu_write TO fgedu_admin;

# 查询角色继承关系
SELECT
r1.rolname as 角色,
r2.rolname as 继承自
FROM pg_auth_members m
JOIN pg_roles r1 ON m.member = r1.oid
JOIN pg_roles r2 ON m.roleid = r2.oid
ORDER BY r1.rolname, r2.rolname;

— 执行结果
角色 | 继承自
———+———-
fgedu_admin | fgedu_read
fgedu_admin | fgedu_write

3.3.2 PostgreSQL数据库查询有效权限

# 查询角色的有效权限
SELECT
r.rolname,
t.schemaname,
t.tablename,
has_table_privilege(r.rolname, t.tableoid, ‘SELECT’) as 有选择权限,
has_table_privilege(r.rolname, t.tableoid, ‘INSERT’) as 有插入权限,
has_table_privilege(r.rolname, t.tableoid, ‘UPDATE’) as 有更新权限,
has_table_privilege(r.rolname, t.tableoid, ‘DELETE’) as 有删除权限
FROM pg_roles r,
pg_tables t
WHERE r.rolcanlogin = true
AND t.schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY r.rolname, t.schemaname, t.tablename;

风哥提示：权限继承是PostgreSQL的重要特性，通过角色继承可以简化权限管理。建议使用角色继承来组织权限结构，避免直接给用户分配权限。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 PostgreSQL数据库权限审计报告示例

#!/bin/bash
# permission_audit.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 生成权限审计报告
DATE=$(date +%Y-%m-%d)
OUTPUT_FILE=”permission_audit_${DATE}.txt”

psql -U pgsql -d fgedudb << EOF> $OUTPUT_FILE
\echo ‘========== PostgreSQL权限审计报告 ==========’
\echo ‘生成时间: ‘ $(date)
\echo ”
\echo ‘1. 角色信息’
\echo ‘——————————————‘
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication
FROM pg_roles
ORDER BY rolname;

\echo ”
\echo ‘2. 表权限’
\echo ‘——————————————‘
SELECT
nspname as 模式名,
relname as 表名,
grantee as 被授权者,
privilege_type as 权限类型,
grantor as 授权者
FROM information_schema.role_table_grants
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY nspname, relname, grantee;

\echo ”
\echo ‘3. 角色继承关系’
\echo ‘——————————————‘
SELECT
r1.rolname as 角色,
r2.rolname as 继承自
FROM pg_auth_members m
JOIN pg_roles r1 ON m.member = r1.oid
JOIN pg_roles r2 ON m.roleid = r2.oid
ORDER BY r1.rolname, r2.rolname;
EOF

echo “权限审计报告已生成: $OUTPUT_FILE”

# 执行脚本
$ chmod +x permission_audit.sh
$ ./permission_audit.sh

# 执行结果
权限审计报告已生成: permission_audit_2026-04-02.txt

# 查看报告内容
$ cat permission_audit_2026-04-02.txt
========== PostgreSQL权限审计报告 ==========
生成时间: 2026年 04月 02日星期三 10:00:00 CST

1. 角色信息
——————————————
rolname | rolsuper | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication
——————+———-+—————+————-+————-+—————-
fgedu | f | f | f | t | f
fgedu_admin | f | f | f | t | f
fgedu_read | f | f | f | f | f
fgedu_write | f | f | f | f | f
pgsql | t | t | t | t | t

4.2 PostgreSQL数据库权限问题检测

常见的权限问题及检测方法：

# 检测具有超级用户权限的角色
SELECT rolname FROM pg_roles WHERE rolsuper = true;

# 检测具有创建数据库权限的角色
SELECT rolname FROM pg_roles WHERE rolcreatedb = true;

# 检测具有创建角色权限的角色
SELECT rolname FROM pg_roles WHERE rolcreaterole = true;

# 检测具有复制权限的角色
SELECT rolname FROM pg_roles WHERE rolreplication = true;

# 检测对所有表有全部权限的角色
SELECT
grantee,
COUNT(*) as 权限数
FROM information_schema.role_table_grants
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
GROUP BY grantee
HAVING COUNT(*) > 10;

— 执行结果
rolname
———-
pgsql (1 row)

rolname
———-
pgsql (1 row)

grantee | 权限数
———+——–
fgedu_admin | 12
(1 row)

4.3 PostgreSQL数据库权限清理与优化

权限清理与优化步骤：

# 1. 识别不必要的权限
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM information_schema.role_table_grants
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
AND grantee NOT IN (‘postgres’, ‘fgedu_admin’);

# 2. 回收不必要的权限
REVOKE DELETE ON fgedu_fgedus FROM fgedu;

# 3. 验证权限回收
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM information_schema.role_table_grants
WHERE grantee = ‘fgedu’
AND table_name = ‘fgedu_fgedus’;

# 4. 优化权限结构
— 使用角色继承替代直接授权
REVOKE ALL PRIVILEGES ON fgedu_fgedus FROM fgedu;
GRANT fgedu_read TO fgedu;

风哥教程针对风哥教程针对风哥教程针对生产环境建议：定期进行权限审计，及时清理不必要的权限，使用角色继承来管理权限，确保权限最小化原则。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PostgreSQL数据库权限元数据管理最佳实践

PostgreSQL数据库权限元数据管理最佳实践：

使用角色继承：创建功能角色，通过继承管理权限
权限最小化：只授予必要的权限
定期审计：建立定期权限审计机制
文档化：记录权限分配情况
监控变更：跟踪权限变更
使用默认权限：为新模式和对象设置默认权限

风哥提示：权限管理是数据库安全的核心，建议建立完善的权限管理体系，定期进行权限审计，确保权限分配符合最小化原则。from PostgreSQL视频:www.itpux.com

5.2 PostgreSQL数据库权限审计工具推荐

PostgreSQL数据库权限审计工具：

pgAudit：PostgreSQL的审计扩展
pg_stat_statements：监控SQL执行情况
自定义脚本：如本文中的权限审计脚本
第三方工具：如PgAdmin的权限管理功能
监控系统：如Prometheus+Grafana

5.3 PostgreSQL数据库权限元数据问题排查

# 常见权限问题排查

## 1. 权限不足问题
– 检查用户权限：SELECT * FROM information_schema.role_table_grants WHERE grantee = ‘fgedu’;
– 检查角色继承：SELECT * FROM pg_auth_members WHERE member = (SELECT oid FROM pg_roles WHERE rolname = ‘fgedu’);
– 检查默认权限：SELECT * FROM pg_default_acl;

## 2. 权限冲突问题
– 检查权限重叠：SELECT grantee, table_name, COUNT(*) FROM information_schema.role_table_grants GROUP BY grantee,
table_name HAVING COUNT(*) > 1;
– 检查权限来源：SELECT * FROM information_schema.role_table_grants WHERE table_name = ‘fgedu_fgedus’;

## 3. 权限元数据损坏
– 检查系统目录：SELECT * FROM pg_roles WHERE rolname = ‘fgedu’;
– 重建权限：重新授予必要的权限

持续改进：权限管理是一个持续的过程，需要定期review和优化。建议建立权限管理的规范和流程，不断改进权限管理水平。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG137-PG权限元数据：查询角色与对象权限关系

Part01-基础概念与理论知识

1.1 PostgreSQL数据库权限元数据概念

1.2 PostgreSQL数据库系统目录

1.3 PostgreSQL数据库权限类型

Part02-生产环境规划与建议

2.1 PostgreSQL数据库权限审计规划

2.2 PostgreSQL数据库元数据查询策略

2.3 PostgreSQL数据库安全合规要求

Part03-生产环境项目实施方案

3.1 PostgreSQL数据库角色权限查询

3.1.1 PostgreSQL数据库查询所有角色

3.1.2 PostgreSQL数据库查询角色权限详情

3.2 PostgreSQL数据库对象权限查询

3.2.1 PostgreSQL数据库查询表权限

3.2.2 PostgreSQL数据库查询模式权限

3.3 PostgreSQL数据库权限继承关系查询

3.3.1 PostgreSQL数据库查询角色继承关系

3.3.2 PostgreSQL数据库查询有效权限

Part04-生产案例与实战讲解

4.1 PostgreSQL数据库权限审计报告示例

4.2 PostgreSQL数据库权限问题检测

4.3 PostgreSQL数据库权限清理与优化

Part05-风哥经验总结与分享

5.1 PostgreSQL数据库权限元数据管理最佳实践

5.2 PostgreSQL数据库权限审计工具推荐

5.3 PostgreSQL数据库权限元数据问题排查

相关推荐

联系我们