[GENERAL] Grant column level permissions
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. PostgreSQL is the most secure by default and part of that is making security tools easy to use. 8.4 makes our existing connection and access control more flexible and simpler to understand. Column-Level Permissions DBAs can now grant permissions (SELECT, UPDATE) on specific columns as well as on entire tables. This makes it easier to secure sensitive data in your database. 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. Thanks, Dipti
Re: [GENERAL] 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 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
Re: [GENERAL] Grant column level permissions
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 g...@seespotcode.netwrote:
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
Re: [GENERAL] Grant column level permissions
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 shahdipti1...@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
g...@seespotcode.netwrote:
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
Re: [GENERAL] Grant column level permissions
On Thu, Apr 8, 2010 at 1:41 PM, dipti shah shahdipti1...@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. 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
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 shahdipti1...@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 shahdipti1...@gmail.comwrote:
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
g...@seespotcode.netwrote:
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
Re: [GENERAL] 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 shahdipti1...@gmail.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 shahdipti1...@gmail.comwrote:
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 shahdipti1...@gmail.comwrote:
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 g...@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
Michael Glaesemann
grzm seespotcode net
Re: [GENERAL] Grant column level permissions
On Thu, Apr 8, 2010 at 1:52 PM, dipti shah shahdipti1...@gmail.com 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
