PostgreSQL 中的權限視圖

PostgreSQL 提供了多個系統視圖來查詢權限信息，雖然不像 Oracle 的 DBA_SYS_PRIVS 那樣集中在一個視圖中，但可以通過組合以下視圖獲取完整的系統權限信息。

一 主要權限相關視圖

Oracle 視圖	PostgreSQL 對應視圖	描述
DBA_SYS_PRIVS	pg_roles + pg_auth_members	系統全局權限
ROLE_SYS_PRIVS	pg_roles + pg_auth_members	角色擁有的系統權限
USER_SYS_PRIVS	pg_user + pg_auth_members	用戶擁有的系統權限

二 PostgreSQL 核心權限視圖

2.1 pg_roles / pg_user - 角色/用戶基本信息

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin 
FROM pg_roles;-- 或簡化的用戶視圖
SELECT * FROM pg_user;

2.2 pg_auth_members - 角色成員關系

SELECT roleid::regrole AS role, member::regrole AS member, grantor::regrole AS grantor
FROM pg_auth_members;

2.3 自定義查詢 (類似 DBA_SYS_PRIVS)

SELECT r.rolname AS grantee,CASE WHEN r.rolsuper THEN 'SUPERUSER'WHEN r.rolcreaterole THEN 'CREATE ROLE'WHEN r.rolcreatedb THEN 'CREATE DB'WHEN r.rolcanlogin THEN 'LOGIN'ELSE 'OTHER'END AS privilege,'SYSTEM' AS admin_option
FROM pg_roles r
WHERE r.rolsuper OR r.rolcreaterole OR r.rolcreatedb OR r.rolcanlogin;

三 特定權限檢查查詢

3.1 檢查超級用戶權限

SELECT usename FROM pg_user WHERE usesuper;

3.2 檢查創建數據庫權限

SELECT rolname FROM pg_roles WHERE rolcreatedb;

3.3 檢查創建角色權限

SELECT rolname FROM pg_roles WHERE rolcreaterole;

3.4 檢查登錄權限

SELECT rolname FROM pg_roles WHERE rolcanlogin;

四 對象級權限視圖

PostgreSQL 還提供對象級別的權限視圖，類似于 Oracle 的 DBA_TAB_PRIVS：

4.1 information_schema.role_table_grants

SELECT * FROM information_schema.role_table_grants;

4.2 information_schema.role_column_grants

SELECT * FROM information_schema.role_column_grants;

4.3 pg_namespace - schema 權限

SELECT nspname, nspowner::regrole FROM pg_namespace;

五 權限管理對比表

功能	Oracle	PostgreSQL
超級用戶	DBA_SYS_PRIVS 中的 SYSDBA	pg_roles.rolsuper
創建角色	CREATE ROLE 權限	pg_roles.rolcreaterole
創建數據庫	CREATE DATABASE 權限	pg_roles.rolcreatedb
登錄權限	CREATE SESSION 權限	pg_roles.rolcanlogin
角色授權	GRANT 語句	pg_auth_members 視圖

六 實用權限查詢示例

6.1 綜合權限報告

SELECT r.rolname AS role,CASE WHEN r.rolsuper THEN 'YES' ELSE 'NO' END AS superuser,CASE WHEN r.rolcreaterole THEN 'YES' ELSE 'NO' END AS create_role,CASE WHEN r.rolcreatedb THEN 'YES' ELSE 'NO' END AS create_db,CASE WHEN r.rolcanlogin THEN 'YES' ELSE 'NO' END AS can_login,array(SELECT b.rolname FROM pg_auth_members m JOIN pg_roles b ON m.roleid = b.oid WHERE m.member = r.oid) AS member_of
FROM pg_roles r
ORDER BY 1;

6.2 查找具有特定權限的角色

-- 查找可以創建數據庫的角色
SELECT rolname FROM pg_roles WHERE rolcreatedb;-- 查找可以創建其他角色的角色
SELECT rolname FROM pg_roles WHERE rolcreaterole;

PostgreSQL 的權限系統雖然與 Oracle 有所不同，但通過這些系統視圖可以獲取到同等詳細的信息。