PostgreSQL: Check which privileges a user has on a table

Let’s see how to check what a user can do with a certain PostgreSQL table, or which users have a certain permission on a table.

Check which permissions a user has on a table

Find out which permissions the user missy have on the public.newsletter table:

SELECT privilege_type, is_grantable
    FROM information_schema.role_table_grants
    WHERE
        table_schema = quote_ident('public')
        AND table_name = quote_ident('newsletter')
        AND grantee = quote_ident('missy')
;
 privilege_type | is_grantable 
----------------+--------------
 INSERT         | NO
 SELECT         | NO

The quote_ident() function quotes a name and escape special characters where necessary.

privilege_type is the granted privilege, is_grantable indicates whether the user can grant that permission to other users.

Check which users have a permission on a table

Check which users have the SELECT permission on the public.newsletter table:

SELECT grantee, privilege_type, is_grantable
    FROM information_schema.role_table_grants
    WHERE
        table_schema = quote_ident('public')
        AND table_name = quote_ident('newsletter')
        AND privilege_type = 'SELECT'
;
  grantee  | privilege_type | is_grantable 
-----------+----------------+--------------
 postgres  | SELECT         | YES
 missy     | SELECT         | NO
 p_capaldi | SELECT         | NO

Check on which tables a user has a certain privilege

Check on which tables in the public schema the user missy has an INSERT privilege, and who granted that privilege:

SELECT table_name, grantor
    FROM information_schema.role_table_grants
    WHERE
        table_schema = quote_ident('public')
        AND grantee = quote_ident('missy')
        AND privilege_type = 'SELECT'
;
 table_name | grantor  
------------+----------
 newsletter | postgres

Reference

From PostgreSQL documentation:

• Privileges
• role_table_grants