PostgreSQL全面查看用户权限的方法

目录

• 以下是全面查看用户权限的方法:
• 1. 查看用户基本属性
• 2. 查看数据库级别权限
• 3. 查看模式级别权限
• 4. 查看表级别权限
• 5. 查看列级别权限
• 6. 查看函数权限
• 7. 查看序列权限
• 8. 综合权限查询工具
• 查询特定用户在所有对象上的权限
• 查看用户成员关系（角色继承）
• 9. 高级权限分析查询
• 生成详细的权限报告
• 10. 使用内置函数查看权限
• 实用技巧

在 PostgreSQL 中查看用户权限是一个系统化的过程，需要从多个维度进行查询。

以下是全面查看用户权限的方法:

1. 查看用户基本属性

首先查看用户的基本信息和高级权限：

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              |                  |
+----------+--------------+---------------+---------------+----------------+--php----------------+

– 或者查看所有用户

SELECT * FROM pg_user;

+---------------+----------+-------------+----------+---------+--------------+----------+----------+-----------+
|    usename    | usesysid | usecreatedb | usesuper | userepl | usepythonbypassrls |  passwd  | valuntil | useconfig |
+---------------+----------+-------------+----------+---------+--------------+----------+---------javascript-+-----------+
| postgres      |       10 | t           | t        | t       | t            | ******** |          |           |
| readonly_user |    24600 | f           | f        | f       | f            | ******** |          |           |
+---------------+----------+-------------+----------+---------+-------------javascript-+----------+----------+-----------+

2. 查看数据库级别权限

查看用户对各个数据库的权限：

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. 查看模式级别权限

查看用户在特定数据库中的模式权限：

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. 查看表级别权限

这是最常用的权限查看，显示用户对表的操作权限：

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. 查看列级别权限

查看用户对表中特定列的权限：

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. 查看函数权限

查看用户对函数的执行权限：

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. 查看序列权限

查看用户对php序列的权限：

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. 综合权限查询工具

查询特定用户在所有对象上的权限

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         |

查看用户成员关系（角色继承）

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. 高级权限分析查询

生成详细的权限报告

WITH user_privs AS (
    -- 数据库权限
    SELECT 
        'DATABASE' AS object_type,
        datname AS object_name,
        datacl AS privileges,
        datacl::text AS privileges_text  -- 转换为文本
    FROM pg_database
    WHERE datname = current_database()
    UNION ALL
    -- 模式权限
    SELECT 
        'SCHEMA',
        nspname,
        nspacl AS privileges,
        nspacl::text AS privileges_text  -- 转换为文本
    FROM pg_namespace
    WHERE nspname NOT LIKE 'pg_%' 
    UNION ALL
    -- 表权限
    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  -- 转换为文本
    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%'  -- 使用转换后的文本字段进行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. 使用内置函数查看权限

PostgreSQL 提供了 has_table_privilege() 等函数来检查特定权限：

-- 检查用户对特定表的权限
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)

实用技巧

查看当前用户权限：

-- 查看当前用户在所有表上的权限
SELECT * FROM information_schema.table_privileges;

• 权限说明：
  • 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
• 快速检查用户是否有某个权限：

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 中用户的权限情况，便于进行权限审计和安全管理工作。

到此这篇关于PostgreSQL全面查看用户权限的方法的文章就介绍到这了,更多相关PostgreSQL查看用户权限内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！