From: | dipti shah <shahdipti1980(at)gmail(dot)com> |
---|---|
To: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | John R Pierce <pierce(at)hogranch(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Defining permissions for tables, schema etc.. |
Date: | 2009-12-10 09:24:43 |
Message-ID: | d5b05a950912100124y62f31e98gd6527df9854b3b0a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Tyler | 2009-12-10 10:21:36 | Re: Excessive (and slow) fsync() within single transaction |
Previous Message | Craig Ringer | 2009-12-10 08:37:51 | Re: Defining permissions for tables, schema etc.. |