Re: [GENERAL] Grant column level permissions

[Michał Pawlikowski]

On Thu, Apr 8, 2010 at 1:52 PM, dipti shah  wrote:
> techdb=> select * from techtable;
> ERROR:  permission denied for relation techtable
> techdb=>
>
> ...and it gives permission denied..!

This should work:
SELECT description from techtable;
UPDATE techtable SET description = 'xyz'

This should not work:
select * from techtable;
UPDATE techtable SET other_column = 'xyz'

-- 
Pozdrawiam / Best Regards / Mit freundlichen Grüßen
Michal Pawlikowski
XMPP: misiekb...@gmail.com
FB: http://tinyurl.com/fbmbst
MS: http://myspace.com/mpawlikowski

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Grant column level permissions

[dipti shah]

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  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 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 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 >> > 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.

 

 > 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?
 

 Michael Glaesemann
 grzm seespotcode net




>>>
>>
>

Re: [GENERAL] Grant column level permissions

[dipti shah]

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  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 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 
>> 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.
>>>
>>> 
>>>
>>> > 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?
>>> 
>>>
>>> Michael Glaesemann
>>> grzm seespotcode net
>>>
>>>
>>>
>>>
>>
>

Re: [GENERAL] Grant column level permissions

[Michał Pawlikowski]

On Thu, Apr 8, 2010 at 1:41 PM, dipti shah  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.

test=# CREATE USER user1 ENCRYPTED PASSWORD 'test';
CREATE ROLE
test=# CREATE TABLE t1 (col1 int, col2 int);
CREATE TABLE

test=# REVOKE ALL ON t1 FROM user1 ;
REVOKE

test=# GRANT SELECT (col1) ON t1 TO user1;
GRANT

test=# \c test user1
You are now connected to database "test" as user "user1".

test=> select col1 from t1;
 col1
--
(0 rows)

test=> select col2 from t1;
ERROR:  permission denied for relation t1




-- 
Pozdrawiam / Best Regards / Mit freundlichen Grüßen
Michal Pawlikowski
XMPP: misiekb...@gmail.com
FB: http://tinyurl.com/fbmbst
MS: http://myspace.com/mpawlikowski

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Grant column level permissions

[dipti shah]

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  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 
> 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.
>>
>> 
>>
>> > 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?
>> 
>>
>> Michael Glaesemann
>> grzm seespotcode net
>>
>>
>>
>>
>

Re: [GENERAL] Grant column level permissions

[dipti shah]

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 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.
>
> 
>
> > 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?
> 
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
>

Re: [GENERAL] Grant column level permissions

[Michael Glaesemann]

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.



> 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?


Michael Glaesemann
grzm seespotcode net




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general