Postgres角色权限异常：为何显示无INSERT权限但实际存在？

问题场景 #

我有一张名为resources的表，以及public_anonymous角色。从DBeaver的显示和psql的\z resources输出来看，该角色仅被授予了读取（r）权限：

public_anonymous=r/admin

我多次执行撤销INSERT权限的语句：

REVOKE INSERT ON resources FROM public_anonymous;

但使用pg_catalog.has_table_privilege验证时，结果却显示该角色拥有INSERT权限：

select pg_catalog.has_table_privilege('public_anonymous', 'resources', 'INSERT');
-- 返回结果：t

同时，该表配置了行级安全策略。

附完整的\z resources输出：

Schema |   Name    | Type  |       Access privileges        | Column privileges |                                                                                                Policies                                       
                                                          
--------+-----------+-------+--------------------------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------
 public | resources | table | admin=arwdDxt/admin           +|                   | select_resources (r):                                                                                                                         
                                                         +
        |           |       | public_postgraphile=arwd/admin+|                   |   (u): true                                                                                                                                   
                                                         +
        |           |       | public_anonymous=r/admin      +|                   | update_resources (w):                                                                                                                         
                                                         +
        |           |       | public_admin_user=arwd/admin  +|                   |   (u): (( SELECT resource_authors.user_id                                                                                                     
                                                         +
        |           |       | public_user=ar/admin          +|                   |    FROM resource_authors                                                                                                                      
                                                         +
        |           |       | =a/admin                       |                   |   WHERE ((resource_authors.resource_id = resource_authors.resource_id) AND (resource_authors.user_id = (NULLIF(current_setting('jwt.claims.person_id'::text, true), ''::text))::integer))) IS NOT NULL)+
        |           |       |                                |                   |   to: public_user                                                                                                                             
                                                         +
        |           |       |                                |                   | delete_resources (d):                                                                                                                         
                                                         +
        |           |       |                                |                   |   (u): (( SELECT resource_authors.user_id                                                                                                     
                                                         +
        |           |       |                                |                   |    FROM resource_authors                                                                                                                      
                                                         +
        |           |       |                                |                   |   WHERE ((resource_authors.resource_id = resource_authors.resource_id) AND (resource_authors.user_id = (NULLIF(current_setting('jwt.claims.person_id'::text, true), ''::text))::integer))) IS NOT NULL)+
        |           |       |                                |                   |   to: public_user

原因解析 #

问题出在\z输出里的=a/admin项：

• 这个权限项代表**所有角色（PUBLIC）**被授予了INSERT权限（a是INSERT的缩写）。
• Postgres中，角色会继承PUBLIC的权限，即使单独给public_anonymous设置了r权限，它依然会继承PUBLIC的INSERT权限，所以has_table_privilege返回true。
• 行级安全策略不影响表级权限的判断，has_table_privilege仅检查表级权限配置，和实际能否插入行无关。

解决步骤 #

要彻底移除public_anonymous的INSERT权限，需要先撤销PUBLIC的相关权限：

1. 撤销PUBLIC对resources表的INSERT权限：

REVOKE INSERT ON resources FROM PUBLIC;

2. 重新给需要INSERT权限的角色单独授权（比如public_postgraphile、public_admin_user）：

GRANT INSERT ON resources TO public_postgraphile;
GRANT INSERT ON resources TO public_admin_user;

3. 再次验证权限：

select pg_catalog.has_table_privilege('public_anonymous', 'resources', 'INSERT');

此时应该返回f，表示权限已被移除。

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