Query grants for a table in postgres

Question

How can I query all GRANTS granted to an object in postgres   For example I have table  mytable    GRANT SELECT  INSERT ON mytable TO user1 GRANT UPDATE ON mytable TO user2    I need somthing which gives me   user1  SELECT  INSERT user2  UPDATE

User · Answer

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.

User · Answer

Adding on to  shruti s answer  To query grants for all tables in a schema for a given user  select a tablename          b usename          HAS TABLE PRIVILEGE usename tablename   select   as select         HAS TABLE PRIVILEGE usename tablename   insert   as insert          HAS TABLE PRIVILEGE usename tablename   update   as update          HAS TABLE PRIVILEGE usename tablename   delete   as delete          HAS TABLE PRIVILEGE usename tablename   references   as references  from pg tables a        pg user b  where schemaname  your schema name         and b usename  your user name   order by tablename

User · Answer

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

User · Answer

Here is a script which generates grant queries for a particular table  It omits owner s privileges   SELECT      format          GRANT  s ON TABLE  I  I TO  I s          string agg tg privilege type               tg table schema        tg table name        tg grantee        CASE         WHEN tg is grantable    YES           THEN   WITH GRANT OPTION           ELSE           END         FROM information schema role table grants tg   JOIN pg tables t ON t schemaname   tg table schema AND t tablename   tg table name   WHERE     tg table schema    myschema  AND     tg table name  mytable  AND     t tableowner  lt  gt  tg grantee   GROUP BY tg table schema  tg table name  tg grantee  tg is grantable

User · Answer

I already found it   SELECT grantee  privilege type  FROM information schema role table grants  WHERE table name  mytable

User · Answer

This query will list all of the tables in all of the databases and schemas  uncomment the line s  in the WHERE clause to filter for specific databases  schemas  or tables   with the privileges shown in order so that it s easy to see if a specific privilege is granted or not   SELECT grantee        table catalog        table schema        table name        string agg privilege type       ORDER BY privilege type  AS privileges FROM information schema role table grants  WHERE grantee     postgres   --  and table catalog    somedatabase     uncomment line to filter database    --  and table schema     someschema       uncomment line to filter schema     --  and table name       sometable        uncomment line to filter table     GROUP BY 1  2  3  4    Sample output   grantee  table catalog    table schema   table name      privileges       -------- ---------------- -------------- --------------- ---------------  PUBLIC   adventure works  pg catalog     pg sequence     SELECT           PUBLIC   adventure works  pg catalog     pg sequences    SELECT           PUBLIC   adventure works  pg catalog     pg settings     SELECT  UPDATE

User · Answer

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

[sql] Query grants for a table in postgres

Examples related to sql

Examples related to postgresql

Examples related to grant