Re: Defining permissions for tables, schema etc.. - Mailing list pgsql-general

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