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

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

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

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

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

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

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

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

[sql] Query grants for a table in postgres

Examples related to sql

Examples related to postgresql

Examples related to grant