Lists: | pgsql-general |
---|
From: | "Maton, Brett" <matonb(at)ltresources(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | List Permissions |
Date: | 2011-10-25 11:20:46 |
Message-ID: | CAAn8SBFyqQCQ3u+C_VRsbBRRB64ANx=7WYe5P4YdGgHx-+rXDQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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!
From: | Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com> |
---|---|
To: | "Maton, Brett" <matonb(at)ltresources(dot)co(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: List Permissions |
Date: | 2011-10-25 11:51:48 |
Message-ID: | CA+h6AhguBrjULBBpWc5=NzDMV3D49JkEYAVCHE7JhFfimg0a8w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)ltresources(dot)co(dot)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!
>
From: | Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com> |
---|---|
To: | "Maton, Brett" <matonb(at)ltresources(dot)co(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: List Permissions |
Date: | 2011-10-25 11:56:25 |
Message-ID: | CA+h6Ahg=zuqScpmm19vcMybt-1h+dtVtKqJWBa7FokUc=a2_tQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)rao(at)enterprisedb(dot)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 <matonb(at)ltresources(dot)co(dot)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!
>>
>
>
From: | Venkat Balaji <venkat(dot)balaji(at)verse(dot)in> |
---|---|
To: | "Maton, Brett" <matonb(at)ltresources(dot)co(dot)uk> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: List Permissions |
Date: | 2011-10-25 12:21:09 |
Message-ID: | CAFrxt0geOUjJcgT7EaCjBVYGEJC-GjW+YVCQUgXqWUk6K8E==Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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
From: | "Maton, Brett" <matonb(at)ltresources(dot)co(dot)uk> |
---|---|
To: | Venkat Balaji <venkat(dot)balaji(at)verse(dot)in> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: List Permissions |
Date: | 2011-10-25 12:34:20 |
Message-ID: | CAAn8SBE4kWy9vENvejwy1UWMxnieAVKbNWWPSBZt1p_BhF9UaA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)balaji(at)verse(dot)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
>
From: | Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com> |
---|---|
To: | "Maton, Brett" <matonb(at)ltresources(dot)co(dot)uk> |
Cc: | Venkat Balaji <venkat(dot)balaji(at)verse(dot)in>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: List Permissions |
Date: | 2011-10-25 12:47:57 |
Message-ID: | CA+h6AhgjkZrXa6x2Yxs+iwtD6A=3HWq=gaVnHk=KnbsEnHk=ZQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Tue, Oct 25, 2011 at 6:04 PM, Maton, Brett <matonb(at)ltresources(dot)co(dot)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(dot)balaji(at)verse(dot)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
>>
>
>
From: | "Maton, Brett" <matonb(at)ltresources(dot)co(dot)uk> |
---|---|
To: | Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com> |
Cc: | Venkat Balaji <venkat(dot)balaji(at)verse(dot)in>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: List Permissions |
Date: | 2011-10-25 13:07:00 |
Message-ID: | CAAn8SBG01Ux0Ncic1=eimCQLJ7jwjMgXXdJQxUfbYAe=x3=yWw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Bingo!
Thanks very much
On 25 October 2011 13:47, Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com>wrote:
> On Tue, Oct 25, 2011 at 6:04 PM, Maton, Brett <matonb(at)ltresources(dot)co(dot)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(dot)balaji(at)verse(dot)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
>>>
>>
>>
>