Re: user/grant - best practices handling permission in production system

Hi Greg,
thanks for your suggestions. See below for my comments.

Greg Stark wrote:
> Well there isn't a way to do step 2 in one shot either.
Maybe my explanation was not clear. Step2 means run the DDL/DMLscript to
create and populate all the tables.

> You'll have to
> issue a CREATE statement for each object, it's no extra work to issue
> a GRANT for each object with the specific rights the application
> should have at that time. Think of it as an important part of the
> process of creating a new object.
>
You are right but I don't like so much this approach since I'd want to
provide the DDL/DML script and let the DBA to decide database name and
user name (ie, I don't want to put the username in the DDL/DML script file).

> Note that it's probably not necessary to grant all rights to every
> table. Most applications have some tables that are read-only or
> insert-only from the point of view of the application. Your system
> will be more secure if the application does not have unnecessary
> privileges. So thinking about what rights to grant to the application
> for each object when it's created is not a bad thing.
>
You are right also about that, but maybe it's too much for me. But to
have a real secure DB this should be taken in account.

At the end, these are the steps
1. using postgres user (or another user with grant for creating
database) create the database
2. using the user used in the previous step, create a new user (the one
the webapp will use)
3. give to the new user all permission on the database
4. using the new user, create and populate the tables with the DDL/DML
script.

In this way the user is not the db owner but is the owner of all tables.
Do you think it's acceptable ?

Ste