Query grants for a table in postgres

The query below will give you a list of all users and their permissions on the table in a schema.

select a.schemaname, a.tablename, b.usename,
  HAS_TABLE_PRIVILEGE(usename, quote_ident(schemaname) || '.' || quote_ident(tablename), 'select') as has_select,
  HAS_TABLE_PRIVILEGE(usename, quote_ident(schemaname) || '.' || quote_ident(tablename), 'insert') as has_insert,
  HAS_TABLE_PRIVILEGE(usename, quote_ident(schemaname) || '.' || quote_ident(tablename), 'update') as has_update,
  HAS_TABLE_PRIVILEGE(usename, quote_ident(schemaname) || '.' || quote_ident(tablename), 'delete') as has_delete, 
  HAS_TABLE_PRIVILEGE(usename, quote_ident(schemaname) || '.' || quote_ident(tablename), 'references') as has_references 
from pg_tables a, pg_user b 
where a.schemaname = 'your_schema_name' and a.tablename='your_table_name';

More details on has_table_privilages can be found here.


\z mytable from psql gives you all the grants from a table, but you'd then have to split it up by individual user.


I already found it:

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='mytable'

If you really want one line per user, you can group by grantee (require PG9+ for string_agg)

SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants 
WHERE table_name='mytable'   
GROUP BY grantee;

This should output something like :

 grantee |   privileges   
---------+----------------
 user1   | INSERT, SELECT
 user2   | UPDATE
(2 rows)