Re: Defining permissions for tables, schema etc..

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