Menu
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
376
To determine which users have direct grant access to a table we'll use the DBA_TAB_PRIVS view: SELECT * FROM DBA_TAB_PRIVS; You can check the official documentation for more information about the columns returned from this query, but the critical columns are: GRANTEE is the name of the user with granted access.
Another way to do this is to use the information_schema schema and query the table_privileges table as: $ SELECT * FROM information_schema. table_privileges LIMIT 5; The above query will show detailed information about user privileges on databases as well as tables.
grantee sql_identifier. Name of the role that the privilege was granted to. table_catalog sql_identifier. Name of the database that contains the table (always the current database)
I already found it:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='mytable'
\z mytable from psql gives you all the grants from a table, but you'd then have to split it up by individual user.
109
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
