Query grants for a table in postgres

sql postgresql grant
markus picture markus · Sep 7, 2011 · Viewed 107.7k times · Source

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

Answer

markus picture markus · Sep 7, 2011

I already found it:

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

Related questions

Give all the permissions to a user on a DB

I would like to give an user all the permissions on a database without making it an admin. The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same …

Read More
How to grant all privileges on views to arbitrary user

How do you grant read/select access on all functions and views to an arbitrary user? I use psql --user=postgres -d mydb -f myview.sql to create several functions and views, and then I run: GRANT ALL PRIVILEGES ON …

Read More
Query GRANTS granted to a sequence in postgres

To query GRANTS granted to a table I can use a query like: SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='mytable' (see my old question here: Query grants for a table in postgres) But …

Read More