Postgres: How Can a Role Both Have INSERT Permissions and Not?-CodePudding

I have a table called resources and a user/role called public_anonymous ... and as far as I can tell, the user doesn't have INSERT permissions on that table. Here's DBeaver showing as much:

And here's the output of \z resources (at the psql command line):

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.per
son_id'::text, true), ''::text))::integer))) IS NOT NULL) 
        |           |       |                                |                   |   to: public_user

The key part of that is:

public_anonymous=r/admin

ie. the role has only been granted read ("r") permissions by the admin role.

Furthermore, I have repeatedly tried to remove INSERT permissions by running:

REVOKE INSERT ON resources FROM public_anonymous;

Nevertheless, when I tried using pgTAP to verify my permissions, I was surprised to find that it reported that the user did have INSERT permissions. When I asked the maintainer why it thought that, he explained that it used the pg_catalog.has_table_privilege function ... and sure enough, when I ran:

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

I saw:

 has_table_privilege 
---------------------
 t

I'm not a Postgres expert, so forgive me if this is a dumb question. Also I should note that I have been using both GRANT statements and row-level security policies on this table ...

... but still, I don't understand why multiple sources all say the user doesn't have INSERT permissions, AND I explicitly REVOKE-ed them ... yet pg_catalog.has_table_privilege still thinks I do have the permission.

Can anyone explain what's going on, and possibly how I might remove this permission (for good)?

CodePudding user response：

This ACL item

=a/admin

means that PUBLIC (that is, everyone) has INSERT privileges on the relation. REVOKE that if you don't want it.