PostgreSQL全面查看用戶權(quán)限的方法
更新時間：2025年12月26日 10:19:45 作者：小精靈DBA
PostgreSQL查看用戶權(quán)限的全面方法,包括基本屬性、數(shù)據(jù)庫、模式、表、列、函數(shù)、序列權(quán)限,以及使用工具和內(nèi)置函數(shù)進行綜合查詢,本文介紹PostgreSQL全面查看用戶權(quán)限的方法,感興趣的朋友跟隨小編一起看看吧
在 PostgreSQL 中查看用戶權(quán)限是一個系統(tǒng)化的過程，需要從多個維度進行查詢。
以下是全面查看用戶權(quán)限的方法:
1. 查看用戶基本屬性
首先查看用戶的基本信息和高級權(quán)限：
SELECT
usename AS username,
usesuper AS is_superuser,
usecreatedb AS can_create_db,
userepl AS can_replicate,
usebypassrls AS can_bypass_rls,
valuntil AS password_expires
FROM pg_user
WHERE usename = 'your_username'; -- 替換為要查詢的用戶名+----------+--------------+---------------+---------------+----------------+------------------+ | username | is_superuser | can_create_db | can_replicate | can_bypass_rls | password_expires | +----------+--------------+---------------+---------------+----------------+------------------+ | postgres | t | t | t | t | | +----------+--------------+---------------+---------------+----------------+------------------+
– 或者查看所有用戶
SELECT * FROM pg_user;
+---------------+----------+-------------+----------+---------+--------------+----------+----------+-----------+ | usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig | +---------------+----------+-------------+----------+---------+--------------+----------+----------+-----------+ | postgres | 10 | t | t | t | t | ******** | | | | readonly_user | 24600 | f | f | f | f | ******** | | | +---------------+----------+-------------+----------+---------+--------------+----------+----------+-----------+
2. 查看數(shù)據(jù)庫級別權(quán)限
查看用戶對各個數(shù)據(jù)庫的權(quán)限：
SELECT
datname AS database,
datacl AS privileges
FROM pg_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY datname;
+----------+---------------------------------------------------------------+
| database | privileges |
+----------+---------------------------------------------------------------+
| postgres | |
| test_db | {=Tc/postgres,postgres=CTc/postgres,readonly_user=c/postgres} |
+----------+---------------------------------------------------------------+
3. 查看模式級別權(quán)限
查看用戶在特定數(shù)據(jù)庫中的模式權(quán)限：
SELECT
nspname AS schema,
nspacl AS privileges
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
ORDER BY nspname;
+--------+-------------------------------------------------------------------------------------------------+
| schema | privileges |
+--------+-------------------------------------------------------------------------------------------------+
| public | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner,readonly_user=U/pg_database_owner} |
+--------+-------------------------------------------------------------------------------------------------+
4. 查看表級別權(quán)限
這是最常用的權(quán)限查看，顯示用戶對表的操作權(quán)限：
SELECT
n.nspname AS schema,
c.relname AS table_name,
c.relkind AS type, -- 'r'=table, 'v'=view, 'm'=materialized view
c.relacl AS privileges
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'm')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, c.relname;
+--------+-------------+------+------------------------------------------------------+
| schema | table_name | type | privileges |
+--------+-------------+------+------------------------------------------------------+
| public | author | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | class | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | contacts | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | duty | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | ipdb1 | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | ipdb2 | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | order | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | sample_data | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | student | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | t | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | t1 | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | t_date | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | test | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | testtab01 | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | testtab05 | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| public | testtab08 | r | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
+--------+-------------+------+------------------------------------------------------+
5. 查看列級別權(quán)限
查看用戶對表中特定列的權(quán)限：
SELECT
n.nspname AS schema,
c.relname AS table_name,
a.attname AS column_name,
a.attacl AS privileges
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE a.attnum > 0
AND NOT a.attisdropped
AND a.attacl IS NOT NULL
ORDER BY n.nspname, c.relname, a.attnum;
+------------+-----------------+------------------+---------------+
| schema | table_name | column_name | privileges |
+------------+-----------------+------------------+---------------+
| pg_catalog | pg_subscription | oid | {=r/postgres} |
| pg_catalog | pg_subscription | subdbid | {=r/postgres} |
| pg_catalog | pg_subscription | subskiplsn | {=r/postgres} |
| pg_catalog | pg_subscription | subname | {=r/postgres} |
| pg_catalog | pg_subscription | subowner | {=r/postgres} |
| pg_catalog | pg_subscription | subenabled | {=r/postgres} |
| pg_catalog | pg_subscription | subbinary | {=r/postgres} |
| pg_catalog | pg_subscription | substream | {=r/postgres} |
| pg_catalog | pg_subscription | subtwophasestate | {=r/postgres} |
| pg_catalog | pg_subscription | subdisableonerr | {=r/postgres} |
| pg_catalog | pg_subscription | subslotname | {=r/postgres} |
| pg_catalog | pg_subscription | subsynccommit | {=r/postgres} |
| pg_catalog | pg_subscription | subpublications | {=r/postgres} |
+------------+-----------------+------------------+---------------+
6. 查看函數(shù)權(quán)限
查看用戶對函數(shù)的執(zhí)行權(quán)限：
SELECT
n.nspname AS schema,
p.proname AS function_name,
p.proacl AS privileges
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, p.proname;
+--------+----------------+------------+ | schema | function_name | privileges | +--------+----------------+------------+ | public | inetmultirange | | | public | inetmultirange | | | public | inetmultirange | | | public | inetrange | | | public | inetrange | | +--------+----------------+------------+
7. 查看序列權(quán)限
查看用戶對序列的權(quán)限：
SELECT
n.nspname AS schema,
c.relname AS sequence_name,
c.relacl AS privileges
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'S' -- 序列
ORDER BY n.nspname, c.relname;
+--------+--------------------+--------------------------------------------------+
| schema | sequence_name | privileges |
+--------+--------------------+--------------------------------------------------+
| public | sample_data_id_seq | {postgres=rwU/postgres,readonly_user=U/postgres} |
+--------+--------------------+--------------------------------------------------+
8. 綜合權(quán)限查詢工具
查詢特定用戶在所有對象上的權(quán)限
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'your_username' -- 替換為要查詢的用戶名
ORDER BY table_schema, table_name;
+---------------+--------------------+---------------------------------------+----------------+ | grantee | table_schema | table_name | privilege_type | +---------------+--------------------+---------------------------------------+----------------+ | postgres | information_schema | _pg_foreign_data_wrappers | INSERT | | postgres | information_schema | _pg_foreign_data_wrappers | TRIGGER | | postgres | information_schema | _pg_foreign_data_wrappers | REFERENCES | | postgres | information_schema | _pg_foreign_data_wrappers | TRUNCATE | | postgres | information_schema | _pg_foreign_data_wrappers | DELETE | | postgres | information_schema | _pg_foreign_data_wrappers | UPDATE | | postgres | information_schema | _pg_foreign_data_wrappers | SELECT |
查看用戶成員關(guān)系（角色繼承）
SELECT
rolname AS role_name,
member,
(SELECT rolname FROM pg_roles WHERE oid = m.member) AS member_name,
admin_option
FROM pg_roles r
JOIN pg_auth_members m ON r.oid = m.roleid
WHERE (SELECT rolname FROM pg_roles WHERE oid = m.member) = 'your_username';
+-----------+--------+-------------+--------------+ | role_name | member | member_name | admin_option | +-----------+--------+-------------+--------------+ +-----------+--------+-------------+--------------+
9. 高級權(quán)限分析查詢
生成詳細的權(quán)限報告
WITH user_privs AS (
-- 數(shù)據(jù)庫權(quán)限
SELECT
'DATABASE' AS object_type,
datname AS object_name,
datacl AS privileges,
datacl::text AS privileges_text -- 轉(zhuǎn)換為文本
FROM pg_database
WHERE datname = current_database()
UNION ALL
-- 模式權(quán)限
SELECT
'SCHEMA',
nspname,
nspacl AS privileges,
nspacl::text AS privileges_text -- 轉(zhuǎn)換為文本
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
UNION ALL
-- 表權(quán)限
SELECT
CASE relkind
WHEN 'r' THEN 'TABLE'
WHEN 'v' THEN 'VIEW'
WHEN 'm' THEN 'MATERIALIZED VIEW'
END,
nspname || '.' || relname,
relacl AS privileges,
relacl::text AS privileges_text -- 轉(zhuǎn)換為文本
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind IN ('r', 'v', 'm')
AND nspname NOT IN ('pg_catalog', 'information_schema')
)
SELECT
object_type,
object_name,
privileges
FROM user_privs
WHERE privileges_text LIKE '%postgres%' -- 使用轉(zhuǎn)換后的文本字段進行LIKE匹配
OR privileges IS NULL
ORDER BY object_type, object_name;+-------------+--------------------+---------------------------------------------------------------+
| object_type | object_name | privileges |
+-------------+--------------------+---------------------------------------------------------------+
| DATABASE | test_db | {=Tc/postgres,postgres=CTc/postgres,readonly_user=c/postgres} |
| SCHEMA | information_schema | {postgres=UC/postgres,=U/postgres} |
| TABLE | public.author | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.class | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.contacts | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.duty | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.ipdb1 | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.ipdb2 | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.order | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.sample_data | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.student | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.t | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.t1 | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.t_date | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.test | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.testtab01 | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.testtab05 | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
| TABLE | public.testtab08 | {postgres=arwdDxt/postgres,readonly_user=r/postgres} |
+-------------+--------------------+---------------------------------------------------------------+
10. 使用內(nèi)置函數(shù)查看權(quán)限
PostgreSQL 提供了 has_table_privilege() 等函數(shù)來檢查特定權(quán)限：
-- 檢查用戶對特定表的權(quán)限
SELECT
has_table_privilege('your_username', 'schema_name.table_name', 'SELECT') AS can_select,
has_table_privilege('your_username', 'schema_name.table_name', 'INSERT') AS can_insert,
has_table_privilege('your_username', 'schema_name.table_name', 'UPDATE') AS can_update,
has_table_privilege('your_username', 'schema_name.table_name', 'DELETE') AS can_delete;
SELECT
has_table_privilege('readonly_user', 'public.t1', 'SELECT') AS can_select,
has_table_privilege('readonly_user', 'public.t1', 'INSERT') AS can_insert,
has_table_privilege('readonly_user', 'public.t1', 'UPDATE') AS can_update,
has_table_privilege('readonly_user', 'public.t1', 'DELETE') AS can_delete;
can_select | can_insert | can_update | can_delete
------------+------------+------------+------------
t | f | f | f
(1 row)
實用技巧
查看當前用戶權(quán)限：
-- 查看當前用戶在所有表上的權(quán)限 SELECT * FROM information_schema.table_privileges;
- 權(quán)限說明：
r= SELECT (“read”)w= UPDATE (“write”)a= INSERT (“append”)d= DELETED= TRUNCATEx= REFERENCESt= TRIGGERX= EXECUTEU= USAGEC= CREATEc= CONNECTT= TEMPORARY
- 快速檢查用戶是否有某個權(quán)限：
SELECT has_database_privilege('username', 'databasename', 'connect');
SELECT has_schema_privilege('username', 'schemaname', 'usage');
SELECT has_table_privilege('username', 'tablename', 'select');
SELECT has_database_privilege('readonly_user', 'test_db', 'connect');
has_database_privilege
------------------------
t
(1 row)
test_db=# SELECT has_schema_privilege('readonly_user', 'public', 'usage');
has_schema_privilege
----------------------
t
(1 row)
test_db=# SELECT has_table_privilege('readonly_user', 't1', 'select');
has_table_privilege
---------------------
t
(1 row)通過這些查詢，可以全面了解 PostgreSQL 中用戶的權(quán)限情況，便于進行權(quán)限審計和安全管理工作。
到此這篇關(guān)于PostgreSQL全面查看用戶權(quán)限的方法的文章就介紹到這了,更多相關(guān)PostgreSQL查看用戶權(quán)限內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
PostgreSQL使用pg_locks視圖查看鎖類型、表OID、進程ID及模式,結(jié)合pg_class過濾表,通過pg_stat_activity分析等待時間,檢查阻塞進程與查詢,優(yōu)化查詢和隔離級別解決鎖問題2025-06-06
這篇文章主要介紹了PostgreSQL批量update與oracle差異,本文給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-01-01
這篇文章主要介紹了PostgreSQL 實現(xiàn)子查詢返回多行的案例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Postgresql 實現(xiàn)查詢一個表/所有表的所有列名
這篇文章主要介紹了Postgresql 實現(xiàn)查詢一個表/所有表的所有列名，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
解決PostgreSQL數(shù)據(jù)庫連接報錯:psql:?error:?FATAL:?password?authent
這篇文章主要給大家介紹了關(guān)于如何解決PostgreSQL數(shù)據(jù)庫連接報錯:psql:?error:?FATAL:?password?authentication?failed?for?user?"postgres"的相關(guān)資料,在使用PostgreSQL時,一些關(guān)鍵配置的錯誤可能導(dǎo)致數(shù)據(jù)庫無法正常啟動,需要的朋友可以參考下2024-05-05
這篇文章主要介紹了postgresql 如何關(guān)閉自動提交的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL教程(十三)：數(shù)據(jù)庫管理詳解
這篇文章主要介紹了PostgreSQL教程(十三)：數(shù)據(jù)庫管理詳解,本文講解了概述、創(chuàng)建數(shù)據(jù)庫、修改數(shù)據(jù)庫配置、刪除數(shù)據(jù)庫、表空間,需要的朋友可以參考下2015-05-05
PostgreSQL 是一個功能強大的開源關(guān)系型數(shù)據(jù)庫系統(tǒng),基于 C 語言實現(xiàn),采用 PostgreSQL 許可證,這是一種自由軟件許可證,允許用戶自由使用、修改和分發(fā)源代碼,所以本文將給大家介紹PostgreSQL15.x安裝的詳細教程,需要的朋友可以參考下2024-09-09
這篇文章主要給大家介紹了關(guān)于PostgreSQL中日期/時間函數(shù)的相關(guān)資料,文章通過實例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2022-01-01
這篇文章主要介紹了三種PostgreSQL實時打印查詢的方法：1.通過日志配置記錄所有SQL；2.利用pg_stat_activity監(jiān)控活躍查詢；3.使用pg_stat_statements分析歷史性能,并提醒生產(chǎn)環(huán)境應(yīng)避免全量記錄以減少性能損耗,需要的朋友可以參考下2025-09-09