List Permissions

Hi,

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

Or something....

Thanks for any help!

You can get it from psql terminal.

postgres=# \z table-name

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Tue, Oct 25, 2011 at 4:50 PM, Maton, Brett <matonb@ltresources.co.uk>wrote:

Hi,

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

Or something....

Thanks for any help!

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

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

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