Re: Defining permissions for tables, schema etc..

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(at)postnewspapers(dot)com(dot)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
>>
>
> ... pre 8.4 :-)
>
> 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
>