Grant column level permissions

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&gt;

Michael Glaesemann
grzm seespotcode net

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@seespotcode.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&gt;

Michael Glaesemann
grzm seespotcode net

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@gmail.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@seespotcode.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&gt;

Michael Glaesemann
grzm seespotcode net

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@gmail.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@gmail.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@seespotcode.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&gt;

Michael Glaesemann
grzm seespotcode net