What a awesome response!!! Thanks a ton all of you.
Special Thanks to Craig for absolutely brillient reply. I will test all you said and will get back if I have any questions.
Thanks,
Dipti
On Thu, Dec 10, 2009 at 2:07 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
On 10/12/2009 4:21 PM, John R Pierce wrote:
dipti shah wrote:
Hi,
We have started using PostGreSQL for more than 3 months and it looks
awesome. Currently, we have been suing superuser by default as login
account. Now, the users are getting increased and we want to go away
with using superuser by default. We want to create the separate user
account for every users and want to define the permission for each of
them. For example, we want particular user cannot create schema, he
can create tables only in particular schema, he can updates only few
tables and also updates only few columns etc. In short, we want to
define all available permission options. I am not aware of anything
starting from creating new user account to assigning column level
permissions. Could anyone please help me to start with this. What is
the best way to start?
there are no per column privileges in postgres
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
ON [ TABLE ] tablename [, ...]
TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT SELECT ( fieldname ) ON sometable TO someuser;
... and use \dp tablename to show.
It's made me really rather happy as I've been able to drop several cumbersome triggers in favour of simple column-list grants.
Oh, re my earlier post:
In my example I messed up the last line. You'd want adminUser to INHERIT too, otherwise explicit SET ROLE commands would be needed to do anything useful with it. Sorry about that.
I also managed to make it sound like roles could specify themselves as non-inheritable. It's the role _member_ that controls whether or not privs are inherited, though sometimes an intermediate member may block inheritance (via NOINHERIT of roles it's a member of) for a role that is its self INHERIT. In practice, you'll probably want to use INHERIT almost all the time and won't be too worried by this.
--
Craig Ringer