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

score 144 · Accepted Answer · answered Sep 07 '11 at 15:40

144

I already found it:

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

answered Sep 07 '11 at 15:40

markus

6,258
13
41
68

score 114 · Answer 2 · answered Sep 07 '11 at 15:32

114

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

answered Sep 07 '11 at 15:32

CPJ

1,635
1
9
9

would you run this directly from the sql pane or pg command line? – Daniel L. VanDenBosch Aug 25 '17 at 19:49
4

@DanielL.VanDenBosch: all the meta-commands, like `\z`, are for psql. And psql is the command-line interface to PostgreSQL. – Mike Sherrill 'Cat Recall' Nov 20 '17 at 20:20
\dp tableName does the same thing. – TarHalda Oct 25 '22 at 17:31

score 47 · Answer 3 · edited Sep 01 '20 at 00:10

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.

This is the only answer here that computes permissions obtained from membership in other roles, so it gets my vote. On the other hand, I would say `has_table_privilege(usename, contact(schemaname, '.', tablename), ...)` to avoid ambiguity. — Paul A Jungwirth, Mar 28 '17 at 16:28

score 34 · Answer 4 · answered Jun 10 '14 at 15:39

34

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)

answered Jun 10 '14 at 15:39

Nicolas Payart

1,046
14
28

1

Almost what I want, can I have the exact `GRANT`s like pg_dump outputs? – brauliobo Jun 16 '16 at 10:08

isapir · Answer 5 · 2018-01-16T17:23:05.243

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 |
...

this gives only the rows matching the user who executed it ... not all grants — Ricky Levi, Apr 09 '19 at 11:17

score 5 · Answer 6 · answered Apr 01 '19 at 16:04

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;

This works well, assuming you log in as a user with appropriate permissions. Nitpick: I advise that a cross join should be written explicitly, e.g. `FROM pg_tables AS a CROSS JOIN pg_user AS b` rather than the SQL 92 way of doing it with a comma `from pg_tables a, pg_user b` — Davos, Jul 18 '19 at 01:44

score 2 · Answer 7 · answered Sep 15 '17 at 08:45

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 <> tg.grantee
  GROUP BY tg.table_schema, tg.table_name, tg.grantee, tg.is_grantable;

Query grants for a table in postgres

7 Answers7

Linked