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
Add a comment
|
7 Answers
I already found it:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='mytable'
Comments
\z mytable from psql gives you all the grants from a table, but you'd then have to split it up by individual user.
3 Comments
Daniel L. VanDenBosch
would you run this directly from the sql pane or pg command line?
Mike Sherrill 'Cat Recall'
@DanielL.VanDenBosch: all the meta-commands, like
\z, are for psql. And psql is the command-line interface to PostgreSQL.
TarHalda
\dp tableName does the same thing.
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.
3 Comments
Paul A Jungwirth
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.
Daniel
Plus One - THIS IS PURE GOLD!
user3186301
Finally someone gave the right 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)
1 Comment
brauliobo
Almost what I want, can I have the exact
GRANTs like pg_dump outputs?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 |
...
1 Comment
Ricky Levi
this gives only the rows matching the user who executed it ... not all grants
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;
1 Comment
Davos
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 bHere 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;
Here the statement if you want to get it for views:
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_views v ON v.schemaname = tg.table_schema AND v.viewname = tg.table_name
WHERE
tg.table_schema = 'myschema' AND
tg.table_name='myview'
AND tg.grantee <> 'myschema'
GROUP BY tg.table_schema, tg.table_name, tg.grantee, tg.is_grantable
