How can I list a users permissions table by table?
i.e. User Joe
has read/write on table1
has read on table2
no access on table 3
For a particular user you can use below function. You can write a SQL query or script which takes table names from "pg_tables" one by one.
has_table_privilege(user, table, privilege)
Example :
I am checking if user "postgres" has "select" privilege on "table1".
postgres=# select has_table_privilege('postgres','public.table1','select');
has_table_privilege
---------------------
t
(1 row)
For current user (user you logged in as) you can use the following function
has_table_privilege(table, privilege)
I am checking if the current_user has "select" privilege on "table1"
Example:
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
postgres=# select has_table_privilege('public.table1','select');
has_table_privilege
---------------------
t
Below link has all the other functions regarding checking permissions
Hope this helps !
Thanks
VB
