[GENERAL] 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!
Re: [GENERAL] List Permissions
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 mat...@ltresources.co.ukwrote: 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!
Re: [GENERAL] List Permissions
Forgot to post the reference manual link. Here you go. http://www.postgresql.org/docs/9.0/static/sql-grant.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Tue, Oct 25, 2011 at 5:21 PM, Raghavendra raghavendra@enterprisedb.com wrote: 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 mat...@ltresources.co.ukwrote: 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!
Re: [GENERAL] List Permissions
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
Re: [GENERAL] List Permissions
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
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
Re: [GENERAL] List Permissions
On Tue, Oct 25, 2011 at 6:04 PM, Maton, Brett mat...@ltresources.co.ukwrote:
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
Re: [GENERAL] List Permissions
Bingo!
Thanks very much
On 25 October 2011 13:47, Raghavendra raghavendra@enterprisedb.comwrote:
On Tue, Oct 25, 2011 at 6:04 PM, Maton, Brett mat...@ltresources.co.ukwrote:
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
