Re: List Permissions - Mailing list pgsql-general

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