From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | John R Pierce <pierce(at)hogranch(dot)com> |
Cc: | dipti shah <shahdipti1980(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Defining permissions for tables, schema etc.. |
Date: | 2009-12-10 08:37:51 |
Message-ID: | 4B20B35F.6020306@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | dipti shah | 2009-12-10 09:24:43 | Re: Defining permissions for tables, schema etc.. |
Previous Message | Craig Ringer | 2009-12-10 08:22:16 | Re: Defining permissions for tables, schema etc.. |