Thread: Defining permissions for tables, schema etc..

On 12/10/2009 01:00 PM, 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?
>
> Thanks,
> Dipti

http://www.postgresql.org/docs/8.4/interactive/user-manag.html

this will be a good starting point


--Ashish

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, but the rest of what
you're asking for is pretty straight forward.

permissions are managed with the GRANT and REVOKE commands, which can
operate on databases, schemas, tables, sequences, functions, and views.
and probably a few more things I'm not thinking of.

On 10/12/2009 3:30 PM, 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?

(Before you read on, be aware that I do *not* work with PostgreSQL in
security-critical environments, and am only in the process of setting up
proper user rights as part of my own app development. I don't promise my
comments are good or even correct, though I've tried to ensure they are so.)

First: use roles rather than GRANTing priveleges directly to users.

You can think of a PostgreSQL `ROLE' as somewhat like a group in an
ACL-based system. Like in most ACL-based systems, where groups can be
members of other groups, so roles can have other roles. This permits you
to structure and document how you hand out priveleges using roles and
GRANTs of rights to those roles. You then only have to hand one role to
most users, making it a lot easier to maintain and understand what your
users' rights are.

In most cases, you should create one or more non-login ROLEs for
different user privelege levels or special rights and abilities.
Granting rights directly to each user quickly becomes a shrieking
nightmare and should be avoided at all costs.

Roles that represent user privelege levels should inherit from the lower
privelege levels and should be inheritable. Roles with special rights or
abilities should not inherit anything, you'll grant them directly to a
user. They should still be inheritable unless you want to have to
explicitly use `SET ROLE' to gain their effects.

Once you've mapped out your design in terms of roles and priveleges, you
then need to GRANT the roles you've created the appropriate rights on
the database objects.

Now create a test user and GRANT them the lowest-priveleged role. Test
it to make sure they can do what they're meant to and no more. GRANT
them the next most priveleged role and repeat. Etc.

Finally, after you've tested everything and you are confident that your
roles work, GRANT the appropriate role to each user.


For example, this creates a basic user role, two user roles with
enhanced priveleges, and an admin user:


CREATE ROLE basicUser INHERIT;
COMMENT ON ROLE basicUser IS 'User with minimum rights';

CREATE ROLE accountsUser INHERIT IN ROLE basicUser;
COMMENT ON ROLE accountsUser IS 'User who can update customer accounts';

CREATE ROLE salesSupervisor INHERIT IN ROLE basicUser;
COMMENT ON ROLE salesSupervisor IS 'User who can override prices and do
other sales-related special tasks';

CREATE ROLE adminUser IN ROLE accountsUser, salesSupervisor;
COMMENT ON ROLE adminUser IS 'Non-superuser with all rights any other
user may have';



I'd then GRANT rights as appropriate to functions, tables (or just some
columns of tables), etc. In some cases you'll want to use triggers to
test for role membership if you're doing something complicated or
business-logic related. It's also sometimes necessary to use SECURITY
DEFINER functions to enable a user to do something very limited on a
database object that should otherwise be admin-only.

Once the assignment of priveleges is done done you can:


GRANT basicUser TO myusername;
ALTER USER myusername NOSUPERUSER;

... and start testing. Lots. Writing a test suite of SQL scripts and
expected results is probably a good idea.

--
Craig Ringer

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

On Thu, Dec 10, 2009 at 4:24 AM, dipti shah <shahdipti1980@gmail.com> wrote:
> Special Thanks to Craig for absolutely brillient reply. I will test all you
> said and will get back if I have any questions.

agreed!  you should add this commentary to the interactive document
page mentioned above thread.  the manual sure could use with more of
this type of explanation of how to put things together.