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

全面掌握PostgreSQL用户权限：一套系统化的查询方法论

理清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              |                  |
+----------+--------------+---------------+---------------+----------------+------------------+

当然，如果想一览所有用户，直接查询pg_user表会更方便。

2. 数据库级别权限：连接的钥匙

用户能不能连上一个数据库，就看这里了。查询pg_database系统表，可以清楚地看到每个数据库的访问控制列表（ACL）。

SELECT
    datname AS database,
    datacl AS privileges
FROM pg_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY datname;

输出结果中，privileges字段就记录了详细的权限分配情况。

3. 模式级别权限：命名空间的通行证

进入数据库后，用户能在哪些模式（Schema）里活动？答案藏在pg_namespace里。这个查询帮你过滤掉系统模式，专注于业务模式。

SELECT
    nspname AS schema,
    nspacl AS privileges
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema'
ORDER BY nspname;

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;

结果集直观地展示了每个对象上的权限分配，比如{postgres=arwdDxt/postgres, readonly_user=r/postgres}就表示postgres用户拥有所有权限，而readonly_user只有读取（SELECT）权限。

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;

6. 函数与序列权限

除了数据，程序逻辑（函数）和自增序列也是重要的数据库对象。它们的权限分别记录在pg_proc和pg_class（relkind = 'S'）中。

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

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

7. 综合查询与高级分析

面对复杂的权限体系，一些综合查询工具能极大提升效率。

信息模式视图：标准化的视角

使用information_schema.role_table_grants可以以一种更标准化、可读性更好的方式查看授权情况。

SELECT
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.role_table_grants
WHERE grantee = ‘your_username‘
ORDER BY table_schema, table_name;

权限全景报告：使用CTE深度整合

对于需要生成详细审计报告的场景，可以利用公共表表达式（CTE）将不同层次的权限信息整合在一起：

WITH user_privs AS (
    -- 整合数据库、模式、表权限的查询...
)
SELECT
    object_type,
    object_name,
    privileges
FROM user_privs
WHERE ...
ORDER BY object_type, object_name;

这种查询能一次性生成涵盖多对象类型的权限报告，非常适合深度审计。

8. 实用工具函数：快速检查

PostgreSQL 提供了一系列has_*_privilege()函数，用于快速检查某一项特定权限，这在编写自动化脚本或进行实时验证时非常方便。

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

关键技巧速查

• 解码ACL字符串：看到arwdDxt这类缩写别发懵，它们其实是权限的简写：
  • 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
• 随时验证：记住has_database_privilege(), has_schema_privilege(), has_table_privilege()这三个函数，它们是权限验证的“瑞士军刀”。

说到底，管理PostgreSQL权限就是一个从宏观到微观、逐层深入的过程。掌握这套查询组合拳，无论是日常运维、安全审计还是排查访问问题，你都能做到心中有数，手到擒来。