PostgreSQL新用户权限机制及权限查询方法咨询

1. 为何创建用户并分配Schema后即可执行大量操作？ #

PostgreSQL的权限体系和Oracle存在核心差异，原因如下：

• 登录权限默认赋予：PostgreSQL中CREATE USER等价于CREATE ROLE ... WITH LOGIN，会自动为用户授予LOGIN权限（对应Oracle的CREATE SESSION），因此创建后无需额外授权即可登录数据库。
• Schema所有者的天然权限：执行ALTER SCHEMA test_user OWNER TO test_user后，该用户成为对应Schema的所有者。PostgreSQL中Schema所有者默认拥有该Schema内的全部权限——包括创建表、视图、函数等对象，以及对这些对象的读写、修改、删除权限。
• PUBLIC角色的默认权限：所有PostgreSQL用户默认属于PUBLIC角色，该角色默认拥有public Schema的USAGE和CREATE权限（允许在public下创建对象），同时默认允许普通用户创建以自己用户名命名的Schema，无需额外授权。这也是用户能执行更多操作的原因之一。

2. 如何查看用户的权限范围？ #

可以通过psql内置命令或查询系统表两种方式查看：

查看用户的基础角色属性 #

• 使用psql命令：

  \du test_user
  

  会展示用户是否拥有超级用户、创建角色、创建数据库、登录等核心权限。
• 查询系统表pg_roles：

  SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit
  FROM pg_roles
  WHERE rolname = 'test_user';
  

查看用户在Schema上的权限 #

• 使用psql命令：

  \dn+ test_user
  

  会展示Schema的所有者、权限列表等信息。
• 查询系统表pg_namespace：

  SELECT 
    n.nspname AS schema_name,
    pg_get_userbyid(n.nspowner) AS owner,
    array_to_string(n.nspacl, ', ') AS schema_privileges
  FROM pg_namespace n
  WHERE n.nspname = 'test_user';
  

查看用户在Schema内对象的权限 #

• 使用psql命令（查看test_user Schema下所有对象权限）：

  \dp test_user.*
  

• 查询系统表pg_class：

  SELECT 
    c.relname AS object_name,
    CASE c.relkind 
      WHEN 'r' THEN 'TABLE'
      WHEN 'v' THEN 'VIEW'
      WHEN 'f' THEN 'FUNCTION'
      ELSE 'OTHER'
    END AS object_type,
    array_to_string(c.relacl, ', ') AS object_privileges
  FROM pg_class c
  JOIN pg_namespace n ON c.relnamespace = n.oid
  WHERE n.nspname = 'test_user';
  

查看默认权限配置 #

若想了解新创建对象的默认权限分配，可执行：

SELECT * FROM default_privileges;

内容的提问来源于stack exchange，提问作者drk