最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

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)

實用技巧

查看當(dāng)前用戶權(quán)限：

-- 查看當(dāng)前用戶在所有表上的權(quán)限
SELECT * FROM information_schema.table_privileges;
  • 權(quán)限說明：
    • r = SELECT (“read”)
    • w = UPDATE (“write”)
    • a = INSERT (“append”)
    • d = DELETE
    • D = TRUNCATE
    • x = REFERENCES
    • t = TRIGGER
    • X = EXECUTE
    • U = USAGE
    • C = CREATE
    • c = CONNECT
    • T = 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 實現(xiàn)查詢出的數(shù)據(jù)為空,則設(shè)為0的操作

    postgresql 實現(xiàn)查詢出的數(shù)據(jù)為空,則設(shè)為0的操作

    這篇文章主要介紹了postgresql 實現(xiàn)查詢出的數(shù)據(jù)為空,則設(shè)為0的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • pgsql的UUID生成函數(shù)實例

    pgsql的UUID生成函數(shù)實例

    這篇文章主要介紹了pgsql的UUID生成函數(shù)實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • Postgresql鎖機制詳解(表鎖和行鎖)

    Postgresql鎖機制詳解(表鎖和行鎖)

    這篇文章主要介紹了Postgresql鎖機制詳解(表鎖和行鎖)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2020-12-12
  • 詳解PostgreSQL中實現(xiàn)數(shù)據(jù)透視表的三種方法

    詳解PostgreSQL中實現(xiàn)數(shù)據(jù)透視表的三種方法

    數(shù)據(jù)透視表（Pivot Table）是進行數(shù)據(jù)匯總、分析、瀏覽和展示的強大工具,可以幫助我們了解數(shù)據(jù)中的對比情況、模式和趨勢,是數(shù)據(jù)分析師和運營人員必備技能之一,本給大家介紹PostgreSQL中實現(xiàn)數(shù)據(jù)透視表的三種方法,需要的朋友可以參考下
    2024-04-04
  • PostgreSQL 序列(Sequence) 與 Oracle 序列對比差異分析

    PostgreSQL 序列(Sequence) 與 Oracle 序列對比差異分

    PostgreSQL 和 Oracle 都提供了序列(Sequence)功能,但在實現(xiàn)細節(jié)和使用方式上存在一些重要差異,下面給大家介紹PostgreSQL 序列(Sequence) 與 Oracle 序列對比差異分析,感興趣的朋友一起看看吧
    2025-05-05
  • postgreSQL中的內(nèi)連接和外連接實現(xiàn)操作

    postgreSQL中的內(nèi)連接和外連接實現(xiàn)操作

    這篇文章主要介紹了postgreSQL中的內(nèi)連接和外連接實現(xiàn)操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • PostgreSQL簡介及實戰(zhàn)應(yīng)用

    PostgreSQL簡介及實戰(zhàn)應(yīng)用

    PostgreSQL是一種功能強大的開源關(guān)系型數(shù)據(jù)庫管理系統(tǒng),以其穩(wěn)定性、高性能、擴展性和復(fù)雜查詢能力在眾多項目中得到廣泛應(yīng)用,本文將從基礎(chǔ)概念講起,逐步深入到高級特性、性能優(yōu)化和實戰(zhàn)應(yīng)用,幫助讀者全面掌握PostgreSQL,感興趣的朋友跟隨小編一起學(xué)習(xí)吧
    2025-08-08
  • PostgreSQL實現(xiàn)透視表查詢的方法詳解

    PostgreSQL實現(xiàn)透視表查詢的方法詳解

    PostgreSQL 8.3版本發(fā)布時,引入了一個名為tablefunc的新擴展,這個擴展提供了一組非常有趣的函數(shù),其中之一是交叉表函數(shù),用于創(chuàng)建數(shù)據(jù)透視表,這就是我們將在本文中討論的內(nèi)容,本文給大家介紹了PostgreSQL實現(xiàn)透視表查詢的方法,需要的朋友可以參考下
    2024-12-12
  • PostgreSQL優(yōu)雅的進行遞歸查詢的實戰(zhàn)指南

    PostgreSQL優(yōu)雅的進行遞歸查詢的實戰(zhàn)指南

    在實際開發(fā)中,我們經(jīng)常會遇到樹形結(jié)構(gòu)或圖結(jié)構(gòu)的數(shù)據(jù)需求,這些場景的核心問題是：如何高效查詢具有層級/遞歸關(guān)系的數(shù)據(jù)？所以本文將從基礎(chǔ)到實戰(zhàn),手把手教你掌握遞歸查詢的精髓,需要的朋友可以參考下
    2026-01-01
  • PostgreSQL 更新JSON,JSONB字段的操作

    PostgreSQL 更新JSON,JSONB字段的操作

    這篇文章主要介紹了PostgreSQL 更新JSON,JSONB字段的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01

最新評論

牟定县| 郧西县| 九龙县| 于都县| 巨鹿县| 三亚市| 五大连池市| 庆城县| 论坛| 宁波市| 孝昌县| 大港区| 南和县| 新乐市| 四子王旗| 神池县| 鞍山市| 和静县| 云龙县| 南阳市| 成安县| 福泉市| 杭州市| 左贡县| 光泽县| 岢岚县| 滨州市| 疏附县| 安吉县| 綦江县| 梁平县| 双鸭山市| 广南县| 嵊州市| 涟源市| 洮南市| 同德县| 沂南县| 普兰县| 宁波市| 青浦区|