PostgreSQL全面查看用戶(hù)權(quán)限的方法
更新時(shí)間：2025年12月26日 10:19:45 作者：小精靈DBA
PostgreSQL查看用戶(hù)權(quán)限的全面方法,包括基本屬性、數(shù)據(jù)庫(kù)、模式、表、列、函數(shù)、序列權(quán)限,以及使用工具和內(nèi)置函數(shù)進(jìn)行綜合查詢(xún),本文介紹PostgreSQL全面查看用戶(hù)權(quán)限的方法,感興趣的朋友跟隨小編一起看看吧
在 PostgreSQL 中查看用戶(hù)權(quán)限是一個(gè)系統(tǒng)化的過(guò)程，需要從多個(gè)維度進(jìn)行查詢(xún)。
以下是全面查看用戶(hù)權(quán)限的方法:
1. 查看用戶(hù)基本屬性
首先查看用戶(hù)的基本信息和高級(jí)權(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'; -- 替換為要查詢(xún)的用戶(hù)名+----------+--------------+---------------+---------------+----------------+------------------+ | username | is_superuser | can_create_db | can_replicate | can_bypass_rls | password_expires | +----------+--------------+---------------+---------------+----------------+------------------+ | postgres | t | t | t | t | | +----------+--------------+---------------+---------------+----------------+------------------+
– 或者查看所有用戶(hù)
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ù)庫(kù)級(jí)別權(quán)限
查看用戶(hù)對(duì)各個(gè)數(shù)據(jù)庫(kù)的權(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. 查看模式級(jí)別權(quán)限
查看用戶(hù)在特定數(shù)據(jù)庫(kù)中的模式權(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. 查看表級(jí)別權(quán)限
這是最常用的權(quán)限查看，顯示用戶(hù)對(duì)表的操作權(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. 查看列級(jí)別權(quán)限
查看用戶(hù)對(duì)表中特定列的權(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)限
查看用戶(hù)對(duì)函數(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)限
查看用戶(hù)對(duì)序列的權(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)限查詢(xún)工具
查詢(xún)特定用戶(hù)在所有對(duì)象上的權(quán)限
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'your_username' -- 替換為要查詢(xún)的用戶(hù)名
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 |
查看用戶(hù)成員關(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. 高級(jí)權(quán)限分析查詢(xún)
生成詳細(xì)的權(quán)限報(bào)告
WITH user_privs AS (
-- 數(shù)據(jù)庫(kù)權(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)換后的文本字段進(jì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ù)來(lái)檢查特定權(quán)限：
-- 檢查用戶(hù)對(duì)特定表的權(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)
實(shí)用技巧
查看當(dāng)前用戶(hù)權(quán)限：
-- 查看當(dāng)前用戶(hù)在所有表上的權(quán)限 SELECT * FROM information_schema.table_privileges;
- 權(quán)限說(shuō)明：
r= SELECT (“read”)w= UPDATE (“write”)a= INSERT (“append”)d= DELETED= TRUNCATEx= REFERENCESt= TRIGGERX= EXECUTEU= USAGEC= CREATEc= CONNECTT= TEMPORARY
- 快速檢查用戶(hù)是否有某個(gè)權(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)通過(guò)這些查詢(xún)，可以全面了解 PostgreSQL 中用戶(hù)的權(quán)限情況，便于進(jìn)行權(quán)限審計(jì)和安全管理工作。
到此這篇關(guān)于PostgreSQL全面查看用戶(hù)權(quán)限的方法的文章就介紹到這了,更多相關(guān)PostgreSQL查看用戶(hù)權(quán)限內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
postgresql 實(shí)現(xiàn)查詢(xún)出的數(shù)據(jù)為空,則設(shè)為0的操作
這篇文章主要介紹了postgresql 實(shí)現(xiàn)查詢(xún)出的數(shù)據(jù)為空,則設(shè)為0的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
這篇文章主要介紹了pgsql的UUID生成函數(shù)實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
這篇文章主要介紹了Postgresql鎖機(jī)制詳解(表鎖和行鎖)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
詳解PostgreSQL中實(shí)現(xiàn)數(shù)據(jù)透視表的三種方法
數(shù)據(jù)透視表（Pivot Table）是進(jìn)行數(shù)據(jù)匯總、分析、瀏覽和展示的強(qiáng)大工具,可以幫助我們了解數(shù)據(jù)中的對(duì)比情況、模式和趨勢(shì),是數(shù)據(jù)分析師和運(yùn)營(yíng)人員必備技能之一,本給大家介紹PostgreSQL中實(shí)現(xiàn)數(shù)據(jù)透視表的三種方法,需要的朋友可以參考下2024-04-04
PostgreSQL 序列(Sequence) 與 Oracle 序列對(duì)比差異分
PostgreSQL 和 Oracle 都提供了序列(Sequence)功能,但在實(shí)現(xiàn)細(xì)節(jié)和使用方式上存在一些重要差異,下面給大家介紹PostgreSQL 序列(Sequence) 與 Oracle 序列對(duì)比差異分析,感興趣的朋友一起看看吧2025-05-05
postgreSQL中的內(nèi)連接和外連接實(shí)現(xiàn)操作
這篇文章主要介紹了postgreSQL中的內(nèi)連接和外連接實(shí)現(xiàn)操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL簡(jiǎn)介及實(shí)戰(zhàn)應(yīng)用
PostgreSQL是一種功能強(qiáng)大的開(kāi)源關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng),以其穩(wěn)定性、高性能、擴(kuò)展性和復(fù)雜查詢(xún)能力在眾多項(xiàng)目中得到廣泛應(yīng)用,本文將從基礎(chǔ)概念講起,逐步深入到高級(jí)特性、性能優(yōu)化和實(shí)戰(zhàn)應(yīng)用,幫助讀者全面掌握PostgreSQL,感興趣的朋友跟隨小編一起學(xué)習(xí)吧2025-08-08
PostgreSQL實(shí)現(xiàn)透視表查詢(xún)的方法詳解
PostgreSQL 8.3版本發(fā)布時(shí),引入了一個(gè)名為tablefunc的新擴(kuò)展,這個(gè)擴(kuò)展提供了一組非常有趣的函數(shù),其中之一是交叉表函數(shù),用于創(chuàng)建數(shù)據(jù)透視表,這就是我們將在本文中討論的內(nèi)容,本文給大家介紹了PostgreSQL實(shí)現(xiàn)透視表查詢(xún)的方法,需要的朋友可以參考下2024-12-12
PostgreSQL優(yōu)雅的進(jìn)行遞歸查詢(xún)的實(shí)戰(zhàn)指南
在實(shí)際開(kāi)發(fā)中,我們經(jīng)常會(huì)遇到樹(shù)形結(jié)構(gòu)或圖結(jié)構(gòu)的數(shù)據(jù)需求,這些場(chǎng)景的核心問(wèn)題是：如何高效查詢(xún)具有層級(jí)/遞歸關(guān)系的數(shù)據(jù)？所以本文將從基礎(chǔ)到實(shí)戰(zhàn),手把手教你掌握遞歸查詢(xún)的精髓,需要的朋友可以參考下2026-01-01
這篇文章主要介紹了PostgreSQL 更新JSON,JSONB字段的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01