Re: Grant column level permissions

Ohh...sorry. It works but I am wondering why pg_namespace doesn't display
any information.

techdb=> select description from techtable;
description
-------------
(0 rows)

techdb=> select number from techtable;
ERROR: permission denied for relation techtable

Thanks a ton.

On Thu, Apr 8, 2010 at 5:22 PM, dipti shah <shahdipti1980(at)gmail(dot)com> wrote:

> I also tried below:
>
> techdb=# revoke all ON techtable from public;
> REVOKE
> techdb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn
> where pc.relnamespace=pn.oid and pn.nspname='techdb' and
> pc.relname='techtable';
> relname | relacl
> -----------+-----------------------------
> techtable | {postgres=arwdDxt/postgres}
> (1 row)
>
>
> techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
> user1;
> GRANT
> techdb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn
> where pc.relnamespace=pn.oid and pn.nspname='techdb' and
> pc.relname='techtable';
> relname | relacl
> -----------+-----------------------------
> techtable | {postgres=arwdDxt/postgres}
> (1 row)
>
> Please note that giving select permission on description column doesn't
> made any difference in permissions set pf pg_namespace,
>
> techdb=# \q
> > psql -h techdbdev1.lon -d techdb -E
> psql (8.4.1)
> Type "help" for help.
>
> techdb=> select * from techtable;
> ERROR: permission denied for relation techtable
> techdb=>
>
> ...and it gives permission denied..!
>
> Please help me to sort this out.
>
> Thanks.
>
> On Thu, Apr 8, 2010 at 5:11 PM, dipti shah <shahdipti1980(at)gmail(dot)com>wrote:
>
>> Okay. I think I got it but it is not working the way it should. I have
>> given select permission on one column but still it is displaying both the
>> columns. Could you please tell me what is wrong.
>>
>>
>> techdb=# GRANT SELECT (description), UPDATE (description) ON techtable TO
>> user1;
>> GRANT
>> sysdb=> select * from techtable;
>> number | description
>> --------+-------------
>> (0 rows)
>>
>> techdb=>
>>
>> Thanks.
>>
>>
>> On Thu, Apr 8, 2010 at 5:02 PM, dipti shah <shahdipti1980(at)gmail(dot)com>wrote:
>>
>>> Yup. I read it and tired couple of ways but couldn't figured out how to
>>> specify column names. It gives me below error message and hence, I asked for
>>> the example.
>>>
>>> GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( *column* [, ...] )
>>>
>>> [,...] | ALL [ PRIVILEGES ] ( *column* [, ...] ) }
>>> ON [ TABLE ] *tablename* [, ...]
>>> TO { [ GROUP ] *rolename* | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>>>
>>>
>>> techdb=# grant select(column['description']) ON techtable TO user1;
>>> ERROR: syntax error at or near "column"
>>> LINE 1: grant select(column['description']) ON techtable TO user1;
>>> ^
>>>
>>> Thanks,
>>> Dipti.
>>>
>>>
>>> On Thu, Apr 8, 2010 at 4:13 PM, Michael Glaesemann <grzm(at)seespotcode(dot)net
>>> > wrote:
>>>
>>>>
>>>> On Apr 8, 2010, at 4:22 , dipti shah wrote:
>>>>
>>>> > Hi, from postgesql features list mentioned at
>>>> > http://www.postgresql.org/about/press/features84.html, I came to know
>>>> that
>>>> > it is possible to grant column level permissions.
>>>>
>>>> <snip/>
>>>>
>>>> > Could anyone please give me the example of how to grant column level
>>>> > permissions? Basically, I want to give permissions to set of
>>>> > users(user-group) to only couple of columns in my table.
>>>>
>>>> Have you reviewed the fine documentation?
>>>> <http://www.postgresql.org/docs/8.4/interactive/sql-grant.html>
>>>>
>>>> Michael Glaesemann
>>>> grzm seespotcode net
>>>>
>>>>
>>>>
>>>>
>>>
>>
>