Re: List Permissions - Mailing list pgsql-general

On Tue, Oct 25, 2011 at 6:04 PM, Maton, Brett <matonb@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.balaji@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