user/grant - best practices handling permission in production system

From: Stefano Nichele <stefano(dot)nichele(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: user/grant - best practices handling permission in production system
Date: 2009-07-23 21:09:39
Message-ID: 4A68D193.6020800@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All,
I have some questions for you about the best way to handle permission on
a database in a production system.
The final goal is to have a web application connected to the db using a
single user that must run select/delete/insert/update (and maybe truncate)

In my opinion that user should NOT own the db and the db itself should
NOT be created using that user. Of course that user should NOT be able
to create database or other users.

The steps could be:
1. using postgres user (or another user with grant for creating
database) create the database
2. using the user used in step 1, create the schema and populate tables
with initial data
3. using the user used in the previous step, create a new user (the one
the webapp will use)
4. give to the new user the grant on all database objects for
select/delete/insert/update

At this point the webapp should work correctly.
The main missing point for me is how to perform step 4 in a simple way
since it seems there is not a way to give the right grants to all db
objects in one shot.

What do you think about that ? What are the common practices for a
production system ?

thanks a lot
ste

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brent Wood 2009-07-23 21:17:32 Re: commercial adaptation of postgres
Previous Message Jong Chun Park 2009-07-23 19:48:34 Re: A question on PSQL 8.3 setup