On Tue, Oct 25, 2011 at 6:04 PM, Maton, Brett <mat...@ltresources.co.uk>wrote:
> Thanks for the replies. > Actually this was question posed by one of my colleagues, what he really > wants to know is if there is the equivalent of MySQL's > > select * from all_tab_privs_recd where grantee = 'your user' > > Thanks again, > Brett > > > You have that too... select * from information_schema.role_table_grants where grantee='your user'; --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ > On 25 October 2011 13:21, Venkat Balaji <venkat.bal...@verse.in> wrote: > >> My answers are in line in RED - >> >> 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 >> >> http://www.postgresql.org/docs/9.0/static/functions-info.html >> >> Hope this helps ! >> >> Thanks >> VB >> > >